--什么是動態SQL？動態PL/SQL
--動態SQL是指在運行時刻才構建執行的SQL語句
--動態PL/SQL是指整個PL/SQL代碼塊都是動態構建，然后再編譯執行

--動態SQL來可以用來干什么？

--執行DDL語句

--支持WEB引用的即席查詢和即席更新需求

--軟編碼的業務規則和公式

--先來看dbms_sql包的使用 DECLAREv_cur number;v_sql varchar2(1000);v_result number; BEGINv_cur := dbms_sql.open_cursor;v_sql := 'update ma_users set user_point = :point where user_name = :name';dbms_sql.parse(v_cur, v_sql, dbms_sql.native);dbms_sql.bind_variable(v_cur, ':point', 10000);dbms_sql.bind_variable(v_cur, ':name', '亂世佳人');v_result := dbms_sql.execute(v_cur);dbms_sql.close_cursor(v_cur);END; /

--NDS：原生動態SQL。相較于DBMS_SQL包執行動態SQL要簡單的多

--怎么簡單法呢？

--只要一個語句就夠了
EXECUTE IMMEDIATE --立即執行

--語法
EXECUTE IMMEDIATE SQL_string
[into {defined_varibale[,defined_varibale2]...|record} ]
[using [in|out|in out]] bind_argument
[, [in|out|in out] bind_argument];

--sql_string :包含了SQL語句或者PL/SQL代碼塊的字符串表達式
--defined_varibale：用于接收查詢中某一列值的變量
--record: 用戶自定義類型或者基于%rowtype的記錄，可以接收查詢返回的一行值
--bind_argument:表達式，表達式的值將傳給SQL語句或者PL/SQL塊，也可以是一個標識符
--這個標識符作為PL/SQL塊中調用的函數或者過程的輸入或者輸出變量

--into:這個字句用于單行的查詢，對于查詢結果的每一列的值，必須提供一個單獨的變量或者一個兼容的記錄類型的一個
--字段
--USING子句：利用這個子句給SQL字符串提供綁定參數，同時用于動態SQL和動態PL/SQL
--使用動態PL/SQL時可以指定一個參數模式，缺省模式是IN

DECLAREv_cur number;v_sql varchar2(1000);v_result number; BEGINv_sql := 'update ma_users set user_point = :point where user_name = :name';execute immediate v_sql using 10000, '亂世佳人';END; /--EXECUTE IMMEDIATE 可以用于除多行查詢以外的SQL語句或者PL/SQL塊 ,bulk collect INTO --如果sql_string后面帶分號，則會按照一個PL/SQL塊來處理 否則就是DML或者DDL --字符串可以帶有綁定參數的占位符，但是對象的名字，比如表的名字或者列的名字，不能通過綁定變量傳進去 DECLAREv_cur number;v_sql varchar2(1000);v_result number; BEGINv_sql := 'BEGIN update :TAB_NAME set user_point = :point where user_name = :name; END ;';execute immediate v_sqlusing 'MA_USERS',10000, '亂世佳人';END; /

--想一想為什么對象名字不能通過綁定變量進行傳遞？

--例子, --最簡單的例子，執行建表語句 BEGINEXECUTE IMMEDIATE 'CREATE TABLE USER_ORDER(USER_ID VARCHAR2(32),ORDER_ID VARCHAR2(32))'; END; / --更簡單，可以創建通用的方法，比如 create or replace procedure exec_ddl(ddl_string in varchar2) authid current_user is begin EXECUTE IMMEDIATE ddl_string; end ; / begin exec_ddl('create table user_order_product(order_id varchar2(32),product_id varchar2(32))'); end ; /--傳遞表名，返回數量 create or replace function count_tab(i_table_name in varchar2)return number isv_count number; beginEXECUTE IMMEDIATE 'select count(1) from ' || i_table_nameinto v_count;-- EXECUTE IMMEDIATE 'select count(1) from :table_name'-- into v_count using i_table_name;return v_count; end; /begin if count_tab('ma_users') <10 then dbms_output.put_line('用戶量不超過十個，太遜了');end if; end ; /

--動態更新列，傳進列的名稱，就可以更新相應列的值

create or replace function update_col(i_col in varchar2,i_val in varchar2,i_start_date in date,i_end_date in date) return number isbeginexecute immediate 'update ma_users set ' || i_col ||'=:1 where created_date between :2 and :3'using i_val, i_start_date, i_end_date;return sql%rowcount; end; /

--上述例子使用了綁定參數，對UPDAE語句分析結束后，引擎就會把幾個占位符用USING子句中的值替換

