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

特此记录下，备以后查阅。
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;