[PL/SQL] 善用動態SQL、PL/SQL

自Oracle 8i後，動態SQL有了第二個選擇：native dynamic SQL (NDS)。相對於DBMS_SQL，
他的優點除了較易撰寫，而且也能較快執行，Oracle ERP開的很多Package也都有他的影子。
動態 SQL
語法：
EXECUTE IMMEDIATE Sql_string
[INTO {define_list … record}]

[USING [IN OUT IN OUT] bind_list …];
Sql_string
此為字串運算式，可包括SQL語句或PL/SQL區塊。
define_list
此為變數，接收查詢回來的資料欄位值。
record
此可以是以使用者定義的TYPE或％ROWTYPE為基礎的紀錄，接收查詢回來的資料列。
bind_list
此為運算式，其值是傳給Sql_string。或為識別字，可當作輸出輸入變數使用。
INTO 子句
用於單列查詢上。指定接收查詢回來的資料欄位值。
USING 子句
此能讓你對SQL字串提共繫結引數，此用法通常與PL/SQL有關，預設值為IN。




測試一：
SQL> SELECT DEPTNO, DNAME FROM DEPT;
DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
BEGIN
EXECUTE IMMEDIATE 'UPDATE DEPT SET DNAME = ''TEST'' WHERE DNAME = ''ACCOUNTING''';
END;
/

PL/SQL 程序順利完成.
SQL> SELECT DEPTNO, DNAME FROM DEPT;
DEPTNO DNAME
---------- ----------------------------
10 TEST
20 RESEARCH
30 SALES
40 OPERATIONS

測試二：
SQL> DECLARE
2 V_NAME DEPT.DNAME%TYPE;
3 BEGIN
4 EXECUTE IMMEDIATE 'SELECT DNAME FROM DEPT WHERE DEPTNO= 10'
5 INTO V_NAME;
6 DBMS_OUTPUT.PUT_LINE(‘DNAME IS ’V_NAME);
7 END;
8 /
DNAME IS TEST
PL/SQL 程序順利完成.

測試三：
SQL> DECLARE
2 V_NAME DEPT.DNAME%TYPE;
3 BEGIN
4 EXECUTE IMMEDIATE 'SELECT DNAME FROM DEPT WHERE DEPTNO= :NO'
5 INTO V_NAME
6 USING IN 10;
7 DBMS_OUTPUT.PUT_LINE('DNAME IS 'V_NAME);
8 END;
9 /
DNAME IS TEST
PL/SQL 程序順利完成.




動態 PL/SQL
語法：
OPEN {cursor_variable :host_cursor_variable } FOR SQL_string
[USING bind_argument …];
cursor_variable
此為弱類型CURSOR變數。
:host_cursor_variable
此為CURSOR變數，主要宣告於像是Oracle Call Interface程式的PL/SQL主機環境中。
SQL_string
此主要為需動態執行的SELECT敘述。
USING 子句
此能讓你對SQL字串提共繫結引數，規則與IMMEDIATE相同。


測試：
CREATE OR REPLACE PROCEDURE SP_PMS_SCH_SCHSLIP(IN_STR_DT IN NUMBER,
IN_END_DT IN NUMBER) IS
-- 宣告 REF CURSOR TYPE
TYPE CURSOR_TYPE IS REF CURSOR;

-- 宣告 CUR_PM_SLIP 為 CURSOR_TYPE的CURSOR
CUR_PM_SLIP CURSOR_TYPE;

V_CUR VARCHAR2(3000);
V_SLIP_NO PM_SCHSLIP.SLIP_NO%TYPE;
V_FST_STR_DT PM_SCHSLIP.FST_STR_DT%TYPE;
V_STATUS PM_SCHSLIP.STATUS%TYPE;
V_EST_END_DT PM_SCHSLIP.EST_END_DT%TYPE;
V_SCH_SW PM_SCHSLIP.SCH_SW%TYPE;
V_SCH_ADJ PM_SCHSLIP.SCH_ADJ%TYPE;
V_PRIORITY PM_SCHSLIP.PRIORITY%TYPE;
BEGIN
-- SQL敘述
V_CUR :=
'SELECT A.SLIP_NO, DECODE(SIGN(NVL(A.EST_STR_DT,0)-:1),1,A.EST_STR_DT,:2)
STR_DT, A.STATUS,A.EST_END_DT,A.SCH_SW, A.SCH_ADJ, A.PRIORITY
FROM PM_SLIPM A
WHERE A.GENERATE_CODE = ''Y''
AND A.STATUS NOT IN (''F'',''C'',''H'')
AND NVL(A.EST_STR_DT,0) <= :3
AND NVL(A.SCH_SW,''2'') = ''1'''V_ORDERBY;
-- 開啟CURSOR
OPEN CUR_PM_SLIP
FOR V_CUR USING IN_STR_DT, IN_STR_DT, IN_END_DT; LOOP
-- 擷取資料
FETCH CUR_PM_SLIP INTO V_SLIP_NO, V_FST_STR_DT, V_STATUS,
V_EST_END_DT,V_SCH_SW, V_SCH_ADJ, V_PRIORITY;

EXIT WHEN CUR_PM_SLIP%NOTFOUND;
-- INSERT DATA ...
END LOOP;
COMMIT;
END;
/
其中:1、:2、:3對應IN_STR_DT, IN_STR_DT, IN_END_DT變數