ORACLE 动态语句
2024-08-29 13:53:34
供稿：网友
在一般的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或根据需求使用循环语句来进行处理。这里就不进行介绍了。
