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

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




  1 CREATE PROCEDURE "BI_DM"."SP_GCYP_REPORT" (    
  2 startdate varchar(20)
  3 )
  4 dynamic result sets 1
  5 LANGUAGE SQL
  6 
  7 begin 
  8 declare L_Workdate_Start INT;
  9 declare D_Workdate_Start DATE;
 10 declare D_Workdate_End DATE;
 11 
 12 ---动态sql变量声明
 13 --declare sqlcreatetable varchar(5000);
 14 declare sqldelete varchar(5000);
 15 declare sqlinsert varchar(5000);
 16 declare sql1 varchar(5000);
 17 declare sql2 varchar(5000);
 18 declare sql3 varchar(5000);
 19 declare sqlinsert2 varchar(5000);
 20 
 21 declare monthnumber varchar(32);
 22 declare lastmonthnumber varchar(32);
 23 declare yearnumber varchar(32);
 24 
 25 --循环参数
 26 declare num1 varchar(32);
 27 declare num2 varchar(32);
 28 
 29 
 30     --------------定义输出游标，定义必须在变量赋值的上面，否则会报错。max合并前三列，和最后一列-------------------------------------
 31      declare clientcur cursor with return  
 32     for 
 33     select id,b.ITEMNAME
 34         ,max(本月采购数量) as 本月采购数量
 35         ,max(上月末库存数量) as 上月末库存数量
 36         ,max(本月采购总金额) as 本月采购总金额
 37         ,max(本月院内使用数量) as 本月院内使用数量
 38     from ***** a
 39     join ***** b on a.ID=b.itemid
 40     group by id,b.ITEMNAME;
 41     
 42     --------------定义输出游标-------------------------------------
 43    
 44 
 45 -- set D_Workdate_Start=to_date(substr(startdate,1,8)||'01 00:00:00','yyyy-mm-dd hh24:mi:ss');
 46   set D_Workdate_Start=to_date(startdate||' 00:00:00','yyyy-mm-dd hh24:mi:ss');
 47 set D_Workdate_Start= truncate(D_Workdate_Start,'MM');--获取选取的月份第一天
 48  set D_Workdate_End=D_Workdate_Start + 1 month;
 49   
 50  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;
 51  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;
 52  set yearnumber=year(D_Workdate_Start);
 53 
 54 
 55  ---- ---- ----此为建表备用,如果没有该表则创建 ---- ---- ---- ----
 56  /*
 57  set sqlcreatetable =' create table ******
 58 (ID varchar(128)
 59 ,ITEMNAME varchar(128)
 60 ,本月采购数量 varchar(128)
 61 ,上月末库存数量  varchar(128)
 62 ,本月采购总金额  varchar(128)
 63 ,本月院内使用数量 varchar(128)
 64 )';
 65 
 66 if ((select count(1) from syscat.tables where TABNAME='*****')=0)
 67     then
 68  PREPARE create1 FROM sqlcreatetable;
 69  EXECUTE create1;   
 70 end if;*/
 71  ---- ---- ----此为建表备用,如果没有该表则创建 ---- ---- ---- ----
 72 
 73 
 74 
 75 --------------循环算每个月库存-------------------------------------
 76 set sql2=' ';
 77 set num1=to_number(monthnumber);
 78 
 79  while num1 > 0 do  
 80   set num2=case when to_char(num1)<10 then 0||to_char(num1) else to_char(num1) end;
 81  
 82   set sql2=sql2||'M'||num2||'I_Q 
 83     - M'||num2||'O_Q
 84     + ';    
 85     set num1=num1-1;
 86 end while;  
 87 ------------------去掉最后的加号---------------------------------
 88 set sql2=substr(sql2,1,length(sql2)-2);
 89 --------------循环算每个月库存-------------------------------------
 90 
 91 
 92 --------------插入拼接的三列数据-------------------------------
 93 set sql1='M'||monthnumber||'I_Q as 本月采购数量,';
 94 set sql2=sql2||' + M00E_Q as 上月末库存数量,'; -----最后加上年初
 95 set sql3='M'||monthnumber||'I_S as 本月采购总金额,';
 96 
 97 if to_number(monthnumber)=1   ---如果是1月，执行用年初库存数量
 98     then
 99     set sql2='M00E_Q as 上月末库存数量,'; 
100 
101 end if; 
102 ---------------------------------------------------
103  set sqldelete='delete from ********';
104  set sqlinsert='insert into  ********(ID,本月采购数量,上月末库存数量,本月采购总金额,本月院内使用数量)
105 select ITEMID,'||sql1||sql2||sql3||'0
106 from SSA_HIS.INV_DRUG_MONTH 
107 where  ORGID_PL=''010101030201'' 
108 and ITEMID in (
109                     select a.ITEMID
110                     from ********* a 
111                     where NEGOTIATESMEDICINEFLAG=1)
112      and year='||yearnumber;
113 --------------插入拼接的三列数据-------------------------------
114 
115 
116 
117 ------------插入本月院内使用数量-----------------------------------
118 
119  set D_Workdate_Start=to_date(D_Workdate_Start,'yyyy-mm-dd');
120  set D_Workdate_End=to_date(D_Workdate_End,'yyyy-mm-dd');
121  set sqlinsert2='insert into  *********(ID,本月院内使用数量)
122 SELECT b.ITEMID
123     , SUM(b.Quantity ) Quantity
124 from (
125         select ITEMID
126         from *********
127         where ItemID in (select ItemID
128                                         from ******** a
129                                         where NEGOTIATESMEDICINEFLAG=1    )
130         and  CateID like ''01%''                                              
131                                         )a 
132        join ******** b on a.ITEMID =b.ITEMID 
133 where  b.BillDate > to_date('''||D_Workdate_Start||''',''yyyy-mm-dd'') 
134    AND b.BillDate <= to_date('''||D_Workdate_End||''',''yyyy-mm-dd'') 
135     group by b.ItemID ';
136 ------------插入本月院内使用数量-----------------------------------
137 
138  PREPARE delete1 FROM sqldelete; --清除历史记录
139  EXECUTE delete1;   
140  PREPARE insert1 FROM sqlinsert;--插入月库存
141  EXECUTE insert1;      
142  PREPARE insert2 FROM sqlinsert2;--插入使用量
143  EXECUTE insert2;      
144 
145 
146 
147            
148 
149 open clientcur;
150 
151 end

 

posted @ 2022-05-24 17:07  管风琴在角落  阅读(768)  评论(0)  编辑  收藏  举报