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或根據需求使用循環語句來進行處理。這里就不進行介紹了。
