db2存储过程 动态拼接sql 、输出数据集示例
2024-10-21 18:55:10
*****部分都是表名。因为隐私关系，替换为*了。
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
最新文章
- 【leetcode】Fraction to Recurring Decimal
- 彻底弄明白之java多线程中的volatile
- SAP BASIS日常需要做的工作
- C++中静态数据成员
- 关于this指向思考
- JAVA 静态代码块
- Java日期处理类的lenient属性
- hdu-5703 Desert(水题)
- Linux下Apache与Tomcat的完全分布式集群配置（负载均衡）
- android studio adb 打不开
- angularjs中sortable的使用
- SGU 194 Reactor Cooling ——网络流
- Spring事务隔离级别
- C++面向对象作业1
- jenkins 简单实现php集成上线部署
- Unit 3.标签的分类和嵌套规则
- 本地Linux备份服务器[Client]定期备份云服务器[Server]上的文件(下)
- GIT命令介绍
- python之网络通信协议
- Winio驱动在64位windows下无法使用的解决方法