时间戳

首页 > 解决方案 > 存储过程不选择数据

mysql - 存储过程不选择数据

问题描述

我试图在数据库控制台中手动执行选择查询然后它给了我结果。但是当我在过程中使用相同的选择查询时它不起作用。

错误 ：

错误 1329 (02000)：无数据 - 提取、选择或处理零行

代码 ：

DELIMITER $$
DROP PROCEDURE IF EXISTS abc_19;
CREATE PROCEDURE abc_19()
BEGIN
    DECLARE lc_current_time DATETIME;
    DECLARE lc_calc_value INT;
    DECLARE auto_assign TINYINT;
    DECLARE total_sum INT;
    DECLARE check_count INT;
    BEGIN
        DECLARE select_cursor CURSOR FOR SELECT count(id) as count,A.auto_assign, B.sum, truncate(B.sum*100.00/100,0) as check_count FROM cli_group_number A INNER JOIN (Select auto_assign, count(id) sum from cli_group_number where cli_group_id = 5 Group by auto_assign) B on A.auto_assign = B.auto_assign WHERE cli_group_id = 5 and sip_from_uri ='' Group by sip_from_uri, A.auto_assign;
        SET lc_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');

        OPEN select_cursor;
        LOOP
            FETCH select_cursor INTO lc_calc_value,auto_assign,total_sum,check_count;
            IF lc_calc_value <= check_count THEN
                insert into report(current_date,alert_id,alert_name,type,status,email,trunk_cli_id,triggered_value,threshold_value) values (lc_current_time,19,'CLI',4,1,'abc@ghi.com',5,check_count,lc_calc_value);
                INSERT INTO mails (`userid`,`date`,`subject`,`body`,`from`,`to`,`status`,`parent_id`) VALUES (1,lc_current_time,'Alarm : CLI',concat('Hello Admin,
                                                Name : abc,
                                                Type : def,
                                                Threshold : 100.00
                                                Period : 60
                                                Group : test 
                                                Value : ',lc_calc_value),'def@ghi.com','abc@ghi.com',1,0);
            END IF;
        END LOOP;
        CLOSE select_cursor;
    END;
END$$

DELIMITER ;

标签： mysql

解决方案

当您尝试阅读结尾时，游标会引发异常。

您的代码中有一个无限循环。

例外是使您免于这样做。

捕获异常，以便您可以优雅地退出循环。

BEGIN
    -- create a variable to track whether the cursor is out of rows
    DECLARE done TINYINT DEFAULT FALSE;
    DECLARE select_cursor CURSOR FOR SELECT count(id) as count,A.auto_assign, B.sum, truncate(B.sum*100.00/100,0) as check_count FROM cli_group_number A INNER JOIN (Select auto_assign, count(id) sum from cli_group_number where cli_group_id = 5 Group by auto_assign) B on A.auto_assign = B.auto_assign WHERE cli_group_id = 5 and sip_from_uri ='' Group by sip_from_uri, A.auto_assign;
    -- create an exception handler to catch the "no data" condition and flip the value of "done"
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET lc_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');

    OPEN select_cursor;
-- label the loop
my_made_up_loop_label:
    LOOP
        FETCH select_cursor INTO lc_calc_value,auto_assign,total_sum,check_count;
        -- test whether the exception handler has caught the exception and flipped the value, and leave the loop if so; this requires a label for the loop
        IF done IS TRUE THEN
          LEAVE my_made_up_loop_label;
        END IF;
        IF lc_calc_value ...

推荐阅读