Debian Codegraph @

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

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