PLSQL中使用动态SQL编程.doc

PL/SQL中使用动态SQL编程 在PL/SQL程序设计过程中，会遇到很多必须使用动态sql的地方，oracle系统所提供的DMBS_SQL包可以帮助你解决问题。 (一)介绍 DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种: functionopen_cursor:打开一个动态游标,并返回一个整型; procedureclose_cursor(cinoutinteger);关闭一个动态游标,参数为open_cursor所打开的游标; procedureparse(cininteger,statementinvarchar2,language_flagininteger):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native); proceduredefine_column(cininteger,positionininteger,columnanydatatype,column_sizeininteger):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述); functionexecute(cininteger):执行游标,并返回处理一个整型，1表示成功，0表示失败,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略); functionfetch_rows(cininteger):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端; procedurecolumn_value(cininteger,positionininteger,value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量; procedurebind_variable(cininteger,nameinvarchar2,value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值; 以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql (二)一般过程 对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤: opencursor-parse-definecolumn-excute-fetchrows-closecursor; 而对于dml操作(insert,update)则需要进行以下几个步骤: opencursor-parse-bindvariable-execute-closecursor; 对于delete操作只需要进行以下几个步骤: opencursor-parse-execute-closecursor; (三)实例应用 1.declare v_cidinteger; v_updatestrvarchar2(100); v_rowupdatedinteger; begin v_cid:=dbms_sql.open_cursor; v_updatestr:=updateempsetcomm=400whereempno=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 whenothersthen dbms_sql.close_cursor(v_cid); raise; end; 2.createorreplacefunctionupdatecomm(p_m%type,p_empnoemp.empno%type returnintegeras v_cidinteger; v_updatestrvarchar2(100); v_rowupdatedinteger; begin v_cid:=dbms_sql.open_cursor; v_updatestr:=updateempsetcomm=:commwhereempno=: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); returnp_rowsupdated; exception whenothersthen dbms_sql.close_cursor(v_cid); raise; end; 调用- declare ainteger; begin a:=updatecomm(5000,a); dbms_output.put_line(a); end; 3.createorreplaceproceduredynamiccopy(p_deptno1emp.deptno%typedefaultnull,p_deptno2emp.deptno%typedefaultnull) as v_cidinteger; v_selectvarchar2(100); v_empnochar(4); v_enamevarchar2(10); v_deptnochar(2); v_dummyinteger; begin v_cid:=dbms_sql.open_cursor; v_select:=selectempno,ename,deptnofromempwheredeptnoin(: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 ifdbms_sql.fetch_rows(v_cid)=0then exit; endif; 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); insertintoemp1(empno,ename,deptno)values(v_empno,v_ename,v_deptno); endloop; dbms_sql.close_cursor(v_cid); commit; exception whenothersthen dbms_sql.close_cursor(v_cid); raise; end; 4.DDL语句：DDL中联编变量是非法的，即使在解析后不能够调用bind_variable过程。另外，DDL解析后立即执行，不需要调用EXECUTE过程，即使调用了也没有用。 createorreplaceprocedurerecreatetable(p_tableinvarchar2,p_descriptioninvarchar2) as v_cursornumber; v_createstringvarchar2(100); v_dropstringvarchar2(100); begin v_cursor:=dbms_sql.open_cursor; v_dropstring:=droptable|p_table; begin dbms_sql.parse(v_cursor,v_dropstring,dbms_sql.v7); exception whenothersthen ifsqlcode!=-942then raise; endif; end; v_createstrin