PL/SQL開發中動態SQL的使用方法

　　內容摘要在PL/SQL開發過程中使用SQLPL/SQL可以實現大部份的需求但是在某些特殊的情況下在PL/SQL中使用標准的SQL語句或DML語句不能實現自己的需求比如需要動態建表或某個不確定的操作需要動態執行這就需要使用動態SQL來實現本文通過幾個實例來詳細的講解動態SQL的使用
　　
　　本文適宜讀者范圍Oracle初級中級
　　
　　系統環境
　　
　　OSwindows Professional (英文版)
　　
　　Oracle
　　
　　正文
　　
　　一般的PL/SQL程序設計中在DML和事務控制的語句中可以直接使用SQL但是DDL語句及系統控制語句卻不能在PL/SQL中直接使用要想實現在PL/SQL中使用DDL語句及系統控制語句可以通過使用動態SQL來實現
　　
　　首先我們應該了解什麼是動態SQL在Oracle數據庫開發PL/SQL塊中我們使用的SQL分為靜態SQL語句和動態SQL語句所謂靜態SQL指在PL/SQL塊中使用的SQL語句在編譯時是明確的執行的是確定對象而動態SQL是指在PL/SQL塊編譯時SQL語句是不確定的如根據用戶輸入的參數的不同而執行不同的操作編譯程序對動態語句部分不進行處理只是在程序運行時動態地創建語句對語句進行語法分析並執行該語句
　　
　　Oracle中動態SQL可以通過本地動態SQL來執行也可以通過DBMS_SQL包來執行下面就這兩種情況分別進行說明
　　
　　一本地動態SQL
　　
　　本地動態SQL是使用EXECUTE IMMEDIATE語句來實現的
　　
　　本地動態SQL執行DDL語句
　　
　　需求根據用戶輸入的表名及字段名等參數動態建表
　　
　　create or replace procedure proc_test
　　(
　　table_name in varchar　　 表名
　　field in varchar　　　　　字段名
　　datatype in varchar　　　字段類型
　　field in varchar　　　　　字段名
　　datatype in varchar　　　　字段類型
　　) as
　　str_sql varchar();
　　begin
　　str_sql:=create table ||table_name||(||field|| ||datatype||||field|| ||datatype||);
　　execute immediate str_sql;　 動態執行DDL語句
　　exception
　　when others then
　　null;
　　end ;
　　
　　以上是編譯通過的存儲過程代碼下面執行存儲過程動態建表
　　
　　SQL> execute proc_test(dinya_testidnumber() not nullnamevarchar());
　　
　　PL/SQL procedure successfully completed
　　
　　SQL> desc dinya_test;
　　Name Type　　　　　Nullable Default Comments
　　
　　ID　 NUMBER()
　　
　　NAME VARCHAR() Y
　　
　　SQL>
　　
　　到這裡就實現了我們的需求使用本地動態SQL根據用戶輸入的表名及字段名字段類型等參數來實現動態執行DDL語句
　　
　　本地動態SQL執行DML語句
　　
　　需求將用戶輸入的值插入到上例中建好的dinya_test表中
　　
　　create or replace procedure proc_insert
　　(
　　id in number　　　　　　　　　　　　　　　　 輸入序號
　　name in varchar　　　　　　　　　　　　　　 輸入姓名
　　) as
　　str_sql varchar();
　　begin
　　str_sql:=insert into dinya_test values(::);
　　execute immediate str_sql using idname; 動態執行插入操作
　　exception
　　when others then
　　null;
　　end ;
　　
　　執行存儲過程插入數據到測試表中
　　
　　SQL> execute proc_insert(dinya);
　　PL/SQL procedure successfully completed
　　SQL> select * from dinya_test;
　　ID　　　NAME
　　　　　dinya
　　
　　在上例中本地動態SQL執行DML語句時使用了using子句按順序將輸入的值綁定到變量如果需要輸出參數可以在執行動態SQL的時候使用RETURNING INTO 子句如
　　
　　declare
　　p_id number:=;
　　v_count number;
　　begin
　　v_string:=select count(*) from table_name a where aid=:id;
　　execute immediate v_string into v_count using p_id;
　　end ;
　　
　　更多的關於動態SQL中關於返回值及為輸出輸入綁定變量執行參數模式的問題請讀者自行做測試
　　
　　二使用DBMS_SQL包
　　
　　使用DBMS_SQL包實現動態SQL的步驟如下A先將要執行的SQL語句或一個語句塊放到一個字符串變量中B使用DBMS_SQL包的parse過程來分析該字符串C使用DBMS_SQL包的bind_variable過程來綁定變量D使用DBMS_SQL包的execute函數來執行語句
　　
　　使用DBMS_SQL包執行DDL語句
　　
　　需求使用DBMS_SQL包根據用戶輸入的表名字段名及字段類型建表
　　
　　create or replace procedure proc_dbms_sql
　　(
　　table_name in varchar　　　 表名
　　field_name in varchar　　　字段名
　　datatype in varchar　　　　字段類型
　　field_name in varchar　　　字段名
　　datatype in varchar　　　　 字段類型
　　)as
　　v_cursor number;　　　　　　　定義光標
　　v_string varchar();　　　定義字符串變量
　　v_row number;　　　　　　　　　行數
　　begin
　　v_cursor:=dbms_sqlopen_cursor;　　 為處理打開光標
　　v_string:=create table ||table_name||(||field_name|| ||datatype||||field_name|| ||datatype||);
　　dbms_sqlparse(v_cursorv_stringdbms_sqlnative);　　分析語句
　　v_row:=dbms_sqlexecute(v_cursor);　 執行語句
　　dbms_sqlclose_cursor(v_cursor);　　 關閉光標
　　exception
　　when others then
　　dbms_sqlclose_cursor(v_cursor);　關閉光標
　　raise;
　　end;
　　
　　以上過程編譯通過後執行過程創建表結構
　　
　　SQL> execute proc_dbms_sql(dinya_testidnumber() not nullnamevarchar());
　　
　　PL/SQL procedure successfully completed
　　
　　SQL> desc dinya_test;
　　Name Type　　　　　Nullable Default Comments
　　
　　ID　 NUMBER()
　　NAME VARCHAR() Y
　　
　　SQL>
　　
　　使用DBMS_SQL包執行DML語句
　　
　　需求使用DBMS_SQL包根據用戶輸入的值更新表中相對應的記錄
　　
　　查看表中已有記錄
　　
　　SQL> select * from dinya_test;
　　ID NAME
　　 Oracle
　　 CSDN
　　 ERP
　　SQL>
　　
　　建存儲過程並編譯通過
　　
　　create or replace procedure proc_dbms_sql_update
　　(
　　id number
　　name varchar
　　)as
　　v_cursor number;　　　　　　定義光標
　　v_string varchar();　 字符串變量
　　v_row number;　　　　　　　 行數
　　begin
　　v_cursor:=dbms_sqlopen_cursor;　　為處理打開光標
　　v_string:=update dinya_test a set aname=:p_name where aid=:p_id;
　　dbms_sqlparse(v_cursorv_stringdbms_sqlnative);　 分析語句
　　dbms_sqlbind_variable(v_cursor:p_namename);　　 綁定變量
　　dbms_sqlbind_variable(v_cursor:p_idid);　　　　　綁定變量
　　v_row:=dbms_sqlexecute(v_cursor);　　　　　　　　　　　執行動態ＳＱＬ
　　dbms_sqlclose_cursor(v_cursor);　　　　　　　　　　　　關閉光標
　　exception
　　when others then
　　dbms_sqlclose_cursor(v_cursor);　　　　　　　　關閉光標
　　raise;
　　end;
　　
　　執行過程根據用戶輸入的參數更新表中的數據
　　
　　SQL> execute proc_dbms_sql_update(csdn_dinya);
　　
　　PL/SQL procedure successfully completed
　　
　　SQL> select * from dinya_test;
　　ID NAME
　　 Oracle
　　 csdn_dinya
　　 ERP
　　SQL>
　　
　　執行過程後將第二條的name字段的數據更新為新值csdn_dinya這樣就完成了使用dbms_sql包來執行DML語句的功能
　　
　　使用DBMS_SQL中如果要執行的動態語句不是查詢語句使用DBMS_SQLExecute或DBMS_SQLVariable_Value來執行如果要執行動態語句是查詢語句則要使用DBMS_SQLdefine_column定義輸出變量然後使用DBMS_SQLExecute DBMS_SQLFetch_Rows DBMS_SQLColumn_Value及DBMS_SQLVariable_Value來執行查詢並得到結果
　　
　　總結說明
　　
　　在Oracle開發過程中我們可以使用動態SQL來執行DDL語句DML語句事務控制語句及系統控制語句但是需要注意的是PL/SQL塊中使用動態SQL執行DDL語句的時候與別的不同在DDL中使用綁定變量是非法的（bind_variable(v_cursor:p_namename)）分析後不需要執行DBMS_SQLBind_Variable直接將輸入的變量加到字符串中即可另外DDL是在調用DBMS_SQL
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22089.html