summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/suite/compat/oracle/r/sp-code.result242
-rw-r--r--mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result325
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-code.test128
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test339
-rw-r--r--sql/sp_head.cc29
-rw-r--r--sql/sp_head.h33
-rw-r--r--sql/sp_pcontext.cc12
-rw-r--r--sql/sp_pcontext.h13
-rw-r--r--sql/sql_lex.cc145
-rw-r--r--sql/sql_lex.h84
-rw-r--r--sql/sql_yacc.yy1
-rw-r--r--sql/sql_yacc_ora.yy81
-rw-r--r--sql/structs.h17
13 files changed, 1410 insertions, 39 deletions
diff --git a/mysql-test/suite/compat/oracle/r/sp-code.result b/mysql-test/suite/compat/oracle/r/sp-code.result
index 5f21da6eb16..557906a545f 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -1029,11 +1029,11 @@ SHOW PROCEDURE CODE p1;
Pos Instruction
0 cpush cur1@0
1 cpush cur2@1
-2 cursor_copy_struct rec1@0
+2 cursor_copy_struct cur1 rec1@0
3 set rec1@0 NULL
-4 cursor_copy_struct rec2@1
+4 cursor_copy_struct cur1 rec2@1
5 set rec2@1 NULL
-6 cursor_copy_struct rec3@2
+6 cursor_copy_struct cur2 rec3@2
7 set rec3@2 NULL
8 set rec1.a@0["a"] 10
9 set rec1.b@0["b"] 'bbb'
@@ -1041,3 +1041,239 @@ Pos Instruction
11 cpop 2
DROP PROCEDURE p1;
DROP TABLE t1;
+#
+# MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
+#
+CREATE PROCEDURE p1
+AS
+CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
+CURSOR cur1 IS SELECT 10 AS a, 'b0' AS b;
+CURSOR cur2 IS SELECT 10 AS a, 'b0' AS b;
+BEGIN
+FOR rec1 IN cur1
+LOOP
+SELECT rec1.a, rec1.b;
+rec1.a:= 11;
+rec1.b:= 'b1';
+SELECT rec1.a, rec1.b;
+END LOOP;
+FOR rec0 IN cur0
+LOOP
+rec0.a:= 10;
+rec0.b:='b0';
+END LOOP;
+FOR rec2 IN cur2
+LOOP
+rec2.a:= 10;
+rec2.b:='b0';
+END LOOP;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cpush cur0@0
+1 cpush cur1@1
+2 cpush cur2@2
+3 cursor_copy_struct cur1 rec1@0
+4 copen cur1@1
+5 cfetch cur1@1 rec1@0
+6 jump_if_not 13(13) "cur1"%FOUND
+7 stmt 0 "SELECT rec1.a, rec1.b"
+8 set rec1.a@0["a"] 11
+9 set rec1.b@0["b"] 'b1'
+10 stmt 0 "SELECT rec1.a, rec1.b"
+11 cfetch cur1@1 rec1@0
+12 jump 6
+13 cursor_copy_struct cur0 rec0@1
+14 copen cur0@0
+15 cfetch cur0@0 rec0@1
+16 jump_if_not 21(21) "cur0"%FOUND
+17 set rec0.a@1["a"] 10
+18 set rec0.b@1["b"] 'b0'
+19 cfetch cur0@0 rec0@1
+20 jump 16
+21 cursor_copy_struct cur2 rec2@2
+22 copen cur2@2
+23 cfetch cur2@2 rec2@2
+24 jump_if_not 29(29) "cur2"%FOUND
+25 set rec2.a@2["a"] 10
+26 set rec2.b@2["b"] 'b0'
+27 cfetch cur2@2 rec2@2
+28 jump 24
+29 cpop 3
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1
+AS
+CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
+BEGIN
+FOR rec0 IN cur0
+LOOP
+DECLARE
+CURSOR cur1 IS SELECT 11 AS a, 'b1' AS b;
+BEGIN
+rec0.a:= 11;
+rec0.b:= 'b0';
+FOR rec1 IN cur1
+LOOP
+rec1.a:= 11;
+rec1.b:= 'b1';
+DECLARE
+CURSOR cur2 IS SELECT 12 AS a, 'b2' AS b;
+BEGIN
+FOR rec2 IN cur2
+LOOP
+rec2.a:=12;
+rec2.b:='b2';
+END LOOP;
+END;
+END LOOP;
+END;
+END LOOP;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cpush cur0@0
+1 cursor_copy_struct cur0 rec0@0
+2 copen cur0@0
+3 cfetch cur0@0 rec0@0
+4 jump_if_not 29(29) "cur0"%FOUND
+5 cpush cur1@1
+6 set rec0.a@0["a"] 11
+7 set rec0.b@0["b"] 'b0'
+8 cursor_copy_struct cur1 rec1@1
+9 copen cur1@1
+10 cfetch cur1@1 rec1@1
+11 jump_if_not 26(26) "cur1"%FOUND
+12 set rec1.a@1["a"] 11
+13 set rec1.b@1["b"] 'b1'
+14 cpush cur2@2
+15 cursor_copy_struct cur2 rec2@2
+16 copen cur2@2
+17 cfetch cur2@2 rec2@2
+18 jump_if_not 23(23) "cur2"%FOUND
+19 set rec2.a@2["a"] 12
+20 set rec2.b@2["b"] 'b2'
+21 cfetch cur2@2 rec2@2
+22 jump 18
+23 cpop 1
+24 cfetch cur1@1 rec1@1
+25 jump 11
+26 cpop 1
+27 cfetch cur0@0 rec0@0
+28 jump 4
+29 cpop 1
+DROP PROCEDURE p1;
+#
+# MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
+#
+CREATE PROCEDURE p1
+AS
+BEGIN
+FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+LOOP
+SELECT rec1.a, rec1.b;
+rec1.a:= 11;
+rec1.b:= 'b1';
+SELECT rec1.a, rec1.b;
+END LOOP;
+FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+LOOP
+rec0.a:= 10;
+rec0.b:='b0';
+END LOOP;
+FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+LOOP
+rec2.a:= 10;
+rec2.b:='b0';
+END LOOP;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cpush [implicit_cursor]@0
+1 cursor_copy_struct [implicit_cursor] rec1@0
+2 copen [implicit_cursor]@0
+3 cfetch [implicit_cursor]@0 rec1@0
+4 jump_if_not 11(11) "[implicit_cursor]"%FOUND
+5 stmt 0 "SELECT rec1.a, rec1.b"
+6 set rec1.a@0["a"] 11
+7 set rec1.b@0["b"] 'b1'
+8 stmt 0 "SELECT rec1.a, rec1.b"
+9 cfetch [implicit_cursor]@0 rec1@0
+10 jump 4
+11 cpop 1
+12 cpush [implicit_cursor]@0
+13 cursor_copy_struct [implicit_cursor] rec0@1
+14 copen [implicit_cursor]@0
+15 cfetch [implicit_cursor]@0 rec0@1
+16 jump_if_not 21(21) "[implicit_cursor]"%FOUND
+17 set rec0.a@1["a"] 10
+18 set rec0.b@1["b"] 'b0'
+19 cfetch [implicit_cursor]@0 rec0@1
+20 jump 16
+21 cpop 1
+22 cpush [implicit_cursor]@0
+23 cursor_copy_struct [implicit_cursor] rec2@2
+24 copen [implicit_cursor]@0
+25 cfetch [implicit_cursor]@0 rec2@2
+26 jump_if_not 31(31) "[implicit_cursor]"%FOUND
+27 set rec2.a@2["a"] 10
+28 set rec2.b@2["b"] 'b0'
+29 cfetch [implicit_cursor]@0 rec2@2
+30 jump 26
+31 cpop 1
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1
+AS
+BEGIN
+FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+LOOP
+rec0.a:= 11;
+rec0.b:= 'b0';
+FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+LOOP
+rec1.a:= 11;
+rec1.b:= 'b1';
+FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+LOOP
+rec2.a:=12;
+rec2.b:='b2';
+END LOOP;
+END LOOP;
+END LOOP;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cpush [implicit_cursor]@0
+1 cursor_copy_struct [implicit_cursor] rec0@0
+2 copen [implicit_cursor]@0
+3 cfetch [implicit_cursor]@0 rec0@0
+4 jump_if_not 29(29) "[implicit_cursor]"%FOUND
+5 set rec0.a@0["a"] 11
+6 set rec0.b@0["b"] 'b0'
+7 cpush [implicit_cursor]@1
+8 cursor_copy_struct [implicit_cursor] rec1@1
+9 copen [implicit_cursor]@1
+10 cfetch [implicit_cursor]@1 rec1@1
+11 jump_if_not 26(26) "[implicit_cursor]"%FOUND
+12 set rec1.a@1["a"] 11
+13 set rec1.b@1["b"] 'b1'
+14 cpush [implicit_cursor]@2
+15 cursor_copy_struct [implicit_cursor] rec2@2
+16 copen [implicit_cursor]@2
+17 cfetch [implicit_cursor]@2 rec2@2
+18 jump_if_not 23(23) "[implicit_cursor]"%FOUND
+19 set rec2.a@2["a"] 12
+20 set rec2.b@2["b"] 'b2'
+21 cfetch [implicit_cursor]@2 rec2@2
+22 jump 18
+23 cpop 1
+24 cfetch [implicit_cursor]@1 rec1@1
+25 jump 11
+26 cpop 1
+27 cfetch [implicit_cursor]@0 rec0@0
+28 jump 4
+29 cpop 1
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
index 683b02c3b4f..04bb4298ada 100644
--- a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
+++ b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
@@ -976,3 +976,328 @@ HEX(rec1.c)
C3BF61
DROP PROCEDURE p1;
DROP TABLE t1;
+#
+# MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
+#
+# IN followed by a non-identifier
+CREATE PROCEDURE p1 AS
+CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+FOR rec IN 10
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOOP
+NULL;
+END LOOP;
+END' at line 6
+# IN followed by a quoted identifier: table.column
+CREATE PROCEDURE p1 AS
+CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+FOR rec IN c1.c2
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOOP
+NULL;
+END LOOP;
+END' at line 6
+# IN followed by a quoted identifier: .table.column
+CREATE PROCEDURE p1 AS
+CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+FOR rec IN .c1.c2
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOOP
+NULL;
+END LOOP;
+END' at line 6
+# IN followed by a quoted identifier: schema.table.column
+CREATE PROCEDURE p1 AS
+CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+FOR rec IN c1.c2.c3
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOOP
+NULL;
+END LOOP;
+END' at line 6
+# IN followed by an unknown cursor name
+CREATE PROCEDURE p1 AS
+CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+FOR rec IN c2
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+ERROR 42000: Undefined CURSOR: c2
+# Make sure "rec" shadows other declarations outside the loop
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10, 'b0');
+CREATE PROCEDURE p1 AS
+rec INT:=10;
+CURSOR c1 IS SELECT a,b FROM t1;
+BEGIN
+FOR rec IN c1
+LOOP
+SELECT rec.a;
+END LOOP;
+SELECT rec;
+END;
+$$
+CALL p1;
+rec.a
+10
+rec
+10
+DROP PROCEDURE p1;
+DROP TABLE t1;
+# Make sure "rec" is not visible after END LOOP
+CREATE PROCEDURE p1 AS
+CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+FOR rec IN c1
+LOOP
+NULL;
+END LOOP;
+rec.a:= 10;
+END;
+$$
+ERROR HY000: Unknown structured system variable or ROW routine variable 'rec'
+# Make sure that duplicate column names are not allowed
+CREATE PROCEDURE p1 AS
+CURSOR cur IS SELECT 'a' AS a, 'A' as a;
+BEGIN
+FOR rec IN cur
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+CALL p1;
+ERROR 42S21: Duplicate column name 'a'
+DROP PROCEDURE p1;
+# A complete working example
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10,'b0');
+INSERT INTO t1 VALUES (11,'b1');
+INSERT INTO t1 VALUES (12,'b2');
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+CREATE PROCEDURE p1 AS
+CURSOR cur IS SELECT a, b FROM t1;
+BEGIN
+FOR rec IN cur
+LOOP
+SELECT rec.a, rec.b;
+INSERT INTO t2 VALUES (rec.a, rec.b);
+rec.a:= rec.a + 1000;
+rec.b:= 'b' || rec.b;
+INSERT INTO t3 VALUES (rec.a, rec.b);
+END LOOP;
+END;
+$$
+CALL p1();
+rec.a rec.b
+10 b0
+rec.a rec.b
+11 b1
+rec.a rec.b
+12 b2
+SELECT * FROM t2;
+a b
+10 b0
+11 b1
+12 b2
+SELECT * FROM t3;
+a b
+1010 bb0
+1011 bb1
+1012 bb2
+DROP PROCEDURE p1;
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
+#
+# Parse error in the cursor SELECT statement
+CREATE PROCEDURE p1 AS
+BEGIN
+FOR rec IN (SELECT a, b FROM)
+LOOP
+SELECT rec.a, rec.b;
+END LOOP;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')
+LOOP
+SELECT rec.a, rec.b;
+END LOOP;
+END' at line 3
+# Make sure "rec" is not visible after END LOOP
+CREATE PROCEDURE p1 AS
+BEGIN
+FOR rec IN (SELECT 'test' AS a)
+LOOP
+NULL;
+END LOOP;
+rec.a:= 10;
+END;
+$$
+ERROR HY000: Unknown structured system variable or ROW routine variable 'rec'
+# Make sure "rec" is not visible inside the SELECT statement
+CREATE PROCEDURE p1 AS
+BEGIN
+FOR rec IN (SELECT rec)
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+CALL p1;
+ERROR 42S22: Unknown column 'rec' in 'field list'
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1 AS
+BEGIN
+FOR rec IN (SELECT rec.a)
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+CALL p1;
+ERROR 42S02: Unknown table 'rec' in field list
+DROP PROCEDURE p1;
+# Totally confusing name mixture
+CREATE TABLE rec (rec INT);
+INSERT INTO rec VALUES (10);
+CREATE PROCEDURE p1 AS
+BEGIN
+FOR rec IN (SELECT rec FROM rec)
+LOOP
+SELECT rec.rec;
+END LOOP;
+END;
+$$
+CALL p1;
+rec.rec
+10
+DROP PROCEDURE p1;
+DROP TABLE rec;
+# Make sure that duplicate column names are not allowed
+CREATE PROCEDURE p1 AS
+BEGIN
+FOR rec IN (SELECT 'a' AS a, 'A' as a)
+LOOP
+NULL;
+END LOOP;
+END;
+$$
+CALL p1;
+ERROR 42S21: Duplicate column name 'a'
+DROP PROCEDURE p1;
+# A complete working example
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10,'b0');
+INSERT INTO t1 VALUES (11,'b1');
+INSERT INTO t1 VALUES (12,'b2');
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+CREATE PROCEDURE p1 AS
+BEGIN
+FOR rec IN (SELECT a, b FROM t1)
+LOOP
+SELECT rec.a, rec.b;
+INSERT INTO t2 VALUES (rec.a, rec.b);
+rec.a:= rec.a + 1000;
+rec.b:= 'b'|| rec.b;
+INSERT INTO t3 VALUES (rec.a, rec.b);
+END LOOP;
+END;
+$$
+CALL p1();
+rec.a rec.b
+10 b0
+rec.a rec.b
+11 b1
+rec.a rec.b
+12 b2
+SELECT * FROM t2;
+a b
+10 b0
+11 b1
+12 b2
+SELECT * FROM t3;
+a b
+1010 bb0
+1011 bb1
+1012 bb2
+DROP PROCEDURE p1;
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+# A combination of explicit and implicit cursors
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10,'b1');
+INSERT INTO t1 VALUES (11,'b2');
+INSERT INTO t1 VALUES (12,'b3');
+CREATE PROCEDURE p1 AS
+BEGIN
+FOR rec1 IN (SELECT a, b FROM t1)
+LOOP
+DECLARE
+CURSOR cur2 IS SELECT a+1000 AS a, 'bb'||b AS b FROM t1 WHERE a=rec1.a AND b=rec1.b;
+BEGIN
+SELECT rec1.a, rec1.b;
+FOR rec2 IN cur2
+LOOP
+SELECT rec2.a, rec2.b;
+END LOOP;
+END;
+END LOOP;
+FOR rec1 IN (SELECT a,b FROM t1)
+LOOP
+FOR rec2 IN (SELECT a+2000 AS a,'bbb'||b AS b FROM t1 WHERE a=rec1.a AND b=rec1.b)
+LOOP
+SELECT rec2.a, rec2.b;
+END LOOP;
+END LOOP;
+END;
+$$
+CALL p1();
+rec1.a rec1.b
+10 b1
+rec2.a rec2.b
+1010 bbb1
+rec1.a rec1.b
+11 b2
+rec2.a rec2.b
+1011 bbb2
+rec1.a rec1.b
+12 b3
+rec2.a rec2.b
+1012 bbb3
+rec2.a rec2.b
+2010 bbbb1
+rec2.a rec2.b
+2011 bbbb2
+rec2.a rec2.b
+2012 bbbb3
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-code.test b/mysql-test/suite/compat/oracle/t/sp-code.test
index bc766e868fd..00c8109c038 100644
--- a/mysql-test/suite/compat/oracle/t/sp-code.test
+++ b/mysql-test/suite/compat/oracle/t/sp-code.test
@@ -789,3 +789,131 @@ DELIMITER ;$$
SHOW PROCEDURE CODE p1;
DROP PROCEDURE p1;
DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+ CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
+ CURSOR cur1 IS SELECT 10 AS a, 'b0' AS b;
+ CURSOR cur2 IS SELECT 10 AS a, 'b0' AS b;
+BEGIN
+ FOR rec1 IN cur1
+ LOOP
+ SELECT rec1.a, rec1.b;
+ rec1.a:= 11;
+ rec1.b:= 'b1';
+ SELECT rec1.a, rec1.b;
+ END LOOP;
+ FOR rec0 IN cur0
+ LOOP
+ rec0.a:= 10;
+ rec0.b:='b0';
+ END LOOP;
+ FOR rec2 IN cur2
+ LOOP
+ rec2.a:= 10;
+ rec2.b:='b0';
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+ CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
+BEGIN
+ FOR rec0 IN cur0
+ LOOP
+ DECLARE
+ CURSOR cur1 IS SELECT 11 AS a, 'b1' AS b;
+ BEGIN
+ rec0.a:= 11;
+ rec0.b:= 'b0';
+ FOR rec1 IN cur1
+ LOOP
+ rec1.a:= 11;
+ rec1.b:= 'b1';
+ DECLARE
+ CURSOR cur2 IS SELECT 12 AS a, 'b2' AS b;
+ BEGIN
+ FOR rec2 IN cur2
+ LOOP
+ rec2.a:=12;
+ rec2.b:='b2';
+ END LOOP;
+ END;
+ END LOOP;
+ END;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+ FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+ LOOP
+ SELECT rec1.a, rec1.b;
+ rec1.a:= 11;
+ rec1.b:= 'b1';
+ SELECT rec1.a, rec1.b;
+ END LOOP;
+ FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+ LOOP
+ rec0.a:= 10;
+ rec0.b:='b0';
+ END LOOP;
+ FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+ LOOP
+ rec2.a:= 10;
+ rec2.b:='b0';
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+ FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+ LOOP
+ rec0.a:= 11;
+ rec0.b:= 'b0';
+ FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+ LOOP
+ rec1.a:= 11;
+ rec1.b:= 'b1';
+ FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+ LOOP
+ rec2.a:=12;
+ rec2.b:='b2';
+ END LOOP;
+ END LOOP;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test b/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test
index b873ba8c11b..94db6b49cf4 100644
--- a/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test
+++ b/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test
@@ -1055,3 +1055,342 @@ DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
+--echo #
+
+--echo # IN followed by a non-identifier
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1 AS
+ CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+ FOR rec IN 10
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # IN followed by a quoted identifier: table.column
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1 AS
+ CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+ FOR rec IN c1.c2
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # IN followed by a quoted identifier: .table.column
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1 AS
+ CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+ FOR rec IN .c1.c2
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # IN followed by a quoted identifier: schema.table.column
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1 AS
+ CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+ FOR rec IN c1.c2.c3
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # IN followed by an unknown cursor name
+
+DELIMITER $$;
+--error ER_SP_CURSOR_MISMATCH
+CREATE PROCEDURE p1 AS
+ CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+ FOR rec IN c2
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Make sure "rec" shadows other declarations outside the loop
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10, 'b0');
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+ rec INT:=10;
+ CURSOR c1 IS SELECT a,b FROM t1;
+BEGIN
+ FOR rec IN c1
+ LOOP
+ SELECT rec.a;
+ END LOOP;
+ SELECT rec;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo # Make sure "rec" is not visible after END LOOP
+
+DELIMITER $$;
+--error ER_UNKNOWN_STRUCTURED_VARIABLE
+CREATE PROCEDURE p1 AS
+ CURSOR c1 IS SELECT 'test' AS a FROM DUAL;
+BEGIN
+ FOR rec IN c1
+ LOOP
+ NULL;
+ END LOOP;
+ rec.a:= 10;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Make sure that duplicate column names are not allowed
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+ CURSOR cur IS SELECT 'a' AS a, 'A' as a;
+BEGIN
+ FOR rec IN cur
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+--error ER_DUP_FIELDNAME
+CALL p1;
+DROP PROCEDURE p1;
+
+
+--echo # A complete working example
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10,'b0');
+INSERT INTO t1 VALUES (11,'b1');
+INSERT INTO t1 VALUES (12,'b2');
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+ CURSOR cur IS SELECT a, b FROM t1;
+BEGIN
+ FOR rec IN cur
+ LOOP
+ SELECT rec.a, rec.b;
+ INSERT INTO t2 VALUES (rec.a, rec.b);
+ rec.a:= rec.a + 1000;
+ rec.b:= 'b' || rec.b;
+ INSERT INTO t3 VALUES (rec.a, rec.b);
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+SELECT * FROM t2;
+SELECT * FROM t3;
+DROP PROCEDURE p1;
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
+--echo #
+
+--echo # Parse error in the cursor SELECT statement
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1 AS
+BEGIN
+ FOR rec IN (SELECT a, b FROM)
+ LOOP
+ SELECT rec.a, rec.b;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Make sure "rec" is not visible after END LOOP
+
+DELIMITER $$;
+--error ER_UNKNOWN_STRUCTURED_VARIABLE
+CREATE PROCEDURE p1 AS
+BEGIN
+ FOR rec IN (SELECT 'test' AS a)
+ LOOP
+ NULL;
+ END LOOP;
+ rec.a:= 10;
+END;
+$$
+DELIMITER ;$$
+
+--echo # Make sure "rec" is not visible inside the SELECT statement
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+BEGIN
+ FOR rec IN (SELECT rec)
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+--error ER_BAD_FIELD_ERROR
+CALL p1;
+DROP PROCEDURE p1;
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+BEGIN
+ FOR rec IN (SELECT rec.a)
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+--error ER_UNKNOWN_TABLE
+CALL p1;
+DROP PROCEDURE p1;
+
+--echo # Totally confusing name mixture
+
+CREATE TABLE rec (rec INT);
+INSERT INTO rec VALUES (10);
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+BEGIN
+ FOR rec IN (SELECT rec FROM rec)
+ LOOP
+ SELECT rec.rec;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE rec;
+
+
+--echo # Make sure that duplicate column names are not allowed
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+BEGIN
+ FOR rec IN (SELECT 'a' AS a, 'A' as a)
+ LOOP
+ NULL;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+--error ER_DUP_FIELDNAME
+CALL p1;
+DROP PROCEDURE p1;
+
+
+--echo # A complete working example
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10,'b0');
+INSERT INTO t1 VALUES (11,'b1');
+INSERT INTO t1 VALUES (12,'b2');
+CREATE TABLE t2 LIKE t1;
+CREATE TABLE t3 LIKE t1;
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+BEGIN
+ FOR rec IN (SELECT a, b FROM t1)
+ LOOP
+ SELECT rec.a, rec.b;
+ INSERT INTO t2 VALUES (rec.a, rec.b);
+ rec.a:= rec.a + 1000;
+ rec.b:= 'b'|| rec.b;
+ INSERT INTO t3 VALUES (rec.a, rec.b);
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+SELECT * FROM t2;
+SELECT * FROM t3;
+DROP PROCEDURE p1;
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+--echo # A combination of explicit and implicit cursors
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10,'b1');
+INSERT INTO t1 VALUES (11,'b2');
+INSERT INTO t1 VALUES (12,'b3');
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+BEGIN
+ FOR rec1 IN (SELECT a, b FROM t1)
+ LOOP
+ DECLARE
+ CURSOR cur2 IS SELECT a+1000 AS a, 'bb'||b AS b FROM t1 WHERE a=rec1.a AND b=rec1.b;
+ BEGIN
+ SELECT rec1.a, rec1.b;
+ FOR rec2 IN cur2
+ LOOP
+ SELECT rec2.a, rec2.b;
+ END LOOP;
+ END;
+ END LOOP;
+ FOR rec1 IN (SELECT a,b FROM t1)
+ LOOP
+ FOR rec2 IN (SELECT a+2000 AS a,'bbb'||b AS b FROM t1 WHERE a=rec1.a AND b=rec1.b)
+ LOOP
+ SELECT rec2.a, rec2.b;
+ END LOOP;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 2b5b1db5ddf..5b7e4f854e7 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -4207,7 +4207,10 @@ void
sp_instr_cursor_copy_struct::print(String *str)
{
sp_variable *var= m_ctx->find_variable(m_var);
+ const LEX_STRING *name= m_lex_keeper.cursor_name();
str->append(STRING_WITH_LEN("cursor_copy_struct "));
+ str->append(name->str, name->length);
+ str->append(' ');
str->append(var->name.str, var->name.length);
str->append('@');
str->append_ulonglong(m_var);
@@ -4665,3 +4668,29 @@ bool sp_head::add_open_cursor(THD *thd, sp_pcontext *spcont, uint offset,
sp_instr_copen(instructions(), spcont, offset);
return i == NULL || add_instr(i);
}
+
+
+bool sp_head::add_for_loop_open_cursor(THD *thd, sp_pcontext *spcont,
+ sp_variable *index,
+ const sp_pcursor *pcursor, uint coffset)
+{
+ sp_instr *instr_copy_struct=
+ new (thd->mem_root) sp_instr_cursor_copy_struct(instructions(),
+ spcont, pcursor->lex(),
+ index->offset);
+ if (instr_copy_struct == NULL || add_instr(instr_copy_struct))
+ return true;
+
+ sp_instr_copen *instr_copen=
+ new (thd->mem_root) sp_instr_copen(instructions(), spcont, coffset);
+ if (instr_copen == NULL || add_instr(instr_copen))
+ return true;
+
+ sp_instr_cfetch *instr_cfetch=
+ new (thd->mem_root) sp_instr_cfetch(instructions(),
+ spcont, coffset);
+ if (instr_cfetch == NULL || add_instr(instr_cfetch))
+ return true;
+ instr_cfetch->add_to_varlist(index);
+ return false;
+}
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 37ef4b6825f..8322a92636e 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -450,6 +450,28 @@ public:
List<sp_assignment_lex> *parameters);
/**
+ Generate an initiation code for a CURSOR FOR LOOP, e.g.:
+ FOR index IN cursor
+
+ The code generated by this method does the following during SP run-time:
+ - Initializes the index ROW-type variable from the cursor
+ (the structure is copied from the cursor to the index variable)
+ - The cursor gets opened
+ - The first records is fetched from the cursor to the variable "index".
+
+ @param thd - the current thread (for mem_root and error reporting)
+ @param spcont - the current parse context
+ @param index - the loop "index" ROW-type variable
+ @param pcursor - the cursor
+ @param coffset - the cursor offset
+ @retval true - on error (EOM)
+ @retval false - on success
+ */
+ bool add_for_loop_open_cursor(THD *thd, sp_pcontext *spcont,
+ sp_variable *index,
+ const sp_pcursor *pcursor, uint coffset);
+
+ /**
Returns true if any substatement in the routine directly
(not through another routine) modifies data/changes table.
@@ -771,10 +793,12 @@ private:
class sp_lex_cursor: public sp_lex_local, public Query_arena
{
+ LEX_STRING m_cursor_name;
public:
sp_lex_cursor(THD *thd, const LEX *oldlex, MEM_ROOT *mem_root_arg)
:sp_lex_local(thd, oldlex),
- Query_arena(mem_root_arg, STMT_INITIALIZED_FOR_SP)
+ Query_arena(mem_root_arg, STMT_INITIALIZED_FOR_SP),
+ m_cursor_name(null_lex_str)
{ }
sp_lex_cursor(THD *thd, const LEX *oldlex)
:sp_lex_local(thd, oldlex),
@@ -802,6 +826,8 @@ public:
thd->free_list= NULL;
return false;
}
+ const LEX_STRING *cursor_name() const { return &m_cursor_name; }
+ void set_cursor_name(const LEX_STRING &name) { m_cursor_name= name; }
};
@@ -974,6 +1000,11 @@ public:
{
m_lex->safe_to_cache_query= 0;
}
+
+ const LEX_STRING *cursor_name() const
+ {
+ return m_lex->cursor_name();
+ }
private:
LEX *m_lex;
diff --git a/sql/sp_pcontext.cc b/sql/sp_pcontext.cc
index 3a3ad0713b5..dd70c444d4e 100644
--- a/sql/sp_pcontext.cc
+++ b/sql/sp_pcontext.cc
@@ -621,6 +621,18 @@ const sp_pcursor *sp_pcontext::find_cursor(uint offset) const
}
+bool sp_pcursor::check_param_count_with_error(uint param_count) const
+{
+ if (param_count != (m_param_context ?
+ m_param_context->context_var_count() : 0))
+ {
+ my_error(ER_WRONG_PARAMCOUNT_TO_CURSOR, MYF(0), LEX_STRING::str);
+ return true;
+ }
+ return false;
+}
+
+
const Spvar_definition *
sp_variable::find_row_field(const LEX_STRING &var_name,
const LEX_STRING &field_name,
diff --git a/sql/sp_pcontext.h b/sql/sp_pcontext.h
index 6d8f5e1baf8..f6ed0ace60c 100644
--- a/sql/sp_pcontext.h
+++ b/sql/sp_pcontext.h
@@ -296,6 +296,7 @@ public:
{ }
class sp_pcontext *param_context() const { return m_param_context; }
class sp_lex_cursor *lex() const { return m_lex; }
+ bool check_param_count_with_error(uint param_count) const;
};
@@ -643,6 +644,18 @@ public:
const sp_pcursor *find_cursor(const LEX_STRING name,
uint *poff, bool current_scope_only) const;
+ const sp_pcursor *find_cursor_with_error(const LEX_STRING name,
+ uint *poff,
+ bool current_scope_only) const
+ {
+ const sp_pcursor *pcursor= find_cursor(name, poff, current_scope_only);
+ if (!pcursor)
+ {
+ my_error(ER_SP_CURSOR_MISMATCH, MYF(0), name.str);
+ return NULL;
+ }
+ return pcursor;
+ }
/// Find cursor by offset (for SHOW {PROCEDURE|FUNCTION} CODE only).
const sp_pcursor *find_cursor(uint offset) const;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index ca35535973e..d0d60b2c7b2 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5416,8 +5416,42 @@ sp_variable *LEX::sp_add_for_loop_variable(THD *thd, const LEX_STRING name,
*/
spvar->field_def.pack_flag= (FIELDFLAG_NUMBER |
f_settype((uint) MYSQL_TYPE_LONGLONG));
- if (sp_variable_declarations_finalize(thd, 1, NULL, NULL, value))
+
+ if (!value && !(value= new (thd->mem_root) Item_null(thd)))
+ return NULL;
+
+ spvar->default_value= value;
+ sp_instr_set *is= new (this->thd->mem_root)
+ sp_instr_set(sphead->instructions(),
+ spcont, spvar->offset, value,
+ this, true);
+ if (is == NULL || sphead->add_instr(is))
return NULL;
+ spcont->declare_var_boundary(0);
+ return spvar;
+}
+
+
+sp_variable *
+LEX::sp_add_for_loop_cursor_variable(THD *thd,
+ const LEX_STRING name,
+ const sp_pcursor *pcursor,
+ uint coffset)
+{
+ sp_variable *spvar= spcont->add_variable(thd, name);
+ spcont->declare_var_boundary(1);
+ spvar->field_def.field_name= spvar->name.str;
+ spvar->default_value= new (thd->mem_root) Item_null(thd);
+
+ Cursor_rowtype *ref;
+ if (!(ref= new (thd->mem_root) Cursor_rowtype(coffset)))
+ return NULL;
+ spvar->field_def.set_cursor_rowtype_ref(ref);
+
+ if (sphead->add_for_loop_open_cursor(thd, spcont, spvar, pcursor, coffset))
+ return NULL;
+
+ spcont->declare_var_boundary(0);
return spvar;
}
@@ -5453,7 +5487,8 @@ bool LEX::sp_for_loop_condition(THD *thd, const Lex_for_loop_st &loop)
/**
Generate the FOR LOOP condition code in its own lex
*/
-bool LEX::sp_for_loop_index_and_bounds(THD *thd, const Lex_for_loop_st &loop)
+bool LEX::sp_for_loop_intrange_condition_test(THD *thd,
+ const Lex_for_loop_st &loop)
{
spcont->set_for_loop(loop);
sphead->reset_lex(thd);
@@ -5463,6 +5498,80 @@ bool LEX::sp_for_loop_index_and_bounds(THD *thd, const Lex_for_loop_st &loop)
}
+bool LEX::sp_for_loop_cursor_condition_test(THD *thd,
+ const Lex_for_loop_st &loop)
+{
+ spcont->set_for_loop(loop);
+ sphead->reset_lex(thd);
+ const LEX_STRING *cursor_name= spcont->find_cursor(loop.m_cursor_offset);
+ Item *expr= new (thd->mem_root) Item_func_cursor_found(thd, *cursor_name,
+ loop.m_cursor_offset);
+ if (thd->lex->sp_while_loop_expression(thd, expr))
+ return true;
+ return thd->lex->sphead->restore_lex(thd);
+}
+
+
+bool LEX::sp_for_loop_intrange_declarations(THD *thd, Lex_for_loop_st *loop,
+ const LEX_STRING &index,
+ const Lex_for_loop_bounds_st &bounds)
+{
+ if (!(loop->m_index=
+ bounds.m_index->sp_add_for_loop_variable(thd, index,
+ bounds.m_index->get_item())))
+ return true;
+ if (!(loop->m_upper_bound=
+ bounds.m_upper_bound->sp_add_for_loop_upper_bound(thd,
+ bounds.m_upper_bound->get_item())))
+ return true;
+ loop->m_direction= bounds.m_direction;
+ loop->m_implicit_cursor= 0;
+ return false;
+}
+
+
+bool LEX::sp_for_loop_cursor_declarations(THD *thd,
+ Lex_for_loop_st *loop,
+ const LEX_STRING &index,
+ const Lex_for_loop_bounds_st &bounds)
+{
+ Item *item= bounds.m_index->get_item();
+ Item_splocal *item_splocal;
+ Item_field *item_field;
+ LEX_STRING name;
+ uint coffs;
+ const sp_pcursor *pcursor;
+
+ if ((item_splocal= item->get_item_splocal()))
+ {
+ name= item_splocal->m_name;
+ }
+ else if ((item_field= item->type() == Item::FIELD_ITEM ?
+ static_cast<Item_field *>(item) : NULL) &&
+ item_field->table_name == NULL)
+ {
+ name.str= (char *) item_field->field_name;
+ name.length= strlen(item_field->field_name);
+ }
+ else
+ {
+ thd->parse_error();
+ return true;
+ }
+ if (!(pcursor= spcont->find_cursor_with_error(name, &coffs, false)))
+ return true;
+
+ if (!(loop->m_index= sp_add_for_loop_cursor_variable(thd, index,
+ pcursor, coffs)))
+ return true;
+ loop->m_upper_bound= NULL;
+ loop->m_direction= bounds.m_direction;
+ loop->m_cursor_offset= coffs;
+ loop->m_implicit_cursor= bounds.m_implicit_cursor;
+ return false;
+}
+
+
/**
Generate a code for a FOR loop index increment
*/
@@ -5487,7 +5596,7 @@ bool LEX::sp_for_loop_increment(THD *thd, const Lex_for_loop_st &loop)
}
-bool LEX::sp_for_loop_finalize(THD *thd, const Lex_for_loop_st &loop)
+bool LEX::sp_for_loop_intrange_finalize(THD *thd, const Lex_for_loop_st &loop)
{
sphead->reset_lex(thd);
@@ -5503,6 +5612,18 @@ bool LEX::sp_for_loop_finalize(THD *thd, const Lex_for_loop_st &loop)
}
+bool LEX::sp_for_loop_cursor_finalize(THD *thd, const Lex_for_loop_st &loop)
+{
+ sp_instr_cfetch *instr=
+ new (thd->mem_root) sp_instr_cfetch(sphead->instructions(),
+ spcont, loop.m_cursor_offset);
+ if (instr == NULL || sphead->add_instr(instr))
+ return true;
+ instr->add_to_varlist(loop.m_index);
+ // Generate a jump to the beginning of the loop
+ return sp_while_loop_finalize(thd);
+}
+
/***************************************************************************/
bool LEX::sp_declare_cursor(THD *thd, const LEX_STRING name,
@@ -5517,6 +5638,7 @@ bool LEX::sp_declare_cursor(THD *thd, const LEX_STRING name,
my_error(ER_SP_DUP_CURS, MYF(0), name.str);
return true;
}
+ cursor_stmt->set_cursor_name(name);
i= new (thd->mem_root)
sp_instr_cpush(sphead->instructions(), spcont, cursor_stmt,
spcont->current_cursor_count());
@@ -5538,19 +5660,10 @@ bool LEX::sp_open_cursor(THD *thd, const LEX_STRING name,
{
uint offset;
const sp_pcursor *pcursor;
- if (!(pcursor= spcont->find_cursor(name, &offset, false)))
- {
- my_error(ER_SP_CURSOR_MISMATCH, MYF(0), name.str);
- return true;
- }
- if ((pcursor->param_context() ?
- pcursor->param_context()->context_var_count() : 0) !=
- (parameters ? parameters->elements : 0))
- {
- my_error(ER_WRONG_PARAMCOUNT_TO_CURSOR, MYF(0), name.str);
- return true;
- }
- return sphead->add_open_cursor(thd, spcont, offset,
+ uint param_count= parameters ? parameters->elements : 0;
+ return !(pcursor= spcont->find_cursor_with_error(name, &offset, false)) ||
+ pcursor->check_param_count_with_error(param_count) ||
+ sphead->add_open_cursor(thd, spcont, offset,
pcursor->param_context(), parameters);
}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 3cdf05377dd..5f02bbac9b6 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2942,6 +2942,8 @@ public:
return NULL;
}
+ virtual const LEX_STRING *cursor_name() const { return &null_lex_str; }
+
void start(THD *thd);
const char *substatement_query(THD *thd) const;
@@ -3351,6 +3353,8 @@ public:
{
return add_placeholder(thd, name, pos - substatement_query(thd), end - pos);
}
+
+ /* Integer range FOR LOOP methods */
sp_variable *sp_add_for_loop_variable(THD *thd, const LEX_STRING name,
Item *value);
sp_variable *sp_add_for_loop_upper_bound(THD *thd, Item *value)
@@ -3358,8 +3362,84 @@ public:
LEX_STRING name= { C_STRING_WITH_LEN("[upper_bound]") };
return sp_add_for_loop_variable(thd, name, value);
}
- bool sp_for_loop_index_and_bounds(THD *thd, const Lex_for_loop_st &loop);
- bool sp_for_loop_finalize(THD *thd, const Lex_for_loop_st &loop);
+ bool sp_for_loop_intrange_declarations(THD *thd, Lex_for_loop_st *loop,
+ const LEX_STRING &index,
+ const Lex_for_loop_bounds_st &bounds);
+ bool sp_for_loop_intrange_condition_test(THD *thd, const Lex_for_loop_st &loop);
+ bool sp_for_loop_intrange_finalize(THD *thd, const Lex_for_loop_st &loop);
+
+ /* Cursor FOR LOOP methods */
+ bool sp_for_loop_cursor_declarations(THD *thd, Lex_for_loop_st *loop,
+ const LEX_STRING &index,
+ const Lex_for_loop_bounds_st &bounds);
+ sp_variable *sp_add_for_loop_cursor_variable(THD *thd,
+ const LEX_STRING name,
+ const class sp_pcursor *cur,
+ uint coffset);
+ bool sp_for_loop_cursor_condition_test(THD *thd, const Lex_for_loop_st &loop);
+ bool sp_for_loop_cursor_finalize(THD *thd, const Lex_for_loop_st &);
+
+ /* Generic FOR LOOP methods*/
+
+ /*
+ Generate FOR loop declarations and
+ initialize "loop" from "index" and "bounds".
+
+ @param [IN] thd - current THD, for mem_root and error reporting
+ @param [OUT] loop - the loop generated SP variables are stored here,
+ together with additional loop characteristics.
+ @param [IN] index - the loop index variable name
+ @param [IN] bounds - the loop bounds (in sp_assignment_lex format)
+ and additional loop characteristics,
+ as created by the sp_for_loop_bounds rule.
+ @retval true - on error
+ @retval false - on success
+
+ This methods adds declarations:
+ - An explicit integer or cursor%ROWTYPE "index" variable
+ - An implicit ingeger upper bound variable, in case of integer range loops
+ - A CURSOR, in case of an implicit CURSOR loops
+ The generated variables are stored into "loop".
+ Additional loop characteristics are copies from "bounds" to "loop".
+ */
+ bool sp_for_loop_declarations(THD *thd, Lex_for_loop_st *loop,
+ const LEX_STRING &index,
+ const Lex_for_loop_bounds_st &bounds)
+ {
+ return bounds.is_for_loop_cursor() ?
+ sp_for_loop_cursor_declarations(thd, loop, index, bounds) :
+ sp_for_loop_intrange_declarations(thd, loop, index, bounds);
+ }
+
+ /*
+ Generate a conditional jump instruction to leave the loop,
+ using a proper condition depending on the loop type:
+ - Item_func_le -- integer range loops
+ - Item_func_ge -- integer range reverse loops
+ - Item_func_cursor_found -- cursor loops
+ */
+ bool sp_for_loop_condition_test(THD *thd, const Lex_for_loop_st &loop)
+ {
+ return loop.is_for_loop_cursor() ?
+ sp_for_loop_cursor_condition_test(thd, loop) :
+ sp_for_loop_intrange_condition_test(thd, loop);
+ }
+
+ /*
+ Generate "increment" instructions followed by a jump to the
+ condition test in the beginnig of the loop.
+ "Increment" depends on the loop type and can be:
+ - index:= index + 1; -- integer range loops
+ - index:= index - 1; -- integer range reverse loops
+ - FETCH cursor INTO index; -- cursor loops
+ */
+ bool sp_for_loop_finalize(THD *thd, const Lex_for_loop_st &loop)
+ {
+ return loop.is_for_loop_cursor() ?
+ sp_for_loop_cursor_finalize(thd, loop) :
+ sp_for_loop_intrange_finalize(thd, loop);
+ }
+ /* End of FOR LOOP methods */
bool add_signal_statement(THD *thd, const class sp_condition_value *value);
bool add_resignal_statement(THD *thd, const class sp_condition_value *value);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 45af801272a..a796d8af40d 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -769,6 +769,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr)
Lex_field_type_st Lex_field_type;
Lex_dyncol_type_st Lex_dyncol_type;
Lex_for_loop_st for_loop;
+ Lex_for_loop_bounds_st for_loop_bounds;
/* pointers */
Create_field *create_field;
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 2360b735674..021d6701f7a 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -179,6 +179,7 @@ void ORAerror(THD *thd, const char *s)
Lex_field_type_st Lex_field_type;
Lex_dyncol_type_st Lex_dyncol_type;
Lex_for_loop_st for_loop;
+ Lex_for_loop_bounds_st for_loop_bounds;
struct
{
LEX_STRING name;
@@ -273,10 +274,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%parse-param { THD *thd }
%lex-param { THD *thd }
/*
- Currently there are 102 shift/reduce conflicts.
+ Currently there are 103 shift/reduce conflicts.
We should not introduce new conflicts any more.
*/
-%expect 102
+%expect 103
/*
Comments for TOKENS.
@@ -1158,6 +1159,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%type <assignment_lex>
assignment_source_lex
assignment_source_expr
+ for_loop_bound_expr
%type <sp_assignment_lex_list>
cursor_actual_parameters
@@ -1341,6 +1343,7 @@ END_OF_INPUT
%type <spname> sp_name
%type <spvar> sp_param_name sp_param_name_and_type
%type <for_loop> sp_for_loop_index_and_bounds
+%type <for_loop_bounds> sp_for_loop_bounds
%type <num> opt_sp_for_loop_direction
%type <spvar_mode> sp_opt_inout
%type <index_hint> index_hint_type
@@ -3338,6 +3341,22 @@ assignment_source_expr:
}
;
+for_loop_bound_expr:
+ assignment_source_lex
+ {
+ Lex->sphead->reset_lex(thd, $1);
+ }
+ expr
+ {
+ DBUG_ASSERT($1 == thd->lex);
+ $$= $1;
+ $$->sp_lex_in_use= true;
+ $$->set_item_and_free_list($3, NULL);
+ if ($$->sphead->restore_lex(thd))
+ MYSQL_YYABORT;
+ }
+ ;
+
cursor_actual_parameters:
assignment_source_expr
{
@@ -3803,22 +3822,48 @@ opt_sp_for_loop_direction:
;
sp_for_loop_index_and_bounds:
- ident_directly_assignable
+ ident_directly_assignable sp_for_loop_bounds
{
- Lex->sphead->reset_lex(thd);
+ if (Lex->sp_for_loop_declarations(thd, &$$, $1, $2))
+ MYSQL_YYABORT;
+ }
+ ;
+
+sp_for_loop_bounds:
+ IN_SYM opt_sp_for_loop_direction for_loop_bound_expr
+ DOT_DOT_SYM for_loop_bound_expr
+ {
+ $$.m_direction= $2;
+ $$.m_index= $3;
+ $$.m_upper_bound= $5;
+ $$.m_implicit_cursor= false;
}
- IN_SYM opt_sp_for_loop_direction expr
+ | IN_SYM opt_sp_for_loop_direction for_loop_bound_expr
{
- if (!($<spvar>$= Lex->sp_add_for_loop_variable(thd, $1, $5)))
- MYSQL_YYABORT;
- Lex->sphead->reset_lex(thd);
+ $$.m_direction= $2;
+ $$.m_index= $3;
+ $$.m_upper_bound= NULL;
+ $$.m_implicit_cursor= false;
}
- DOT_DOT_SYM expr
+ | IN_SYM opt_sp_for_loop_direction '(' sp_cursor_stmt ')'
{
- $$.m_index= $<spvar>6;
- if (!($$.m_upper_bound= Lex->sp_add_for_loop_upper_bound(thd, $8)))
+ DBUG_ASSERT(Lex->sphead);
+ LEX_STRING name= {C_STRING_WITH_LEN("[implicit_cursor]") };
+ if (Lex->sp_declare_cursor(thd, name, $4, NULL))
+ MYSQL_YYABORT;
+ $$.m_direction= 1;
+ if (!($$.m_index= new (thd->mem_root) sp_assignment_lex(thd, thd->lex)))
+ MYSQL_YYABORT;
+ $$.m_index->sp_lex_in_use= true;
+ Lex->sphead->reset_lex(thd, $$.m_index);
+ Item *item= new (thd->mem_root) Item_field(thd,
+ Lex->current_context(),
+ NullS, NullS, name.str);
+ $$.m_index->set_item_and_free_list(item, NULL);
+ if (Lex->sphead->restore_lex(thd))
MYSQL_YYABORT;
- $$.m_direction= $4;
+ $$.m_upper_bound= NULL;
+ $$.m_implicit_cursor= true;
}
;
@@ -3905,7 +3950,7 @@ sp_labeled_control:
{
if (Lex->sp_push_loop_label(thd, $1)) // The inner WHILE block
MYSQL_YYABORT;
- if (Lex->sp_for_loop_index_and_bounds(thd, $4))
+ if (Lex->sp_for_loop_condition_test(thd, $4))
MYSQL_YYABORT;
}
LOOP_SYM
@@ -3917,7 +3962,9 @@ sp_labeled_control:
}
pop_sp_loop_label // The inner WHILE block
{
- if (Lex->sp_block_finalize(thd)) // The outer DECLARE..BEGIN..END
+ Lex_spblock tmp;
+ tmp.curs= MY_TEST($4.m_implicit_cursor);
+ if (Lex->sp_block_finalize(thd, tmp)) // The outer DECLARE..BEGIN..END
MYSQL_YYABORT;
}
| labels_declaration_oracle REPEAT_SYM
@@ -3960,17 +4007,19 @@ sp_unlabeled_control:
{
if (Lex->sp_push_loop_empty_label(thd)) // The inner WHILE block
MYSQL_YYABORT;
- if (Lex->sp_for_loop_index_and_bounds(thd, $3))
+ if (Lex->sp_for_loop_condition_test(thd, $3))
MYSQL_YYABORT;
}
LOOP_SYM
sp_proc_stmts1
END LOOP_SYM
{
+ Lex_spblock tmp;
+ tmp.curs= MY_TEST($3.m_implicit_cursor);
if (Lex->sp_for_loop_finalize(thd, $3))
MYSQL_YYABORT;
Lex->sp_pop_loop_empty_label(thd); // The inner WHILE block
- if (Lex->sp_block_finalize(thd)) // The outer DECLARE..BEGIN..END
+ if (Lex->sp_block_finalize(thd, tmp)) // The outer DECLARE..BEGIN..END
MYSQL_YYABORT;
}
| REPEAT_SYM
diff --git a/sql/structs.h b/sql/structs.h
index e0fb05f05da..1b3c0b7a7f2 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -674,22 +674,37 @@ public:
};
+struct Lex_for_loop_bounds_st
+{
+public:
+ class sp_assignment_lex *m_index;
+ class sp_assignment_lex *m_upper_bound;
+ int8 m_direction;
+ bool m_implicit_cursor;
+ bool is_for_loop_cursor() const { return m_upper_bound == NULL; }
+};
+
+
struct Lex_for_loop_st
{
public:
class sp_variable *m_index;
class sp_variable *m_upper_bound;
- int m_direction;
+ int m_cursor_offset;
+ int8 m_direction;
+ bool m_implicit_cursor;
void init()
{
m_index= 0;
m_upper_bound= 0;
m_direction= 0;
+ m_implicit_cursor= false;
}
void init(const Lex_for_loop_st &other)
{
*this= other;
}
+ bool is_for_loop_cursor() const { return m_upper_bound == NULL; }
};
View Lorry Controller Status