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();

SELECT...INTO Examples
----------------------

A SELECT...INTO example for sql_mode=DEFAULT:

SET sql_mode=DEFAULT;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
 DECLARE rec1 ROW(a INT, b VARCHAR(32));
 SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();

The above example returns:

+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
|     10 | b10    |
+--------+--------+

A SELECT...INTO example for sql_mode=ORACLE:

SET sql_mode=ORACLE;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
 rec1 ROW(a INT, b VARCHAR(32));
BEGIN
 SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();

The above example returns:

+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
|     10 | b10    |
+--------+--------+

An example for sql_mode=ORACLE using table%ROWTYPE variables as SELECT..INTO
targets:

SET sql_mode=ORACLE;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
 rec1 t1%ROWTYPE;
BEGIN
 SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();

The above example returns:

+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
|     10 | b10    |
+--------+--------+

An example for sql_mode=ORACLE using cursor%ROWTYPE variables as SELECT..INTO
targets:

SET sql_mode=ORACLE;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
 CURSOR cur1 IS SELECT * FROM t1;
 rec1 cur1%ROWTYPE;
BEGIN
 SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();

The above example returns:

+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
|     10 | b10    |
+--------+--------+

URL: https://mariadb.com/kb/en/row/https://mariadb.com/kb/en/row/