Oracle存储过程：游标与动态SQL
1.创建包+游标
Sql代码
CREATE OR REPLACE PACKAGE RefCursor
IS type t_RefCursor
IS
ref CURSOR;
END RefCursor;
2.创建存储过程，定义in,out参数。
Sql代码
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_o