ORACLE 动态语句

来源：互联网发布：java找工作用什么软件编辑：程序博客网时间：2024/06/05 06:14

在一般的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或根据需求使用循环语句来进行处理。这里就不进行介绍了。