DBMS_SQL包的使用

PL/SQL中使用动态SQL编程  
  
在PL/SQL程序设计过程中，会遇到很多必须使用动态sql的地方，oracle系统所提供的DMBS_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为字段的值;  
  
以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql  
  
(二)一般过程  
对于一般的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;   
  
(三)实例应用  
1. declare   
v_cid integer ;  
v_updatestr varchar2(100);  
v_rowupdated integer ;  
begin   
v_cid:=dbms_sql .open_cursor;  
v_updatestr:='update emp set comm=400 where empno=7499' ;  
dbms_sql .parse(v_cid,v_updatestr,dbms_sql .native);  
v_rowupdated:=dbms_sql .execute (v_cid);  
dbms_sql .close_cursor(v_cid);  
exception  
when  others  then   
dbms_sql .close_cursor(v_cid);  
raise;  
end ;  
2.create   or   replace   function  updatecomm(p_comm emp.comm%type, p_empno emp.empno%type  
return   integer   as   
v_cid integer ;  
v_updatestr varchar2(100);  
v_rowupdated integer ;  
begin   
v_cid:=dbms_sql .open_cursor;  
v_updatestr:='update emp set comm=:comm where empno=:empno' ;  
dbms_sql .parse(v_cid,v_updatestr,dbms_sql .native);  
dbms_sql .bind_variable(v_cid,'comm' , 'p_comm' );  
dbms_sql .bind_variable(v_cid,'empno' , 'p_empno' );  
v_rowupdated:=dbms_sql .execute (v_cid);  
dbms_sql .close_cursor(v_cid);  
return  p_rowsupdated;  
exception  
when  others  then   
dbms_sql .close_cursor(v_cid);  
raise;  
end ;  
调用--   
declare   
a integer ;  
begin   
a:=updatecomm(5000,a);  
dbms_output.put_line(a);  
end ;  
3.create   or   replace   procedure  dynamiccopy(p_deptno1 emp.deptno%type  default   null ,p_deptno2 emp.deptno%type  default   null )  
as   
v_cid integer ;  
v_select varchar2(100);  
v_empno char (4);   
v_ename varchar2(10);  
v_deptno char (2);  
v_dummy integer ;  
begin   
v_cid:=dbms_sql .open_cursor;  
v_select:='select empno,ename,deptno from emp where deptno in(:d1,:d2)' ;  
dbms_sql .parse(v_cid,v_select,dbms_sql .native);  
dbms_sql .bind_variable(v_cid,'d1' ,p_deptno1);  
dbms_sql .bind_variable(v_cid,'d2' ,p_deptno2);  
dbms_sql .define_column(v_cid,1,v_empno,4);  
dbms_sql .define_column(v_cid,2,v_ename,10);  
dbms_sql .define_column(v_cid,3,v_deptno,2);  
v_dummy:=dbms_sql .execute (v_cid);  
loop  
if dbms_sql .fetch_rows(v_cid)=0 then   
exit;  
end  if;  
dbms_sql .column_value(v_cid,1,v_empno);  
dbms_sql .column_value(v_cid,2,v_ename);  
dbms_sql .column_value(v_cid,3,v_deptno);  
insert   into  emp1(empno,ename,deptno)  values (v_empno,v_ename,v_deptno);  
end  loop;  
dbms_sql .close_cursor(v_cid);  
commit ;  
exception  
when  others  then   
dbms_sql .close_cursor(v_cid);  
raise;  
end ;   
4.DDL语句：DDL中联编变量是非法的，即使在解析后不能够调用bind_variable过程。另外，DDL解析后立即执行，不需要调用EXECUTE 过程，即使调用了也没有用。  
create   or   replace   procedure  recreatetable(p_table  in  varchar2,p_description  in  varchar2)  
as   
v_cursor number;  
v_createstring varchar2(100);  
v_dropstring varchar2(100);  
begin   
v_cursor:=dbms_sql .open_cursor;  
v_dropstring:='drop table' ||p_table;  
begin   
dbms_sql .parse(v_cursor,v_dropstring,dbms_sql .v7);  
exception  
when  others  then   
if sqlcode!=-942 then   
raise;  
end  if;  
end ;  
v_createstring:='create table' ||p_table||p_description;  
dbms_sql .parse(v_cursor,v_createstring,dbms_sql .native);  
dbms_sql .close_cursor(v_cursor);  
exception  
when  others  then   
dbms_sql .close_cursor(v_cursor);  
raise;  
end ;