動態SQL四(DBMS.SQL) (PL/SQL 二十七)

官網連結
在Oracle 9i之前(不包含9i)都是用這個，現在都用上一篇的寫法，這裡稍微寫一下
官網的Execution Flow寫了8步，我將我的理解寫下來：
1.OPEN_CURSOR：打開游標
2.PARSE：解析SQL
3.BIND_VARIABLE or BIND_ARRAY：參數挷定
4.DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY：設定游標的欄位
5.EXECUTE：執行游標
6.FETCH_ROWS or EXECUTE_AND_FETCH：取得游標的一條記錄
7.VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG：按第4步的順序取出欄位
8.CLOSE_CURSOR：關閉游標

※查詢

DECLARE
    dynamic_sql VARCHAR2(200);
    save_cur NUMBER;
    xxx_emp_ename EMP.ENAME%TYPE;
    xxx_emp_job EMP.JOB%TYPE;
    xxx_emp_sal EMP.SAL%TYPE;
    xxx_state NUMBER;
    xxx_emp_deptno EMP.DEPTNO%TYPE := 30;
BEGIN
    save_cur := DBMS_SQL.OPEN_CURSOR;
    dynamic_sql := 'SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = :p1';
    DBMS_SQL.PARSE(save_cur, dynamic_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(save_cur, 'p1', xxx_emp_deptno);
    DBMS_SQL.DEFINE_COLUMN(save_cur, 1, xxx_emp_ename, 10);
    DBMS_SQL.DEFINE_COLUMN(save_cur, 2, xxx_emp_job, 9);
    DBMS_SQL.DEFINE_COLUMN(save_cur, 3, xxx_emp_sal);
    xxx_state := DBMS_SQL.EXECUTE(save_cur);
    
    LOOP
        EXIT WHEN DBMS_SQL.FETCH_ROWS(save_cur) = 0;
        DBMS_SQL.COLUMN_VALUE(save_cur, 1, xxx_emp_ename);
        DBMS_SQL.COLUMN_VALUE(save_cur, 2, xxx_emp_job);
        DBMS_SQL.COLUMN_VALUE(save_cur, 3, xxx_emp_sal);
    
        DBMS_OUTPUT.PUT_LINE(xxx_emp_ename);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_job);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_sal || CHR(10));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(save_cur);
END;

※SQL裡的參數一定要「:」開頭，而BIND_VARIABLE第二個參數看要不要「:」都可以

※PARSE的DBMS_SQL.NATIVE是解析用的版本，8i以後(包含)都用這個，官網有寫v6、v7，應該就是舊版本用的

※EXECUTE後用xxx_state接，指的是改變的行數，因為是SELECT，所以一定是0

※修改

DECLARE
    dynamic_sql VARCHAR2(200);
    save_cur NUMBER;
    xxx_emp_empno EMP.EMPNO%TYPE := 7499;
    xxx_emp_comm EMP.COMM%TYPE := 700;
    xxx_state NUMBER;
BEGIN
    save_cur := DBMS_SQL.OPEN_CURSOR;
    dynamic_sql := 'UPDATE EMP SET COMM = :p1 WHERE EMPNO = :p2';
    DBMS_SQL.PARSE(save_cur, dynamic_sql, DBMS_SQL.NATIVE);
    
    DBMS_SQL.BIND_VARIABLE(save_cur, 'p1', xxx_emp_comm);
    DBMS_SQL.BIND_VARIABLE(save_cur, 'p2', xxx_emp_empno);
    xxx_state := DBMS_SQL.EXECUTE(save_cur);
    
    DBMS_OUTPUT.PUT_LINE(xxx_state);
    DBMS_SQL.CLOSE_CURSOR(save_cur);
    -- COMMIT;
END;

※因為是修改，所以第4、7步就省了