mysql存储过程之cursor
CREATE PROCEDURE curdemo()BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;
嵌套cursor的例子
这里用一个临时cur2做暂存DROP PROCEDURE IF EXISTS get_major_info_for_all_dept;DELIMITER //
-- Create procedure get_major_info_for_all_dept()
CREATE PROCEDURE get_major_info_for_all_dept()
BEGIN
DECLARE dept_id VARCHAR(3) ;
DECLARE major_description VARCHAR(50) ;
DECLARE concat_description VARCHAR(60);
DECLARE flag1 VARCHAR(5) DEFAULT 'START' ;
DECLARE flag2 VARCHAR(5) DEFAULT 'START' ;
-- Declare cursor for outside looping purpose to get all departments from the dept table
DECLARE getDept CURSOR FOR
SELECT deptid
FROM dept
ORDER BY deptid;
--Declare cursor for getting each and every major description of each department.
DECLARE getMajorsFromEachDept CURSOR FOR
SELECT description
FROM major
WHERE deptid = dept_id;
-- Record not found means for inner loop no more major record left for that dept_id and for outer loop no more record left for dept-id
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag1 = 'END';
-- Open cursor
OPEN getDept;
-- loop to extract each major of each department and insert into messages table
-- until no record left.
WHILE flag1<>'END' DO
-- to get deptid
FETCH getDept INTO dept_id;
IF flag1 <> 'END' THEN
OPEN getMajorsFromEachDept;
SET flag2= flag1;
WHILE flag1 <> 'END' DO
FETCH getMajorsFromEachDept INTO major_description;
IF flag1 <> 'END' THEN
SET concat_description = CONCAT(dept_id,'*',major_description);
INSERT INTO messages
VALUES (concat_description);
END IF;
END WHILE;
-- free memory
CLOSE getMajorsFromEachDept;
SET flag1 = flag2;
END IF;
END WHILE;
-- Loop end.
-- free memory
CLOSE getDept;
--- Display all records of Messages Table---------
SELECT * FROM messages;
END;
//
DELIMITER ;
另一种cursor方案
CREATE PROCEDURE curdemo()BEGIN
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
OPEN cur1;
OPEN cur2;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
END;
CLOSE cur1;
CLOSE cur2;
END
页:
[1]