概述
使用動态sql是在編寫pl/sql過程時經常使用的方法之一。很多情況下，比如根據業務的需要，如果輸入不同查詢條件，則生成不同的執行sql查詢語句，對于這種情況需要使用動态sql來完成。再比如，對于分頁的情況，對于不同的表，必定存在不同的字段，是以使用靜态sql則隻能針對某幾個特定的表來形成分頁。而使用動态的sql，則可以對不同的表，不同的字段進行不同的分頁。這些情況的處理通常都是用動态sql來完成。
動态sql和靜态sql
靜态sql
靜态sql通常用于完成可以确定的任務。比如傳遞部門号調用存儲過程，傳回該部門的所有雇員及薪水資訊，則該語句為
select ename，sal into lv_ename,lv_sal from scott.emp where deptno=&dno;
對于上述類似的dml語句在第一次運作時進行編譯，而後續再次調用，則不再編譯該過程。即一次編譯，多次調用，使用的相同的執行計劃。此種方式被稱之為使用的是靜态的sql。
動态sql
動态sql通常是用來根據不同的需求完成不同的任務。比如分頁查詢，對于表emp分頁，需要使用字段雇員姓名，薪水，雇用日期，且按薪水降序生成報表，每頁顯示行資料。而對于表sales，需要使用字段雇員名稱，客戶名稱，銷售數量，銷售日期，且按銷售日期升序排列。以上兩種情況，可以建立存儲過程來對其進行分頁，通過定義變量，根據輸入不同的表名，字段名，排序方法來生成不同的sql語句。對于輸入不同的參數，sql在每次運作時需要事先對其編譯。即多次調用則需要多次編譯，此稱之為動态sql。動态sql語句通常存放在字元串變量中，且sql語句可以包含占位符(使用冒号開頭)。也可以直接将動态sql緊跟在execute immediate語句之後，如
execute immediate 'alter table emp enable row movement'
兩者的異同
靜态sql為直接嵌入到pl/sql中的代碼，而動态sql在運作時，根據不同的情況産生不同的sql語句。
靜态sql為在執行前編譯，一次編譯，多次運作。動态sql同樣在執行前編譯，但每次執行需要重新編譯。
靜态sql可以使用相同的執行計劃，對于确定的任務而言，靜态sql更具有高效性。但缺乏靈活性動态sql使用了不同的執行計劃，效率不如靜态sql，但能夠解決複雜的問題。
動态sql語句的幾種方法
a.使用execute immediate語句
包括ddl語句，dcl語句，dml語句以及單行的select 語句。該方法不能用于處理多行查詢語句。
b.使用open-for，fetch和close語句
對于處理動态多行的查詢操作，可以使用open-for語句打開遊标，使用fetch語句循環提取資料，最終使用close語句關閉遊标。
c.使用批量動态sql
即在動态sql中使用bulk子句，或使用遊标變量時在fetch中使用bulk ，或在forall語句中使用bulk子句來實作。
d.使用系統提供的pl/sql包dbms_sql來實作動态sql(該方式本文中不做介紹)。
動态sql的文法
下面是動态sql常用的文法之一
execute immediate dynamic_sql_string
[into defined_variable1, defined_variable2, ...]
[using [in | out | in out] bind_argument1, bind_argument2,
...][{returning | return} field1, field2, ... into bind_argument1,
bind_argument2, ...]
文法描述
dynamic_sql_string：存放指定的sql語句或pl/sql塊的字元串變量
defined_variable1：用于存放單行查詢結果，使用時必須使用into關鍵字，類似于使用
select ename into v_name from scott.emp；
隻不過在動态sql時，将into defined_variable1移出到dynamic_sql_string語句之外。
bind_argument1：用于給動态sql語句傳入或傳出參數，使用時必須使用using關鍵字，in表示傳入的參數，out表示傳出的參數，in out則既可以傳入，也可傳出。returning | return 子句也是存放sql動态傳回值的變量。
使用要點
a.execute immediate執行dml時，不會送出該dml事務，需要使用顯示送出(commit)或作為execute immediate自身的一部分。
b.execute immediate執行ddl,dcl時會自動送出其執行的事務。
c.對于多行結果集的查詢，需要使用遊标變量或批量動态sql，或者使用臨時表來實作。
d.當執行sql時，其尾部不需要使用分号，當執行pl/sql 代碼時，其尾部需要使用分号。
f.動态sql中的占位符以冒号開頭，緊跟任意字母或數字表示。
動态sql的使用(ddl，dcl，dml以及單行結果集)
例：使用execute immediate處理ddl操作。在存儲過程之中封裝一簡單的ddl語句，通過傳入表名來進行調用。
sql> create table tb2 as select * from emp;
table created.
sql> create or replace procedure trunc_table(table_name varchar2)
2 as
3 sql_statement varchar2(100);
4 begin
5 sql_statement := 'truncate table ' || table_name; --為變量進行指派，用于生成動态sql語句
6 execute immediate sql_statement; --使用execute immediate執行動态sql語句
7 end;
8 /
procedure created.
sql> select count(1) from tb2;
count(1)
----------
14
sql> exec trunc_table('tb2');
pl/sql procedure successfully completed.
例：使用execute immediate處理dcl操作，下面使用sys帳戶建立存儲過程grant_sys_priv用于給使用者授予權限
sql> conn sys/redhat@orcl as sysdba
sql>create or replace procedure grant_sys_priv(priv varchar2, username varchar2)
is
sql_stat varchar2(100);
begin
sql_stat := 'grant ' || priv || ' to ' || username;
execute immediate sql_stat;
end;
/
sql> exec grant_sys_priv('connect','usr1');
例：使用execute immediate處理沒有參數傳入傳出的dml語句
sql> select * from tb2 where empno = 7900;
empno ename sal
---------- ----------- ------------
7900 james 950
sql> declare
2 sql_stat varchar2(100);
3 begin
4 sql_stat := 'delete from scott.tb2 where empno = 7900';
5 execute immediate sql_stat;
6 end;
7 /
no rows selected
例：有參數傳入的dml語句(使用using子句)，對于使用了參數傳入的動态sql，需要使用using子句來指明傳入的參數。在下面的示例中，為表tb2插入一條記錄，在dml語句中使用了四個占位符(占位符用以冒号開頭，緊跟任意字母或數字表示)。是以在使用execute immediate使用using子句為其指定其參數。
sql> declare
3 lv_empno tb2.empno%type :=7900;
4 lv_ename tb2.ename%type :='james';
5 lv_sal tb2.sal%type :=950;
6 begin
7 sql_stat := 'insert into tb2 values(:1,:2,:3)'; --dml語句中使用了占位符
8 execute immediate sql_stat using lv_empno,lv_ename,lv_sal; --為占位符指定參數或值
9 commit;
10* end;
sql> select * from tb2 where empno=7900;
empno ename sal
---------- ---------------- -------------
7900 james 950
例：處理包含returning子句的dml語句。下面的示例中，對表tb2進行更新，使用了兩個占位符，一個是:percent，一個是:eno，是以在使用execute immediate執行動态。dml時，需要使用using子句且帶兩個輸入參數。其次，動态dml中使用了returning sal into :salary，是以execute immediate後也必須使用returning into varialbe_name。
sql> declare
2 sql_stat varchar2(100);
3 v_sal tb2.sal%type;
4 begin
5 sql_stat :='update tb2 set sal = sal * (1 + :percent/100)'
6 || ' where empno = :eno returning sal into :v_sal'; --使用了占位符:eno，:salary，以及returning子句
7 execute immediate sql_stat using &1,&2 returning into v_sal; --必須使用using及returning子句
8 commit;
9 dbms_output.put_line('new salary: ' || v_sal);
10 end;
11 /
enter value for 1: 10
enter value for 2: 7900
old 7: execute immediate sql_stat using &1,&2 returning into v_sal;
new 7: execute immediate sql_stat using 10,7900 returning into v_sal;
new salary: 1045
例：處理包含檢索值的單行查詢。下面的示例中，使用select 查詢獲得單行結果集，使用了占位符:name，是以也需要使用using子句為其傳遞參數
3 emp_record tb2%rowtype;
5 sql_stat := 'select * from tb2 where ename = upper(:name)';
6 execute immediate sql_stat into emp_record using '&name';
7 dbms_output.put_line('the salary is ' || emp_record.sal || ' for ' || emp_record.ename);
8 end;
9 /
enter value for name: james
old 6: execute immediate sql_stat into emp_record using '&name';
new 6: execute immediate sql_stat into emp_record using 'james';
the salary is 1045 for james
遊标和動态sql處理多行結果集的查詢語句
其主要流程為
1.定義遊标變量
type cursortype is ref cursor;
cursor_variable cursortype;
2.打開遊标變量
open cursor_variable for dynamic_string
[using bind_argument[,bind_argument]...]
3.循環提取資料
fetch cursor_variable into {var1[,var2]...| record_variable};
exit when cursor_variable%notfound
4.關閉遊标變量
close cursor_variable;
使用遊标變量處理查詢多行結果集
下面的示例中，首先定義了一個遊标類型，接下來定義遊标變量，以及存放結果集的變量，動态查詢語句将獲得多個結果集。
open cursorname for select ... 時，其select 語句使用了字元串變量(動态sql)，其後緊跟using子句。
2 type emp_cur_type is ref cursor;
3 emp_cv emp_cur_type;
4 emp_record tb2%rowtype;
5 sql_stat varchar2(100);
6 v_empno tb2.empno%type := &inputno;
7 begin
8 sql_stat := 'select * from tb2 where empno = :no'; --動态多行結果集查詢語句
9 open emp_cv for sql_stat using v_empno; --open時使用動态查詢語句以及using子句來傳遞參數
10 loop
11 fetch emp_cv into emp_record; --從結果集中提取記錄
12 exit when emp_cv%notfound;
13 dbms_output.put_line('employee name: ' || emp_record.ename || ' ,salary: ' || emp_record.sal);
14 end loop;
15* end;
enter value for inputno: 7900
old 6: v_empno tb2.empno%type := &inputno;
new 6: v_empno tb2.empno%type := 7900;
employee name: james ,salary: 1045
bulk子句和動态sql的使用
動态sql中使用bulk子句的文法
execute immediate dynamic_string --dynamic_string用于存放動态sql字元串
[bulk collect into define_variable[,define_variable...]] --存放查詢結果的集合變量
[using bind_argument[,argument...]] --使用參數傳遞給動态sql
[{returning | return} --傳回子句
bulk collect into return_variable[,return_variable...]]; --存放傳回結果的集合變量
使用bulk collect into子句處理動态sql中t的多行查詢可以加快處理速度，進而提高應用程式的性能。當使用bulk子句時，集合類型可以是pl/sql所支援的索引表、嵌套表和varry，但集合元 素必須使用sql資料類型。常用的三種語句支援bulk子句，分别為execute immediate， fetch 和forall。
下面的例子，首先定義了兩個表類型以及其變量，接下來使用動态sql語句來更新tb2的薪水，使用execute immediate配合bulk collec into 來處理結果集。
2 type ename_table_type is table of tb2.ename%type index by binary_integer;
3 type sal_table_type is table of tb2.sal%type index by binary_integer;
4 ename_table ename_table_type;
5 sal_table sal_table_type;
6 sql_stat varchar2(200);
7 v_percent number := &percent;
8 v_dno number := &dno;
9 begin
10 sql_stat := 'update tb2 set sal = sal * (1 + :percent/100)'
11 || ' where deptno = :dno'
12 || ' returning ename,sal into :name,:salary';
13 execute immediate sql_stat using v_percent,v_dno
14 returning bulk collect into ename_table,sal_table;
15 for i in 1..ename_table.count loop
16 dbms_output.put_line('employee ' || ename_table(i) || ' salary is: ' || sal_table(i));
17 end loop;
18* end;
enter value for percent: 10
old 7: v_percent number := &percent;
new 7: v_percent number := 10;
enter value for dno: 20
old 8: v_dno number := &dno;
new 8: v_dno number := 20;
employee smith salary is: 880
employee jones salary is: 3272.5
employee scott salary is: 3300
employee adams salary is: 1210
employee ford salary is: 3300
使用execute immediate 結合bulk子句處理多行查詢。下面示例中，與前一個示例相同，隻不過其動态sql有查詢語句組成，且傳回多個結果集，同樣使用了bulk collect into來傳遞結果。
sql>declare
type ename_table_type is table of tb2.ename%type index by binary_integer;
type sal_table_type is table of tb2.sal%type index by binary_integer;
ename_table ename_table_type;
sal_table sal_table_type;
sql_stat varchar2(100);
v_dno number := &dno;
sql_stat := 'select ename,sal from tb2 where deptno = :v_dno'; --動态dql語句，未使用returning子句
execute immediate sql_stat bulk collect into ename_table,sal_table using v_dno;
for i in 1..ename_table.count loop
dbms_output.put_line('employee ' || ename_table(i) || ' salary is: ' || sal_table(i));
end loop;
old 7: v_dno number := &dno;
new 7: v_dno number := 20;
使用fetch子句結合bulk子句處理多行結果集
下面的示例中首先定義了遊标類型，遊标變量以及複合類型，複合變量，接下來從動态sql中open遊标，然後使用fetch将結果存放到複合變量中。即使用open，fetch代替了execute immediate來完成動态sql的執行。
sql> declare
2 type empcurtype is ref cursor;
3 emp_cv empcurtype;
4 type ename_table_type is table of tb2.ename%type index by binary_integer;
5 ename_table ename_table_type;
6 sql_stat varchar2(120);
8 sql_stat := 'select ename from tb2 where deptno = :dno';
9 open emp_cv for sql_stat using &dno;
10 fetch emp_cv bulk collect into ename_table;
11 for i in 1..ename_table.count loop
12 dbms_output.put_line('employee name: ' || ename_table(i));
13 end loop;
14 close emp_cv;
15* end;enter value for dno: 20
old 9: open emp_cv for sql_stat using &dno;
new 9: open emp_cv for sql_stat using 20;
employee name: smith
employee name: jones
employee name: scott
employee name: adams
employee name: ford
在forall語句中使用bulk子句
下面是forall子句的文法
forall index in lower bound..upper bound --forall循環計數
execute immediate dynamic_string --結合execute immediate來執行動态sql語句
using bind_argument | bind_argument(index) --綁定輸入參數
[bind_argument | bind_argument(index)]...
[{returning | return} bulk collect into bind_argument[,bind_argument...]]; --綁定傳回結果集
forall子句允許為動态sql輸入變量，但forall子句僅支援的dml(insert,delete,update)語句，不支援動态的select語句。
下面的示例中，首先聲明了兩個複合類型以及複合變量，接下來為複合變量ename_table指派，以形成動态sql語句。緊接着使用forall子句結合execute immediate 來提取結果集。
2 type ename_table_type is table of tb2.ename%type;
3 type sal_table_type is table of tb2.sal%type;
4 ename_table ename_table_type;
5 sal_table sal_table_type;
6 sql_stat varchar2(100);
8 ename_table := ename_table_type('blake','ford','miller');
9 sql_stat := 'update tb2 set sal = sal * 1.1 where ename = :1'
10 || ' returning sal into :2';
11 forall i in 1..ename_table.count
12 execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
13 for j in 1..sal_table.count loop
14 dbms_output.put_line('the ' || ename_table(j) || '''' || 's new salalry is ' || sal_table(j));
15 end loop;
16* end;
the blake's new salalry is 3448.5
the ford's new salalry is 3993
the miller's new salalry is 1573
常見錯誤
1、使用動态ddl時，不能使用綁定變量。下面的示例中，在建立表示，使用了綁定變量:dno，在執行的時候收到了錯誤資訊。
3 v_deptno varchar2(5) := '30';
5 sql_stat := 'create table tb_emp ' || ' as select * from emp '
6 || ' where deptno = :dno';
7 execute immediate sql_stat using v_deptno;
declare
*
error at line 1:
ora-01027: bind variables not allowed for data definition operations
ora-06512: at line 7
解決辦法，将綁定變量直接拼接，如下：
sql_stat := 'create table tb_tmp ' || 'as select * from scott.emp ' || 'where deptno = ' || v_deptno;
2、不能使用schema對象作為綁定參數，下面的示例中，動态sql語句查詢需要傳遞表名，是以收到了錯誤提示。
sql_stat varchar2(100);
v_tbname varchar2(20);
v_count number;
execute immediate 'select count(*) from :tb_name'
into v_count;
dbms_output.put_line('the table record is ' || v_count);
execute immediate 'select count(*) from ' || v_tbname into v_count;
3、動态sql塊不能使用分号結束(;)
下面的示例中，動态sql語句使用了分号來結束，收到錯誤提示。
execute immediate 'select count(*) from emp;' --此處多出了分号，應該去掉
end;4、動态pl/sql塊不能使用正斜杠來結束塊，但是塊結尾處必須要使用分号(;)
2 plsql_block varchar2(300);
4 plsql_block := 'declare ' ||
5 ' v_date date; ' ||
6 ' begin ' ||
7 ' select sysdate into v_date from dual; ' ||
8 ' dbms_output.put_line(to_char(v_date,''yyyy-mm-dd'')); ' ||
9 ' end;
10 /'; --此處多出了/，應該将其去掉
11 execute immediate plsql_block;
12* end;declare
ora-06550: line 2, column 18:
pls-00103: encountered the symbol "/" the symbol "/" was ignored.
ora-06512: at line 11
4、空值傳遞的問題
下面的示例中對表tb_emp更新，并将空值更新到sal列，直接使用using null收到錯誤提示。
2 sql_stat varchar2(200);
3 v_empno number := 7900;
5 sql_stat := 'update tb2 set sal = 1.1 * :new_sal where empno = :eno';
6 execute immediate sql_stat using null,v_empno; --此處不能直接使用null
execute immediate sql_stat using null,v_empno;
*
error at line 6:
ora-06550: line 6, column 36:
pls-00457: expressions have to be of sql types
ora-06550: line 6, column 3:
pl/sql: statement ignored
正确的處理方法
4 v_sal tb2.sal%type; --聲明一個新變量，但不指派
5 begin
6 sql_stat := 'update tb2 set sal = 1.1 * :new_sal where empno = :eno';
7 execute immediate sql_stat using v_sal,v_empno;
8 commit;
9 end;
10 /
5、日期和字元型必須要使用引号來處理
下面的示例中，使用了日期型變量，未使用引号标注，且使用了變量綁定，但直接輸入日期型資料，而不加引号，則收到錯誤提示。
2 sql_stat varchar2(100);
3 v_date date :=&dt;
4 v_empno number :=7900;
5 v_ename tb2.ename%type;
6 v_sal tb2.sal%type;
7 begin
8 sql_stat := 'select ename,sal from tb2 where hiredate=:v_date';
9 execute immediate sql_stat
10 into v_ename,v_sal
11 using v_date;
12 dbms_output.put_line('employee name '||v_ename||', sal is '||v_sal);
13* end;
14 /
enter value for dt: 1987-05-23
old 3: v_date date :=&dt;
new 3: v_date date :=1987-05-23;
v_date date :=1987-05-23;
*
error at line 3:
ora-06550: line 3, column 27:
pls-00382: expression is of wrong type
ora-06550: line 3, column 20:
pl/sql: item ignored
ora-06550: line 11, column 16:
pls-00320: the declaration of the type of this expression is incomplete or malformed
ora-06550: line 9, column 10:
處理辦法一
執行時輸入帶引号的字串
sql> /
enter value for dt: '1981-05-01'
old 3: v_date date :=&dt;
new 3: v_date date :='1981-05-01';
employee name blake, sal is 2850
處理辦法二
在聲明變量時指派用引号，如下
v_date date :='&dt';
如存在字元格式轉換，可以直接使用轉換函數，如
v_date date :=to_date('&dt','dd-mon-rr');
如果上面的例子中，動态sql語句不使用綁定日期變量，而是将其連接配接成字元串，則可以使用下面的方式來實作
sql_stat varchar2(100);
v_date date :='&dt';
v_empno number :=7900;
v_ename tb_emp.ename%type;
v_sal tb_emp.sal%type;
sql_stat := 'select ename,sal from tb_emp where hiredate=' || chr(39) ||v_date|| chr(39); --chr(39)代表單引号
execute immediate sql_stat
into v_ename,v_sal;
dbms_output.put_line('employee name '||v_ename||', sal is '||v_sal);
6、單行select 查詢不能使用returning into傳回
下面的示例中，使用了動态的單行select查詢，并且使用了returning子句來傳回值。事實上，returning coloumn_name into 子句僅僅支援對dml結果集的傳回，是以，收到了錯誤提示。sql>declare
3 v_empno tb2.empno%type := &empno;
4 v_ename tb2.ename%type;
6 sql_stat := ' select ename from tb2 where empno = :eno';
7 execute immediate sql_stat using v_empno returning ename into v_ename;
8 dbms_output.put_line('employee name: ' || v_ename);
enter value for empno: 7900
old 3: v_empno tb2.empno%type := &empno;
new 3: v_empno tb2.empno%type := 7900;
execute immediate sql_stat using v_empno returning ename into v_ename;
*
error at line 7:
ora-06550: line 7, column 54:
pls-00103: encountered the symbol "ename" when expecting one of the following:
into bulk
the symbol "ename" was ignored.
上，returning coloumn_name into
解決方式
去掉動态sql語句中的returning coloumn_name into子句，在執行execute immediate時，直接使用into子句來傳遞值。
execute immediate sql_stat into v_ename using v_empno;
參考至:http://blog.csdn.net/robinson_0612/article/details/6118010
http://blog.csdn.net/robinson_0612/article/details/6118049
本文原創，轉載請注明出處、作者
如有錯誤，歡迎指正
作者：czmmiao 文章出處：http://czmmiao.iteye.com/blog/1822979
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)