Java代码调用存储过程没反应

冷不防 2022-06-07 11:25 315阅读 0赞

**“花前对酒不忍触,共粉泪,两簌簌”**  
今天在用使用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