Mysql存储过程中使用cursor

矫情吗；* 2021-09-25 15:46 340阅读 0赞

**一、表**  
学生表  
CREATE TABLE \`t\_student\` (  
   \`stuNum\` int(11) NOT NULL auto\_increment,  
   \`stuName\` varchar(20) default NULL,  
   \`birthday\` date default NULL,  
   PRIMARY KEY  (\`stuNum\`)  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  
学生分数表  
CREATE TABLE \`t\_stu\_score\` (  
   \`id\` int(11) NOT NULL auto\_increment,  
   \`stuNum\` int(11) default NULL,  
   \`score\` decimal(6,2) default NULL,  
   PRIMARY KEY  (\`id\`),  
   KEY \`FK\_t\_stu\_score\` (\`stuNum\`),  
   CONSTRAINT \`FK\_t\_stu\_score\` FOREIGN KEY (\`stuNum\`) REFERENCES \`t\_student\` (\`stuNum\`)  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  
学生详细表  
CREATE TABLE \`t\_stu\_detail\` (  
   \`id\` int(11) NOT NULL auto\_increment,  
   \`stuName\` varchar(20) default NULL,  
   \`score\` decimal(6,2) default NULL,  
   PRIMARY KEY  (\`id\`)  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  
将t\_Student和t\_stu\_score表中满足一定条件的数据插入到t\_stu\_detail中。  
  
  
**二、过程**  
DELIMITER &&  
 CREATE PROCEDURE proc\_AddStuDetail( IN p\_score DECIMAL(6,2) )  
  BEGIN  
DECLARE vstuNum INT;  
DECLARE vstuName VARCHAR(20);  
DECLARE vbirthday DATE;  
DECLARE vscore DECIMAL(6,2);  
DECLARE done INT;  
  
\-- 定义游标  
DECLARE stuCursor CURSOR  
FOR  
SELECT stuNum,stuName,birthday FROM t\_Student;  
  
\-- 定义结束标记  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
  
\-- 打开游标  
OPEN stuCursor;  
  
\-- 循环  
stuLoop:LOOP  
\-- 取游标中的数据  
FETCH stuCursor INTO vstuNum,vstuName,vbirthday;  
IF done = 1 THEN  
LEAVE stuLoop;  
END IF;  
  
IF DATE(vbirthday) >= '1990-03-01' THEN  
SELECT score INTO vscore FROM t\_stu\_score WHERE stuNum = vstuNum;  
IF vscore >= p\_score THEN  
INSERT INTO t\_stu\_detail VALUES(NULL,vstuNum,vscore);  
END IF;  
END IF;  
END LOOP stuLoop;  
  
\-- 关闭游标  
CLOSE stuCursor;  
  END  
&&  
DELIMITER ;  
  
三、调用过程  
CALL proc\_AddStuDetail(86);