Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-cursor-decl.test

raw
1 SET 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
11 CREATE TABLE t1 (a INT);
12 insert into t1 values (1);
13 insert into t1 values (2);
14 DELIMITER $$;
15 CREATE PROCEDURE p1
16 AS
17 CURSOR c IS SELECT a FROM t1;
18 var1 varchar(10);
19 BEGIN
20 OPEN c;
21 fetch c into var1;
22 SELECT c%ROWCOUNT,var1;
23 close c;
24 END;
25 $$
26 DELIMITER ;$$
27 CALL p1;
28 DROP PROCEDURE p1;
29 drop table t1;
30
31 --echo #
32 --echo # Variable after condition declaration
33 --echo #
34
35 CREATE TABLE t1 (col1 INT);
36 insert into t1 values (1);
37 create unique index t1_col1 on t1 (col1);
38 DELIMITER $$;
39 CREATE PROCEDURE p1
40 AS
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;
47 BEGIN
48 var1:='';
49 insert into t1 values (1);
50 select var1;
51 END;
52 $$
53 DELIMITER ;$$
54 CALL p1;
55 DROP PROCEDURE p1;
56 drop table t1;
57
58 --echo #
59 --echo # Condition after cursor declaration
60 --echo #
61
62 CREATE TABLE t1 (col1 INT);
63 insert into t1 values (1);
64 create unique index t1_col1 on t1 (col1);
65 DELIMITER $$;
66 CREATE PROCEDURE p1
67 AS
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;
76 BEGIN
77 var1:='';
78 insert into t1 values (1);
79 SELECT var1;
80 END;
81 $$
82 DELIMITER ;$$
83 CALL p1;
84 DROP PROCEDURE p1;
85 drop table t1;
86
87 --echo #
88 --echo # Cursor after handler declaration
89 --echo #
90
91 CREATE TABLE t1 (col1 INT);
92 insert into t1 values (1);
93 create unique index t1_col1 on t1 (col1);
94 DELIMITER $$;
95 --error ER_PARSE_ERROR
96 CREATE PROCEDURE p1
97 AS
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;
106 BEGIN
107 var1:='';
108 insert into t1 values (1);
109 SELECT var1;
110 END;
111 $$
112 DELIMITER ;$$
113 drop table t1;
114
115 --echo #
116 --echo # Condition after handler declaration
117 --echo #
118
119 CREATE TABLE t1 (col1 INT);
120 insert into t1 values (1);
121 create unique index t1_col1 on t1 (col1);
122 DELIMITER $$;
123 --error ER_PARSE_ERROR
124 CREATE PROCEDURE p1
125 AS
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';
135 BEGIN
136 var1:='';
137 insert into t1 values (1);
138 SELECT var1;
139 END;
140 $$
141 DELIMITER ;$$
142 drop table t1;
143
144 --echo #
145 --echo # Variable after handler declaration
146 --echo #
147
148 CREATE TABLE t1 (col1 INT);
149 insert into t1 values (1);
150 create unique index t1_col1 on t1 (col1);
151 DELIMITER $$;
152 --error ER_PARSE_ERROR
153 CREATE PROCEDURE p1
154 AS
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';
164 BEGIN
165 var1:='';
166 insert into t1 values (1);
167 SELECT var1;
168 END;
169 $$
170 DELIMITER ;$$
171 drop table t1;
172
173 --echo #
174 --echo # Variable after cursor (inner block)
175 --echo #
176
177 CREATE TABLE t1 (col1 INT);
178 insert into t1 values (1);
179 insert into t1 values (2);
180 create unique index t1_col1 on t1 (col1);
181 DELIMITER $$;
182 CREATE PROCEDURE p1
183 AS
184 CURSOR c IS SELECT col1 FROM t1;
185 var1 varchar(40);
186 BEGIN
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;
216 END;
217 $$
218 DELIMITER ;$$
219 CALL p1;
220 DROP PROCEDURE p1;
221 drop table t1;
222
223 --echo #
224 --echo # Cursor declaration and row type declaration in same block
225 --echo #
226
227 CREATE TABLE t1 (a INT, b VARCHAR(10));
228 insert into t1 values(1,'a');
229 delimiter $$;
230 CREATE PROCEDURE p1()
231 AS
232 CURSOR cur1 IS SELECT a FROM t1;
233 rec1 cur1%ROWTYPE;
234 BEGIN
235 rec1.a:= 10;
236 END;
237 $$
238 delimiter ;$$
239 call p1;
240 DROP PROCEDURE p1;
241 drop table t1;
242
243
244 --echo #
245 --echo # Recursive cursor and cursor%ROWTYPE declarations in the same block
246 --echo #
247
248 delimiter $$;
249 CREATE PROCEDURE p1
250 AS
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;
258 BEGIN
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;
270 END;
271 $$
272 DELIMITER ;$$
273 CALL p1();
274 DROP 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
281 DELIMITER $$;
282 CREATE PROCEDURE p1
283 AS
284 a INT DEFAULT 10;
285 CURSOR cur1 IS SELECT a;
286 rec1 cur1%ROWTYPE;
287 BEGIN
288 CREATE TABLE t1 AS SELECT rec1.a;
289 SHOW CREATE TABLE t1;
290 DROP TABLE t1;
291 END;
292 $$
293 DELIMITER ;$$
294 CALL p1();
295 DROP PROCEDURE p1;