全面学习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
2.DML
3、 DML之绑定变量
小结:这里主要应用了几个常见的子过程，更多的子过程内容只是了解了下，详情请参考ORACLE安装目录下的DBMSSQL.SQL。
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>
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>
在这个例子里，我要用动态语句去更新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>
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>
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。
