Debian Codegraph @

mysql-test/suite/compat/oracle/r/sp-default-param.result

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