关于存储过程

最新推荐文章于 2024-07-06 15:55:08 发布

原创最新推荐文章于 2024-07-06 15:55:08 发布 · 628 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

4 篇文章

订阅专栏

背景描述

要求：通过存储过程，将临时表里符合要求的数据依次添加到实时数据表里，在同事帮助下该存储过程得以完成。该存储过程如下：
注意点：

存储过程的加载顺序：
- 变量和条件声明
- Cursor声明
- 程序代码

使用loop循环
- loop循环可以与leave结合使用，这样可以直接退出循环，相当于java中的break或return

如果遇到摸个变量的值为字符串而无法解析，可以使用转义符将其设定为字符处，具体情况见下面代码103行

行
1
2
3	DELIMITER $$
4
5	USE `mrms2`$$
6
7	DROP PROCEDURE IF EXISTS `mrms_procedure_dataHandler`$$
8
9	CREATE DEFINER=`root`@`localhost` PROCEDURE `mrms_procedure_dataHandler`()
10	BEGIN
11
12	-- declare start ---------------------------------------------------------------------------
13	-- 创建临时总表的临时变量
14	DECLARE tmp_id INT(11) DEFAULT 0;
15	DECLARE tmp_count_code VARCHAR(11) DEFAULT 0;
16	DECLARE tmp_count_count INT(11) DEFAULT 0;
17
18	-- 创建临时单个编码表的几个临时变量
19	DECLARE tmp_enterpriseEnglishName VARCHAR(20);
20	DECLARE tmp_code VARCHAR(11);
21	DECLARE tmp_classes VARCHAR(20);
22	DECLARE tmp_typeName VARCHAR(20);
23	DECLARE tmp_medicineName VARCHAR(20);
24	DECLARE tmp_agent VARCHAR(20);
25	DECLARE tmp_guige VARCHAR(20);
26	DECLARE tmp_unit VARCHAR(20);
27	DECLARE tmp_total INT(11);
28	DECLARE tmp_unitPrice FLOAT;
29	DECLARE tmp_totalPrice FLOAT;
30	DECLARE tmp_maker VARCHAR(64);
31	DECLARE tmp_createTime TIMESTAMP;
32	DECLARE tmp_comment VARCHAR(20);
33	DECLARE tmp_status INT(11);
34	DECLARE tmp_isBackUp TINYINT(1);
35
36	-- 创建记录金额和数量的临时变量
37	DECLARE tmp_sum_price FLOAT DEFAULT 0;
38	DECLARE tmp_sum_count INT DEFAULT 0;
39
40	-- 创建记录每种药品，计划金额和数量的变量
41	DECLARE tmp_plan_price FLOAT DEFAULT 0;
42	DECLARE tmp_plan_count INT DEFAULT 0;
43
44	-- 创建循环的临时变量i
45	DECLARE i INT;
46	-- 创建循环结束的临时变量，其实等于临时总表的最后一个id值
47	DECLARE finishId INT;
48
49
50	DECLARE hasMain BOOLEAN DEFAULT FALSE;
51	DECLARE isTmpEmpty INT DEFAULT 0;
52
53	-- 创建临时总表的游标
54	DECLARE cur_count CURSOR FOR SELECT id,CODE,COUNT FROM tmp_table_code_count;
55	-- 创建单个编码表的游标
56	DECLARE cur_singleCURSOR FOR SELECT enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUpFROM tmp_table_single_code;
57
58	-- 创建临时表，用来存储每个编码的个数
59	DROP TABLE IF EXISTS tmp_table_code_count;
60	CREATE TEMPORARY TABLE tmp_table_code_count(
61	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
62	`code` VARCHAR(11),
63	`count` INT(11),
64	PRIMARY KEY (`id`)
65	)ENGINE=INNODB DEFAULT CHARSET=utf8;
66
67	-- 创建临时表，用来将按药品编码查询，按照CODE和IsBackUp排序后查询出来的结果临时放入此表中
68	DROP TABLE IF EXISTS tmp_table_single_code;
69	CREATE TEMPORARY TABLE tmp_table_single_code(
70	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
71	`enterpriseEnglishName` VARCHAR(20),
72	`code` VARCHAR(11),
73	`classes` VARCHAR(20),
74	`typeName` VARCHAR(20),
75	`medicineName` VARCHAR(20),
76	`agent` VARCHAR(20),
77	`guige` VARCHAR(20),
78	`unit` VARCHAR(20),
79	`total` INT(11),
80	`unitPrice` FLOAT,
81	`totalPrice` FLOAT,
82	`maker` VARCHAR(64),
83	`createTime` TIMESTAMP,
84	`comment` VARCHAR(20),
85	`status` INT(11),
86	`isBackUp` TINYINT(1),
87	PRIMARY KEY (`id`)
88	)ENGINE=INNODB DEFAULT CHARSET=utf8;
89
90	-- init data start ---------------------------------------------------------------------------
91	-- 写入临时总表数据
92	INSERT INTO tmp_table_code_count(CODE,COUNT) SELECT CODE,COUNT(1) cound FROM mrms_medicine_info_tmp GROUP BY CODE;
93
94	-- 记录最后数据的id，用于下面第一层循环的结束标记
95	SELECT MAX(id) INTO finishId FROM tmp_table_code_count;
96	SELECT COUNT(1) INTO isTmpEmpty FROM tmp_table_code_count;
97
98	-- handler start ---------------------------------------------------------------------------
99	-- 第1层循环
100	IF isTmpEmpty !=0 THEN
101	OPEN cur_count;
102	curCountLoop:LOOP
103	FETCH cur_count INTO tmp_id,tmp_count_code,tmp_count_count;
104	-- 依据临时总表的编码，把单个编码的所有记录写入临时单个编码表
105	INSERT INTO tmp_table_single_code(enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUp)
106	SELECT enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUpFROM mrms_medicine_info_tmp
107	WHERE CODE = '\'' + tmp_count_code + '\'' ORDER BY CODE,isBackUp;
108
109
110	-- 第2层循环
111	OPEN cur_single;
112	SET i = 0;
113	SET tmp_sum_price = 0;
114	SET tmp_plan_count = 0;
115	SET hasMain = FALSE;
116
117	WHILE i < tmp_count_count DO
118	FETCH cur_singleINTO tmp_enterpriseEnglishName,tmp_code,tmp_classes,tmp_typeName,tmp_medicineName,tmp_agent,tmp_guige,tmp_unit,tmp_total,tmp_unitPrice,tmp_totalPrice,tmp_maker,tmp_createTime,tmp_comment,tmp_status,tmp_isBackUp;
119
120	-- 获取计划中的金额和数量
121	SELECT totalPrice,COUNT INTO tmp_plan_price,tmp_plan_count FROM mrms_plan WHERE CODE = '\'' + tmp_code + '\'' AND enterpriseEnglishName = '\'' + tmp_enterpriseEnglishName + '\'' LIMIT 1;
122
123	-- 业务判断
124	IF i = 0 THEN
125	IF tmp_isBackUp =0 THEN
126	SET hasMain = TRUE;
127	IF tmp_sum_price < tmp_plan_price \|\| tmp_sum_count < tmp_plan_count THEN
128	INSERT INTO mrms_medicine_info_cur(enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUp)
129	VALUES (tmp_enterpriseEnglishName,tmp_code,tmp_classes,tmp_typeName,tmp_medicineName,tmp_agent,tmp_guige,tmp_unit,tmp_total,tmp_unitPrice,tmp_totalPrice,tmp_maker,tmp_createTime,tmp_comment,tmp_status,tmp_isBackUp);
130
131
132	SET tmp_sum_price = tmp_sum_price + tmp_totalPrice;
133	SET tmp_sum_count = tmp_sum_count + tmp_total;
134	END IF;
135
136	END IF;
137	ELSE
138	IF hasMain = TRUE && tmp_isBackUp = 1 && ( tmp_sum_price < tmp_plan_price \|\| tmp_sum_count < tmp_plan_count) THEN
139	INSERT INTO mrms_medicine_info_cur(enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUp)
140	VALUES (tmp_enterpriseEnglishName,tmp_code,tmp_classes,tmp_typeName,tmp_medicineName,tmp_agent,tmp_guige,tmp_unit,tmp_total,tmp_unitPrice,tmp_totalPrice,tmp_maker,tmp_createTime,tmp_comment,tmp_status,tmp_isBackUp);
141
142
143	SET tmp_sum_price = tmp_sum_price + tmp_totalPrice;
144	SET tmp_sum_count = tmp_sum_count + tmp_total;
145	END IF;
146
147	END IF;
148
149	-- i++
150	SET i = i + 1;
151	END WHILE;
152	CLOSE cur_single;
153
154	-- 第2层循环结束后清空临时表:tmp_table_single_code
155	DELETE FROM tmp_table_single_code;
156
157	-- 如果是最后一个，则执行完上面的逻辑后，就退出循环
158	IF tmp_id = finishId THEN
159	LEAVE curCountLoop;
160	END IF;
161
162
163	END LOOP;
164	CLOSE cur_count;
165
166	END IF;
167	-- end ---------------------------------------------------------------------------
168
169	END$$
170
171	DELIMITER ;