1、第 22 章 動態 SQL范例：利用動態SQL 在執行時創建一張數據表范例：直接在程序中編寫 DDL 或DML范例：編寫 PL/SQL 塊調用函數BEGINDBMS_OUTPUT.put_line(數據表： | get_table_count_fun(mldnjava) ;CREATE OR REPLACE FUNCTION get_table_count_fun(p_table_name VARCHAR2) RETURN NUMBER AS v_sql_s ementVARCHAR2(200) ;- 定義操作的SQL 語句v_countNUMBER ;- 保存表中BEGINSELECT CO

2、UNT(*)O v_count FROM user_tables WHERE table_name=UPP_table_name) ; IF v_count = 0 THEN- 數據表不存在- 錯誤：無法直接使用 DDL 操作CREATE TABLE p_table_name (idNUMBER ,name VARCHAR2(30) NOT NULL , CONSTRAid_pk PRIMARY KEY(id) ;END IF ;- 錯誤：查詢數據表不存在SELECT COUNT(*)O v_count FROM p_table_name ;RETURN v_count ; END ;/CRE

3、ATE OR REPLACE FUNCTION get_table_count_fun(p_table_name VARCHAR2) RETURN NUMBER AS v_sql_s ementVARCHAR2(200) ;- 定義操作的SQL 語句v_countNUMBER ;- 保存表中BEGINSELECT COUNT(*)O v_count FROM user_tables WHERE table_name=UPP_table_name) ;IF v_count = 0 THEN- 數據表不存在v_sql_s ement := CREATE TABLE | p_table_name |

