MySQL在存储过程中使用游标

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;

 

MySQL在存储过程中使用游标

标签：exist   art   open   cti   处理   comment   begin   state   not found