ORACLE 动态语句

10/28/2009来源:Oracle教程人气:8376

 在一般的PL/SQL程序开发中,可以使用SQL的DML语句和事务控制语句,但是DDL语句及会话语句却不能在PL/SQL中直接使用,要想实现在PL/SQL中使用DDL语句及会话控制语句,可以通过动态SQL来实现。

       所谓动态SQL是指在PL/SQL块编译时SQL语句是不确定的,例如根据用户输入参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句,对语句进行语法分析并执行该语句。 Oracle中的动态SQL可以通过本地动态SQL命令来执行,也可以通过DBMS_SQL程序包来执行。

       通常在开发中用简单的本地动态SQL就能解决问题,在下面我会用别的方法来实现。给出执行本地动态SQL的语法:

EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list];

其中: dynamic_sql_string 是动态SQL语句字符串 INTO子句用于接受SELECT语句选择的纪录值。 USING子句用于接受绑定输入参数变量。

例子1:

DECLARE

       sql_s varchar2(200);

       emp_id number(4):=7566;

       emp_rec emp%rowtype;

BEGIN

       EXECUTE  IMMEDIATE  'create  table  table_name (id number,amt  number)';  

       sql_s:='select * from emp where empno=:id;

       EXECUTE  IMMEDIATE  sql_s into  emp_rec  using  emp_id;

END;

       这段代码首先执行一条创建的动态SQL,接着执行了带参数的SELECT语句。EXECUTE IMMEDIATE语句只能用于处理返回单行或没有返回的SQL语句,要处理返回多行的动态SQL就要使用REF游标的OPEN...FOR语句。下面就来讨论:

例2:

       要求:用户输入多个批次号(lot_number)和物料号(key_number )或多个批次号(lot_number)和供应商名(ver_apell)来求库存中物料的数目为了让代码结构清晰,我使用包来创建代码:

首先,创建包头部分:

create  or  replace  package  SMT_Traceability_p    is

     type s_s_qty is ref cursor;

     PRocedure surplus_stock(key_number  in       sfism4.c_see_iqc_check_detail.key_part_no%type,

     lot_number in varchar2,

     ver_apell  in  sfism6.r_smt_inv_tran_t.ver%type,

     su_st_qty out s_s_qty);

end SMT_Traceability_p;

接着,创建包体:

create  or  replace  package  body  SMT_Traceability_p   is

           procedure surplus_stock(

                  key_number   in  sfism4.c_see_iqc_check_detail.key_part_no%type,

                  lot_number  in  varchar2,

                  ver_apell  in  sfism6.r_smt_inv_tran_t.ver%type,

                  su_st_qty out s_s_qty)   is

        cicd_qty number;

        rsit_qty number;

        v_sql varchar2(10000);

        begin

               if (key_number  is  null)  then

                       open  su_st_qty  for  select   0   from   dual;

              elsif (ver_apell = 'nosupply'  and  lot_number  is  not  null) then

                       v_sql := 'select  (select   nvl(sum(cicd.qty),0)  

                        from  sfism4.c_see_iqc_check_detail   cicd   where  

                       cicd.key_part_no = ''' || ltrim(rtrim(key_number, ' '), ' ') || '''

                       and

                       cicd.lot_no  in (' || lot_number || '))- (select   nvl(sum(rsit.qty),0)

                        from  sfism6.r_smt_inv_tran_t   rsit   where  

                        rsit.key_part_no = ''' || ltrim(rtrim(key_number, ' '), ' ') || '''

                       and rsit.lot_no   in  (' || lot_number || '))   from   dual';

                  open   su_st_qty   for    v_sql;

            else

                   select     sum(cicd.qty)    into    cicd_qty    

                   from    sfism4.c_see_iqc_check_detail   cicd  

                   where    cicd.key_part_no = ltrim(rtrim(key_number, ' '), ' ')

                   and cicd.supply = ltrim(rtrim(ver_apell, ' '), ' ');

                   select    sum(rsit.qty)   into    rsit_qty    from  

                  sfism6.r_smt_inv_tran_t   rsit    where  

                  rsit.key_part_no = ltrim(rtrim(key_number, ' '), ' ')

                 and     rsit.ver = ltrim(rtrim(ver_apell, ' '), ' ');

                if  (cicd_qty is null)  then

                       cicd_qty := 0;

                       rsit_qty := 0;

                        /* elsif(rsit_qtyes is null) then rsit_qty:=0;*/

                end if;

              open   su_st_qty   for   select   cicd_qty  -  nvl(rsit_qty, 0)   re_num

              from   dual;

          end if;

      end;

        注释:因为用户会输入一个或多个批次号,并且输入时的格式是固定的,每个批次号用单引号引起来,批次号和批次号之间用逗号隔开,即:

           '1TOB8311CJL',''RJC4633012/33'

         请注意  rsit.lot_no   in  (' || lot_number || '))  这种写法。如果用户输入是一个批次号时,可以把上面那段动态SQL写成下面这种形式:

             v_sql := 'select    (select     sum(cicd.qty)   

             from   sfism4.c_see_iqc_check_detail   cicd   where

             cicd.key_part_no  =  :  key_number   and   

             cicd.lot_no   in  (:lot_number))- (select sum(rsit.qty)

             from   sfism6.r_smt_inv_tran_t   rsit    where

             rsit.key_part_no  =  : key_number   and    rsit.lot_no

            in  ( : lot_number ))    from    dual ';

            open    su_st_qty    for    v_sql    using

             ey_number, lot_number, key_number, lot_number;

            另外,儅需要对ref  cursor中的内容进行处理时,就需要使用fetch su_st_qty   into   variable_name或根据需求使用循环语句来进行处理。这里就不进行介绍了。