Dynamic SQL/PL

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;

－－－－－－－－－－－－－－－－－－－－－－
5.自己的实例

PACKAGE BODY GSOL_INQUIRE_ALL
AS
/* In use by Expansion to indicate completion of item population by both Verified and Unverified suppliers */
PROCEDURE PROCESS_ITEM_EXPANSION_STATUS(in_inquiry_id                IN inquire_all.inquiry_id%TYPE,
                                        in_item_populate_status      IN inquire_all.item_populate_status%TYPE DEFAULT 'Y',
                                        in_supplier_cnt              IN inquire_all.supplier_cnt%TYPE,
                                        in_product_cnt               IN inquire_all.product_cnt%TYPE)
IS
v_CurMonth         number;
v_PrevMonth        number;
v_cursor           number;
v_row              number;
v_sqlStr           varchar2(500);

BEGIN
v_CurMonth := to_number(to_char(sysdate, 'MM'));
IF (v_CurMonth=1)
  THEN
    v_PrevMonth := 12;
  ELSE
    v_PrevMonth := v_CurMonth -1;
END IF;
v_sqlStr := ' SET ITEM_POPULATE_STATUS = '|| ''''|| in_item_populate_status  || ''''|| ', ITEM_POPULATE_DATE = sysdate, '
            || ' SUPPLIER_CNT = ' || in_supplier_cnt || ', PRODUCT_CNT = ' || in_product_cnt || ' WHERE INQUIRY_ID = '||in_inquiry_id
            || ' AND FORM_COMPLETE_STATUS = ''Y''';

 /* update supplier/product count in current month's table */
   BEGIN
     v_cursor := dbms_sql.open_cursor;
     dbms_sql.parse(v_cursor,'UPDATE INQUIRE_ALL_' || v_CurMonth || v_sqlStr, dbms_sql.NATIVE);
     v_row := dbms_sql.execute(v_cursor);
     dbms_sql.close_cursor(v_cursor);
   EXCEPTION
     WHEN others THEN
        dbms_sql.close_cursor(v_cursor);
        RAISE_APPLICATION_ERROR(-20200,'Error in updating table [INQUIRE_ALL_' || v_CurMonth ||'] SQLERROR: ' || SQLERRM(sqlcode));
   END;
  
   /* update supplier/product count in previous month's tables */
   BEGIN
     v_cursor := dbms_sql.open_cursor;
     dbms_sql.parse(v_cursor, 'UPDATE INQUIRE_ALL_' || v_PrevMonth || v_sqlStr, dbms_sql.NATIVE);
     v_row := dbms_sql.execute(v_cursor);
     dbms_sql.close_cursor(v_cursor);
   EXCEPTION
     WHEN others THEN
        dbms_sql.close_cursor(v_cursor);
        RAISE_APPLICATION_ERROR(-20200,'Error in updating table [INQUIRE_ALL_' || v_PrevMonth ||'] SQLERROR: ' || SQLERRM(sqlcode));
   END;
COMMIT;
EXCEPTION
   WHEN others THEN
      ROLLBACK;
      RAISE_APPLICATION_ERROR(-20200, 'Error updating table inquire_all_xx for population');
END PROCESS_ITEM_EXPANSION_STATUS;

//其他procedure...
END GSOL_INQUIRE_ALL;

说明：简单的说，本例子，是用来更新本月和上月的数据。
1.v_sqlStr := ' SET ITEM_POPULATE_STATUS = '|| ''''|| in_item_populate_status  || ''''|| ', ITEM_POPULATE_DATE = sysdate, '
            || ' SUPPLIER_CNT = ' || in_supplier_cnt || ', PRODUCT_CNT = ' || in_product_cnt || ' WHERE INQUIRY_ID = '||in_inquiry_id
            || ' AND FORM_COMPLETE_STATUS = ''Y''';中，in_item_populate_status　为character,两边要加上两个单引号，如后面的FORM_COMPLETE_STATUS = ''Y''　一样．当传的参数为character的时候要注意。
2.在dbms_sql.parse(v_cursor, queryString, dbms_sql.NATIVE); 的queryString 中，不可以有运算．例如v_PrevMonth，必须提前计算，不可以直接queryString中用　v_CurMonth－1

