Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2 #
3 # MDEV-10582 sql_mode=ORACLE: explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND
4 #
5 #
6 # Cursor attributes outside of an SP context
7 #
8 SELECT c%ISOPEN;
9 ERROR 42000: Undefined CURSOR: c
10 SELECT c%FOUND;
11 ERROR 42000: Undefined CURSOR: c
12 SELECT c%NOTFOUND;
13 ERROR 42000: Undefined CURSOR: c
14 SELECT c%ROWCOUNT;
15 ERROR 42000: Undefined CURSOR: c
16 #
17 # Undefinite cursor attributes
18 #
19 CREATE PROCEDURE p1
20 AS
21 BEGIN
22 SELECT c%ISOPEN;
23 END;
24 $$
25 ERROR 42000: Undefined CURSOR: c
26 CREATE PROCEDURE p1
27 AS
28 BEGIN
29 SELECT c%ROWCOUNT;
30 END;
31 $$
32 ERROR 42000: Undefined CURSOR: c
33 CREATE PROCEDURE p1
34 AS
35 BEGIN
36 SELECT c%FOUND;
37 END;
38 $$
39 ERROR 42000: Undefined CURSOR: c
40 CREATE PROCEDURE p1
41 AS
42 BEGIN
43 SELECT c%NOTFOUND;
44 END;
45 $$
46 ERROR 42000: Undefined CURSOR: c
47 #
48 # Not opened cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT
49 #
50 CREATE PROCEDURE p1
51 AS
52 CURSOR c IS SELECT 1 AS c FROM DUAL;
53 BEGIN
54 SELECT c%ROWCOUNT;
55 END;
56 $$
57 CALL p1;
58 ERROR 24000: Cursor is not open
59 DROP PROCEDURE p1;
60 CREATE PROCEDURE p1
61 AS
62 CURSOR c IS SELECT 1 AS c FROM DUAL;
63 BEGIN
64 SELECT c%FOUND;
65 END;
66 $$
67 CALL p1;
68 ERROR 24000: Cursor is not open
69 DROP PROCEDURE p1;
70 CREATE PROCEDURE p1
71 AS
72 CURSOR c IS SELECT 1 AS c FROM DUAL;
73 BEGIN
74 SELECT c%NOTFOUND;
75 END;
76 $$
77 CALL p1;
78 ERROR 24000: Cursor is not open
79 DROP PROCEDURE p1;
80 #
81 # Not opened cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT with INVALID_CURSOR exception
82 #
83 CREATE PROCEDURE p1
84 AS
85 CURSOR c IS SELECT 1 AS c FROM DUAL;
86 BEGIN
87 SELECT c%ROWCOUNT;
88 EXCEPTION
89 WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
90 END;
91 $$
92 CALL p1;
93 c%ROWCOUNT
94 msg
95 INVALID_CURSOR caught
96 DROP PROCEDURE p1;
97 CREATE PROCEDURE p1
98 AS
99 CURSOR c IS SELECT 1 AS c FROM DUAL;
100 BEGIN
101 SELECT c%FOUND;
102 EXCEPTION
103 WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
104 END;
105 $$
106 CALL p1;
107 c%FOUND
108 msg
109 INVALID_CURSOR caught
110 DROP PROCEDURE p1;
111 CREATE PROCEDURE p1
112 AS
113 CURSOR c IS SELECT 1 AS c FROM DUAL;
114 BEGIN
115 SELECT c%NOTFOUND;
116 EXCEPTION
117 WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
118 END;
119 $$
120 CALL p1;
121 c%NOTFOUND
122 msg
123 INVALID_CURSOR caught
124 DROP PROCEDURE p1;
125 #
126 # print()
127 #
128 CREATE TABLE t1 (a INT);
129 CREATE PROCEDURE p1
130 AS
131 CURSOR c IS SELECT * FROM t1 ORDER BY a;
132 BEGIN
133 EXPLAIN EXTENDED SELECT c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
134 END;
135 $$
136 CALL p1();
137 id select_type table type possible_keys key key_len ref rows filtered Extra
138 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
139 Warnings:
140 Note 1003 select "c"%ISOPEN AS "c%ISOPEN","c"%ROWCOUNT AS "c%ROWCOUNT","c"%FOUND AS "c%FOUND","c"%NOTFOUND AS "c%NOTFOUND"
141 DROP PROCEDURE p1;
142 DROP TABLE t1;
143 #
144 # Declared data type of the attributes
145 #
146 CREATE TABLE t1 (a INT);
147 INSERT INTO t1 VALUES (10);
148 CREATE PROCEDURE p1
149 AS
150 CURSOR c IS SELECT * FROM t1 ORDER BY a;
151 BEGIN
152 OPEN c;
153 CREATE TABLE t2 AS SELECT c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
154 SHOW CREATE TABLE t2;
155 DROP TABLE t2;
156 CLOSE c;
157 END;
158 $$
159 CALL p1();
160 Table Create Table
161 t2 CREATE TABLE "t2" (
162 "c%ISOPEN" int(1) NOT NULL,
163 "c%ROWCOUNT" bigint(21) DEFAULT NULL,
164 "c%FOUND" int(1) DEFAULT NULL,
165 "c%NOTFOUND" int(1) DEFAULT NULL
166 )
167 DROP PROCEDURE p1;
168 DROP TABLE t1;
169 #
170 # Core functionality
171 #
172 CREATE TABLE t1 (a INT);
173 INSERT INTO t1 VALUES (10);
174 INSERT INTO t1 VALUES (20);
175 INSERT INTO t1 VALUES (30);
176 CREATE PROCEDURE p1
177 AS
178 a INT:=0;
179 CURSOR c IS SELECT * FROM t1 ORDER BY a;
180 BEGIN
181 SELECT a, c%ISOPEN;
182 OPEN c;
183 /*
184 After OPEN and before FETCH:
185 - %ROWCOUNT returns 0
186 - %FOUND and %NOTFOUND return NULL
187 */
188 SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
189 FETCH c INTO a;
190 SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
191 FETCH c INTO a;
192 SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
193 FETCH c INTO a;
194 SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
195 FETCH c INTO a;
196 SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
197 CLOSE c;
198 SELECT a, c%ISOPEN;
199 /*
200 After reopen and before FETCH:
201 - %ROWCOUNT returns 0
202 - %FOUND and %NOTFOUND return NULL
203 */
204 OPEN c;
205 SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
206 FETCH c INTO a;
207 SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
208 CLOSE c;
209 END;
210 $$
211 CALL p1();
212 a c%ISOPEN
213 0 0
214 a c%ISOPEN c%ROWCOUNT c%FOUND c%NOTFOUND
215 0 1 0 NULL NULL
216 a c%ISOPEN c%ROWCOUNT c%FOUND c%NOTFOUND
217 10 1 1 1 0
218 a c%ISOPEN c%ROWCOUNT c%FOUND c%NOTFOUND
219 20 1 2 1 0
220 a c%ISOPEN c%ROWCOUNT c%FOUND c%NOTFOUND
221 30 1 3 1 0
222 a c%ISOPEN c%ROWCOUNT c%FOUND c%NOTFOUND
223 30 1 3 0 1
224 a c%ISOPEN
225 30 0
226 a c%ISOPEN c%ROWCOUNT c%FOUND c%NOTFOUND
227 30 1 0 NULL NULL
228 a c%ISOPEN c%ROWCOUNT c%FOUND c%NOTFOUND
229 10 1 1 1 0
230 DROP PROCEDURE p1;
231 DROP TABLE t1;
232 #
233 # %NOTFOUND as a loop exit condition
234 #
235 CREATE TABLE t1 (a INT);
236 INSERT INTO t1 VALUES (10);
237 INSERT INTO t1 VALUES (20);
238 INSERT INTO t1 VALUES (30);
239 CREATE PROCEDURE p1
240 AS
241 a INT:=0;
242 CURSOR c IS SELECT * FROM t1 ORDER BY a;
243 BEGIN
244 OPEN c;
245 LOOP
246 FETCH c INTO a;
247 EXIT WHEN c%NOTFOUND;
248 SELECT a;
249 END LOOP;
250 CLOSE c;
251 END;
252 $$
253 CALL p1();
254 a
255 10
256 a
257 20
258 a
259 30
260 DROP PROCEDURE p1;
261 DROP TABLE t1;
262 #
263 # %FOUND as a loop exit condition
264 #
265 CREATE TABLE t1 (a INT);
266 INSERT INTO t1 VALUES (10);
267 INSERT INTO t1 VALUES (20);
268 INSERT INTO t1 VALUES (30);
269 CREATE PROCEDURE p1
270 AS
271 a INT:=0;
272 CURSOR c IS SELECT * FROM t1 ORDER BY a;
273 BEGIN
274 OPEN c;
275 LOOP
276 FETCH c INTO a;
277 EXIT WHEN NOT c%FOUND;
278 SELECT a;
279 END LOOP;
280 CLOSE c;
281 END;
282 $$
283 CALL p1();
284 a
285 10
286 a
287 20
288 a
289 30
290 DROP PROCEDURE p1;
291 DROP TABLE t1;
292 #
293 # End of MDEV-10582 sql_mode=ORACLE: explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND
294 #
295 #
296 # MDEV-10597 Cursors with parameters
297 #
298 #
299 # OPEN with a wrong number of parameters
300 #
301 CREATE TABLE t1 (a INT, b VARCHAR(10));
302 CREATE PROCEDURE p1(a_a INT,a_b VARCHAR)
303 AS
304 v_a INT;
305 v_b VARCHAR(10);
306 CURSOR c (p_a INT, p_b VARCHAR) IS SELECT * FROM t1 WHERE a=p_a;
307 BEGIN
308 OPEN c(a_a);
309 LOOP
310 FETCH c INTO v_a, v_b;
311 EXIT WHEN c%NOTFOUND;
312 DBMS_OUTPUT.PUT_LINE('Fetched a record a='||TO_CHAR(v_a)||' b='||v_b);
313 END LOOP;
314 CLOSE c;
315 END;
316 $$
317 ERROR 42000: Incorrect parameter count to cursor 'c'
318 DROP TABLE t1;
319 #
320 # Cursor parameters are not visible outside of the cursor
321 #
322 CREATE PROCEDURE p1(a_a INT)
323 AS
324 v_a INT;
325 CURSOR c (p_a INT) IS SELECT a FROM t1 WHERE a=p_a;
326 BEGIN
327 OPEN c(a_a);
328 p_a:=10;
329 END;
330 $$
331 ERROR HY000: Unknown system variable 'p_a'
332 CREATE PROCEDURE p1(a_a INT)
333 AS
334 v_a INT;
335 CURSOR c (p_a INT) IS SELECT a FROM t1 WHERE a=p_a;
336 BEGIN
337 p_a:=10;
338 OPEN c(a_a);
339 END;
340 $$
341 ERROR HY000: Unknown system variable 'p_a'
342 #
343 # Cursor parameter shadowing a local variable
344 #
345 CREATE TABLE t1 (a INT);
346 INSERT INTO t1 VALUES (1);
347 CREATE PROCEDURE p1(a INT)
348 AS
349 v_a INT:=NULL;
350 p_a INT:=NULL;
351 CURSOR c (p_a VARCHAR2) IS SELECT a FROM t1 WHERE p_a IS NOT NULL;
352 BEGIN
353 OPEN c(a);
354 FETCH c INTO v_a;
355 IF c%NOTFOUND THEN
356 BEGIN
357 SELECT 'No records found' AS msg;
358 RETURN;
359 END;
360 END IF;
361 CLOSE c;
362 SELECT 'Fetched a record a='||v_a AS msg;
363 INSERT INTO t1 VALUES (v_a);
364 END;
365 $$
366 CALL p1(1);
367 msg
368 Fetched a record a=1
369 SELECT * FROM t1;
370 a
371 1
372 1
373 CALL p1(NULL);
374 msg
375 No records found
376 SELECT * FROM t1;
377 a
378 1
379 1
380 DROP PROCEDURE p1;
381 DROP TABLE t1;
382 #
383 # Parameters in SELECT list
384 #
385 CREATE PROCEDURE p1(a_a INT, a_b VARCHAR)
386 AS
387 v_a INT;
388 v_b VARCHAR(10);
389 CURSOR c (p_a INT, p_b VARCHAR) IS SELECT p_a,p_b FROM DUAL;
390 BEGIN
391 FOR i IN 0..1
392 LOOP
393 OPEN c(a_a + i,a_b);
394 LOOP
395 FETCH c INTO v_a, v_b;
396 EXIT WHEN c%NOTFOUND;
397 SELECT 'Fetched a record a=' || v_a || ' b=' || v_b AS msg;
398 END LOOP;
399 CLOSE c;
400 END LOOP;
401 END;
402 $$
403 CALL p1(1,'b1');
404 msg
405 Fetched a record a=1 b=b1
406 msg
407 Fetched a record a=2 b=b1
408 DROP PROCEDURE p1;
409 #
410 # Parameters in SELECT list + UNION
411 #
412 CREATE PROCEDURE p1(a_a INT, a_b VARCHAR)
413 AS
414 v_a INT;
415 v_b VARCHAR(10);
416 CURSOR c (p_a INT, p_b VARCHAR) IS
417 SELECT p_a,p_b FROM DUAL
418 UNION ALL
419 SELECT p_a+1,p_b||'b' FROM DUAL;
420 BEGIN
421 OPEN c(a_a,a_b);
422 LOOP
423 FETCH c INTO v_a, v_b;
424 EXIT WHEN c%NOTFOUND;
425 SELECT 'Fetched a record a=' || v_a || ' b=' || v_b AS msg;
426 END LOOP;
427 CLOSE c;
428 END;
429 $$
430 CALL p1(1,'b1');
431 msg
432 Fetched a record a=1 b=b1
433 msg
434 Fetched a record a=2 b=b1b
435 DROP PROCEDURE p1;
436 #
437 # Parameters in SELECT list + type conversion + warnings
438 #
439 CREATE PROCEDURE p1(a_a VARCHAR)
440 AS
441 v_a INT;
442 CURSOR c (p_a INT) IS SELECT p_a FROM DUAL;
443 BEGIN
444 OPEN c(a_a);
445 LOOP
446 FETCH c INTO v_a;
447 EXIT WHEN c%NOTFOUND;
448 SELECT 'Fetched a record a=' || v_a AS msg;
449 END LOOP;
450 CLOSE c;
451 END;
452 $$
453 CALL p1('1b');
454 msg
455 Fetched a record a=1
456 Warnings:
457 Warning 1265 Data truncated for column 'p_a' at row 0
458 CALL p1('b1');
459 msg
460 Fetched a record a=0
461 Warnings:
462 Warning 1366 Incorrect integer value: 'b1' for column ``.``.`p_a` at row 0
463 DROP PROCEDURE p1;
464 #
465 # One parameter in SELECT list + subselect
466 #
467 CREATE PROCEDURE p1(a_a VARCHAR)
468 AS
469 v_a VARCHAR(10);
470 CURSOR c (p_a VARCHAR) IS
471 SELECT p_a FROM DUAL UNION SELECT REVERSE(p_a) FROM DUAL;
472 BEGIN
473 OPEN c((SELECT a_a));
474 LOOP
475 FETCH c INTO v_a;
476 EXIT WHEN c%NOTFOUND;
477 SELECT v_a;
478 END LOOP;
479 CLOSE c;
480 END;
481 $$
482 CALL p1('ab');
483 v_a
484 ab
485 v_a
486 ba
487 DROP PROCEDURE p1;
488 #
489 # Two parameters in SELECT list + subselect
490 #
491 SET sql_mode=ORACLE;
492 CREATE PROCEDURE p1()
493 AS
494 v_a VARCHAR(10);
495 v_b VARCHAR(20);
496 CURSOR c (p_a VARCHAR, p_b VARCHAR) IS
497 SELECT p_a, p_b FROM DUAL
498 UNION
499 SELECT p_b, p_a FROM DUAL;
500 BEGIN
501 OPEN c((SELECT 'aaa'),(SELECT 'bbb'));
502 LOOP
503 FETCH c INTO v_a, v_b;
504 EXIT WHEN c%NOTFOUND;
505 SELECT v_a, v_b;
506 END LOOP;
507 CLOSE c;
508 END;
509 $$
510 CALL p1();
511 v_a v_b
512 aaa bbb
513 v_a v_b
514 bbb aaa
515 DROP PROCEDURE p1;
516 #
517 # Two parameters in SELECT list + two parameters in WHERE + subselects
518 #
519 SET sql_mode=ORACLE;
520 CREATE PROCEDURE p1(a_a VARCHAR, a_b VARCHAR)
521 AS
522 v_a VARCHAR(10);
523 v_b VARCHAR(20);
524 CURSOR c (value_a VARCHAR, value_b VARCHAR,
525 pattern_a VARCHAR, pattern_b VARCHAR) IS
526 SELECT value_a, value_b FROM DUAL WHERE value_a LIKE pattern_a
527 UNION
528 SELECT value_b, value_a FROM DUAL WHERE value_b LIKE pattern_b;
529 BEGIN
530 OPEN c((SELECT 'aaa'),(SELECT 'bbb'),(SELECT a_a),(SELECT a_b));
531 LOOP
532 FETCH c INTO v_a, v_b;
533 EXIT WHEN c%NOTFOUND;
534 SELECT v_a, v_b;
535 END LOOP;
536 CLOSE c;
537 END;
538 $$
539 CALL p1('%','%');
540 v_a v_b
541 aaa bbb
542 v_a v_b
543 bbb aaa
544 CALL p1('aaa','xxx');
545 v_a v_b
546 aaa bbb
547 CALL p1('xxx','bbb');
548 v_a v_b
549 bbb aaa
550 CALL p1('xxx','xxx');
551 DROP PROCEDURE p1;
552 #
553 # Parameters in SELECT list + stored function
554 #
555 CREATE FUNCTION f1 (a VARCHAR) RETURN VARCHAR
556 AS
557 BEGIN
558 RETURN a || 'y';
559 END;
560 $$
561 CREATE PROCEDURE p1(a_a VARCHAR)
562 AS
563 v_a VARCHAR(10);
564 v_b VARCHAR(10);
565 CURSOR c (p_sel_a VARCHAR, p_cmp_a VARCHAR) IS
566 SELECT p_sel_a, p_cmp_a FROM DUAL;
567 BEGIN
568 OPEN c(f1(a_a), f1(a_a));
569 LOOP
570 FETCH c INTO v_a, v_b;
571 EXIT WHEN c%NOTFOUND;
572 SELECT v_a;
573 END LOOP;
574 CLOSE c;
575 END;
576 $$
577 CALL p1('x');
578 v_a
579 xy
580 CALL p1(f1(COALESCE(NULL, f1('x'))));
581 v_a
582 xyyy
583 DROP PROCEDURE p1;
584 DROP FUNCTION f1;
585 #
586 # One parameter in WHERE clause
587 #
588 CREATE TABLE t1 (a INT, b VARCHAR(10));
589 CREATE TABLE t2 (a INT, b VARCHAR(10));
590 INSERT INTO t1 VALUES (1,'11');
591 INSERT INTO t1 VALUES (1,'12');
592 INSERT INTO t1 VALUES (2,'21');
593 INSERT INTO t1 VALUES (2,'22');
594 INSERT INTO t1 VALUES (3,'31');
595 INSERT INTO t1 VALUES (3,'32');
596 CREATE PROCEDURE p1(a_a INT)
597 AS
598 v_a INT;
599 v_b VARCHAR(10);
600 CURSOR c (p_a INT) IS SELECT a,b FROM t1 WHERE a=p_a;
601 BEGIN
602 OPEN c(a_a);
603 LOOP
604 FETCH c INTO v_a, v_b;
605 EXIT WHEN c%NOTFOUND;
606 INSERT INTO t2 VALUES (v_a,v_b);
607 END LOOP;
608 CLOSE c;
609 END;
610 $$
611 CALL p1(1);
612 SELECT * FROM t2;
613 a b
614 1 11
615 1 12
616 DROP TABLE t1;
617 DROP TABLE t2;
618 DROP PROCEDURE p1;
619 #
620 # Two parameters in WHERE clause
621 #
622 CREATE TABLE t1 (a INT, b VARCHAR(10));
623 CREATE TABLE t2 (a INT, b VARCHAR(10));
624 INSERT INTO t1 VALUES (1,'11');
625 INSERT INTO t1 VALUES (1,'12');
626 INSERT INTO t1 VALUES (2,'21');
627 INSERT INTO t1 VALUES (2,'22');
628 INSERT INTO t1 VALUES (3,'31');
629 INSERT INTO t1 VALUES (3,'32');
630 CREATE PROCEDURE p1(a_a INT, a_b VARCHAR)
631 AS
632 v_a INT;
633 v_b VARCHAR(10);
634 CURSOR c (p_a INT, p_b VARCHAR) IS SELECT a,b FROM t1 WHERE a=p_a AND b=p_b;
635 BEGIN
636 OPEN c(a_a, a_b);
637 LOOP
638 FETCH c INTO v_a, v_b;
639 EXIT WHEN c%NOTFOUND;
640 INSERT INTO t2 VALUES (v_a,v_b);
641 END LOOP;
642 CLOSE c;
643 END;
644 $$
645 CALL p1(1,'11');
646 SELECT * FROM t2;
647 a b
648 1 11
649 DROP TABLE t1;
650 DROP TABLE t2;
651 DROP PROCEDURE p1;
652 #
653 # Parameters in WHERE and HAVING clauses
654 #
655 CREATE TABLE t1 (name VARCHAR(10), value INT);
656 INSERT INTO t1 VALUES ('but',1);
657 INSERT INTO t1 VALUES ('but',1);
658 INSERT INTO t1 VALUES ('but',1);
659 INSERT INTO t1 VALUES ('bin',1);
660 INSERT INTO t1 VALUES ('bin',1);
661 INSERT INTO t1 VALUES ('bot',1);
662 CREATE PROCEDURE p1 (arg_name_limit VARCHAR, arg_total_limit INT)
663 AS
664 v_name VARCHAR(10);
665 v_total INT;
666 -- +0 is needed to work around the bug MDEV-11081
667 CURSOR c(p_v INT) IS
668 SELECT name, SUM(value + p_v) + 0 AS total FROM t1
669 WHERE name LIKE arg_name_limit
670 GROUP BY name HAVING total>=arg_total_limit;
671 BEGIN
672 FOR i IN 0..1
673 LOOP
674 OPEN c(i);
675 LOOP
676 FETCH c INTO v_name, v_total;
677 EXIT WHEN c%NOTFOUND;
678 SELECT v_name, v_total;
679 END LOOP;
680 CLOSE c;
681 END LOOP;
682 END;
683 $$
684 CALL p1('%', 2);
685 v_name v_total
686 bin 2
687 v_name v_total
688 but 3
689 v_name v_total
690 bin 4
691 v_name v_total
692 bot 2
693 v_name v_total
694 but 6
695 CALL p1('b_t', 0);
696 v_name v_total
697 bot 1
698 v_name v_total
699 but 3
700 v_name v_total
701 bot 2
702 v_name v_total
703 but 6
704 DROP PROCEDURE p1;
705 DROP TABLE t1;
706 #
707 # One parameter in LIMIT clause
708 #
709 CREATE TABLE t1 (a INT, b VARCHAR(10));
710 INSERT INTO t1 VALUES (1,'b1');
711 INSERT INTO t1 VALUES (2,'b2');
712 INSERT INTO t1 VALUES (3,'b3');
713 INSERT INTO t1 VALUES (4,'b4');
714 INSERT INTO t1 VALUES (5,'b5');
715 INSERT INTO t1 VALUES (6,'b6');
716 CREATE PROCEDURE p1(a_a INT)
717 AS
718 v_a INT;
719 v_b VARCHAR(10);
720 CURSOR c (p_a INT) IS SELECT a,b FROM t1 ORDER BY a LIMIT p_a;
721 BEGIN
722 CREATE TABLE t2 (a INT, b VARCHAR(10));
723 OPEN c(a_a);
724 LOOP
725 FETCH c INTO v_a, v_b;
726 EXIT WHEN c%NOTFOUND;
727 INSERT INTO t2 VALUES (v_a,v_b);
728 END LOOP;
729 CLOSE c;
730 SELECT * FROM t2;
731 DROP TABLE t2;
732 END;
733 $$
734 CALL p1(1);
735 a b
736 1 b1
737 CALL p1(3);
738 a b
739 1 b1
740 2 b2
741 3 b3
742 CALL p1(6);
743 a b
744 1 b1
745 2 b2
746 3 b3
747 4 b4
748 5 b5
749 6 b6
750 DROP TABLE t1;
751 DROP PROCEDURE p1;
752 #
753 # End of MDEV-10597 Cursors with parameters
754 #
755 #
756 # MDEV-12209 sql_mode=ORACLE: Syntax error in a OPEN cursor with parameters makes the server crash
757 #
758 CREATE TABLE t1 (a INT, b VARCHAR(10));
759 INSERT INTO t1 VALUES (1,'A');
760 CREATE PROCEDURE p1(a INT,b VARCHAR)
761 AS
762 CURSOR c (p_a INT, p_b VARCHAR) IS SELECT * FROM t1 WHERE a=p_a;
763 BEGIN
764 OPEN c(a+, b);
765 LOOP
766 FETCH c INTO a, b;
767 EXIT WHEN c%NOTFOUND;
768 SELECT a, b;
769 END LOOP;
770 CLOSE c;
771 END;
772 $$
773 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 ' b);
774 LOOP
775 FETCH c INTO a, b;
776 EXIT WHEN c%NOTFOUND;
777 SELECT a, b;
778 END LOOP;
779 CLO...' at line 5
780 DROP TABLE t1;
781 #
782 # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
783 #
784 CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3));
785 INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123');
786 INSERT INTO t1 VALUES (2,'b2','2001-01-02 10:20:30.123');
787 CREATE TABLE t2 LIKE t1;
788 CREATE PROCEDURE p1()
789 AS
790 v_a t1.a%TYPE;
791 v_b t1.b%TYPE;
792 v_c t1.c%TYPE;
793 CURSOR c IS SELECT a,b,c FROM t1;
794 BEGIN
795 OPEN c;
796 LOOP
797 FETCH c INTO v_a, v_b, v_c;
798 EXIT WHEN c%NOTFOUND;
799 INSERT INTO t2 (a,b,c) VALUES (v_a, v_b, v_c);
800 END LOOP;
801 CLOSE c;
802 END;
803 $$
804 CALL p1();
805 SELECT * FROM t2;
806 a b c
807 1 b1 2001-01-01 10:20:30.123
808 2 b2 2001-01-02 10:20:30.123
809 DROP TABLE t2;
810 DROP PROCEDURE p1;
811 DROP TABLE t1;
812 #
813 # MDEV-12007 Allow ROW variables as a cursor FETCH target
814 #
815 CREATE TABLE t1 (a INT, b VARCHAR(32));
816 INSERT INTO t1 VALUES (10,'b10');
817 INSERT INTO t1 VALUES (20,'b20');
818 INSERT INTO t1 VALUES (30,'b30');
819 CREATE PROCEDURE p1 AS
820 rec ROW(a INT, b VARCHAR(32));
821 CURSOR c IS SELECT a,b FROM t1;
822 BEGIN
823 OPEN c;
824 LOOP
825 FETCH c INTO rec;
826 EXIT WHEN c%NOTFOUND;
827 SELECT ('rec=(' || rec.a ||','|| rec.b||')') AS c;
828 END LOOP;
829 CLOSE c;
830 END;
831 $$
832 CALL p1();
833 c
834 rec=(10,b10)
835 c
836 rec=(20,b20)
837 c
838 rec=(30,b30)
839 DROP PROCEDURE p1;
840 DROP TABLE t1;
841 #
842 # MDEV-12441 Variables declared after cursors with parameters lose values
843 #
844 CREATE PROCEDURE p1() AS
845 x0 INT:=100;
846 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
847 x1 INT:=101;
848 BEGIN
849 OPEN cur(10,11);
850 CLOSE cur;
851 SELECT x0, x1;
852 END;
853 $$
854 CALL p1();
855 x0 x1
856 100 101
857 DROP PROCEDURE p1;
858 CREATE TABLE t1 (a INT);
859 CREATE PROCEDURE p1() AS
860 x0 INT:=100;
861 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
862 x1 t1.a%TYPE:=101;
863 BEGIN
864 OPEN cur(10,11);
865 CLOSE cur;
866 SELECT x0, x1;
867 END;
868 $$
869 CALL p1();
870 x0 x1
871 100 101
872 DROP PROCEDURE p1;
873 DROP TABLE t1;
874 CREATE PROCEDURE p1() AS
875 x0 INT:=100;
876 CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
877 x1 ROW(a INT,b INT):=ROW(101,102);
878 BEGIN
879 OPEN cur(10,11);
880 CLOSE cur;
881 SELECT x0, x1.a, x1.b;
882 END;
883 $$
884 CALL p1();
885 x0 x1.a x1.b
886 100 101 102
887 DROP PROCEDURE p1;
888 CREATE TABLE t1 (a INT, b VARCHAR(10));
889 INSERT INTO t1 VALUES (10,'Tbl-t1.b0');
890 CREATE PROCEDURE p1() AS
891 x0 INT:=100;
892 CURSOR cur(cp1 INT, cp2 INT) IS SELECT a,b FROM t1;
893 x1 t1%ROWTYPE:=ROW(101,'Var-x1.b0');
894 BEGIN
895 SELECT x0, x1.a, x1.b;
896 OPEN cur(10,11);
897 FETCH cur INTO x1;
898 CLOSE cur;
899 SELECT x0, x1.a, x1.b;
900 END;
901 $$
902 CALL p1();
903 x0 x1.a x1.b
904 100 101 Var-x1.b0
905 x0 x1.a x1.b
906 100 10 Tbl-t1.b0
907 DROP PROCEDURE p1;
908 DROP TABLE t1;
909 CREATE TABLE t1 (a INT, b VARCHAR(10));
910 INSERT INTO t1 VALUES (10,'Tbl-t1.b0');
911 CREATE PROCEDURE p1() AS
912 x0 INT:=100;
913 CURSOR cur(cp1 INT, cp2 INT) IS SELECT a,b FROM t1;
914 x1 cur%ROWTYPE:=ROW(101,'Var-x1.b0');
915 BEGIN
916 SELECT x0, x1.a, x1.b;
917 OPEN cur(10,11);
918 FETCH cur INTO x1;
919 CLOSE cur;
920 SELECT x0, x1.a, x1.b;
921 END;
922 $$
923 CALL p1();
924 x0 x1.a x1.b
925 100 101 Var-x1.b0
926 x0 x1.a x1.b
927 100 10 Tbl-t1.b0
928 DROP PROCEDURE p1;
929 DROP TABLE t1;
930 #
931 # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
932 #
933 DECLARE
934 CURSOR c IS SELECT 1 AS c FROM DUAL;
935 BEGIN
936 OPEN c;
937 SELECT
938 c%ISOPEN,
939 c%NOTFOUND,
940 c%FOUND,
941 c%ROWCOUNT;
942 CLOSE c;
943 END;
944 $$
945 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
946 def c%ISOPEN 3 1 1 N 32897 0 63
947 def c%NOTFOUND 3 1 0 Y 32896 0 63
948 def c%FOUND 3 1 0 Y 32896 0 63
949 def c%ROWCOUNT 8 21 1 Y 32896 0 63
950 c%ISOPEN c%NOTFOUND c%FOUND c%ROWCOUNT
951 1 NULL NULL 0
952 #
953 # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
954 #
955 CREATE TABLE t1
956 (
957 JOBN varchar(18) NOT NULL,
958 pk int(11) NOT NULL,
959 PRIMARY KEY (pk),
960 KEY (JOBN)
961 );
962 CREATE PROCEDURE p1
963 AS
964 lS NUMBER(10) :=0;
965 CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x';
966 BEGIN
967 FOR lbpd IN cBPD LOOP
968 lS:=lS+1;
969 END LOOP;
970 EXCEPTION
971 WHEN OTHERS THEN
972 BEGIN
973 SELECT SQLERRM;
974 END;
975 END;
976 $$
977 CALL p1();
978 DROP PROCEDURE p1;
979 DROP TABLE t1;
980 #
981 # Start of 10.8 tests
982 #
983 #
984 # MDEV-10654 IN, OUT, INOUT parameters in CREATE FUNCTION
985 #
986 DECLARE
987 va INT;
988 CURSOR cur (a IN INT) IS SELECT a FROM dual;
989 BEGIN
990 OPEN cur(1);
991 FETCH cur INTO va;
992 CLOSE cur;
993 SELECT va;
994 END;
995 $$
996 va
997 1
998 DECLARE
999 va INT;
1000 CURSOR cur (a OUT INT) IS SELECT a FROM dual;
1001 BEGIN
1002 OPEN cur(1);
1003 FETCH cur INTO va;
1004 CLOSE cur;
1005 SELECT va;
1006 END;
1007 $$
1008 ERROR 42000: This version of MariaDB doesn't yet support 'OUT/INOUT cursor parameter'
1009 DECLARE
1010 va INT;
1011 CURSOR cur (a INOUT INT) IS SELECT a FROM dual;
1012 BEGIN
1013 OPEN cur(1);
1014 FETCH cur INTO va;
1015 CLOSE cur;
1016 SELECT va;
1017 END;
1018 $$
1019 ERROR 42000: This version of MariaDB doesn't yet support 'OUT/INOUT cursor parameter'
1020 #
1021 # End of 10.8 tests
1022 #
1023 # Start of 11.4 tests
1024 #
1025 # MDEV-36047 Package body variables are not allowed as FETCH targets
1026 #
1027 CREATE PACKAGE pkg AS
1028 FUNCTION f1 RETURN INT;
1029 END;
1030 $$
1031 CREATE PACKAGE BODY pkg AS
1032 vc INT := 0;
1033 FUNCTION f1 RETURN INT AS
1034 CURSOR cur IS SELECT 1 AS c FROM DUAL;
1035 BEGIN
1036 OPEN cur;
1037 FETCH cur INTO vc; -- SHOW CODE should display vc with a "PACKAGE_BODY" prefix
1038 CLOSE cur;
1039 RETURN vc;
1040 END;
1041 BEGIN
1042 DECLARE
1043 CURSOR cur IS SELECT 1 AS c FROM DUAL;
1044 BEGIN
1045 OPEN cur;
1046 FETCH cur INTO vc; -- SHOW CODE should display vc without a prefix
1047 CLOSE cur;
1048 END;
1049 END;
1050 $$
1051 SELECT pkg.f1() FROM DUAL;
1052 pkg.f1()
1053 1
1054 DROP PACKAGE pkg;
1055 CREATE PACKAGE pkg AS
1056 FUNCTION f1 RETURN TEXT;
1057 END;
1058 $$
1059 CREATE PACKAGE BODY pkg AS
1060 vc1 INT := 0;
1061 FUNCTION f1 RETURN TEXT AS
1062 CURSOR cur IS SELECT 1 AS c1, 2 AS c2 FROM DUAL;
1063 vc2 INT := 0;
1064 BEGIN
1065 OPEN cur;
1066 FETCH cur INTO vc1, vc2;
1067 CLOSE cur;
1068 RETURN vc1 || ' ' || vc2;
1069 END;
1070 END;
1071 $$
1072 SELECT pkg.f1() FROM DUAL;
1073 pkg.f1()
1074 1 2
1075 DROP PACKAGE pkg;
1076 CREATE PACKAGE pkg AS
1077 FUNCTION f1 RETURN TEXT;
1078 END;
1079 $$
1080 CREATE PACKAGE BODY pkg AS
1081 vc ROW(p1 INT, p2 INT);
1082 FUNCTION f1 RETURN TEXT AS
1083 CURSOR cur IS SELECT 1 AS c1, 2 AS c2 FROM DUAL;
1084 BEGIN
1085 OPEN cur;
1086 FETCH cur INTO vc;
1087 CLOSE cur;
1088 RETURN vc.p1 || ' ' || vc.p2;
1089 END;
1090 END;
1091 $$
1092 SELECT pkg.f1() FROM DUAL;
1093 pkg.f1()
1094 1 2
1095 DROP PACKAGE pkg;
1096 # End of 11.4 tests