全面学习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 >

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。