3.raise error:  RAISE_APPLICATION_ERROR(-20200, 'Error updating table inquire_all_xx for population');)自定义error.
  RAISE_APPLICATION_ERROR(errorCode,description);
  这样的error在java中调用时，可以被打印．

 (转)RAISE_APPLICATION_ERROR 的声明：
  可能不是很多人知道 RAISE_APPLICATION_ERROR 的用途是什么，虽然从字面上已经猜到这个函数是干什么用的。
      其实 RAISE_APPLICATION_ERROR 是将应用程序专有的错误从服务器端转达到客户端应用程序。
 
      RAISE_APPLICATION_ERROR 的声明：
 
      PROCEDURE RAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
 
      里面的错误代码和内容，都是自定义的。说明是自定义，当然就不是系统中已经命名存在的错误类别，是属于一种自定义事务错误类型，才调用此函数。
      error_number_in 之容许从 -20000 到 -20999 之间，这样就不会与 ORACLE 的任何错误代码发生冲突。
      error_msg_in 的长度不能超过 2K，否则截取 2K。
 
 
举个例吧：
阻止小于18岁的用户增加到数据库 employee 表中
 
CREATE OR REPALCE TRIGGER minimun_age_check
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
      IF ADD_MONTHS( :new.birth_date, 18*12) > SYSDATE
      THEN
             RAISE_APPLICATION_ERROR(-20001, 'Employees must at least eighteen years of age.');
      END IF;
END;
在客户端，你可以写一个类似下面的程序，来测试一下。
 
DECLARE
 
    no_babies_allowed EXCEPTION;
 
     /*将名称与用于触发器中的错误号码关联起来*/
     PRAGMA EXCEPTION_INIT(no_babies_allowed, -20001);
 
BEGIN
 
       INSERT INTO employee ....;
 
EXCEPTION
       WHEN no_babies_allowed
       THEN
              /*
              || SQLERRM 将传递给内置过程 RAISE_APPLICATION_ERROR 的消息返回
              */
              DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

4.  DBMS_OUTPUT.PUT_LINE　的使用，　这里没有用到，但是有时候可以用。找了个例子。
　SET   serveroutput   on 
  DECLARE 
      /*   Declare   variables   to   be   used   in   this   block.   */ 
      v_Num1             NUMBER   :=   1; 
      v_Num2             NUMBER   :=   2; 
      v_String1       VARCHAR2(50)   :=   'Hello   World!'; 
      v_String2       VARCHAR2(50)   :=   '--   This   message   brought   to   you   by   PL/SQL!'; 
      v_OutputStr   VARCHAR2(50); 
  BEGIN 
      INSERT   INTO   temp_table   (num_col,   char_col) 
          VALUES   (v_Num1,   v_String1); 
      INSERT   INTO   temp_table   (num_col,   char_col) 
          VALUES   (v_Num2,   v_String2); 
  SELECT   char_col 
          INTO   v_OutputStr 
  FROM   temp_table 
  WHERE   num_col   =   v_Num1; 
      DBMS_OUTPUT.PUT_LINE(v_OutputStr); 
      
      SELECT   char_col 
          INTO   v_OutputStr 
  FROM   temp_table 
  WHERE   num_col   =   v_Num2; 
      DBMS_OUTPUT.PUT_LINE(v_OutputStr);

5.　关于Exception,本例中间block有捕获Exception,最后还有一个．做testing的时候，我把最后那个Exception comment掉，否则，前面的如果有错，以后只到最后那个Exception才停止。下面是转载，原因有些相近。
(转自su_fisher的ORACLE专栏)异常结束后继续执行收藏
当你运行一个过程,例如：
 
Declear
Begin
      DELETE TABLE t_a where .....;
      UPDATE t_b set .....;
      DELETE TABLE t_c where .....;
Exception
      WHEN OTHERS THEN
              ..............;
End；
 
如果其中一步发生异常，但我依然想继续执行下面的程序，按照上面的代码是不可能的。只要发生异常程序只会去到 Exception 的块中。
 
那怎么办呢？
 
只要你将程序改成：
 
Declear
Begin
 
      begin
           DELETE TABLE t_a where .....;
      Exception
           WHEN OTHERS THEN
              null;
      end;
 
 
      begin
           UPDATE t_b set .....;
      Exception
           WHEN OTHERS THEN
              null;
      end;
 
 
      begin
           DELETE TABLE t_c where .....;
      Exception
           WHEN OTHERS THEN
              null;
      end;
Exception
      WHEN OTHERS THEN
              ..............;
End；
 
 
现在就可以实现你想要的程序流程了。
 
第一步，删除表 t_a 时，如果发生异常，程序就会进入匿名块的异常处理，而且异常处理并不进行任何处理，之后程序将进行 UPDATE t_b 的步骤。

6.testing／调用 :
  在SQL Navigator中，exec GSOL_INQUIRE_ALL.PROCESS_ITEM_EXPANSION_STATUS(8000003045213,'I',1067,1077);
  shell scripts中也如此．