--當一個語句在執行的時候，運行引擎會把SQL語句中的每一個占位符用USING語句中對應的綁定參數替換
--注意不能傳進NULL直接量，必須通過一個數據類型正確但恰好是NULL值的變量傳入

--using語句不能綁定專屬于PL/SQL的數據類型，比如布爾類型，關聯數組以及用戶自定義的記錄類型
--支持所有的SQL數據類型

--現在假設需要設計一個任務調度，可以每隔一段時間跑一次，需要入參可配置，任務也可配置create table ma_schedue_task(task_id varchar2(50),procedure_name varchar2(100)); create table ma_schedue_param(task_id varchar2(50),param_order number,param_value varchar2(100)); declarev_task_id varchar(32); begininsert into ma_schedue_taskvalues(sys_guid(), 'test_bind(:1,:2)')returning task_id into v_task_id;insert into ma_schedue_param values (v_task_id, 1, 'test1');insert into ma_schedue_param values (v_task_id, 2, 'test2');commit; end; / create or replace procedure test_bind(i_test1 in varchar2,i_test2 in varchar2) isbegindbms_output.put_line(i_test1 || i_test2);end test_bind; / declarecursor cur_task isselect * from ma_schedue_task;v_sql varchar2(4000); beginfor v in cur_task loopv_sql := ' begin execute immediate '' begin ' || v.procedure_name ||' ; end;'' using ';for param in (select r.param_valuefrom ma_schedue_param rwhere r.task_id = v.task_idorder by param_order) loopv_sql := v_sql || ' ''' || param.param_value || ''',';end loop;v_sql := rtrim(v_sql, ',') || '; end;';dbms_output.put_line(v_sql);execute immediate v_sql;end loop; end; /

---OPEN FOR 語句
--上節課講到游標變量時，用到這個語句
--這個語句可以用來實現多行的動態查詢

--語法 OPEN {cursor_variable|:host_cursor_variable} for sql_string [using bind_argument[,bind_argument]... ];--cursor_variable； 弱類型的游標變量 -- :host_cursor_variable ：在PL/SQL宿主環境比如OCI程序中聲明的游標變量 -- sql_string: 包含了將要動態執行的SELECT 語句 --Using 字句：跟 execute immediate語句遵守同樣的規則create or replace function show_table(i_table in varchar2,i_where in varchar2)return sys_refcursor isv_cur sys_refcursor; beginopen v_cur for 'select * from ' || i_table || ' where ' || i_where;return v_cur; end show_table; / declarev_cur sys_refcursor;v_user ma_users%rowtype; beginv_cur := show_table('ma_users', ' user_name=''亂世佳人''');loopfetch v_curinto v_user;exit when v_cur%notfound;dbms_output.put_line(v_user.user_name || '積分' || v_user.user_point);end loop;close v_cur; end; /

--一旦使用OPEN FOR 打開一個查詢，接下來獲取數據，關閉游標變量，檢查游標屬性的語法規則和靜態游標變量
--以及硬編碼的顯示游標都是一樣的

--執行一個OPEN FOR 語句是，PL/SQL引擎將會做以下事情

--用一個游標變量關聯查詢字符串中的查詢語句
--對綁定參數值，然后用這些值替換查詢字符串中的占位符
--執行查詢
--識別出結果集
--將游標位置置于結果集的第一行
--把已處理行計數器歸零，這個計數器也就是SQL/rowcount返回的值

--值得注意的是，查詢語句中的任何綁定參數，都是游標變量在打開時才求值的
--也就是說，如果要把不同的綁定參數值用于同一個動態查詢，必須用這些參數再執行一個新的OPEN FOR語句

create or replace procedure show_col(i_table in varchar2,i_col in varchar2,i_where in varchar2 := null)isv_cur sys_refcursor;v_val varchar2(1000); beginopen v_cur for 'select ' || i_col || ' from ' || i_table || ' where ' || nvl(i_where,' 1=1');loopfetch v_curinto v_val;exit when v_cur%notfound;if v_cur%rowcount = 1 thendbms_output.put_line(i_col || ' of ' || i_table);end if;dbms_output.put_line(v_val);end loop;close v_cur; end show_col; / begin show_col('ma_users','user_name'); end ; / --上述例子把數據提取到一個單獨的變量中，還可以把數據提取到一系列變量中 --也可以放到一個記錄中 --可以為不同的需求創建不同類型的記錄類型，這些類型可以放置包中，以便全局可用

