“花前对酒不忍触,共粉泪,两簌簌”
今天在用使用SSM框架的程序中调用存储过程.但是在控制台上打印出了调用存储过程的代码.可是数据表中就是没有改变.
存储过程:
CREATE DEFINER=`root`@`%` PROCEDURE `proc_wash_quantitative_pay_info_2_salespersoninfo_month_copy2`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE t_year VARCHAR(4);
DECLARE t_month VARCHAR(6);
DECLARE t_month_query VARCHAR(10);
DECLARE t_people_code VARCHAR(10);
DECLARE t_channel_id VARCHAR(15);
DECLARE t_empname VARCHAR(20);
DECLARE t_amount_pay_nocash VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT SUBSTR(a.`month`,1,4) as 'tyear',a.`month`,b.channel_id,b.people_code, a.empname,a.pay FROM original_offline_quantitative_pay_info a JOIN salespersoninfo b ON a.channel_id = b.channel_id AND a.empname=b.people_name;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO t_year,t_month,t_id,t_people_code,t_empname,t_amount_pay_nocash;
INSERT INTO salespersoninfo_amount_pay_noncash(`year`,`month`,id,salespersoninfo_id,salespersoninfo_name,amount_pay_noncash,insert_time,amount_pay_noncash2)VALUES(t_year,t_month,t_id,t_people_code,t_empname,t_amount_pay_nocash,NOW(),t_amount_pay_nocash);
UNTIL done END REPEAT;
CLOSE cur1;
END
可能是因为SSM框架不支持存储过程中的游标…改后的存储过程为
CREATE DEFINER=`root`@`%` PROCEDURE `proc_wash_quantitative_pay_info_2_salespersoninfo_month`(dateTime varchar(20))
BEGIN
INSERT INTO salespersoninfo_amount_pay_noncash (
`year`,
`month`,
id,
salespersoninfo_id,
salespersoninfo_name,
amount_pay_noncash,
insert_time,
amount_pay_noncash2
) SELECT
SUBSTR(a.`month`, 1, 4) AS 'tyear',
a.`month`,
b.id,
b.people_code,
a.empname,
a.pay,
now(),
a.pay
FROM
original_offline_quantitative_pay_info a
JOIN salespersoninfo b ON a.channel_id = b.channel_id
AND a.empname = b.people_name
AND a. MONTH = dateTime;
END