#在存储过程中使用游标

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl(
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(50) NOT NULL,
    author VARCHAR(50) NOT NULL,
    PRIMARY KEY(id)
);

DROP PROCEDURE IF EXISTS sp7;
CREATE PROCEDURE sp7(INOUT rst VARCHAR(100), OUT rst_code INT, OUT rst_msg TEXT) COMMENT ‘在存储过程中使用游标‘
BEGIN
    DECLARE err_sta VARCHAR(5) DEFAULT ‘‘;
    DECLARE err_msg TEXT DEFAULT ‘‘;

    DECLARE csr_id INT; 
    DECLARE csr_title VARCHAR(50);
    DECLARE csr_author VARCHAR(50);
    DECLARE csr_done INT DEFAULT FALSE;#声明游标结束标识
    DECLARE csr CURSOR FOR SELECT id,title,author FROM tbl;#声明游标

    DECLARE EXIT HANDLER FOR SQLWARNING,SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 err_sta=RETURNED_SQLSTATE,err_msg=MESSAGE_TEXT;
        SET rst_code=FALSE;
        SET rst_msg=CONCAT(‘[Err] ‘,err_sta,‘ - ‘,err_msg);
        ROLLBACK;
    END;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET csr_done = TRUE;#游标NOT FOUND处理程序

    #SIGNAL SQLSTATE ‘42000‘ SET MESSAGE_TEXT = ‘when some value is not allowed‘;#自定义异常返回消息

    START TRANSACTION;

    OPEN csr;#开启游标
        lp: LOOP

            FETCH csr INTO csr_id,csr_title,csr_author;#按照查询表列的顺序

            IF csr_done THEN
                LEAVE lp;
            END IF;
            
            SET rst = CONCAT(csr_title,‘;‘,rst);

        END LOOP lp;
    CLOSE csr;#关闭游标

    #INSERT INTO tbl(title,author) VALUES(‘Learn CSharp‘,‘James‘);
    #INSERT INTO tbl(title,author) VALUES(NULL,‘Jen‘);#[Err] 23000 - Column ‘title‘ cannot be null

    COMMIT;

    SET rst_code=TRUE, rst_msg=‘‘;
END

SET @rst=‘‘;
CALL sp7(@rst,@rst_code,@rst_msg);
SELECT @rst as rst,@rst_code AS rst_code,@rst_msg as rst_msg;