--OPEN FOR 中的USING子句
--對于查詢語句只能用IN 模式
--通過綁定參數，可以提升SQL語句性能，而且代碼更容易編寫和維護
--可以顯著減少需要緩存在SGA中的編譯后不同的語句數量

create or replace procedure show_col1(i_table in varchar2,i_col in varchar2,i_dcol in varchar2,i_bg_date in date,i_end_date in date)isv_cur sys_refcursor;v_val varchar2(1000); beginopen v_cur for 'select ' || i_col || ' from ' || i_table || ' where ' || i_dcol || ' between :1 and :2'using i_bg_date, i_end_date;loopfetch v_curinto v_val;exit when v_cur%notfound;if v_cur%rowcount = 1 thendbms_output.put_line(i_col || ' of ' || i_table);end if;dbms_output.put_line(v_val);end loop;close v_cur; end show_col1; /begin show_col1('ma_users','user_name','REGISTER_DATE',date '2016-01-01',sysdate); end ; /

--四種動態SQL方法
-----------------------------------------------------------------------------------------------------------
--類型 　　　　　　　　　　 說明 　　　　　　　　　　 　　　　　　　　　　　　　　　　　　　　　　　　　　 使用NDS語句
--第一種方法 　　　　　　 非查詢；只用于UPATE,INSERT,MERGE,DELETE和DDL語句且不帶有綁定變量 　　　　 不帶USING into子句的
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　-- EXECUTE IMMEDIATE語句
--第二種方法 　　　　　　 非查詢；只用于UPATE,INSERT,MERGE,DELETE且帶有綁定變量個數固定 　　　　　　 帶有USING子句的
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　-- EXECUTE IMMEDIATE語句
--第三種方法 　　　　　　 帶有確定數量的列和綁定變量，返回只有一行數據 　　　　　　　　　　　　　　　　　　 帶有USING into子句的
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　--之單行查詢 EXECUTE IMMEDIATE語句
--第三種方法之 　　　　　帶有固定數量的列和綁定變量，返回多行數據 　　　　　　　　　　　　　　　　　　　　 帶有USING BULK COLLECT INTO 的EXECUTE IMMEDIATE語句
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　--多行查詢 或者動態字符串的OPEN FOR 語句
--第四種方法　　　　　　語句中選定的列的數量或者綁定數量等到允許時刻才能確定 　　　　　　　　　　　　　　　　 使用DBMS_SQL包
--
-------------------------------------------------------------------------------------------------------------

--綁定變量
--使用綁定變量的規則和情況

--SQL語句中可以綁定的是，可以把動態字符串中的占位符替換成數值的直接量（文本，變量，復雜的表達式）
--不能綁定模式元素的名字（表或者列）或者SQL語句的一整塊，比如where 子句
--對于這部分，必須使用拼接的方式

beginexecute immediate 'update :table set :col = :value where 1=1'using 'ma_users', 'user_point', 10000; end; / --為什么會有一個限制呢？ --當給EXECUTE IMMEDIATE 傳入一個字符串時，運行時引擎首先必須要解析這個語句 --解析的目的是保證SQL語句是定義良好的 beginexecute immediate 'update ma_users set user_point = :value where 1=1'using 10000; end; /

--參數的模式：in,out,in out

