
The routine_body consists of a valid SQL procedure statement. This can be a
simple statement such as SELECT or INSERT, or it can be a compound statement
written using BEGIN and END. Compound statements can contain declarations,
loops, and other control structure statements. See Programmatic and Compound
Statements for syntax details.

MariaDB allows routines to contain DDL statements, such as CREATE and DROP.
MariaDB also allows stored procedures (but not stored functions) to contain
SQL transaction statements such as COMMIT.

For additional information about statements that are not allowed in stored
routines, see Stored Routine Limitations.

Invoking stored procedure from within programs
----------------------------------------------

For information about invoking stored procedures from within programs written
in a language that has a MariaDB/MySQL interface, see CALL.

OR REPLACE
----------

If the optional OR REPLACE clause is used, it acts as a shortcut for:

DROP PROCEDURE IF EXISTS name;
CREATE PROCEDURE name ...;

with the exception that any existing privileges for the procedure are not
dropped.

sql_mode
--------

MariaDB stores the sql_mode 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 SQL mode in effect when the routine is
invoked.

Character Sets and Collations
-----------------------------

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 will be 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.

Oracle Mode
-----------

MariaDB starting with 10.3
--------------------------
From MariaDB 10.3, a subset of Oracle's PL/SQL language has been supported in
addition to the traditional SQL/PSM-based MariaDB syntax. See Oracle mode from
MariaDB 10.3 for details on changes when running Oracle mode.

Examples
--------

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 Delimiters in the mysql client.

DELIMITER //

CREATE PROCEDURE simpleproc (OUT param1 INT)
 BEGIN
 SELECT COUNT(*) INTO param1 FROM t;
 END;
//

DELIMITER ;

CALL simpleproc(@a);

SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+

Character set and collation:

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 ;

CREATE OR REPLACE:

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)

URL: https://mariadb.com/kb/en/create-procedure/