mirror of
https://github.com/MariaDB/server.git
synced 2025-02-04 21:02:17 +01:00
bf5a144e16
This patch includes: - MDEV-19639 sql_mode=ORACLE: Wrong SHOW PROCEDURE output for sysvar:=expr - MDEV-19640 Wrong SHOW PROCEDURE output for SET GLOBAL sysvar1=expr, sysvar2=expr - Preparatory refactoring for MySQL WL#4179 Detailed change list: 1. Changing sp_create_assignment_lex() to accept the position in the exact query buffer instead of a "bool no_lookahead". This actually fixes MDEV-19639. In the previous reduction sp_create_assignment_lex() was called too late, when the parser went far the from beginning of the statement, so only a part of the statement got into sp_instr_stmt. 2. Generating "SET" or "SET GLOBAL" inside sp_create_assignment_instr() depending on the option type. This fixes MDEV-19640. In the previous reduction the code passed (through no_lookahead) the position of the word GLOBAL inside sp_create_assignment_lex(), which worked only for the left-most assignment. 3. Fixing the affected rules to use: - ident_cli instead of ident - ident_cli_set_usual_case instead of ident_set_usual_case 4. Changing the input parameter in: - LEX::set_system_variable() - LEX::call_statement_start() - LEX::set_variable() from just LEX_CSTRING to Lex_ident_sys_st for stricter data type constrol: to make sure that noone passes an ident_cli (a fragment of the original query in the client character set) instead of server-side identifier (utf8 identifier allocated on THD when needed). 5. Adding Lex_ident_sys() in places where the affected functions are called. 6. Moving all calls of sp_create_assignment_lex() to the places just before parsing set_expr_or_default. This makes the grammar clearer, because sp_create_assignment_lex() and sp_create_assignment_instr() now stay near each other, so the balance of LEX's push/pop can be read easier. This will also help to WL#4179. 7. Adding class sp_lex_set_var Moving the initialization code from sp_create_assignment_lex() to the constructor of sp_lex_set_var. This will also help to WL#4179. 8. Moving a part of the "set" grammar rule into a separate rule "set_param". This makes the grammar easier to read and removes one shift/reduce conflict.
1516 lines
25 KiB
Text
1516 lines
25 KiB
Text
SET sql_mode=ORACLE;
|
|
#
|
|
# Testing exceptions in the top-level blocks
|
|
#
|
|
# No HANDLER declarations, no exceptions
|
|
CREATE FUNCTION f1 RETURN INT
|
|
AS
|
|
BEGIN
|
|
RETURN 10;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 freturn int 10
|
|
SELECT f1();
|
|
f1()
|
|
10
|
|
DROP FUNCTION f1;
|
|
# No HANDLER declarations, no code, no exceptions
|
|
CREATE PROCEDURE p1 ()
|
|
IS
|
|
BEGIN
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 jump 2
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
# No HANDLER declarations, no code, some exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
EXCEPTION
|
|
WHEN 1002 THEN v:=225;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 jump 1
|
|
1 hpush_jump 4 1 EXIT
|
|
2 set v@0 225
|
|
3 hreturn 0 4
|
|
4 hpop 1
|
|
set @v= 10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
10
|
|
DROP PROCEDURE p1;
|
|
# No HANDLER declarations, some code, some exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=224;
|
|
EXCEPTION
|
|
WHEN 1002 THEN v:=225;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 jump 3
|
|
1 set v@0 224
|
|
2 jump 6
|
|
3 hpush_jump 1 1 EXIT
|
|
4 set v@0 225
|
|
5 hreturn 0 6
|
|
6 hpop 1
|
|
set @v= 10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
224
|
|
DROP PROCEDURE p1;
|
|
# Some HANDLER declarations, no code, no exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
EXIT HANDLER FOR 1000
|
|
BEGIN
|
|
v:=123;
|
|
END;
|
|
BEGIN
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 hpush_jump 3 1 EXIT
|
|
1 set v@0 123
|
|
2 hreturn 0 3
|
|
3 hpop 1
|
|
set @v= 10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
10
|
|
DROP PROCEDURE p1;
|
|
# Some HANDLER declarations, no code, some exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
EXIT HANDLER FOR 1000
|
|
BEGIN
|
|
v:=123;
|
|
END;
|
|
BEGIN
|
|
EXCEPTION
|
|
WHEN 1002 THEN v:=225;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 hpush_jump 3 1 EXIT
|
|
1 set v@0 123
|
|
2 hreturn 0 6
|
|
3 hpush_jump 6 1 EXIT
|
|
4 set v@0 225
|
|
5 hreturn 0 6
|
|
6 hpop 2
|
|
set @v= 10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
10
|
|
DROP PROCEDURE p1;
|
|
# Some HANDLER declarations, some code, no exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
EXIT HANDLER FOR 1000
|
|
BEGIN
|
|
v:=123;
|
|
END;
|
|
BEGIN
|
|
v:=223;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 hpush_jump 3 1 EXIT
|
|
1 set v@0 123
|
|
2 hreturn 0 4
|
|
3 set v@0 223
|
|
4 hpop 1
|
|
set @v= 10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
223
|
|
DROP PROCEDURE p1;
|
|
# Some HANDLER declarations, some code, some exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT VARCHAR2(20))
|
|
IS
|
|
EXIT HANDLER FOR 1000
|
|
BEGIN
|
|
v:=123;
|
|
END;
|
|
CONTINUE HANDLER FOR 1001
|
|
BEGIN
|
|
SET v=223;
|
|
END;
|
|
BEGIN
|
|
v:= 1;
|
|
EXCEPTION
|
|
WHEN 1002 THEN SET v=225;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 hpush_jump 3 1 EXIT
|
|
1 set v@0 123
|
|
2 hreturn 0 12
|
|
3 hpush_jump 8 1 CONTINUE
|
|
4 set v@0 223
|
|
5 hreturn 1
|
|
6 set v@0 1
|
|
7 jump 12
|
|
8 hpush_jump 6 1 EXIT
|
|
9 set v@0 225
|
|
10 hreturn 0 12
|
|
11 jump 6
|
|
12 hpop 3
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# Testing EXCEPTIONS in internal blocks
|
|
#
|
|
# No HANDLER declarations, no code, no exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=123;
|
|
BEGIN
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v@0 123
|
|
1 jump 5
|
|
SET @v=10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
123
|
|
DROP PROCEDURE p1;
|
|
# No HANDLER declarations, no code, some exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=123;
|
|
BEGIN
|
|
EXCEPTION
|
|
WHEN 20002 THEN v:=335;
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v@0 123
|
|
1 jump 2
|
|
2 hpush_jump 5 1 EXIT
|
|
3 set v@0 335
|
|
4 hreturn 0 5
|
|
5 hpop 1
|
|
SET @v=10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
123
|
|
DROP PROCEDURE p1;
|
|
# No HANDLER declarations, some code, no exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=123;
|
|
BEGIN
|
|
v:=223;
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v@0 123
|
|
1 set v@0 223
|
|
2 jump 6
|
|
SET @v=10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
223
|
|
DROP PROCEDURE p1;
|
|
# No HANDLER declarations, some code, some exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=123;
|
|
BEGIN
|
|
v:=223;
|
|
EXCEPTION
|
|
WHEN 20002 THEN v:=335;
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v@0 123
|
|
1 jump 4
|
|
2 set v@0 223
|
|
3 jump 7
|
|
4 hpush_jump 2 1 EXIT
|
|
5 set v@0 335
|
|
6 hreturn 0 7
|
|
7 hpop 1
|
|
SET @v=10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
223
|
|
DROP PROCEDURE p1;
|
|
# Some HANDLER declarations, no code, no exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=123;
|
|
DECLARE
|
|
EXIT HANDLER FOR 1000
|
|
BEGIN
|
|
v:=323;
|
|
END;
|
|
BEGIN
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v@0 123
|
|
1 hpush_jump 4 1 EXIT
|
|
2 set v@0 323
|
|
3 hreturn 0 4
|
|
4 hpop 1
|
|
SET @v=10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
123
|
|
DROP PROCEDURE p1;
|
|
# Some HANDLER declarations, no code, some exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=123;
|
|
DECLARE
|
|
EXIT HANDLER FOR 1000
|
|
BEGIN
|
|
v:=323;
|
|
END;
|
|
BEGIN
|
|
EXCEPTION
|
|
WHEN 20002 THEN v:=335;
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v@0 123
|
|
1 hpush_jump 4 1 EXIT
|
|
2 set v@0 323
|
|
3 hreturn 0 7
|
|
4 hpush_jump 7 1 EXIT
|
|
5 set v@0 335
|
|
6 hreturn 0 7
|
|
7 hpop 2
|
|
SET @v=10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
123
|
|
DROP PROCEDURE p1;
|
|
# Some HANDLER declarations, some code, no exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=123;
|
|
DECLARE
|
|
EXIT HANDLER FOR 1000
|
|
BEGIN
|
|
v:=323;
|
|
END;
|
|
BEGIN
|
|
v:= 324;
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v@0 123
|
|
1 hpush_jump 4 1 EXIT
|
|
2 set v@0 323
|
|
3 hreturn 0 5
|
|
4 set v@0 324
|
|
5 hpop 1
|
|
SET @v=10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
324
|
|
DROP PROCEDURE p1;
|
|
# Some HANDLER declarations, some code, some exceptions
|
|
CREATE PROCEDURE p1 (v IN OUT INT)
|
|
IS
|
|
BEGIN
|
|
v:=123;
|
|
DECLARE
|
|
EXIT HANDLER FOR 1000
|
|
BEGIN
|
|
v:=323;
|
|
END;
|
|
BEGIN
|
|
v:= 324;
|
|
EXCEPTION WHEN 2002 THEN v:= 325;
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v@0 123
|
|
1 hpush_jump 6 1 EXIT
|
|
2 set v@0 323
|
|
3 hreturn 0 9
|
|
4 set v@0 324
|
|
5 jump 9
|
|
6 hpush_jump 4 1 EXIT
|
|
7 set v@0 325
|
|
8 hreturn 0 9
|
|
9 hpop 2
|
|
SET @v=10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
324
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# Testing EXIT statement
|
|
#
|
|
CREATE FUNCTION f1 RETURN INT
|
|
IS
|
|
i INT := 0;
|
|
BEGIN
|
|
LOOP
|
|
i:= i + 1;
|
|
IF i >= 5 THEN
|
|
EXIT;
|
|
END IF;
|
|
END LOOP;
|
|
RETURN i;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set i@0 0
|
|
1 set i@0 i@0 + 1
|
|
2 jump_if_not 1(1) i@0 >= 5
|
|
3 jump 4
|
|
4 freturn int i@0
|
|
SELECT f1() FROM DUAL;
|
|
f1()
|
|
5
|
|
DROP FUNCTION f1;
|
|
CREATE FUNCTION f1 RETURN INT
|
|
IS
|
|
i INT := 0;
|
|
BEGIN
|
|
LOOP
|
|
i:= i + 1;
|
|
EXIT WHEN i >=5;
|
|
END LOOP;
|
|
RETURN i;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set i@0 0
|
|
1 set i@0 i@0 + 1
|
|
2 jump_if_not 1(0) i@0 >= 5
|
|
3 jump 4
|
|
4 freturn int i@0
|
|
SELECT f1() FROM DUAL;
|
|
f1()
|
|
5
|
|
DROP FUNCTION f1;
|
|
CREATE FUNCTION f1 RETURN INT
|
|
IS
|
|
i INT := 0;
|
|
BEGIN
|
|
LOOP
|
|
BEGIN
|
|
i:= i + 1;
|
|
IF i >= 5 THEN
|
|
EXIT;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN i:= 1000;
|
|
END;
|
|
END LOOP;
|
|
RETURN i;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set i@0 0
|
|
1 jump 5
|
|
2 set i@0 i@0 + 1
|
|
3 jump_if_not 8(8) i@0 >= 5
|
|
4 jump 10
|
|
5 hpush_jump 2 1 EXIT
|
|
6 set i@0 1000
|
|
7 hreturn 0 8
|
|
8 hpop 1
|
|
9 jump 5
|
|
10 freturn int i@0
|
|
SELECT f1() FROM DUAL;
|
|
f1()
|
|
5
|
|
DROP FUNCTION f1;
|
|
CREATE PROCEDURE p1(a IN OUT INT)
|
|
IS
|
|
i INT := 0;
|
|
BEGIN
|
|
LOOP
|
|
LOOP
|
|
BEGIN
|
|
i:= i + 1;
|
|
IF i >=5 THEN
|
|
EXIT;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN a:=1000;
|
|
END;
|
|
END LOOP;
|
|
i:= i + 100;
|
|
EXIT;
|
|
END LOOP;
|
|
a:= i;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN a:=11;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set i@1 0
|
|
1 jump 14
|
|
2 set i@1 i@1 + 1
|
|
3 jump_if_not 8(8) i@1 >= 5
|
|
4 jump 10
|
|
5 hpush_jump 2 2 EXIT
|
|
6 set a@0 1000
|
|
7 hreturn 0 8
|
|
8 hpop 1
|
|
9 jump 5
|
|
10 set i@1 i@1 + 100
|
|
11 jump 12
|
|
12 set a@0 i@1
|
|
13 jump 17
|
|
14 hpush_jump 5 2 EXIT
|
|
15 set a@0 11
|
|
16 hreturn 0 17
|
|
17 hpop 1
|
|
set @v= 10;
|
|
CALL p1(@v);
|
|
SELECT @v;
|
|
@v
|
|
105
|
|
DROP PROCEDURE p1;
|
|
# Testing RETURN in procedures
|
|
CREATE PROCEDURE p1 (a IN OUT INT)
|
|
AS
|
|
BEGIN
|
|
IF a < 10 THEN
|
|
BEGIN
|
|
a:= a + 1;
|
|
RETURN;
|
|
END;
|
|
END IF;
|
|
a:= 200;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
BEGIN
|
|
a:= 100;
|
|
RETURN;
|
|
END;
|
|
END;
|
|
/
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 jump 6
|
|
1 jump_if_not 4(4) a@0 < 10
|
|
2 set a@0 a@0 + 1
|
|
3 preturn
|
|
4 set a@0 200
|
|
5 jump 9
|
|
6 hpush_jump 1 1 EXIT
|
|
7 set a@0 100
|
|
8 preturn
|
|
9 hpop 1
|
|
DROP PROCEDURE p1;
|
|
# Testing FOR loop statement
|
|
CREATE FUNCTION f1 (a INT, b INT) RETURN INT
|
|
AS
|
|
total INT := 0;
|
|
BEGIN
|
|
FOR i IN 1 .. a
|
|
LOOP
|
|
total:= total + i;
|
|
IF i = b THEN
|
|
EXIT;
|
|
END IF;
|
|
END LOOP;
|
|
RETURN total;
|
|
END
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set total@2 0
|
|
1 set i@3 1
|
|
2 set [target_bound]@4 a@0
|
|
3 jump_if_not 9(9) i@3 <= [target_bound]@4
|
|
4 set total@2 total@2 + i@3
|
|
5 jump_if_not 7(7) i@3 = b@1
|
|
6 jump 9
|
|
7 set i@3 i@3 + 1
|
|
8 jump 3
|
|
9 freturn int total@2
|
|
SELECT f1(3, 100) FROM DUAL;
|
|
f1(3, 100)
|
|
6
|
|
SELECT f1(3, 2) FROM DUAL;
|
|
f1(3, 2)
|
|
3
|
|
DROP FUNCTION f1;
|
|
CREATE FUNCTION f1 (a INT, b INT) RETURN INT
|
|
AS
|
|
total INT := 0;
|
|
BEGIN
|
|
FOR i IN REVERSE 1..a
|
|
LOOP
|
|
total:= total + i;
|
|
IF i = b THEN
|
|
EXIT;
|
|
END IF;
|
|
END LOOP;
|
|
RETURN total;
|
|
END
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set total@2 0
|
|
1 set i@3 a@0
|
|
2 set [target_bound]@4 1
|
|
3 jump_if_not 9(9) i@3 >= [target_bound]@4
|
|
4 set total@2 total@2 + i@3
|
|
5 jump_if_not 7(7) i@3 = b@1
|
|
6 jump 9
|
|
7 set i@3 i@3 + -1
|
|
8 jump 3
|
|
9 freturn int total@2
|
|
SELECT f1(3, 100) FROM DUAL;
|
|
f1(3, 100)
|
|
6
|
|
SELECT f1(3, 2) FROM DUAL;
|
|
f1(3, 2)
|
|
5
|
|
DROP FUNCTION f1;
|
|
# Testing labeled FOR LOOP statement
|
|
CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURN INT
|
|
AS
|
|
total INT := 0;
|
|
BEGIN
|
|
<<la>>
|
|
FOR ia IN 1 .. a
|
|
LOOP
|
|
total:= total + 1000;
|
|
<<lb>>
|
|
FOR ib IN 1 .. b
|
|
LOOP
|
|
total:= total + 1;
|
|
EXIT lb WHEN ib = limitb;
|
|
EXIT la WHEN ia = limita;
|
|
END LOOP lb;
|
|
END LOOP la;
|
|
RETURN total;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set total@4 0
|
|
1 set ia@5 1
|
|
2 set [target_bound]@6 a@0
|
|
3 jump_if_not 17(17) ia@5 <= [target_bound]@6
|
|
4 set total@4 total@4 + 1000
|
|
5 set ib@7 1
|
|
6 set [target_bound]@8 b@2
|
|
7 jump_if_not 15(15) ib@7 <= [target_bound]@8
|
|
8 set total@4 total@4 + 1
|
|
9 jump_if_not 11(0) ib@7 = limitb@3
|
|
10 jump 15
|
|
11 jump_if_not 13(0) ia@5 = limita@1
|
|
12 jump 17
|
|
13 set ib@7 ib@7 + 1
|
|
14 jump 7
|
|
15 set ia@5 ia@5 + 1
|
|
16 jump 3
|
|
17 freturn int total@4
|
|
SELECT f1(2, 1, 2, 2) FROM DUAL;
|
|
f1(2, 1, 2, 2)
|
|
1001
|
|
SELECT f1(2, 2, 2, 2) FROM DUAL;
|
|
f1(2, 2, 2, 2)
|
|
2003
|
|
SELECT f1(2, 3, 2, 3) FROM DUAL;
|
|
f1(2, 3, 2, 3)
|
|
2004
|
|
DROP FUNCTION f1;
|
|
# Testing labeled ITERATE in a labeled FOR LOOP
|
|
CREATE FUNCTION f1(a INT) RETURN INT
|
|
AS
|
|
total INT:= 0;
|
|
BEGIN
|
|
<<li>>
|
|
FOR i IN 1 .. a
|
|
LOOP
|
|
total:= total + 1000;
|
|
IF i = 5 THEN
|
|
ITERATE li;
|
|
END IF;
|
|
total:= total + 1;
|
|
END LOOP;
|
|
RETURN total;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set total@1 0
|
|
1 set i@2 1
|
|
2 set [target_bound]@3 a@0
|
|
3 jump_if_not 11(11) i@2 <= [target_bound]@3
|
|
4 set total@1 total@1 + 1000
|
|
5 jump_if_not 8(8) i@2 = 5
|
|
6 set i@2 i@2 + 1
|
|
7 jump 3
|
|
8 set total@1 total@1 + 1
|
|
9 set i@2 i@2 + 1
|
|
10 jump 3
|
|
11 freturn int total@1
|
|
SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
|
|
f1(3) f1(4) f1(5) f1(6)
|
|
3003 4004 5004 6005
|
|
DROP FUNCTION f1;
|
|
CREATE FUNCTION f1(a INT) RETURN INT
|
|
AS
|
|
total INT:= 0;
|
|
BEGIN
|
|
<<li>>
|
|
FOR i IN 1 .. a
|
|
LOOP
|
|
FOR j IN 1 .. 2
|
|
LOOP
|
|
total:= total + 1000;
|
|
IF i = 5 THEN
|
|
ITERATE li;
|
|
END IF;
|
|
total:= total + 1;
|
|
END LOOP;
|
|
END LOOP;
|
|
RETURN total;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set total@1 0
|
|
1 set i@2 1
|
|
2 set [target_bound]@3 a@0
|
|
3 jump_if_not 16(16) i@2 <= [target_bound]@3
|
|
4 set j@4 1
|
|
5 set [target_bound]@5 2
|
|
6 jump_if_not 14(14) j@4 <= [target_bound]@5
|
|
7 set total@1 total@1 + 1000
|
|
8 jump_if_not 11(11) i@2 = 5
|
|
9 set i@2 i@2 + 1
|
|
10 jump 3
|
|
11 set total@1 total@1 + 1
|
|
12 set j@4 j@4 + 1
|
|
13 jump 6
|
|
14 set i@2 i@2 + 1
|
|
15 jump 3
|
|
16 freturn int total@1
|
|
SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
|
|
f1(3) f1(4) f1(5) f1(6)
|
|
6006 8008 9008 11010
|
|
DROP FUNCTION f1;
|
|
CREATE FUNCTION f1(a INT) RETURN INT
|
|
AS
|
|
total INT:= 0;
|
|
BEGIN
|
|
<<lj>>
|
|
FOR j IN 1 .. 2
|
|
LOOP
|
|
<<li>>
|
|
FOR i IN 1 .. a
|
|
LOOP
|
|
total:= total + 1000;
|
|
IF i = 5 THEN
|
|
ITERATE li;
|
|
END IF;
|
|
total:= total + 1;
|
|
END LOOP;
|
|
END LOOP;
|
|
RETURN total;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set total@1 0
|
|
1 set j@2 1
|
|
2 set [target_bound]@3 2
|
|
3 jump_if_not 16(16) j@2 <= [target_bound]@3
|
|
4 set i@4 1
|
|
5 set [target_bound]@5 a@0
|
|
6 jump_if_not 14(14) i@4 <= [target_bound]@5
|
|
7 set total@1 total@1 + 1000
|
|
8 jump_if_not 11(11) i@4 = 5
|
|
9 set i@4 i@4 + 1
|
|
10 jump 6
|
|
11 set total@1 total@1 + 1
|
|
12 set i@4 i@4 + 1
|
|
13 jump 6
|
|
14 set j@2 j@2 + 1
|
|
15 jump 3
|
|
16 freturn int total@1
|
|
SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
|
|
f1(3) f1(4) f1(5) f1(6)
|
|
6006 8008 10008 12010
|
|
DROP FUNCTION f1;
|
|
# Testing CONTINUE statement
|
|
CREATE FUNCTION f1(a INT) RETURN INT
|
|
AS
|
|
total INT:= 0;
|
|
BEGIN
|
|
FOR i IN 1 .. a
|
|
LOOP
|
|
CONTINUE WHEN i=5;
|
|
total:= total + 1;
|
|
END LOOP;
|
|
RETURN total;
|
|
END;
|
|
/
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set total@1 0
|
|
1 set i@2 1
|
|
2 set [target_bound]@3 a@0
|
|
3 jump_if_not 10(10) i@2 <= [target_bound]@3
|
|
4 jump_if_not 7(0) i@2 = 5
|
|
5 set i@2 i@2 + 1
|
|
6 jump 3
|
|
7 set total@1 total@1 + 1
|
|
8 set i@2 i@2 + 1
|
|
9 jump 3
|
|
10 freturn int total@1
|
|
SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
|
|
f1(3) f1(4) f1(5) f1(6)
|
|
3 4 4 5
|
|
DROP FUNCTION f1;
|
|
#
|
|
# Start of MDEV-10597 Cursors with parameters
|
|
#
|
|
CREATE PROCEDURE p1(arg_value_a VARCHAR, arg_value_b VARCHAR,
|
|
arg_pattern_a VARCHAR, arg_pattern_b VARCHAR)
|
|
AS
|
|
v_a VARCHAR(10);
|
|
v_b VARCHAR(20);
|
|
CURSOR c (p_value_a VARCHAR,
|
|
p_value_b VARCHAR,
|
|
p_pattern_a VARCHAR,
|
|
p_pattern_b VARCHAR,
|
|
p_limit_a INT,
|
|
p_limit_b INT,
|
|
p_unused TEXT) IS
|
|
(SELECT p_value_a, p_value_b FROM DUAL
|
|
WHERE p_value_a LIKE p_pattern_a LIMIT p_limit_a)
|
|
UNION
|
|
(SELECT p_value_b, p_value_a FROM DUAL
|
|
WHERE p_value_b LIKE p_pattern_b LIMIT p_limit_b);
|
|
BEGIN
|
|
OPEN c(arg_value_a, (SELECT arg_value_b),
|
|
arg_pattern_a, arg_pattern_b, 100, 101, 'x');
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT v_a, v_b;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
CALL p1('aaa','bbb','aaa','bbb');
|
|
v_a v_b
|
|
aaa bbb
|
|
v_a v_b
|
|
bbb aaa
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set v_a@4 NULL
|
|
1 set v_b@5 NULL
|
|
2 cpush c@0
|
|
3 set p_value_a@6 arg_value_a@0
|
|
4 set p_value_b@7 (select arg_value_b@1)
|
|
5 set p_pattern_a@8 arg_pattern_a@2
|
|
6 set p_pattern_b@9 arg_pattern_b@3
|
|
7 set p_limit_a@10 100
|
|
8 set p_limit_b@11 101
|
|
9 set p_unused@12 'x'
|
|
10 copen c@0
|
|
11 cfetch c@0 v_a@4 v_b@5
|
|
12 jump_if_not 14(0) "c"%NOTFOUND
|
|
13 jump 16
|
|
14 stmt 0 "SELECT v_a, v_b"
|
|
15 jump 11
|
|
16 cclose c@0
|
|
17 cpop 1
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# End of MDEV-10597 Cursors with parameters
|
|
#
|
|
#
|
|
# MDEV-10914 ROW data type for stored routine variables
|
|
#
|
|
CREATE FUNCTION f1() RETURN INT
|
|
AS
|
|
a ROW(a INT, b INT);
|
|
BEGIN
|
|
a.b:= 200;
|
|
RETURN a.b;
|
|
END;
|
|
$$
|
|
SHOW FUNCTION CODE f1;
|
|
Pos Instruction
|
|
0 set a@0 NULL
|
|
1 set a.b@0[1] 200
|
|
2 freturn int a.b@0[1]
|
|
SELECT f1();
|
|
f1()
|
|
200
|
|
DROP FUNCTION f1;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
rec ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
|
|
BEGIN
|
|
rec:= ROW(10,20.123456,30.123,'test');
|
|
SELECT rec.a, rec.b, rec.c, rec.d;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set rec@0 NULL
|
|
1 set rec@0 (10,20.123456,30.123,'test')
|
|
2 stmt 0 "SELECT rec.a, rec.b, rec.c, rec.d"
|
|
CALL p1;
|
|
rec.a rec.b rec.c rec.d
|
|
10 20.123456 30.123 test
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
rec ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) :=
|
|
ROW(10,20.123456,30.123,'test');
|
|
BEGIN
|
|
SELECT rec.a, rec.b, rec.c, rec.d;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set rec@0 (10,20.123456,30.123,'test')
|
|
1 stmt 0 "SELECT rec.a, rec.b, rec.c, rec.d"
|
|
CALL p1;
|
|
rec.a rec.b rec.c rec.d
|
|
10 20.123456 30.123 test
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
rec1 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
|
|
rec2 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
|
|
BEGIN
|
|
rec1:= ROW(10,20.123456,30.123,'test');
|
|
rec2:= rec1;
|
|
SELECT rec2.a, rec2.b, rec2.c, rec2.d;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set rec1@0 NULL
|
|
1 set rec2@1 NULL
|
|
2 set rec1@0 (10,20.123456,30.123,'test')
|
|
3 set rec2@1 rec1@0
|
|
4 stmt 0 "SELECT rec2.a, rec2.b, rec2.c, rec2.d"
|
|
CALL p1;
|
|
rec2.a rec2.b rec2.c rec2.d
|
|
10 20.123456 30.123 test
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
rec1 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) :=
|
|
ROW(10,20.123456,30.123,'test');
|
|
rec2 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) := rec1;
|
|
BEGIN
|
|
SELECT rec2.a, rec2.b, rec2.c, rec2.d;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set rec1@0 (10,20.123456,30.123,'test')
|
|
1 set rec2@1 rec1@0
|
|
2 stmt 0 "SELECT rec2.a, rec2.b, rec2.c, rec2.d"
|
|
CALL p1;
|
|
rec2.a rec2.b rec2.c rec2.d
|
|
10 20.123456 30.123 test
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# End of MDEV-10914 ROW data type for stored routine variables
|
|
#
|
|
#
|
|
# MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
|
|
#
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
rec1 t1%ROWTYPE;
|
|
BEGIN
|
|
rec1.a:= 10;
|
|
rec1.b:= 'bbb';
|
|
rec1.c:= 10e2;
|
|
rec1.d:= 10.12;
|
|
rec1.c:= rec1.d;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set rec1@0 NULL
|
|
1 set rec1.a@0["a"] 10
|
|
2 set rec1.b@0["b"] 'bbb'
|
|
3 set rec1.c@0["c"] 10e2
|
|
4 set rec1.d@0["d"] 10.12
|
|
5 set rec1.c@0["c"] rec1.d@0["d"]
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable declarations
|
|
#
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
CURSOR cur1 IS SELECT * FROM t1;
|
|
CURSOR cur2 IS SELECT * FROM t1;
|
|
BEGIN
|
|
DECLARE
|
|
rec1,rec2 cur1%ROWTYPE;
|
|
rec3 cur2%ROWTYPE;
|
|
BEGIN
|
|
rec1.a:= 10;
|
|
rec1.b:= 'bbb';
|
|
END;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 cpush cur1@0
|
|
1 cpush cur2@1
|
|
2 cursor_copy_struct cur1 rec1@0
|
|
3 cursor_copy_struct cur1 rec2@1
|
|
4 set rec1@0 NULL
|
|
5 set rec2@1 NULL
|
|
6 cursor_copy_struct cur2 rec3@2
|
|
7 set rec3@2 NULL
|
|
8 set rec1.a@0["a"] 10
|
|
9 set rec1.b@0["b"] 'bbb'
|
|
10 jump 11
|
|
11 cpop 2
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
|
|
#
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
|
|
CURSOR cur1 IS SELECT 10 AS a, 'b0' AS b;
|
|
CURSOR cur2 IS SELECT 10 AS a, 'b0' AS b;
|
|
BEGIN
|
|
FOR rec1 IN cur1
|
|
LOOP
|
|
SELECT rec1.a, rec1.b;
|
|
rec1.a:= 11;
|
|
rec1.b:= 'b1';
|
|
SELECT rec1.a, rec1.b;
|
|
END LOOP;
|
|
FOR rec0 IN cur0
|
|
LOOP
|
|
rec0.a:= 10;
|
|
rec0.b:='b0';
|
|
END LOOP;
|
|
FOR rec2 IN cur2
|
|
LOOP
|
|
rec2.a:= 10;
|
|
rec2.b:='b0';
|
|
END LOOP;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 cpush cur0@0
|
|
1 cpush cur1@1
|
|
2 cpush cur2@2
|
|
3 cursor_copy_struct cur1 rec1@0
|
|
4 copen cur1@1
|
|
5 cfetch cur1@1 rec1@0
|
|
6 jump_if_not 13(13) "cur1"%FOUND
|
|
7 stmt 0 "SELECT rec1.a, rec1.b"
|
|
8 set rec1.a@0["a"] 11
|
|
9 set rec1.b@0["b"] 'b1'
|
|
10 stmt 0 "SELECT rec1.a, rec1.b"
|
|
11 cfetch cur1@1 rec1@0
|
|
12 jump 6
|
|
13 cclose cur1@1
|
|
14 cursor_copy_struct cur0 rec0@1
|
|
15 copen cur0@0
|
|
16 cfetch cur0@0 rec0@1
|
|
17 jump_if_not 22(22) "cur0"%FOUND
|
|
18 set rec0.a@1["a"] 10
|
|
19 set rec0.b@1["b"] 'b0'
|
|
20 cfetch cur0@0 rec0@1
|
|
21 jump 17
|
|
22 cclose cur0@0
|
|
23 cursor_copy_struct cur2 rec2@2
|
|
24 copen cur2@2
|
|
25 cfetch cur2@2 rec2@2
|
|
26 jump_if_not 31(31) "cur2"%FOUND
|
|
27 set rec2.a@2["a"] 10
|
|
28 set rec2.b@2["b"] 'b0'
|
|
29 cfetch cur2@2 rec2@2
|
|
30 jump 26
|
|
31 cclose cur2@2
|
|
32 cpop 3
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
|
|
BEGIN
|
|
FOR rec0 IN cur0
|
|
LOOP
|
|
DECLARE
|
|
CURSOR cur1 IS SELECT 11 AS a, 'b1' AS b;
|
|
BEGIN
|
|
rec0.a:= 11;
|
|
rec0.b:= 'b0';
|
|
FOR rec1 IN cur1
|
|
LOOP
|
|
rec1.a:= 11;
|
|
rec1.b:= 'b1';
|
|
DECLARE
|
|
CURSOR cur2 IS SELECT 12 AS a, 'b2' AS b;
|
|
BEGIN
|
|
FOR rec2 IN cur2
|
|
LOOP
|
|
rec2.a:=12;
|
|
rec2.b:='b2';
|
|
END LOOP;
|
|
END;
|
|
END LOOP;
|
|
END;
|
|
END LOOP;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 cpush cur0@0
|
|
1 cursor_copy_struct cur0 rec0@0
|
|
2 copen cur0@0
|
|
3 cfetch cur0@0 rec0@0
|
|
4 jump_if_not 31(31) "cur0"%FOUND
|
|
5 cpush cur1@1
|
|
6 set rec0.a@0["a"] 11
|
|
7 set rec0.b@0["b"] 'b0'
|
|
8 cursor_copy_struct cur1 rec1@1
|
|
9 copen cur1@1
|
|
10 cfetch cur1@1 rec1@1
|
|
11 jump_if_not 27(27) "cur1"%FOUND
|
|
12 set rec1.a@1["a"] 11
|
|
13 set rec1.b@1["b"] 'b1'
|
|
14 cpush cur2@2
|
|
15 cursor_copy_struct cur2 rec2@2
|
|
16 copen cur2@2
|
|
17 cfetch cur2@2 rec2@2
|
|
18 jump_if_not 23(23) "cur2"%FOUND
|
|
19 set rec2.a@2["a"] 12
|
|
20 set rec2.b@2["b"] 'b2'
|
|
21 cfetch cur2@2 rec2@2
|
|
22 jump 18
|
|
23 cclose cur2@2
|
|
24 cpop 1
|
|
25 cfetch cur1@1 rec1@1
|
|
26 jump 11
|
|
27 cclose cur1@1
|
|
28 cpop 1
|
|
29 cfetch cur0@0 rec0@0
|
|
30 jump 4
|
|
31 cclose cur0@0
|
|
32 cpop 1
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
|
|
#
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
BEGIN
|
|
FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
|
|
LOOP
|
|
SELECT rec1.a, rec1.b;
|
|
rec1.a:= 11;
|
|
rec1.b:= 'b1';
|
|
SELECT rec1.a, rec1.b;
|
|
END LOOP;
|
|
FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
|
|
LOOP
|
|
rec0.a:= 10;
|
|
rec0.b:='b0';
|
|
END LOOP;
|
|
FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
|
|
LOOP
|
|
rec2.a:= 10;
|
|
rec2.b:='b0';
|
|
END LOOP;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 cpush [implicit_cursor]@0
|
|
1 cursor_copy_struct [implicit_cursor] rec1@0
|
|
2 copen [implicit_cursor]@0
|
|
3 cfetch [implicit_cursor]@0 rec1@0
|
|
4 jump_if_not 11(11) "[implicit_cursor]"%FOUND
|
|
5 stmt 0 "SELECT rec1.a, rec1.b"
|
|
6 set rec1.a@0["a"] 11
|
|
7 set rec1.b@0["b"] 'b1'
|
|
8 stmt 0 "SELECT rec1.a, rec1.b"
|
|
9 cfetch [implicit_cursor]@0 rec1@0
|
|
10 jump 4
|
|
11 cpop 1
|
|
12 cpush [implicit_cursor]@0
|
|
13 cursor_copy_struct [implicit_cursor] rec0@1
|
|
14 copen [implicit_cursor]@0
|
|
15 cfetch [implicit_cursor]@0 rec0@1
|
|
16 jump_if_not 21(21) "[implicit_cursor]"%FOUND
|
|
17 set rec0.a@1["a"] 10
|
|
18 set rec0.b@1["b"] 'b0'
|
|
19 cfetch [implicit_cursor]@0 rec0@1
|
|
20 jump 16
|
|
21 cpop 1
|
|
22 cpush [implicit_cursor]@0
|
|
23 cursor_copy_struct [implicit_cursor] rec2@2
|
|
24 copen [implicit_cursor]@0
|
|
25 cfetch [implicit_cursor]@0 rec2@2
|
|
26 jump_if_not 31(31) "[implicit_cursor]"%FOUND
|
|
27 set rec2.a@2["a"] 10
|
|
28 set rec2.b@2["b"] 'b0'
|
|
29 cfetch [implicit_cursor]@0 rec2@2
|
|
30 jump 26
|
|
31 cpop 1
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
BEGIN
|
|
FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
|
|
LOOP
|
|
rec0.a:= 11;
|
|
rec0.b:= 'b0';
|
|
FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
|
|
LOOP
|
|
rec1.a:= 11;
|
|
rec1.b:= 'b1';
|
|
FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
|
|
LOOP
|
|
rec2.a:=12;
|
|
rec2.b:='b2';
|
|
END LOOP;
|
|
END LOOP;
|
|
END LOOP;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 cpush [implicit_cursor]@0
|
|
1 cursor_copy_struct [implicit_cursor] rec0@0
|
|
2 copen [implicit_cursor]@0
|
|
3 cfetch [implicit_cursor]@0 rec0@0
|
|
4 jump_if_not 29(29) "[implicit_cursor]"%FOUND
|
|
5 set rec0.a@0["a"] 11
|
|
6 set rec0.b@0["b"] 'b0'
|
|
7 cpush [implicit_cursor]@1
|
|
8 cursor_copy_struct [implicit_cursor] rec1@1
|
|
9 copen [implicit_cursor]@1
|
|
10 cfetch [implicit_cursor]@1 rec1@1
|
|
11 jump_if_not 26(26) "[implicit_cursor]"%FOUND
|
|
12 set rec1.a@1["a"] 11
|
|
13 set rec1.b@1["b"] 'b1'
|
|
14 cpush [implicit_cursor]@2
|
|
15 cursor_copy_struct [implicit_cursor] rec2@2
|
|
16 copen [implicit_cursor]@2
|
|
17 cfetch [implicit_cursor]@2 rec2@2
|
|
18 jump_if_not 23(23) "[implicit_cursor]"%FOUND
|
|
19 set rec2.a@2["a"] 12
|
|
20 set rec2.b@2["b"] 'b2'
|
|
21 cfetch [implicit_cursor]@2 rec2@2
|
|
22 jump 18
|
|
23 cpop 1
|
|
24 cfetch [implicit_cursor]@1 rec1@1
|
|
25 jump 11
|
|
26 cpop 1
|
|
27 cfetch [implicit_cursor]@0 rec0@0
|
|
28 jump 4
|
|
29 cpop 1
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
|
|
#
|
|
#
|
|
# Cursor declaration and cursor%ROWTYPE declaration in the same block
|
|
#
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
INSERT INTO t1 VALUES (1,'a');
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
CURSOR cur1 IS SELECT a FROM t1;
|
|
rec1 cur1%ROWTYPE;
|
|
BEGIN
|
|
rec1.a:= 10;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 cursor_copy_struct cur1 rec1@0
|
|
1 set rec1@0 NULL
|
|
2 cpush cur1@0
|
|
3 set rec1.a@0["a"] 10
|
|
4 cpop 1
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
#
|
|
# Recursive cursor and cursor%ROWTYPE declarations in the same block
|
|
#
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
a INT:=10;
|
|
CURSOR cur1 IS SELECT a;
|
|
rec1 cur1%ROWTYPE;
|
|
CURSOR cur2 IS SELECT rec1.a + 1 "a";
|
|
rec2 cur2%ROWTYPE;
|
|
BEGIN
|
|
OPEN cur1;
|
|
FETCH cur1 INTO rec1;
|
|
CLOSE cur1;
|
|
SELECT rec1.a;
|
|
open cur2;
|
|
FETCH cur2 INTO rec2;
|
|
CLOSE cur2;
|
|
SELECT rec2.a;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set a@0 10
|
|
1 cursor_copy_struct cur1 rec1@1
|
|
2 set rec1@1 NULL
|
|
3 cursor_copy_struct cur2 rec2@2
|
|
4 set rec2@2 NULL
|
|
5 cpush cur1@0
|
|
6 cpush cur2@1
|
|
7 copen cur1@0
|
|
8 cfetch cur1@0 rec1@1
|
|
9 cclose cur1@0
|
|
10 stmt 0 "SELECT rec1.a"
|
|
11 copen cur2@1
|
|
12 cfetch cur2@1 rec2@2
|
|
13 cclose cur2@1
|
|
14 stmt 0 "SELECT rec2.a"
|
|
15 cpop 2
|
|
CALL p1();
|
|
rec1.a
|
|
10
|
|
rec2.a
|
|
11
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# MDEV-12441 Variables declared after cursors with parameters lose values
|
|
#
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x1 INT:=101;
|
|
BEGIN
|
|
OPEN cur(10,11);
|
|
CLOSE cur;
|
|
SELECT x0, x1;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set x0@0 100
|
|
1 set x1@3 101
|
|
2 cpush cur@0
|
|
3 set cp1@1 10
|
|
4 set cp2@2 11
|
|
5 copen cur@0
|
|
6 cclose cur@0
|
|
7 stmt 0 "SELECT x0, x1"
|
|
8 cpop 1
|
|
CALL p1();
|
|
x0 x1
|
|
100 101
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur0(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x1 INT:=101;
|
|
CURSOR cur1(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x2 INT:=102;
|
|
CURSOR cur2(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x3 INT:=103;
|
|
BEGIN
|
|
OPEN cur0(0,1);
|
|
CLOSE cur0;
|
|
SELECT x0, x1, x2, x3;
|
|
OPEN cur1(10,11);
|
|
CLOSE cur1;
|
|
SELECT x0, x1, x2, x3;
|
|
OPEN cur2(20,21);
|
|
CLOSE cur2;
|
|
SELECT x0, x1, x2, x3;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set x0@0 100
|
|
1 set x1@3 101
|
|
2 set x2@6 102
|
|
3 set x3@9 103
|
|
4 cpush cur0@0
|
|
5 cpush cur1@1
|
|
6 cpush cur2@2
|
|
7 set cp1@1 0
|
|
8 set cp2@2 1
|
|
9 copen cur0@0
|
|
10 cclose cur0@0
|
|
11 stmt 0 "SELECT x0, x1, x2, x3"
|
|
12 set cp1@4 10
|
|
13 set cp2@5 11
|
|
14 copen cur1@1
|
|
15 cclose cur1@1
|
|
16 stmt 0 "SELECT x0, x1, x2, x3"
|
|
17 set cp1@7 20
|
|
18 set cp2@8 21
|
|
19 copen cur2@2
|
|
20 cclose cur2@2
|
|
21 stmt 0 "SELECT x0, x1, x2, x3"
|
|
22 cpop 3
|
|
CALL p1();
|
|
x0 x1 x2 x3
|
|
100 101 102 103
|
|
x0 x1 x2 x3
|
|
100 101 102 103
|
|
x0 x1 x2 x3
|
|
100 101 102 103
|
|
DROP PROCEDURE p1;
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x1 t1.a%TYPE:=101;
|
|
BEGIN
|
|
OPEN cur(10,11);
|
|
CLOSE cur;
|
|
SELECT x0, x1;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set x0@0 100
|
|
1 set x1@3 101
|
|
2 cpush cur@0
|
|
3 set cp1@1 10
|
|
4 set cp2@2 11
|
|
5 copen cur@0
|
|
6 cclose cur@0
|
|
7 stmt 0 "SELECT x0, x1"
|
|
8 cpop 1
|
|
CALL p1();
|
|
x0 x1
|
|
100 101
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x1 ROW(a INT,b INT):=ROW(101,102);
|
|
BEGIN
|
|
OPEN cur(10,11);
|
|
CLOSE cur;
|
|
SELECT x0, x1.a, x1.b;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set x0@0 100
|
|
1 set x1@3 (101,102)
|
|
2 cpush cur@0
|
|
3 set cp1@1 10
|
|
4 set cp2@2 11
|
|
5 copen cur@0
|
|
6 cclose cur@0
|
|
7 stmt 0 "SELECT x0, x1.a, x1.b"
|
|
8 cpop 1
|
|
CALL p1();
|
|
x0 x1.a x1.b
|
|
100 101 102
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# MDEV-19640 Wrong SHOW PROCEDURE output for SET GLOBAL sysvar1=expr, sysvar2=expr
|
|
#
|
|
CREATE OR REPLACE PROCEDURE p1() AS
|
|
BEGIN
|
|
SET GLOBAL max_allowed_packet=16000000, max_error_count=60;
|
|
SELECT @@GLOBAL.max_allowed_packet, @@GLOBAL.max_error_count;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 stmt 31 "SET GLOBAL max_allowed_packet=16000000"
|
|
1 stmt 31 "SET GLOBAL max_error_count=60"
|
|
2 stmt 0 "SELECT @@GLOBAL.max_allowed_packet, @..."
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# MDEV-19639 sql_mode=ORACLE: Wrong SHOW PROCEDURE output for sysvar:=expr
|
|
#
|
|
CREATE OR REPLACE PROCEDURE p1() AS
|
|
BEGIN
|
|
max_error_count:=10;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 stmt 31 "max_error_count:=10"
|
|
DROP PROCEDURE p1;
|