SQL 练习 Using ，Case

 

CREATE OR REPLACE PROCEDURE P_B(P_1 IN VARCHAR2, P_2 OUT NUMBER) IS

  STR_SQL VARCHAR2(500);
  P_LINE  NUMBER(10);
  P_STR   VARCHAR2(200);
  P_STR2  VARCHAR2(200);

BEGIN
  P_LINE := 1;

  STR_SQL := 'INSERT INTO TT_EMAIL T(T.KEYID,T.EMAIL_MAIL,T.EMAIL_USER,T.EMAIL_TEL) VALUES (:A,:B,:C,:D)';

  EXECUTE IMMEDIATE STR_SQL
    USING 'A', 'B', 'C', 'D';

  P_LINE := 2;
  P_2    := 123;

  FOR R IN (SELECT T.EMAIL_MAIL, T.EMAIL_USER, T.EMAIL_TEL FROM TT_EMAIL T) LOOP
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
  
    CASE
      WHEN R.EMAIL_TEL = '13800138000' THEN
        P_STR := '138';
      WHEN R.EMAIL_TEL = '10086' THEN
        P_STR := '000';
      ELSE
        P_STR := '其它';
    END CASE;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(P_STR);
  SELECT COUNT(1)
    INTO P_STR2
    FROM (SELECT CASE
                   WHEN T.EMAIL_MAIL = '1@QQ.COM' THEN
                    '1'
                   WHEN T.EMAIL_MAIL = '2@QQ.COM' THEN
                    '2'
                   ELSE
                    '其它的EMAIL'
                 END AS EMAIL
            FROM TT_EMAIL T) V;
  DBMS_OUTPUT.PUT_LINE(P_STR2);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
  
END P_B;