mirror of
https://github.com/MariaDB/server.git
synced 2025-06-09 14:24:42 +02:00
dfdbec1636
Implement default values for parameters of stored routines
in both default and oracle mode.
- Default values for cursor parameters are *NOT* supported yet.
- An IN parameter with DEFAULT followed by an OUT param is not supported yet.
This combination will be enabled together with the arrow syntax:
sp1(v=>p2)
The default values can be either literals or expressions.
When it is an expression, it is only evaluated if the parameter
has not been supplied by the caller
(important if the expression has side effects).
1535 lines
26 KiB
Text
1535 lines
26 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;
|
|
# End of 10.5 tests
|
|
# Start of 11.8 tests
|
|
#
|
|
# MDEV-10862 Stored procedures: default values for parameters (optional parameters)
|
|
#
|
|
CREATE OR REPLACE PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1)
|
|
AS
|
|
var1 INT DEFAULT 5;
|
|
BEGIN
|
|
SELECT par1, par2 FROM DUAL;
|
|
END;
|
|
$$
|
|
SHOW PROCEDURE CODE p1;
|
|
Pos Instruction
|
|
0 set default param par2@1 1
|
|
1 set var1@2 5
|
|
2 stmt 0 "SELECT par1, par2 FROM DUAL"
|
|
DROP PROCEDURE p1;
|
|
# End of 11.8 tests
|