1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
|
# Start of 11.8 tests
#
# MDEV-10862 Stored procedures: default values for parameters (optional parameters)
#
SET sql_mode=oracle;
#
# Basic default parameter test for procedures
#
CREATE OR REPLACE PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1)
AS
BEGIN
SELECT par1, par2 FROM DUAL;
END;
$$
CALL p1(2,3);
par1 par2
2 3
CALL p1(2);
par1 par2
2 1
CALL p1(2,3,4);
ERROR 42000: Incorrect number of arguments for PROCEDURE test.p1; expected 2, got 3
CALL p1();
ERROR 42000: Incorrect number of arguments for PROCEDURE test.p1; expected 2, got 0
DROP PROCEDURE p1;
#
# Basic default parameter test for functions
#
CREATE OR REPLACE FUNCTION f1(par1 INT, par2 INT DEFAULT 1) RETURN INT
AS
BEGIN
RETURN par1 + par2;
END;
$$
SELECT f1(2,3) FROM DUAL;
f1(2,3)
5
SELECT f1(2) FROM DUAL;
f1(2)
3
SELECT f1(2,3,4) FROM DUAL;
ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 2, got 3
SELECT f1() FROM DUAL;
ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 2, got 0
DROP FUNCTION f1;
#
# OUT parameters cannot have default values
#
CREATE OR REPLACE PROCEDURE p1(par1 IN INT DEFAULT 1, par2 OUT INT DEFAULT 2)
AS
BEGIN
SELECT par1 FROM DUAL;
END;
$$
ERROR HY000: Default/ignore value is not supported for such parameter usage
#
# INOUT parameters cannot have default values
#
CREATE OR REPLACE PROCEDURE p1(par1 INOUT INT DEFAULT 1, par2 OUT INT DEFAULT 2)
AS
BEGIN
SELECT par1 FROM DUAL;
END;
$$
ERROR HY000: Default/ignore value is not supported for such parameter usage
#
# Test various data types
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (a INT, b VARCHAR(10));
USE test;
CREATE OR REPLACE PROCEDURE p1(
par1 IN INT DEFAULT 1,
par2 IN VARCHAR(10) DEFAULT 'abc',
par3 IN DATETIME DEFAULT '2010-01-01 12:34:56',
par4 IN DECIMAL(10,2) DEFAULT 123.45,
par5 IN FLOAT DEFAULT 123.45,
par6 IN DOUBLE DEFAULT 123.45,
par7 IN CHAR DEFAULT 'a',
par8 IN BOOLEAN DEFAULT TRUE,
par9 IN ROW(a INT, b VARCHAR(10)) DEFAULT ROW(1, 'abc'),
par10 IN t1.a%TYPE DEFAULT 10,
par11 IN d1.t1.a%TYPE DEFAULT 10,
par12 IN t1%ROWTYPE DEFAULT (1, 'cde'),
par13 IN d1.t1%ROWTYPE DEFAULT (1, 'cde')
)
AS
BEGIN
SELECT par1, par2, par3, par4, par5, par6, par7, par8, par9.b, par10, par11, par12.b, par13.b FROM DUAL;
END;
$$
CALL p1();
par1 par2 par3 par4 par5 par6 par7 par8 par9.b par10 par11 par12.b par13.b
1 abc 2010-01-01 12:34:56 123.45 123.45 123.45 a 1 abc 10 10 cde cde
DROP PROCEDURE p1;
DROP TABLE t1;
DROP DATABASE d1;
#
# Expression as default value is not evaluated if value is provided
#
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
CREATE FUNCTION f1() RETURN INT
AS
BEGIN
INSERT INTO t1 VALUES (NULL);
RETURN LAST_INSERT_ID();
END;
$$
CREATE PROCEDURE p1(par1 IN INT DEFAULT f1())
AS
BEGIN
SELECT par1 FROM DUAL;
END;
$$
CALL p1();
par1
1
CALL p1(10);
par1
10
CALL p1();
par1
2
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP TABLE t1;
#
# Subquery as default value
# NOTE: Oracle does not allow this
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
CREATE PROCEDURE p1(par1 IN INT DEFAULT (SELECT a FROM t1))
AS
BEGIN
SELECT par1 FROM DUAL;
END;
$$
CALL p1();
par1
1
CALL p1(10);
par1
10
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Default value from another parameter
# NOTE: Oracle does not allow this
#
CREATE PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT par1)
AS
BEGIN
SELECT par1, par2 FROM DUAL;
END;
$$
CALL p1(10);
par1 par2
10 10
CALL p1(10,20);
par1 par2
10 20
DROP PROCEDURE p1;
#
# Default value referencing self
# NOTE: Oracle does not allow this
#
CREATE PROCEDURE p1(par1 IN INT DEFAULT par1)
AS
BEGIN
NULL;
END;
$$
ERROR 42000: Undeclared variable: par1
#
# Default parameters in package's routines
#
CREATE PACKAGE p1 AS
PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1);
FUNCTION f1(par1 IN INT, par2 IN INT DEFAULT 1) RETURN INT;
END p1;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE p1(par1 IN INT, par2 IN INT DEFAULT 1)
AS
BEGIN
SELECT par1, par2 FROM DUAL;
END;
FUNCTION f1(par1 IN INT, par2 IN INT DEFAULT 1) RETURN INT
AS
BEGIN
RETURN par1 + par2;
END;
END p1;
$$
CALL p1.p1(5);
par1 par2
5 1
CALL p1.p1(5, 10);
par1 par2
5 10
SELECT p1.f1(5) FROM DUAL;
p1.f1(5)
6
SELECT p1.f1(5, 10) FROM DUAL;
p1.f1(5, 10)
15
DROP PACKAGE p1;
#
# MDEV-35925 OUT and INOUT parameters shouldn't expect a default value
#
CREATE PROCEDURE sp1(p1 IN INT DEFAULT 0, p2 OUT INT)
BEGIN
SET p2 = p2 + 1;
END;
--error ER_NOT_SUPPORTED_YET
CREATE PROCEDURE sp1(p1 IN INT DEFAULT 0, p2 INOUT INT)
BEGIN
SET p2 = p2 + 1;
END;
DELIMITER ;$$
ERROR 42000: This version of MariaDB doesn't yet support 'sparam1 IN <type> DEFAULT <expr>, spparam2 OUT <type>'
# End of 11.8 tests
|
