Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-default-param.test

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