db2存储过程 动态拼接sql 、输出数据集示例

*****部分都是表名。因为隐私关系，替换为*了。  




  1 CREATE PROCEDURE "BI_DM"."SP_GCYP_REPORT" (    
startdate varchar(20)
)
dynamic result sets 1
LANGUAGE SQL

begin 
declare L_Workdate_Start INT;
declare D_Workdate_Start DATE;
declare D_Workdate_End DATE;

---动态sql变量声明
--declare sqlcreatetable varchar(5000);
declare sqldelete varchar(5000);
declare sqlinsert varchar(5000);
declare sql1 varchar(5000);
declare sql2 varchar(5000);
declare sql3 varchar(5000);
declare sqlinsert2 varchar(5000);

declare monthnumber varchar(32);
declare lastmonthnumber varchar(32);
declare yearnumber varchar(32);

--循环参数
declare num1 varchar(32);
declare num2 varchar(32);


    --------------定义输出游标，定义必须在变量赋值的上面，否则会报错。max合并前三列，和最后一列-------------------------------------
     declare clientcur cursor with return  
    for 
    select id,b.ITEMNAME
        ,max(本月采购数量) as 本月采购数量
        ,max(上月末库存数量) as 上月末库存数量
        ,max(本月采购总金额) as 本月采购总金额
        ,max(本月院内使用数量) as 本月院内使用数量
    from ***** a
    join ***** b on a.ID=b.itemid
    group by id,b.ITEMNAME;
    
    --------------定义输出游标-------------------------------------
   

-- set D_Workdate_Start=to_date(substr(startdate,1,8)||'01 00:00:00','yyyy-mm-dd hh24:mi:ss');
  set D_Workdate_Start=to_date(startdate||' 00:00:00','yyyy-mm-dd hh24:mi:ss');
set D_Workdate_Start= truncate(D_Workdate_Start,'MM');--获取选取的月份第一天
 set D_Workdate_End=D_Workdate_Start + 1 month;
  
 set monthnumber=case when month(D_Workdate_Start)<10 then 0||to_char(month(D_Workdate_Start)) else to_char(month(D_Workdate_Start)) end;
 set lastmonthnumber=case when (month(D_Workdate_Start)-1)<10 then 0||to_char(month(D_Workdate_Start)-1) else to_char(month(D_Workdate_Start)-1) end;
 set yearnumber=year(D_Workdate_Start);


 ---- ---- ----此为建表备用,如果没有该表则创建 ---- ---- ---- ----
 /*
 set sqlcreatetable =' create table ******
(ID varchar(128)
,ITEMNAME varchar(128)
,本月采购数量 varchar(128)
,上月末库存数量  varchar(128)
,本月采购总金额  varchar(128)
,本月院内使用数量 varchar(128)
)';

if ((select count(1) from syscat.tables where TABNAME='*****')=0)
    then
 PREPARE create1 FROM sqlcreatetable;
 EXECUTE create1;   
end if;*/
 ---- ---- ----此为建表备用,如果没有该表则创建 ---- ---- ---- ----



--------------循环算每个月库存-------------------------------------
set sql2=' ';
set num1=to_number(monthnumber);

 while num1 > 0 do  
  set num2=case when to_char(num1)<10 then 0||to_char(num1) else to_char(num1) end;
 
  set sql2=sql2||'M'||num2||'I_Q 
    - M'||num2||'O_Q
    + ';    
    set num1=num1-1;
end while;  
------------------去掉最后的加号---------------------------------
set sql2=substr(sql2,1,length(sql2)-2);
--------------循环算每个月库存-------------------------------------


--------------插入拼接的三列数据-------------------------------
set sql1='M'||monthnumber||'I_Q as 本月采购数量,';
set sql2=sql2||' + M00E_Q as 上月末库存数量,'; -----最后加上年初
set sql3='M'||monthnumber||'I_S as 本月采购总金额,';

if to_number(monthnumber)=1   ---如果是1月，执行用年初库存数量
    then
    set sql2='M00E_Q as 上月末库存数量,'; 

end if; 
---------------------------------------------------
 set sqldelete='delete from ********';
 set sqlinsert='insert into  ********(ID,本月采购数量,上月末库存数量,本月采购总金额,本月院内使用数量)
select ITEMID,'||sql1||sql2||sql3||'0
from SSA_HIS.INV_DRUG_MONTH 
where  ORGID_PL=''010101030201'' 
and ITEMID in (
                    select a.ITEMID
                    from ********* a 
                    where NEGOTIATESMEDICINEFLAG=1)
     and year='||yearnumber;
--------------插入拼接的三列数据-------------------------------



------------插入本月院内使用数量-----------------------------------

 set D_Workdate_Start=to_date(D_Workdate_Start,'yyyy-mm-dd');
 set D_Workdate_End=to_date(D_Workdate_End,'yyyy-mm-dd');
 set sqlinsert2='insert into  *********(ID,本月院内使用数量)
SELECT b.ITEMID
    , SUM(b.Quantity ) Quantity
from (
        select ITEMID
        from *********
        where ItemID in (select ItemID
                                        from ******** a
                                        where NEGOTIATESMEDICINEFLAG=1    )
        and  CateID like ''01%''                                              
                                        )a 
       join ******** b on a.ITEMID =b.ITEMID 
where  b.BillDate > to_date('''||D_Workdate_Start||''',''yyyy-mm-dd'') 
   AND b.BillDate <= to_date('''||D_Workdate_End||''',''yyyy-mm-dd'') 
    group by b.ItemID ';
------------插入本月院内使用数量-----------------------------------

 PREPARE delete1 FROM sqldelete; --清除历史记录
 EXECUTE delete1;   
 PREPARE insert1 FROM sqlinsert;--插入月库存
 EXECUTE insert1;      
 PREPARE insert2 FROM sqlinsert2;--插入使用量
 EXECUTE insert2;      



           

open clientcur;

end