使用Oracle的動態SQL支援

Oracle動態SQL

• DBMS_SQL的內建包
• Native Dynamic SQL

Native Dynamic SQL的三種常用功能

• 執行不包含綁定變數的動態DDL或DML
• 執行包含綁定變數的動態DDL或DML
• DQL支援，Data Query Language

情境/簡單流程

代碼

DECLARE  TMPSTR VARCHAR2(4000) := NULL;  TMPI   NUMBER(10) := 0;BEGIN  FOR C IN (SELECT B.TABLE_NAME      BTN              FROM USER_CONSTRAINTS A              LEFT JOIN USER_CONSTRAINTS B                ON A.OWNER = B.OWNER               AND B.R_CONSTRAINT_NAME = A.CONSTRAINT_NAME             WHERE A.OWNER = 'NSTCSA224'               AND A.TABLE_NAME = 'CPM_BRANCH') LOOP    IF (C.BTN IS NOT NULL) THEN      TMPSTR := 'BEGIN SELECT COUNT(*) INTO :CC FROM ' || C.BTN || ' ; END;';      EXECUTE IMMEDIATE TMPSTR USING OUT TMPI;      DBMS_OUTPUT.PUT_LINE('SQL語句: ' || TMPSTR);      DBMS_OUTPUT.PUT_LINE('表名稱：' || C.BTN || '   資料：' || TMPI);    END IF;  END LOOP;EXCEPTION  WHEN OTHERS THEN    DBMS_OUTPUT.PUT_LINE(SQLERRM);END;

參考資料

1、Oracle Database  10g PL/SQL