4、 (idNUMBER ,name VARCHAR2(30)NOT NULL ,CONSTRApk_id_ | p_table_name | PRIMARY KEY(id) ;- 創建數據表EXECUTE IMMEDIATE v_sql_s ement ;- 執行動態SQL END IF ;v_sql_s ement := SELECT COUNT(*) FROM | p_table_name ; - 查詢數據表 EXECUTE IMMEDIATE v_sql_s ementO v_count ;- 執行動態SQL 并保存數據 RETURN v_count ;END ;/范例：為c#scott

5、用戶范例：使用動態SQL 創建表和PL/SQL 塊范例：查詢 mldn_tab 數據表范例：使用綁定變量DECLAREv_sql_s ement VARCHAR2(200) ; v_deptno dept.deptno%TYPE := 60 ; v_dname dept.dname%TYPE := MLDN ; v_locdept.loc%TYPE := ;BEGINv_sql_s ement := INSERTO dept(deptno,dname,loc) VALUES (:dno , :dna , :dl) ;SELECT * FROM mldn_tab ;DECLAREv_sql_s

6、ement VARCHAR2(200) ;v_count NUMBER ;- 保存查找結果BEGINSELECT COUNT(*)O v_count FROM user_tables WHERE table_name=MLDN_TAB ;IF v_count = 0 THEN - 數據表不存在v_sql_s ement := CREATE TABLE mldn_tab( idNUMBERPRIMARY KEY ,urlVARCHAR2(50)NOT NULL) ; - 定義動態SQLEXECUTE IMMEDIATE v_sql_s ement ;ELSE- 數據表存在v_sql_s emen

7、t := TRUNCATE TABLE mldn_tab ; EXECUTE IMMEDIATE v_sql_s ement ;END IF ;v_sql_s ement := BEGIN FOR x IN 1 . 10 LOOPINSERTO mldn_tab(id,url) VALUES (x , - | x) ; END LOOP ;END ; ;EXECUTE IMMEDIATE v_sql_s ement ; COMMIT ;- 提交事務END ;/CONN sys/change_on_install AS SYSDBA ; GRANT CREATE ANY TABLE TO c#s

8、cott ;CONN c#scott/tiger ;END ;/范例：利用集合更新多條范例：更新完成后查詢dept 表范例：查詢數據范例：在創建表時使用綁定變量DECLAREv_sql_s ement VARCHAR2(200) ; v_table_name VARCHAR2(200) := mldn ; v_id_column VARCHAR2(200) := id ;BEGINv_sql_s ement := CREATE TABLE :tn (:ci NUMBRIMARY KEY) ; EXECUTE IMMEDIATE v_sql_s ement USING v_table_name,

9、v_id_column ;END ;DECLAREv_sql_s ement VARCHAR2(200) ; v_empno emp.empno%TYPE := 7369 ; v_emprow emp%ROWTYPE ;BEGINv_sql_s ement := SELECT * FROM emp WHERE empno=:eno ;EXECUTE IMMEDIATE v_sql_s ementO v_emprow USING v_empno ;DBMS_OUTPUT.put_line(雇員： | v_emprow.empno | ，| v_emprow.ename | ，職位： | v_em

10、prow.job) ; END ;/SELECT * FROM dept ;DECLAREv_sql_s ement VARCHAR2(200) ;TYPE deptno_nested IS TABLE OF dept.deptno%TYPE NOT NULL ; TYPE dname_nested IS TABLE OF dept.dname%TYPE NOT NULL ; v_deptno deptno_nested := deptno_nested(10,20,30,40) ;v_dname dname_nested := dname_nested(財務部,研發部,銷售部,操作部) ;B

11、EGINv_sql_s ement := UPDATE dept SET dname=:dna WHERE deptno=:dno ; FOR x IN 1 . v_deptno.COUNT LOOPEXECUTE IMMEDIATE v_sql_s ement USING v_dname(x),v_deptno(x) ; END LOOP ;COMMIT ;END ;/EXECUTE IMMEDIATE v_sql_s ement USING v_deptno,v_dname,v_loc ; COMMIT ;END ;/范例：正確的代碼范例：更新數據，取得更新后的結果范例：刪除數據，取得刪除

12、前的結果范例：編寫部門增加過程CREATE OR REPLACE PROCEDURE dept_insert_proc(p_deptno IN OUT dept.deptno%TYPE ,- 此處可以將p_deptno 的內容回傳p_dname dept.dname%TYPE,- 默認為 IN 模式DECLAREv_sql_s ement VARCHAR2(200) ;- 定義SQL 操作語句 v_emprowemp%ROWTYPE ;- 保存emp 類型 v_empnoemp.empno%TYPE := 7369 ;- 刪除的雇員 v_enameemp.ename%TYPE ;- 刪除的雇員

13、v_salemp.sal%TYPE ;- 刪除的雇員工資BEGINv_sql_s ement := DELETE FROM emp WHERE empno=:eno RETURNING ename,salO :name,:sal ; EXECUTE IMMEDIATE v_sql_s ement USING v_empno RETURNINGO v_ename,v_sal ; DBMS_OUTPUT.put_line(刪除的雇員： | v_empno | ，： | v_ename | ，工資： | v_sal) ;END ;/DECLAREv_sql_s ement VARCHAR2(200)

14、 ;- 定義SQL 操作語句 v_empno emp.empno%TYPE := 7369 ; - 要更新的雇員 v_salary emp.sal%TYPE ;- 保存更新后的sal 內容v_job emp.job%TYPE ;- 保存更新后的job 內容BEGINv_sql_s ement := UPDATE emp SET sal=sal*1.2,job=開發 | WHERE empno=:eno RETURNING sal,jobO :salary,:job ;EXECUTE IMMEDIATE v_sql_s ement USING v_empno RETURNINGO v_salar

15、y,v_job ; DBMS_OUTPUT.put_line(調整后的工資： | v_salary | ，新的職位： | v_job) ;END ;/DECLAREv_sql_s ement VARCHAR2(200) ; v_table_name VARCHAR2(200) := mldn ; v_id_column VARCHAR2(200) := id ;BEGINv_sql_sement := CREATE TABLE | v_table_name | ( | v_id_column | NUMBRIMARY KEY) ;EXECUTE IMMEDIATE v_sql_s ement

16、; END ;/范例：編寫 PL/SQL 塊，調用過程范例：更新時使用BULK COLLECT 語句范例：查詢時使用BULK COLLECTDECLARETYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_EGER ; TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_EGER ; TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_EGER ; v_ename ename_index ;v_job job_index ;

17、v_sal sal_index ;v_sql_s ement VARCHAR2(200) ;- 定義動態SQLv_deptno emp.deptno%TYPE := 10 ;- 查詢 10 部門BEGINv_sql_s ement := UPDATE emp SET sal=sal*1.2 WHERE deptno=:dno | RETURNING ename,job,salO :ena, :ej, :es ; - 此時返回多行更新結果EXECUTE IMMEDIATE v_sql_s ement USING v_deptnoRETURNING BULK COLLECTO v_ename,v_

18、job,v_sal ; FOR x IN 1 . v_ename.COUNT LOOPDBMS_OUTPUT.put_line(雇員： | v_ename(x) | ，職位： | v_job(x) | ，工資： | v_sal(x) ;END LOOP ; END ;/DECLAREv_sql_s ement VARCHAR2(200) ; v_deptno dept.deptno%TYPE ;v_dname dept.dname%TYPE := MLDN 教學部 ;v_locdept.loc%TYPE := ; BEGINv_sql_s ement := BEGINdept_insert_p

19、roc(:dno , :dna , :dl) ; END ; ;- 定義PL/SQL 塊EXECUTE IMMEDIATE v_sql_s ement USING IN OUT v_deptno , IN v_dname , v_loc ; DBMS_OUTPUT.put_line(新增部門為： | v_deptno) ;END ;/p_loc dept.loc%TYPE) AS- 默認為 IN 模式BEGINSELECT MAX(deptno)O p_deptno FROM dept ;- 取得最大的deptno 內容p_deptno := p_deptno + 1 ;- 讓最大值部門加 1

20、，此處不考慮超過 2 位數字情況INSERTO dept(deptno,dname,loc) VALUES (p_deptno,p_dname,p_loc) ; END ;/范例：通過 FORALL 設置多個參數范例：在游標中使用動態SQLDECLAREcur_empSYS_REFCURSOR ;- 定義游標變量DECLARETYPE empno_nested IS TABLE OF emp.empno%TYPE ;- 定義嵌套表 TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_EGER ; - 定義索引表 TYPE job_i

21、ndex IS TABLE OF emp.job%TYPE INDEX BY PLS_EGER ;- 定義索引表 TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_EGER ;- 定義索引表 v_ename ename_index ;- 保存刪除后的v_job job_index ;- 保存刪除后的職位v_sal sal_index ;- 保存刪除后的工資v_empno empno_nested := empno_nested(7369,7566,7788) ;- 定義要刪除雇員v_sql_s ement VARCHAR2(200) ;-

22、 動態SQLBEGINv_sql_s ement := DELETE FROM emp WHERE empno=:eno | RETURNING ename,job,salO :ena , :ej , :es ; - 刪除數據SQLFORALL x IN 1 . v_empno.COUNT- FORALL 綁定多個變量EXECUTE IMMEDIATE v_sql_sement USING v_empno(x) RETURNING BULK COLLECTO v_ename,v_job,v_sal ;FOR x IN 1 . v_ename.COUNT LOOPDBMS_OUTPUT.put_

23、line(雇員： | v_ename(x) | ，職位： | v_job(x) | ，工資： | v_sal(x) ; END LOOP ;END ;/DECLARETYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_EGER ; - 保存雇員 TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_EGER ;- 保存雇員職位 TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_EGER ;- 保存雇員工資 v_ename en

24、ame_index ;v_job job_index ; v_sal sal_index ;v_sql_s ement VARCHAR2(200) ;- 定義動態SQLv_deptno emp.deptno%TYPE := 10 ;- 查詢 10 部門BEGINv_sql_s ement := SELECT ename,job,sal FROM emp WHERE deptno=:dno ;- 此時返回多行更新結果EXECUTE IMMEDIATE v_sql_s ementBULK COLLECTO v_ename,v_job,v_salUSING v_deptno ;- 將多個結果一起返回

25、FOR x IN 1 . v_ename.COUNT LOOPDBMS_OUTPUT.put_line(雇員： | v_ename(x) | ，職位： | v_job(x) | ，工資： | v_sal(x) ; END LOOP ;END ;/范例：利用 FETCH 保存查詢結果范例：查看 DBMS_SQL 包定義范例：通過 DBMS_SQL 包查詢數據DECLAREv_sql_s ement VARCHAR2(200) ;v_cidNUMBER ;- 保存游標 ID，以方便關閉 v_ename emp.ename%TYPE ;v_job emp.job%TYPE ; v_sal emp.s

26、al%TYPE ; v_sNUMBER ;v_deptno emp.deptno%TYPE := 10 ;- 部門SELECT *FROM all_sourceWHERE type=PACKAGE AND name=DBMS_SQL ;DECLAREcur_empSYS_REFCURSOR ;- 定義游標變量TYPE emp_index IS TABLE OF emp%ROWTYPE INDEX BY PLS_EGER ;- 定義索引表v_emprow emp_index ;- 定義emp 行類型v_deptno emp.deptno%TYPE := 10 ;- 定義要查詢雇員的部門BEGIN

27、OPEN cur_emp FOR SELECT * FROM emp WHERE deptno=:dno USING v_deptno ; FETCH cur_emp BULK COLLECTO v_emprow ;CLOSE cur_emp ;FOR x IN 1 . v_emprow.COUNT LOOPDBMS_OUTPUT.put_line(雇員： | v_emprow(x).empno | ，： | v_emprow(x).ename | ，職位： | v_emprow(x).job) ;END LOOP ; END ;/v_emprow emp%ROWTYPE ;- 定義emp 行

28、類型v_deptno emp.deptno%TYPE := 10 ;- 定義要查詢雇員的部門BEGINOPEN cur_emp FOR SELECT * FROM emp WHERE deptno=:dno USING v_deptno ;LOOPFETCH cur_empO v_emprow ;- 取得游標數據EXIT WHEN cur_emp%NOTFOUND ;- 如果沒有數據則退出DBMS_OUTPUT.put_line(雇員： | v_emprow.ename | ，雇員職位： | v_emprow.job) ; END LOOP ;CLOSE cur_emp ; END ;/DECLAREv_sql_s ement VARCHAR2(200) ;v_cidNUMBER ;- 保存游標 ID，以方便關閉 mm%TYPE :=500 ;v_empno emp.empno%TYPE := 7369 ; v_sNUMBER ;BEGINv_cid := DBMS_SQL.open_cursor ;- 打開游標 v_sql_s ement := UPDATE emp SET comm=:ec WHERE empno=:eno ; DBMS_SQL.parse(v_cid , v_sql_s ement ,DBMS_SQL.