動態SQL二(RETURN、配合PROCEDURE) (PL/SQL 二十五)

※RETURN／RETURNING

兩個都一樣，看要用哪個都可以，回傳用的

DECLARE
    dynamic_sql VARCHAR2(200);
    xxx_eno EMP.EMPNO%TYPE := 7499;
    xxx_sal EMP.SAL%TYPE;
    xxx_job EMP.JOB%TYPE;
BEGIN
    dynamic_sql := '
        UPDATE EMP SET SAL = SAL * 1.2, JOB = ''管理''
        WHERE EMPNO = :p1 RETURN SAL, JOB INTO :p2, :p3
    ';
    -- DELETE FROM EMP WHERE EMPNO = :p1 RETURN SAL, JOB INTO :p2, :p3
    
    EXECUTE IMMEDIATE dynamic_sql USING xxx_eno RETURN INTO xxx_sal, xxx_job;
    DBMS_OUTPUT.PUT_LINE(xxx_sal || ' ' || xxx_job);
    -- COMMIT;
END;

※配合PROCEDURE

CREATE OR REPLACE PROCEDURE xxx_emp_proc(
    xxx_eno IN OUT EMP.EMPNO%TYPE,
    xxx_ena EMP.ENAME%TYPE,
    xxx_sal EMP.SAL%TYPE
) IS
BEGIN
    SELECT MAX(EMPNO) INTO xxx_eno FROM EMP;
    xxx_eno := xxx_eno + 1000;
    INSERT INTO EMP(EMPNO, ENAME, SAL)VALUES(xxx_eno, xxx_ena, xxx_sal);
    -- COMMIT;
END;

※呼叫端

DECLARE
    dynamic_sql VARCHAR2(200);
    in_out_eno EMP.EMPNO%TYPE;
    in_ena EMP.ENAME%TYPE := 'abc';
    in_sal EMP.SAL%TYPE := 1800;
BEGIN
    dynamic_sql := '
        BEGIN
            xxx_emp_proc(:p1, :p2, :p3);
        END;
    ';
    EXECUTE IMMEDIATE dynamic_sql USING IN OUT in_out_eno, IN in_ena, in_sal;
    DBMS_OUTPUT.PUT_LINE(in_out_eno);
END;

※呼叫時如果是IN，可以省略不寫

※如果xxx_emp_proc的xxx_eno宣告成OUT，那USING就用OUT、IN OUT都可以；
可是如果宣告成IN OUT，那USING就只能用IN OUT，如果使用OUT，就會出「ORA-06537: OUT 連結變數已連結到 IN 位置」的錯