--執行動態查詢時，所有的綁定參數都必須是IN 模式，除非使用了RETURNING DECLAREV_POINT NUMBER := 1000;V_NAME VARCHAR2(20) := '亂世佳人';V_PHONE varchar2(50); beginexecute immediate 'update ma_users set user_point = :value where USER_NAME= :NAMERETURNING USER_PHONE INTO :PHONE'using V_POINT, V_NAME, OUT V_PHONE;dbms_output.put_line(v_phone); end; / --除了能用在RETURNING子句，OUT,IN OUT模式的綁定參數在執行動態PL/SQL時發揮比較大的作用 --在動態PL/SQL中綁定參數的模式必須要和PL/SQL程序中參數模式一致--重復的占位符 --NDS根據位置而不是名字把USING語句的綁定參數關聯到占位符的 --當執行一個動態SQL字符串，必須為每一個占位符提供一個參數，即便這些占位符是重復的 --如果執行的是一個動態PL/SQL塊，必須為每一個唯一占位符提供一個參數 DECLAREV_POINT NUMBER := 1000;V_NAME VARCHAR2(20) := '亂世佳人';V_PHONE varchar2(50);V_email varchar2(50); beginexecute immediate 'update ma_users set user_point = :value where USER_NAME= :valueRETURNING USER_PHONE,user_email INTO :1,:2'using V_POINT, V_NAME, OUT V_PHONE, out V_email;dbms_output.put_line(v_phone || V_email); end; /DECLAREV_POINT NUMBER := 1000;V_NAME VARCHAR2(20) := '亂世佳人';V_PHONE varchar2(50);V_email varchar2(50); beginexecute immediate 'begin update ma_users set user_name = :value where USER_NAME= :valueRETURNING USER_PHONE,user_email INTO :1,:2 ; end ;'using V_NAME, OUT V_PHONE,out V_email;dbms_output.put_line(v_phone||v_email); end; / --NULL值的傳遞 --把NULL值隱藏在一個變量后面 --通過轉換函數把NULL值顯示的轉換為一個有類型的值DECLAREV_POINT NUMBER := null;V_NAME VARCHAR2(20) := '亂世佳人';V_PHONE varchar2(50); begin/*execute immediate 'update ma_users set user_point = :value where USER_NAME= :NAMERETURNING USER_PHONE INTO :PHONE'using null, V_NAME, OUT V_PHONE;dbms_output.put_line(v_phone);*//*execute immediate 'update ma_users set user_point = :value where USER_NAME= :NAMERETURNING USER_PHONE INTO :PHONE'using V_POINT, V_NAME, OUT V_PHONE;dbms_output.put_line(v_phone);*/execute immediate 'update ma_users set user_point = :value where USER_NAME= :NAMERETURNING USER_PHONE INTO :PHONE'using to_number(null), V_NAME, OUT V_PHONE;dbms_output.put_line(v_phone); end; /

---動態PL/SQL
--NDS可以為我們做以下事情
--創建一個程序，包括帶有全局訪問的數據結構的包
--通過名字獲得或修改全局變量的值
--調用那些在編譯時刻還不知道名字的函數或者過程

--使用動態PL/SQL塊和NDS的規則和技巧

--動態字符串必須是一個有效的PL/SQL塊，這個塊必須以DECLARE或者BEGIN關鍵字開始
--使用END關鍵字和分號結束。如果字符串不以分號結尾，是不會被識別成PL/SQL塊的

--在動態塊中，只能訪問屬于全局范圍的PL/SQL代碼元素。動態塊是在局部包圍塊的作用范圍之外執行的
--在動態PL/SQL塊中拋出的錯誤可以在運行EXECUTE IMMEDIATE語句的局部塊中捕獲并處理

create or replace procedure do_plsql(i_plsql in varchar2) is begin execute immediate 'begin '|| rtrim(i_plsql,';')||' ; end ;'; end ; / --下面這個例子反映了規則二 declare num number ; begin do_plsql('num := 5');exceptionwhen others then dbms_output.put_line(sqlerrm); end ; /begin<<test>>declarenum number;begindo_plsql('test.num := 5');end; end; / create or replace package pkgvars is num number ; end pkgvars;declare num number ; begin do_plsql('pkgvars.num := 5;'); end ; /

--用動態塊替換重復的代碼

--比如有以下方法 procedure do_task(task_name in varchar2) is begin if task_name = 'test' then test;elsif task_name = 'test2' then test2;......end if; end ; / procedure do_task(task_name in varchar2) is begin execute immediate 'begin '||task_name||' ; end ;'; end ; /

--NDS的建議

