Engineering Full Stack Apps with Java and JavaScript
A stored routine allow users to define a set of statements that they can later execute in a single call. Stored procedures and stored functions are the two types of stored routines in MySQL and they were introduced in version 5.0.1. A stored procedure takes in zero or more input parameters and runs specified SQL statements. A stored function takes in zero or more parameters, runs specified SQL statements, and returns exactly one scalar value.
A stored procedure can pass back values through output variables and result sets. A stored procedure’s parameters are declared as IN, OUT or INOUT. A stored function can only output a scalar value, and should always do so. A stored function’s parameters are just input parameters and don’t have IN, OUT or INOUT specified like a stored procedure.
Unlike stored procedures, stored functions cannot return a result set, be recursive, call statements that do a COMMIT or ROLLBACK (COMMIT, ROLLBACK, START TRANSACTION), LOCK and UNLOCK TABLES, SET AUTOCOMMIT=1 when it was not already set to 1, TRUNCATE TABLE and most ALTER, CREATE, DROP, and RENAME commands as they cause an implicit COMMIT.
MySQL stored routines are compiled by MySQL the first time they are invoked in a connection. Subsequent calls in the same connection are cached. However, applications that disconnect every time after running one or few queries, have the penalty of compiling the code every time and not able to use the per-thread compile cache for long periods.
A simple stored procedure can be created as:
DELIMITER |
CREATE PROCEDURE sample_sp_no_param ()
BEGIN
UPDATE emp SET `first name`= 'ChangedHJK' where id = 1;
END
|
DELIMITER ;
A simple function can be created as:
DELIMITER |
CREATE FUNCTION sample_fn_no_param ()
RETURNS INT
BEGIN
DECLARE count INT;
SELECT COUNT(*) INTO count FROM emp;
RETURN count;
END
|
DELIMITER ;
More examples for stored routines ( including those that accept parameters) can be found at 'MySQL Stored Procedure and Stored Function Examples'.
Note:
Invoking Stored Procedures and Functions in MySQL
We can invoke an SP using the CALL statement:
call sample_sp_no_param ();
We need to specify all of the IN and OUT parameters, if they are declared. You also need the EXECUTE privilege to CALL the procedure; and the creator of the SP is given this privilege automatically.
MySQL functions (standard or stored) are invoked by using the function name and passing input parameters:
SELECT sample_fn_no_param ();
Dropping a Stored Procedure and Stored Function in MySQL
To drop a stored procedure or function, we can use the DROP PROCEDURE or the DROP FUNCTION statements respectively. We can use IF EXISTS phrase to avoid error in case the procedure does not exist:
DROP PROCEDURE IF EXISTS sample_sp;
Complete CREATE Syntax for Stored Routines
By default, the stored routine is invoked as the user who defined the stored routine. This can be changed by using the SQL SECURITY clause in CREATE, which can be set to DEFINER or INVOKER; and the default is DEFINER. The definer value itself can be changed by using a DEFINER clause in CREATE. You can change the DEFINER and SQL SECURITY clauses of a stored routine with the ALTER PROCEDURE and ALTER FUNCTION statements.
SQL SECURITY is one of several options that are allowed in CREATE PROCEDURE and CREATE FUNCTION statements. Options are separated by spaces, and zero or more options can be specified. Options available are:
COMMENT 'string'
LANGUAGE SQL
[NOT] DETERMINISTIC
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
SQL SECURITY { DEFINER | INVOKER }
Complete Syntax for Creating a Stored Procedure
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE p_name ([parameter[, . . . ]])
[SQL SECURITY {DEFINER | INVOKER}]
[options . . . ]
[BEGIN]
{statement(s)}
[END]
Complete Syntax for Creating a Stored Function
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION p_name ([parameter[, . . . ]])
[SQL SECURITY {DEFINER | INVOKER}]
[options . . . ]
[BEGIN]
{statement(s)}
[END]
Stored Procedure Result Sets
Stored procedures can send information back in two ways: by updating a variable and by running queries that send back result sets by executing SQL statements inside the stored procedure like a SELECT statement. The programs that call the stored procedure need to be able to handle all of the returned information, whether or not it is returned via a variable. OUT variables must be specified when the procedure is invoked; However, handling of result sets are optional.
Error Handlers
MySQL allows you to specify handlers that allows you to handle known exceptions. A handler is specified with the syntax:
DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR condition statement
Condition is one of the following values:
Statement is the SQL statement to run when the condition is met.
Example:
DECLARE CONTINUE HANDLER FOR 1265 BEGIN … END;
DECLARE CONTINUE HANDLER FOR 1265 SET …
We can also write this by naming the condition as:
DECLARE data_truncation_cond CONDITION FOR 1265;
DECLARE CONTINUE HANDLER FOR data_truncation_cond BEGIN … END;
DECLARE CONTINUE HANDLER FOR data_truncation_cond SET…
Order of DECLARE statements should be variables, conditions, handlers; or mysql will throw an error.
More Notes on MySQL Stored Routines