Master stored procedures in MariaDB Server. This section covers creating, executing, and managing these powerful routines to encapsulate complex logic and improve application performance.
The DROP PROCEDURE statement permanently removes a stored procedure and its associated privileges from the database.
This statement is used to drop a stored procedure. That is, the specified routine is removed from the server along with all privileges specific to the procedure. You must have the ALTER ROUTINE privilege for the routine. If the server system variable is set, that privilege and EXECUTE are granted automatically to the routine creator - see .
The IF EXISTS clause is a MySQL/MariaDB extension. It prevents an error from occurring if the procedure or function does not exist. ANOTE is produced that can be viewed with .
While this statement takes effect immediately, threads which are executing a procedure can continue execution.
IF EXISTS:
This page is licensed: GPLv2, originally from
DROP PROCEDURE [IF EXISTS] sp_nameDROP PROCEDURE simpleproc;DROP PROCEDURE simpleproc;
ERROR 1305 (42000): PROCEDURE test.simpleproc does not exist
DROP PROCEDURE IF EXISTS simpleproc;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1305 | PROCEDURE test.simpleproc does not exist |
+-------+------+------------------------------------------+The ALTER PROCEDURE statement modifies the characteristics of an existing stored procedure, such as its security context or comment, without changing its logic.
This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement. To make such changes, you must drop and re-create the procedure using .
You must have the ALTER ROUTINE privilege for the procedure. By default, that privilege is granted automatically to the procedure creator. See .
This page is licensed: GPLv2, originally from
Stored procedures are precompiled collections of SQL statements stored on the server, allowing for encapsulated logic, parameterized execution, and improved application performance.
A Stored Procedure is a routine invoked with a statement. It may have input parameters, output parameters and parameters that are both input parameters and output parameters.
Here's a skeleton example to see a stored procedure in action:
First, the delimiter is changed, since the function definition will contain the regular semicolon delimiter. The procedure is named Reset_animal_count. MODIFIES SQL DATA indicates that the procedure will perform a write action of sorts, and modify data. It's for advisory purposes only. Finally, there's the actual SQL statement - an UPDATE.
ALTER PROCEDURE proc_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'A more complex example, with input parameters, from an actual procedure used by banks:
See CREATE PROCEDURE for full syntax details.
Security is a key reason. Banks commonly use stored procedures so that applications and users don't have direct access to the tables. Stored procedures are also useful in an environment where multiple languages and clients are all used to perform the same operations.
To find which stored functions are running on the server, use SHOW PROCEDURE STATUS.
or query the routines table in the INFORMATION_SCHEMA database directly:
To find out what the stored procedure does, use SHOW CREATE PROCEDURE.
To drop a stored procedure, use the DROP PROCEDURE statement.
To change the characteristics of a stored procedure, use ALTER PROCEDURE. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using CREATE OR REPLACE PROCEDURE (which retains existing privileges), or DROP PROCEDURE followed CREATE PROCEDURE .
See the article Stored Routine Privileges.
This page is licensed: CC BY-SA / Gnu FDL
The CREATE PROCEDURE statement defines a new stored procedure, specifying its name, parameters (IN, OUT, INOUT), and the SQL statements it executes.
CREATE
[OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type [DEFAULT value or expression]
type:
Any valid MariaDB data type
Creates a . By default, a routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.sp_name when you create it.
When the routine is invoked, an implicit USE`` db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. USE statements within stored routines are disallowed.
When a stored procedure has been created, you invoke it by using the CALL statement (see ).
To execute the CREATE PROCEDURE statement, it is necessary to have the CREATE ROUTINE privilege. By default, MariaDB automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. See also .
The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at routine execution time, as described . Requires the privilege.
If the routine name is the same as the name of a built-in SQL function, you must use a space between the name and the following parenthesis when defining the routine, or a syntax error occurs. This is also true when you invoke the routine later. For this reason, we suggest that it is better to avoid re-using the names of existing SQL functions for your own stored routines.
The IGNORE_SPACE SQL mode applies to built-in functions, not to stored routines. It is always allowable to have spaces after a routine name, regardless of whether IGNORE_SPACE is enabled.
The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.
Each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.
For valid identifiers to use as procedure names, see .
One can't use OR REPLACE together with IF EXISTS.
If the IF NOT EXISTS clause is used, then the procedure will only be created if a procedure with the same name does not already exist. If the procedure already exists, then a warning are triggered by default.
Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.
An IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. An INOUT parameter is initialized by the caller, can be
modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.
For each OUT or INOUT parameter, pass a user-defined variable in theCALL statement that invokes the procedure so that you can obtain its value when the procedure returns. If you are calling the procedure
from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.
As of , each parameter can be defined as having a default value or expression. This can be useful if needing to add extra parameters to a procedure which is already in use.
DETERMINISTIC and NOT DETERMINISTIC apply only to . Specifying DETERMINISTC or NON-DETERMINISTIC in procedures has no effect. The default value is NOT DETERMINISTIC. Functions are DETERMINISTIC when they always return the same value for the same input. For example, a truncate or substring function. Any function involving data, therefore, is always NOT DETERMINISTIC.
CONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA are informative clauses that tell the server what the function does. MariaDB does not check in any way whether the specified clause is correct. If none of these clauses are specified, CONTAINS SQL is used by default.
MODIFIES SQL DATA means that the function contains statements that may modify data stored in databases. This happens if the function contains statements like , , , or DDL.
READS SQL DATA means that the function reads data stored in databases but does not modify any data. This happens if statements are used, but there no write operations are executed.
CONTAINS SQL means that the function contains at least one SQL statement, but it does not read or write any data stored in a database. Examples include or .
NO SQL means nothing, because MariaDB does not currently support any language other than SQL.
The routine_body consists of a valid SQL procedure statement. This can be a simple statement such as or , or it can be a compound statement written using . Compound statements can contain declarations, loops, and other control structure statements. See for syntax details.
MariaDB allows routines to contain DDL statements, such as CREATE and DROP. MariaDB also allows (but not ) to contain SQL transaction statements such as COMMIT.
For additional information about statements that are not allowed in stored routines, see .
For information about invoking from within programs written in a language that has a MariaDB/MySQL interface, see .
If the optional OR REPLACE clause is used, it acts as a shortcut for the following statements, with the exception that any existing for the procedure are not dropped:
MariaDB stores the system variable setting that is in effect at the time a routine is created and always executes the routine with this setting in force, regardless of the server in effect when the routine is invoked.
Procedure parameters can be declared with any character set/collation. If the character set and collation are not specifically set, the database defaults at the time of creation are used. If the database defaults change at a later stage, the stored procedure character set/collation will not be changed at the same time; the stored procedure needs to be dropped and recreated to ensure the same character set/collation as the database is used.
A subset of Oracle's PL/SQL language is supported in addition to the traditional SQL/PSM-based MariaDB syntax. See for details on changes when running Oracle mode.
The following example shows a simple stored procedure that uses an OUT parameter. It uses the DELIMITER command to set a new delimiter for the duration of the process — see .
Character set and collation:
CREATE OR REPLACE:
This page is licensed: CC BY-SA / Gnu FDL
ALTER PROCEDURE simpleproc SQL SECURITY INVOKER;DELIMITER //
CREATE PROCEDURE Reset_animal_count()
MODIFIES SQL DATA
UPDATE animal_count SET animals = 0;
//
DELIMITER ;SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 101 |
+---------+
CALL Reset_animal_count();
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 0 |
+---------+CREATE PROCEDURE
Withdraw /* Routine name */
(parameter_amount DECIMAL(6,2), /* Parameter list */
parameter_teller_id INTEGER,
parameter_customer_id INTEGER)
MODIFIES SQL DATA /* Data access clause */
BEGIN /* Routine body */
UPDATE Customers
SET balance = balance - parameter_amount
WHERE customer_id = parameter_customer_id;
UPDATE Tellers
SET cash_on_hand = cash_on_hand + parameter_amount
WHERE teller_id = parameter_teller_id;
INSERT INTO Transactions VALUES (
parameter_customer_id,
parameter_teller_id,
parameter_amount);
END;SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
Db: test
Name: Reset_animal_count
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-06-03 08:55:03
Created: 2013-06-03 08:55:03
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciSELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE';
+--------------------+
| ROUTINE_NAME |
+--------------------+
| Reset_animal_count |
+--------------------+SHOW CREATE PROCEDURE Reset_animal_count\G
*************************** 1. row ***************************
Procedure: Reset_animal_count
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Reset_animal_count`()
MODIFIES SQL DATA
UPDATE animal_count SET animals = 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciDROP PROCEDURE Reset_animal_count();CREATE
[OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MariaDB data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statementDROP PROCEDURE IF EXISTS name;
CREATE PROCEDURE name ...;DELIMITER //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END;
//
DELIMITER ;
CALL simpleproc(@a);
SELECT @a;
+------+
| @a |
+------+
| 1 |
+------+DELIMITER //
CREATE PROCEDURE simpleproc2 (
OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
BEGIN
SELECT CONCAT('a'),f1 INTO param1 FROM t;
END;
//
DELIMITER ;DELIMITER //
CREATE PROCEDURE simpleproc2 (
OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
BEGIN
SELECT CONCAT('a'),f1 INTO param1 FROM t;
END;
//
ERROR 1304 (42000): PROCEDURE simpleproc2 already exists
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE simpleproc2 (
OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
BEGIN
SELECT CONCAT('a'),f1 INTO param1 FROM t;
END;
//
ERROR 1304 (42000): PROCEDURE simpleproc2 already exists
DELIMITER ;
Query OK, 0 rows affected (0.03 sec)