全面學習DBMS包之DBMS_SQL，dbms包dbms_sql
在PL/SQL編程當中，經常會遇到一些需要動態處理數據或表結構的問題，比如對一批表裡的數據進行處理，或者批量創建表，索引，觸發器等等，這個時候就可以通過DBMS_SQL包進行操作。你可能會有疑問，ORACLE不是提供了EXECUTE IMMEDIATE了嘛？干嘛還要DBMS_SQL包來處理？嘿嘿，不錯，很多人都習慣用EXECUTE IMMEDIATE來動態處理此類需求。下面根據我的理解，解釋一下二者的區別。
1、execute immediate的效率比dbms_sql低 。
execute immediate每次都要進行語句的硬分析，而通過DBMS_SQL.PRASE卻不會。
2、execute immediate可以使用變量
如execute immediate 'select count(*) from tab where uid=:id' using myid into nums;
3、dbms_sql包的功能遠比execute immediate強大，可以實現動態變量傳遞。
總的來說呢。EXECUTE IMMEDIATE可以看成是實現了DBMS_SQL的一部分功能，接下來你看DBMS_SQL有那麼多子過程嘛，那可不是蓋的哦.......
(一)介紹
DBMS_SQL系統包提供了很多函數及過程,現在簡要闡述其中使用頻率較高的幾種:
function open_cursor:打開一個動態游標,並返回一個整型;
procedure close_cursor(c in out integer);關閉一個動態游標,參數為open_cursor所打開的游標; procedure parse(c in integer, statement in varchar2, language_flag in integer):對動態游標所提供的sql語句進行解析,參數C表示游標,statement為sql語句,language-flag為解析sql語句所用oracle版本,一般有V6,V7跟native(在不明白所連database版本時,使用native); procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定義動態游標所能得到的對應值,其中c為動態游標,positon為對應動態sql中的位置(從1開始),column為該值所對應的變量,可以為任何類型,column_size只有在column為定義長度的類型中使用如VARCHAR2,CHAR等(該過程有很多種情況,此處只對一般使用到的類型進行表述);
function execute(c in integer):執行游標,並返回處理一個整型，1表示成功，0表示失敗,代表處理結果(對insert,delete,update才有意義,而對select語句而言可以忽略);
function fetch_rows(c in integer):對游標進行循環取數據,並返回一個整數,為0時表示已經取到游標末端;
procedure column_value(c in integer, position in integer, value):將所取得的游標數據賦值到相應的變量,c為游標,position為位置,value則為對應的變量;
procedure bind_variable(c in integer, name in varchar2, value):定義動態sql語句(DML)中所對應字段的值,c為游標,name為字段名稱,value為字段的值;
(二)一般過程
對於一般的select 操作,如果使用動態的sql語句則需要進行以下幾個步驟:
open cursor --->parse--->define column--->excute--->fetch rows--->close cursor;
而對於dml操作(insert , update )則需要進行以下幾個步驟:
open cursor --->parse--->bind variable--->execute--->close cursor;
對於delete 操作只需要進行以下幾個步驟:
open cursor --->parse--->execute--->close cursor;
對DDL操作需要進行一下幾個步驟
open cursor--->parse---->close cursor
(三)實例
1.DDL
SQL> create or replace procedure pro_test_dbms_sql(l_num in number,l_tabname in varchar2)
2 is
3 l_cur integer;
4 l_sql1 varchar2(400);
5 l_sql2 varchar2(400);
6 begin
7 l_cur :=dbms_sql.open_cursor;
8 if l_num=1 then
9 l_sql2 :='drop table '||l_tabname;
10 dbms_sql.parse(l_cur,l_sql2,dbms_sql.native);
11 dbms_sql.close_cursor(l_cur);
12 end if;
13 if l_num=0 then
14 l_sql1 :='create table '||l_tabname||' (id number(10))';
15 dbms_sql.parse(l_cur,l_sql1,dbms_sql.native);
16 dbms_sql.close_cursor(l_cur);
17 end if;
18 exception
19 when others then
20 raise;
21
22 end;
23 /
過程已創建。
SQL> exec pro_test_dbms_sql(0,'test');
PL/SQL 過程已成功完成。
SQL> select table_name from user_tables where table_name='TEST';
TABLE_NAME
------------------------------
TEST
SQL> exec pro_test_dbms_sql(1,'test');
PL/SQL 過程已成功完成。
SQL> select count(1) from user_tables where table_name='TEST';
COUNT(1)
----------
0
SQL>
SQL> create or replace procedure pro_test_dbms_sql(l_num in number,l_tabname in varchar2)
2 is
3 l_cur integer;
4 l_sql1 varchar2(400);
5 l_sql2 varchar2(400);
6 begin
7 l_cur :=dbms_sql.open_cursor;
8 if l_num=1 then
9 l_sql2 :='drop table '||l_tabname;
10 dbms_sql.parse(l_cur,l_sql2,dbms_sql.native);
11 dbms_sql.close_cursor(l_cur);
12 end if;
13 if l_num=0 then
14 l_sql1 :='create table '||l_tabname||' (id number(10))';
15 dbms_sql.parse(l_cur,l_sql1,dbms_sql.native);
16 dbms_sql.close_cursor(l_cur);
17 end if;
18 exception
19 when others then
20 raise;
21
22 end;
23 /
過程已創建。
SQL> exec pro_test_dbms_sql(0,'test');
PL/SQL 過程已成功完成。
SQL> select table_name from user_tables where table_name='TEST';
TABLE_NAME
------------------------------
TEST
SQL> exec pro_test_dbms_sql(1,'test');
PL/SQL 過程已成功完成。
SQL> select count(1) from user_tables where table_name='TEST';
COUNT(1)
----------
0
SQL>
2.DML
在這個例子裡，我要用動態語句去更新TEMP表LJJE字段，得到JE的累積和。當然直接在LOOP裡去執行UPDATE也可以得到，這裡是為了熟悉和說明DBMS_SQL包的使用拿來舉例。
SQL> select * from temp;
ID JE LJJE
---------- ---------- ----------
1 1000 1000
2 1000
3 2000 3000
4 500 3500
SQL> update temp set ljje=null;
已更新4行。
SQL> select * from temp;
ID JE LJJE
---------- ---------- ----------
1 1000
2
3 2000
4 500
SQL>
SQL> create or replace procedure pro_update_ljje
2 as
3 l_num integer;
4 l_cur integer;
5 l_sql varchar2(400);
6 l_return integer default 0;
7 begin
8 select max(id) into l_num from temp;
9 for i in 1..l_num loop
10 l_cur :=dbms_sql.open_cursor;
11 l_sql:='update temp set ljje=(select sum(nvl(je,0)) from temp where id<='||i||') where id='||i;
12 dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
13 l_return :=dbms_sql.execute(l_cur);
14 commit;
15 dbms_sql.close_cursor(l_cur);
16 end loop;
17 exception
18 when others then
19 dbms_sql.close_cursor(l_cur);
20 dbms_output.put_line('執行失敗');
21 end;
22 /
過程已創建。
SQL> exec pro_update_ljje;
PL/SQL 過程已成功完成。
SQL> select * from temp;
ID JE LJJE
---------- ---------- ----------
1 1000 1000
2 1000
3 2000 3000
4 500 3500
SQL>
3、 DML之綁定變量
SQL> create table test(
2 id integer,
3 country varchar2(20),
4 company varchar2(50),
5 name varchar2(10),
6 address varchar2(100)
7 );
表已創建。
SQL> insert into test
2 select 1,'china','sap lab','Bob','Pudong New Area' from dual
3 union all
4 select 2,'china','sap lab','Myth','Xujiahui' from dual
5 union all
6 select 3,'china','sap lab','Lucy','Huangpu' from dual
7 union all
8 select 4,'china','INXITE','Kate','Jingan' from dual
9 ;
已創建4行。
SQL> commit;
提交完成。
SQL> set linesize 100
SQL> select * from test;
ID COUNTRY COMPANY NAME ADDRESS
---------- ---------- -------------------------------------------------- ---------- ----------
1 china sap lab Bob Pudong New
Area
2 china sap lab Myth Xujiahui
3 china sap lab Lucy Huangpu
4 china INXITE Kate Jingan
SQL>
SQL> create or replace procedure pro_update_address(l_country varchar2,l_address varchar2)
2 as
3 l_cur integer;
4 l_sql varchar2(400);
5 l_return integer;
6 begin
7 l_cur :=dbms_sql.open_cursor;
8 l_sql:='update test set address= :l_address where country= :l_country ' ;
9 dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
10 dbms_sql.bind_variable(l_cur,':l_address',l_address);
11 dbms_sql.bind_variable(l_cur,':l_country',l_country);
12 l_return :=dbms_sql.execute(l_cur);
13 commit;
14 dbms_sql.close_cursor(l_cur);
15 exception
16 when others then
17 dbms_sql.close_cursor(l_cur);
18 end;
19 /
過程已創建。
SQL>
SQL> exec pro_update_address('china','Pudong Area');
PL/SQL 過程已成功完成。
SQL> select * from test;
ID COUNTRY COMPANY NAME ADDRESS
---------- ---------- -------------------------------------------------- ---------- ----------
1 china sap lab Bob Pudong Are
a
2 china sap lab Myth Pudong Are
a
3 china sap lab Lucy Pudong Are
a
4 china INXITE Kate Pudong Are
a
ID COUNTRY COMPANY NAME ADDRESS
---------- ---------- -------------------------------------------------- ---------- ----------
SQL>
小結:這裡主要應用了幾個常見的子過程，更多的子過程內容只是了解了下，詳情請參考ORACLE安裝目錄下的DBMSSQL.SQL。
