Oracle 存储过程：游标与动态SQL

婵娟9428

特此记录下，备以后查阅。
1.创建包+游标
CREATE OR REPLACE PACKAGE RefCursor
IS type t_RefCursor
IS
ref  CURSOR;
END RefCursor;

2.创建存储过程，定义in,out参数。

create or replace
PROCEDURE prc_stock_HistoryDetail(
-- 功能： 查询仓库的进出库记录与每笔结存数量。
--定义参数
p_stockcode IN VARCHAR2, -- 仓库编号
p_stockId   IN VARCHAR2, -- 仓库Id
p_fbegdate  IN VARCHAR2, -- 开始日期
p_fenddate  IN VARCHAR2, -- 截止日期
p_fitemcode IN VARCHAR2, -- 品种
cur_result out sys_refcursor -- 输出游标
)
IS
-- 定义变量
v_stockid   VARCHAR2(32); -- 仓库编号
v_stockName VARCHAR2(32); -- 仓库名称
v_stockCode VARCHAR2(32); -- 仓库名称
v_itemcode  VARCHAR2(32); -- 物资编码
v_instockDate DATE;       -- 出入库日期
v_in_store     NUMBER;        -- 入库数量
v_out_store    NUMBER;        -- 出库数量
v_lost_store   NUMBER;        -- 损耗数量
v_qc_store     NUMBER;        -- 期初数量
v_last_store   NUMBER;        -- 结存数量
v_last_qc_date VARCHAR(32);   -- 最后一次期初日期
-- 临时变量
v_temp_stockid varchar2(32); -- 仓库Id
v_temp_itemcode varchar2(32);-- 物资编码
v_temp_last_store number;    -- 结存

--定义游标
cur_query RefCursor.t_Refcursor;
v_sqlStmt string(10000);
v_sql VARCHAR2(2000);
BEGIN
v_sql :='select t.pk_id from yy_bd_stock t where t.fend =1 order by t.fcode asc';
-- 查询仓库指定日期的 进出库明细记录
v_sqlStmt := '  select tt.fstockid,tt.fcode,tt.fname,tt.fitemcode,tt.finstockdate,sum(tt.in_store) as in_store,sum(tt.out_store) as out_store,sum(tt.lost_store) as lost_store,sum(tt.last_store) as last_store
from (select t.fstockid,a.fcode,a.fname,t.fitemcode,t.finstockdate,decode(t.fiostatus,1,t.fqty,0) as in_store,
decode(t.fiostatus,2,t.fqty,0) as out_store,
decode(t.fiostatus,3,t.fqty,0) as lost_store ,
decode(t.fiostatus,1,t.fqty,0)-decode(t.fiostatus,2,t.fqty,0)-decode(t.fiostatus,3,t.fqty,0)) as last_store
from yy_store_storage t                           
left join yy_bd_stock a on t.fstockid = a.pk_id
where t.fisreset = 0 and t.fitemcode is not null ' ;
-- 仓库编号
if p_stockId is not null then
v_sqlStmt := v_sqlStmt || ' and t.fstockid='''||p_stockId||'''';
end if;
-- 品种
if p_fitemcode is not null then
v_sqlStmt := v_sqlStmt || ' and t.fitemcode='''||p_fitemcode||'''';
end if;  
-- 起始日期
if p_fbegdate is not null then
v_sqlStmt := v_sqlStmt || ' and t.finstockdate >=to_date('''||p_fbegdate||''',''yyyy-MM-dd'')';
end if;
-- 截止日期
if p_fenddate is not null then
v_sqlStmt := v_sqlStmt || ' and t.finstockdate < to_date('''||p_fenddate||''',''yyyy-MM-dd'')';
end if;
v_sqlStmt := v_sqlStmt || ' order by a.fcode asc,t.fitemcode, t.finstockdate asc  
) tt
group by tt.fstockid,tt.fcode,tt.fname,tt.fitemcode,tt.finstockdate
order by tt.fcode asc,tt.fitemcode, tt.finstockdate asc  ';
dbms_output.put_line('=====sqlStmt:'||v_sqlStmt);
-- 打开游标 遍历仓库表。
OPEN cur_query FOR v_sqlStmt;
-- 初始化临时变量
v_temp_stockId := '';
v_temp_itemcode := '';
v_temp_last_store := 0;
LOOP
-- 查询库存表中的进出库记录。获取每笔记录的 进库数量、出库数量、损耗数量、结存数量。然后存入临时表中。
FETCH cur_query
INTO v_stockid,v_stockCode, v_stockName, v_itemcode, v_instockDate, v_in_store, v_out_store, v_lost_store,v_last_store;
dbms_output.put_line('v_stockid:'||v_stockName||',fitemcode:'||v_itemcode||',instockdate:'||v_instockDate);
EXIT WHEN cur_query%notfound;
if (v_stockid is not null ) and  (v_itemcode is not null) then     
-- 遍历记录，如果不是同一仓库与品种.则从新获取对应仓库品种中的期初库存或上日结存数量
if (v_temp_stockid is null or v_temp_stockid!= v_stockid) and (v_temp_itemcode is null or  v_temp_itemcode!= v_itemcode) then
v_temp_stockid := v_stockid;
v_temp_itemcode := v_itemcode;
v_temp_last_store := 0;
-- 调用存储过程，获取结存数量
prc_stock_historystore(v_temp_stockid,p_fbegdate,v_temp_itemcode,v_qc_store);
dbms_output.put_line('期初数量:'||v_qc_store);
v_temp_last_store := nvl(v_qc_store,0)+ nvl(v_last_store,0);
else
v_temp_last_store := v_temp_last_store + nvl(v_last_store,0);
end if;      
-- 通过结存数量，计算每笔的期初数量 -- 结存=期初+入库-出库-损耗      
insert into yy_temp_store_detail (fstockid, fstockcode, finstorkdate, fitemcode, in_store, out_store, lost_store, last_store)
values (v_stockid, '', v_instockDate, v_itemcode, v_in_store, v_out_store, v_lost_store, v_temp_last_store);
end if;   
--dbms_output.put_line('name:'||v_stockName||',itemcode:'||v_itemcode||',date:'||v_instockDate||',in_store:'||v_in_store||',out_store:'||v_out_store);
END LOOP;
CLOSE cur_query;
-- 返回集合
open cur_result for select a.fname,t.finstorkdate,t.fitemcode,t.in_store,t.out_store,t.lost_store,t.last_store
from yy_temp_store_detail t
left join yy_bd_stock a on t.fstockid = a.pk_id
order by a.fcode asc,t.fitemcode, t.finstorkdate asc ;   
END;