Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
It would be nice if stored procedures supported parameters with default values. This way, if I need to modify a stored procedure to add more parameters to it, my old code with calls to the SP can continue to work. Also, this missing feature could be a blocker for migrations from other SQL DBMSes to MariaDB.
To clarify, I would like to be able to do something like:
DELIMITER //
|
CREATE PROCEDURE sp(IN p1 INT DEFAULT 1) BEGIN SELECT p1; END //
|
DELIMITER ;
|
|
|
CALL sp();
|
+------+
|
| p1 |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.00 sec)
|
Query OK, 0 rows affected (0.00 sec)
|
This feature exists e.g. in:
* MS-SQL Server
* PostgresSQL: for functions and for procedures
* SAP HANA. SAP HANA also supports named parameters
* Oracle 10g - note that the value can be an expression
* Sybase - note that the value can be an expression
* DB2
* MemSQL
Note that this is also a long-standing request for MySQL, and users continue to comment on the request to say how useful it would be, e.g. when converting from other DBMSes.
[25 Dec 2005 6:22] Valerii Kravchuk
Thank you for a useful feature request. By optional parameter you mean the one with default value, I believe... So, that the value for it can be ommited in the SP call. Oracle's PL/SQL also has this feature. I think it should be implemented as soon as possible to simplify migration from other RDBMS to MySQL 5.
Attachments
Issue Links
- blocks
-
-
- Open
-
- causes
-
MDEV-35925 OUT and INOUT parameters shouldn't expect a default value
-
- Closed
-
- is duplicated by
-
MDEV-20238
sql_mode="oracle" does not support "DEFAULT" parameters for functions/procedures
-
- Closed
-
- is part of
-
MDEV-35973 Oracle Compatibility Project 1 for 2025
-
- In Progress
-
- relates to
-
-
- Open
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
Activity
Great news. I wonder does it include setting parameter by name? I mean, if
PROCEDURE sp((IN p1 INT DEFAULT 1, IN p2 INT default 0)
how should I set only second paremeter?
sp(default,10);
sp(\N,10);
or (by name)
sp(p2 => 10)
?
It would by great not to set some parameters including those having earlier position.
Passing by name is not included into this task.
It will be done separately, under terms of this ticket:
MDEV-35965 Oracle: Named and Mixed notations for stored routine call parameters
bar I'm curious to know the difference between stored function and UDF. Google AI says this - In MySQL, a UDF (User-Defined Function), also known as a stored function.
I've completed my testing with the following test cases.
1. Default data types including JSON and VECTOR.
2. Expressions as default value
3. Other stored routines in default value expression
4. Out of range value
5. Invalid data type in default value
6. NULL value
7. IN, OUT, INOUT parameters in a stored procedure
8. Packages
9. User defined functions
10. Parameter positions with both allowed and not-allowed default values.
The test results are looking good. OK for push from QA.