Debian Codegraph @

mysql-test/suite/compat/oracle/r/sp-cursor-decl.result

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