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 >
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>
在这个例子里,我要用动态语句去更新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。