--對于共享的程序使用調用者權限 create or replace procedure exec_ddl(ddl_string in varchar2) authid current_user is begin EXECUTE IMMEDIATE ddl_string; end ; /--預估并處理動態的錯誤 --如果使用大量的動態SQL，很容易迷失方向以至于在代碼調試上浪費大量時間 --在調用EXECUTE IMMEDIATE和OPEN FOR 時，總是帶上一個異常處理單元 --在每一個異常處理句柄，記錄下顯示錯誤發生時的錯誤信息以及SQL語句 --可以考慮在這些語句之前加上一個“跟蹤機制” create or replace procedure exec_ddl(ddl_string in varchar2) authid current_user is begin EXECUTE IMMEDIATE ddl_string;EXCEPTIONWHEN OTHERS THEN DBMS_OUTPUT.put_line('FAILLURE:'||DBMS_UTILITY.format_error_backtrace);DBMS_OUTPUT.put_line('ON :'||ddl_string); end ; /--使用綁定而不是拼接 DECLAREV_POINT NUMBER := 1000;V_NAME VARCHAR2(20) := '亂世佳人';V_PHONE varchar2(50); beginexecute immediate 'update ma_users set user_point = :value where USER_NAME= :NAME'using V_POINT, V_NAMEdbms_output.put_line(v_phone); end; / DECLAREV_POINT NUMBER := 1000;V_NAME VARCHAR2(20) := '亂世佳人';V_PHONE varchar2(50); beginexecute immediate 'update ma_users set user_point =' || V_POINT ||'where USER_NAME=''' || V_NAME || '''';dbms_output.put_line(SQL%ROWCOUNT); end; / --只要有可能使用綁定的方式就不要依賴與拼接

--綁定通常更快速
--綁定的編寫和維護都很容易
--綁定有助于避免隱式轉換
--綁定避免了發生代碼注入的可能性

--對于一些場景，如果使用拼接更有效，那也要毫不猶豫的使用拼接方式

--把代碼注入的風險最小化 create or replace procedure show_table1(i_table in varchar2,i_where in varchar2) isv_sql varchar2(1000); beginv_sql := 'declare v_row ' || i_table || '%rowtype;begin select * into v_row from ' || i_table || ' where ' || i_where || 'end ;';dbms_output.put_line(v_sql);-- execute immediate v_sql; end show_table1; /declare beginshow_table1('ma_users',' user_name=''亂世佳人'';delete from ma_users ; '); end; /--代碼注入也叫SQL注入，可以嚴重的威脅程序的安全，動態PL/SQL塊的執行為代碼注入開啟了最大的可能性 --限制用戶權限 --盡可能使用綁定變量，但使用綁定，也喪失了一些靈活性 --檢測動態文本中的危險文本 --用DBMS_ASSERT檢驗輸入 DBMS_ASSERT.SIMPLE_SQL_NAME---什么時候使用DBMS_SQL ---解析非常長的字符串 --EXECUTE IMMEDIATE 執行的字符串大小限制32K，在11g中可以處理一個CLOB，最大長度4GB dbms_sql.parse --可以解析任意長度的SQL和PLSQL---得到查詢的列的信息 --dbms_sql可以對動態游標中的列進行描述，以記錄的關聯數組的形式返回每個列的信息 --用這個功能，可以寫出非常通用的游標處理代碼 --動態SQL的第四種方法 declare cur pls_integer := dbms_sql.open_cursor;cols dbms_sql.desc_tab;ncols pls_integer; begin dbms_sql.parse(cur,'select user_name ,user_point from ma_users',dbms_sql.native);dbms_sql.describe_columns(cur,ncols,cols);for i in 1..ncols loop dbms_output.put_line(cols(i).col_name);end loop ;dbms_sql.close_cursor(cur); end ; / --實現第四種方法的動態SQL的需求 declarecursor cur_task isselect * from ma_schedue_task;v_sql varchar2(4000);v_cur number;v_result number; beginfor v in cur_task loopv_sql := ' begin ' || v.procedure_name || ';end;';v_cur := dbms_sql.open_cursor;dbms_sql.parse(v_cur, v_sql, dbms_sql.native);for param in (select r.param_order, r.param_valuefrom ma_schedue_param rwhere r.task_id = v.task_idorder by param_order) loopdbms_sql.bind_variable(v_cur,':' || param.param_order,param.param_value);end loop;v_result := dbms_sql.execute(v_cur);dbms_sql.close_cursor(v_cur);end loop; end; /

--11g新特性

dbms_sql.to_refcursor --原生態動態sql和DBMS_SQL的交互declaretype string_t is table of varchar2(100);function get_data(i_where in varchar2, i_value in string_t)return sys_refcursor isv_sql varchar2(1000);v_cur number;v_result number;v_result_cur sys_refcursor;beginv_sql := 'select * from ma_users where ' || i_where;v_cur := dbms_sql.open_cursor;dbms_sql.parse(v_cur, v_sql, dbms_sql.native);for i in 1 .. i_value.count loopdbms_sql.bind_variable(v_cur, ':' || i, i_value(i));end loop;v_result := dbms_sql.execute(v_cur);v_result_cur := dbms_sql.to_refcursor(v_cur);return v_result_cur;end get_data; begindeclarev_cur sys_refcursor;v_row ma_users%rowtype;beginv_cur := get_data(' user_name=:1', string_t('亂世佳人'));loopfetch v_curinto v_row;exit when v_cur%notfound;dbms_output.put_line(v_row.user_point);end loop;close v_cur;end; end; /

?

轉載于:https://www.cnblogs.com/yhq1314/p/10615280.html

總結

以上是生活随笔為你收集整理的PL/SQL 训练12--动态sql和绑定变量的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。