Mysql存储过程获取数据并作为行返回

Question

我有一个如下所示的存储过程。

在这个过程中，query应该根据name的匹配返回一行；但是当我尝试使用此 SR 时，查询未获取且 SR 未成功执行。请让我知道我的错误？

DROP TABLE TEMP;

CREATE TABLE TEMP
(
NAME VARCHAR(15)
);

INSERT INTO TEMP ( SELECT DISTINCT(NAME) FROM IMDETAILS);

DELIMITER $$
CREATE PROCEDURE INCIDENT()
BEGIN

DECLARE NAMEE VARCHAR(10);

DECLARE CUR1 CURSOR for SELECT NAME FROM TEMP;

open CUR1;

read_loop: LOOP

FETCH CUR1  INTO NAMEE;

SELECT NAMEE;

insert into incident values ((select distinct(NAME) from IMDETAILS where NAME=NAMEE),

(SELECT COUNT(*) FROM IMDETAILS WHERE NAME=NAMEE AND RESOLVED_DATE=(SELECT CURDATE()) AND STATUS='RESOLVED'),

(SELECT COUNT(*) FROM IMDETAILS WHERE NAME=NAMEE AND RESOLVED_WEEK = WEEK(CURDATE()) AND STATUS='RESOLVED'),

(SELECT COUNT(*) FROM IMDETAILS WHERE NAME=NAMEE AND RESOLVED_MONTH=(SELECT DATE_FORMAT(NOW(),'%M')) AND STATUS='RESOLVED'),

(SELECT POINTS FROM POINT WHERE IMCOUNT<=(SELECT COUNT(*) FROM IMDETAILS WHERE NAME=NAMEE AND RESOLVED_MONTH=(SELECT DATE_FORMAT(NOW(),'%M'))
AND STATUS='RESOLVED' )));

END LOOP;
CLOSE CUR1;

END $$;

DELIMITER;

Answer 1

我假设您的问题是从游标返回了 0 行。尝试修改您的代码以包含完成声明、继续处理程序声明和完成测试。例如

DROP PROCEDURE IF EXISTS INCIDENT;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `INCIDENT`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE NAMEE VARCHAR(10);
DECLARE CUR1 CURSOR for SELECT distinct NAME FROM TEMP;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DROP TABLE IF EXISTS INCIDENTS;
CREATE TABLE INCIDENTS(NAME VARCHAR(3),RESOLVED_DATE_COUNT INT);

open CUR1;
read_loop: LOOP
        FETCH CUR1  INTO NAMEE;

        if done then leave read_loop; end if;

        #SELECT NAMEE;
        insert into incidents values 
        (
        NAMEE,

        (SELECT COUNT(*) FROM IMDETAILS WHERE NAME=NAMEE AND RESOLVED_DATE= CURDATE() AND STATUS='RESOLVED')

        #(SELECT COUNT(*) FROM IMDETAILS WHERE NAME=NAMEE AND RESOLVED_WEEK = WEEK(CURDATE()) AND STATUS='RESOLVED'),
        #(SELECT COUNT(*) FROM IMDETAILS WHERE NAME=NAMEE AND RESOLVED_MONTH=(SELECT DATE_FORMAT(NOW(),'%M')) AND STATUS='RESOLVED'),
        #(SELECT POINTS FROM POINT WHERE IMCOUNT<=(SELECT COUNT(*) FROM IMDETAILS WHERE NAME=NAMEE AND RESOLVED_MONTH=(SELECT DATE_FORMAT(NOW(),'%M'))
        #AND STATUS='RESOLVED' ))
        );

END LOOP;
CLOSE CUR1;

END $$
DELIMITER ;

请注意，我还将游标选择语句更改为 select distinct，插入语句更改为 select namee(当您已经知道这一点时，重新阅读 imdetails 似乎没有多大意义)并修改了第一个 select 以删除不必要的 select for凝乳。所以给

DROP TABLE IF EXISTS IMDETAILS;

CREATE TABLE IMDETAILS(NAME VARCHAR(3),RESOLVED_DATE DATE, STATUS VARCHAR(10));
INSERT INTO IMDETAILS VALUES
('ABC','2016-10-10','RESOLVED'),('ABC','2016-10-10',NULL),('ABC','2016-10-10','RESOLVED'),('ABC','2016-10-10','RESOLVED'),
('DEF','2016-10-10',NULL),('DEF','2016-10-10',NULL),('DEF','2016-10-10',NULL),('DEF','2016-10-10','RESOLVED');

DROP TABLE IF EXISTS TEMP;
CREATE TABLE IF NOT EXISTS TEMP AS
SELECT NAME FROM IMDETAILS  WHERE NAME = 'ABC';

CALL INCIDENT();

SELECT * FROM INCIDENTS;

结果

+------+---------------------+
| NAME | RESOLVED_DATE_COUNT |
+------+---------------------+
| ABC  |                   3 |
+------+---------------------+