Stored Procedures and Stored Functions in MySQL

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:

  • BEGIN and END, before and after the statements are required when you have multiple sql statements. Even with a single SQL statement, it is good practice to surround the body of a stored routine with BEGIN and END, though it is optional.
  • Because the stored procedure has statements that end in the default delimiter (;), we must change the delimiter before create, and change the delimiter back when done creating.
  • Each input parameter and output variable is specified as IN, OUT, or INOUT with name and type. An INOUT argument can be used as both an input parameter and an output variable.
  • The scope of local variables declared in a stored routine is within the stored routine and cannot be accessed from outside. We can decalre a local variable as:
    • DECLARE var_name data_type
  • Declaring and INT type variable as UNSIGNED make sure that it won’t accept negative values.
  • If you get a warning that Data truncated for column ’sql_mode’, it is a known issue and was fixed in MySQL versions 6.0.5 and 5.1.24.

 

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:

  • SQLWARNING
  • NOT FOUND
  • SQLEXCEPTION
  • mysql_error_code
  • condition_name
  • SQLSTATE [VALUE] sqlstate

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

  • MySQL supports the ALTER PROCEDURE and ALTER FUNCTION statements, by which you can change the SQL usage, sql_mode, SQL SECURITY, and COMMENT of a stored routine. You can change all of them together by separating them with a space. You need the ALTER ROUTINE privilege in order to change the stored routine; the creator of the routine is given this privilege automatically.
  • By default, a stored routine is saved with the current sql_mode. Data type incompatibilities and overflow warnings or errors are generated, depending on the sql_mode. You can change sql_mode with ALTER. sql_mode IGNORE_SPACE does not apply to stored routines.
  • The character set and collation are set using the environment variables during routine creation. Changing them cannot be done with ALTER statements and requires dropping and recreating the routine.
  • Stored routines can be named using reserved words.
  • Many common flow control statements are supported in stored routines like IF, CASE, WHILE, REPEAT, LOOP, ITERATE, LEAVE.
  • MySQL support row-based, mixed and statement-based replication modes.
  • Stored routines are stored in the proc table of the mysql database. Stored routine backup can be done by using mysqldump, and during hot and cold backups.
  • If the same inputs always produce the same results a stored routine is said to be deterministic stored routine. The results include both the actions in SQL and the values of any output variables. A non-deterministic stored routine is one in which the same inputs may not always produce the same results. For instance, if you set a variable with the value of current date or time, the result will be different every time we run the routine.

 

Tags: 

Quick Notes Finder Tags

Activities (1) advanced java (1) agile (3) App Servers (6) archived notes (2) Arrays (1) Best Practices (12) Best Practices (Design) (3) Best Practices (Java) (7) Best Practices (Java EE) (1) BigData (3) Chars & Encodings (6) coding problems (2) Collections (15) contests (3) Core Java (All) (55) course plan (2) Database (12) Design patterns (8) dev tools (3) downloads (2) eclipse (9) Essentials (1) examples (14) Exception (1) Exceptions (4) Exercise (1) exercises (6) Getting Started (18) Groovy (2) hadoop (4) hibernate (77) hibernate interview questions (6) History (1) Hot book (5) http monitoring (2) Inheritance (4) intellij (1) java 8 notes (4) Java 9 (1) Java Concepts (7) Java Core (9) java ee exercises (1) java ee interview questions (2) Java Elements (16) Java Environment (1) Java Features (4) java interview points (4) java interview questions (4) javajee initiatives (1) javajee thoughts (3) Java Performance (6) Java Programmer 1 (11) Java Programmer 2 (7) Javascript Frameworks (1) Java SE Professional (1) JPA 1 - Module (6) JPA 1 - Modules (1) JSP (1) Legacy Java (1) linked list (3) maven (1) Multithreading (16) NFR (1) No SQL (1) Object Oriented (9) OCPJP (4) OCPWCD (1) OOAD (3) Operators (4) Overloading (2) Overriding (2) Overviews (1) policies (1) programming (1) Quartz Scheduler (1) Quizzes (17) RabbitMQ (1) references (2) restful web service (3) Searching (1) security (10) Servlets (8) Servlets and JSP (31) Site Usage Guidelines (1) Sorting (1) source code management (1) spring (4) spring boot (3) Spring Examples (1) Spring Features (1) spring jpa (1) Stack (1) Streams & IO (3) Strings (11) SW Developer Tools (2) testing (1) troubleshooting (1) user interface (1) vxml (8) web services (1) Web Technologies (1) Web Technology Books (1) youtube (1)