|
We'll allow ROW type variables as FETCH targets:
where cur is a CURSOR and rec is a ROW type SP variable.
Note, currently an attempt to use FETCH for a ROW type variable returns this error:
ERROR 1328 (HY000): Incorrect number of FETCH variables
|
FETCH from a cursor cur into a ROW variable rec will work as follows:
- The number of fields in cur must match the number of fields in rec. Otherwise, an error is reported.
- Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
- Field names in rec are not important and can differ from field names in cur.
A complete example for sql_mode=ORACLE:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, b VARCHAR(32));
|
INSERT INTO t1 VALUES (10,'b10');
|
INSERT INTO t1 VALUES (20,'b20');
|
INSERT INTO t1 VALUES (30,'b30');
|
|
SET sql_mode=oracle;
|
DROP PROCEDURE IF EXISTS p1;
|
DELIMITER $$
|
CREATE PROCEDURE p1 AS
|
rec ROW(a INT, b VARCHAR(32));
|
CURSOR c IS SELECT a,b FROM t1;
|
BEGIN
|
OPEN c;
|
LOOP
|
FETCH c INTO rec;
|
EXIT WHEN c%NOTFOUND;
|
SELECT ('rec=(' || rec.a ||','|| rec.b||')');
|
END LOOP;
|
CLOSE c;
|
END;
|
$$
|
DELIMITER ;
|
CALL p1();
|
A complete example for sql_mode=DEFAULT:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, b VARCHAR(32));
|
INSERT INTO t1 VALUES (10,'b10');
|
INSERT INTO t1 VALUES (20,'b20');
|
INSERT INTO t1 VALUES (30,'b30');
|
|
SET sql_mode=DEFAULT;
|
DROP PROCEDURE IF EXISTS p1;
|
DELIMITER $$
|
CREATE PROCEDURE p1()
|
BEGIN
|
DECLARE done INT DEFAULT FALSE;
|
DECLARE rec ROW(a INT, b VARCHAR(32));
|
DECLARE c CURSOR FOR SELECT a,b FROM t1;
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
OPEN c;
|
read_loop:
|
LOOP
|
FETCH c INTO rec;
|
IF done THEN
|
LEAVE read_loop;
|
END IF;
|
SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
|
END LOOP;
|
CLOSE c;
|
END;
|
$$
|
DELIMITER ;
|
CALL p1();
|
|
[MDEV-12007]