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;