1SET sql_mode=ORACLE; 2 3--echo # 4--echo # MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations 5--echo # 6 7--echo # 8--echo # Variable after cursor declaration 9--echo # 10 11CREATE TABLE t1 (a INT); 12insert into t1 values (1); 13insert into t1 values (2); 14DELIMITER $$; 15CREATE PROCEDURE p1 16AS 17 CURSOR c IS SELECT a FROM t1; 18 var1 varchar(10); 19BEGIN 20 OPEN c; 21 fetch c into var1; 22 SELECT c%ROWCOUNT,var1; 23 close c; 24END; 25$$ 26DELIMITER ;$$ 27CALL p1; 28DROP PROCEDURE p1; 29drop table t1; 30 31--echo # 32--echo # Variable after condition declaration 33--echo # 34 35CREATE TABLE t1 (col1 INT); 36insert into t1 values (1); 37create unique index t1_col1 on t1 (col1); 38DELIMITER $$; 39CREATE PROCEDURE p1 40AS 41 dup_key CONDITION FOR SQLSTATE '23000'; 42 var1 varchar(40); 43 CONTINUE HANDLER FOR dup_key 44 BEGIN 45 var1:='duplicate key in index'; 46 END; 47BEGIN 48 var1:=''; 49 insert into t1 values (1); 50 select var1; 51END; 52$$ 53DELIMITER ;$$ 54CALL p1; 55DROP PROCEDURE p1; 56drop table t1; 57 58--echo # 59--echo # Condition after cursor declaration 60--echo # 61 62CREATE TABLE t1 (col1 INT); 63insert into t1 values (1); 64create unique index t1_col1 on t1 (col1); 65DELIMITER $$; 66CREATE PROCEDURE p1 67AS 68 var1 varchar(40); 69 var2 integer; 70 CURSOR c IS SELECT col1 FROM t1; 71 dup_key CONDITION FOR SQLSTATE '23000'; 72 CONTINUE HANDLER FOR dup_key 73 BEGIN 74 var1:='duplicate key in index'; 75 END; 76BEGIN 77 var1:=''; 78 insert into t1 values (1); 79 SELECT var1; 80END; 81$$ 82DELIMITER ;$$ 83CALL p1; 84DROP PROCEDURE p1; 85drop table t1; 86 87--echo # 88--echo # Cursor after handler declaration 89--echo # 90 91CREATE TABLE t1 (col1 INT); 92insert into t1 values (1); 93create unique index t1_col1 on t1 (col1); 94DELIMITER $$; 95--error ER_PARSE_ERROR 96CREATE PROCEDURE p1 97AS 98 var1 varchar(40); 99 var2 integer; 100 dup_key CONDITION FOR SQLSTATE '23000'; 101 CONTINUE HANDLER FOR dup_key 102 BEGIN 103 var1:='duplicate key in index'; 104 END; 105 CURSOR c IS SELECT col1 FROM t1; 106BEGIN 107 var1:=''; 108 insert into t1 values (1); 109 SELECT var1; 110END; 111$$ 112DELIMITER ;$$ 113drop table t1; 114 115--echo # 116--echo # Condition after handler declaration 117--echo # 118 119CREATE TABLE t1 (col1 INT); 120insert into t1 values (1); 121create unique index t1_col1 on t1 (col1); 122DELIMITER $$; 123--error ER_PARSE_ERROR 124CREATE PROCEDURE p1 125AS 126 var1 varchar(40); 127 var2 integer; 128 dup_key CONDITION FOR SQLSTATE '23000'; 129 CURSOR c IS SELECT col1 FROM t1; 130 CONTINUE HANDLER FOR dup_key 131 BEGIN 132 var1:='duplicate key in index'; 133 END; 134 divide_by_zero CONDITION FOR SQLSTATE '22012'; 135BEGIN 136 var1:=''; 137 insert into t1 values (1); 138 SELECT var1; 139END; 140$$ 141DELIMITER ;$$ 142drop table t1; 143 144--echo # 145--echo # Variable after handler declaration 146--echo # 147 148CREATE TABLE t1 (col1 INT); 149insert into t1 values (1); 150create unique index t1_col1 on t1 (col1); 151DELIMITER $$; 152--error ER_PARSE_ERROR 153CREATE PROCEDURE p1 154AS 155 var1 varchar(40); 156 var2 integer; 157 dup_key CONDITION FOR SQLSTATE '23000'; 158 CURSOR c IS SELECT col1 FROM t1; 159 CONTINUE HANDLER FOR dup_key 160 BEGIN 161 var1:='duplicate key in index'; 162 END; 163 divide_by_zero CONDITION FOR SQLSTATE '22012'; 164BEGIN 165 var1:=''; 166 insert into t1 values (1); 167 SELECT var1; 168END; 169$$ 170DELIMITER ;$$ 171drop table t1; 172 173--echo # 174--echo # Variable after cursor (inner block) 175--echo # 176 177CREATE TABLE t1 (col1 INT); 178insert into t1 values (1); 179insert into t1 values (2); 180create unique index t1_col1 on t1 (col1); 181DELIMITER $$; 182CREATE PROCEDURE p1 183AS 184 CURSOR c IS SELECT col1 FROM t1; 185 var1 varchar(40); 186BEGIN 187 OPEN c; 188 begin 189 declare 190 CURSOR c IS SELECT col1 FROM t1 where col1=2; 191 var2 integer; 192 dup_key CONDITION FOR SQLSTATE '23000'; 193 CONTINUE HANDLER FOR dup_key 194 BEGIN 195 var1:='duplicate key in index'; 196 END; 197 begin 198 OPEN c; 199 fetch c into var1; 200 SELECT 'inner cursor',var1; 201 insert into t1 values (2); 202 close c; 203 end; 204 end; 205 SELECT var1; 206 fetch c into var1; 207 SELECT c%ROWCOUNT,var1; 208 begin 209 insert into t1 values (2); 210 exception when 1062 then 211 begin 212 SELECT 'dup key caugth'; 213 end; 214 end; 215 close c; 216END; 217$$ 218DELIMITER ;$$ 219CALL p1; 220DROP PROCEDURE p1; 221drop table t1; 222 223--echo # 224--echo # Cursor declaration and row type declaration in same block 225--echo # 226 227CREATE TABLE t1 (a INT, b VARCHAR(10)); 228insert into t1 values(1,'a'); 229delimiter $$; 230CREATE PROCEDURE p1() 231AS 232 CURSOR cur1 IS SELECT a FROM t1; 233 rec1 cur1%ROWTYPE; 234BEGIN 235 rec1.a:= 10; 236END; 237$$ 238delimiter ;$$ 239call p1; 240DROP PROCEDURE p1; 241drop table t1; 242 243 244--echo # 245--echo # Recursive cursor and cursor%ROWTYPE declarations in the same block 246--echo # 247 248delimiter $$; 249CREATE PROCEDURE p1 250AS 251 a INT:=10; 252 b VARCHAR(10):='b0'; 253 c DOUBLE:=0.1; 254 CURSOR cur1 IS SELECT a, b, c; 255 rec1 cur1%ROWTYPE; 256 CURSOR cur2 IS SELECT rec1.a + 1 "a", rec1.b||'0' AS b, rec1.c AS c; 257 rec2 cur2%ROWTYPE; 258BEGIN 259 OPEN cur1; 260 FETCH cur1 INTO rec1; 261 CLOSE cur1; 262 SELECT rec1.a; 263 OPEN cur2; 264 FETCH cur2 INTO rec2; 265 CLOSE cur2; 266 SELECT rec2.a; 267 CREATE TABLE t2 AS SELECT rec2.a AS a, rec2.b AS b, rec2.c AS c; 268 SHOW CREATE TABLE t2; 269 DROP TABLE t2; 270END; 271$$ 272DELIMITER ;$$ 273CALL p1(); 274DROP PROCEDURE p1; 275 276 277--echo # 278--echo # MDEV-12916 Wrong column data type for an INT field of a cursor-anchored ROW variable 279--echo # 280 281DELIMITER $$; 282CREATE PROCEDURE p1 283AS 284 a INT DEFAULT 10; 285 CURSOR cur1 IS SELECT a; 286 rec1 cur1%ROWTYPE; 287BEGIN 288 CREATE TABLE t1 AS SELECT rec1.a; 289 SHOW CREATE TABLE t1; 290 DROP TABLE t1; 291END; 292$$ 293DELIMITER ;$$ 294CALL p1(); 295DROP PROCEDURE p1; 296