More

    Stored Procedures and Stored Functions in MySQL

    A stored routine allows 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, whereas a stored function takes in zero or more parameters, runs specified SQL statements, and returns exactly one scalar value.

    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.

    IMPORTANT POINTS ON STORED PROCEDURES

    1. 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. 
    2. Stored procedures can 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, TRUNCATE TABLE and have most ALTER, CREATE, DROP, and RENAME commands.

     IMPORTANT POINTS ON STORED FUNCTIONS

    1. 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.
    2. 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 cannot have most ALTER, CREATE, DROP, and RENAME commands (as they cause an implicit COMMIT).

    CREATING STORED PROCEDURES AND STORED FUNCTIONS

    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 ;

    Important Notes:

    1. 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, even though it is optional.
    2. 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.
    3. 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.
    4. The scope of local variables declared in a stored routine is within the stored routine and cannot be accessed from outside.
      • We can declare a local variable as:
        • DECLARE var_name data_type
    5. Declaring an INT type variable as UNSIGNED make sure that it won’t accept negative values.
    6. If you get a warning that Data truncated for column ’sql_mode’ in some versions, 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;

    REFERENCE: 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 allow 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.

    IMPORTANT ADDITIONAL NOTES ON MYSQL STORED ROUTINES

    1. 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.
    2. 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.
    3. 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.
    4. Stored routines can be named using reserved words.
    5. Many common flow control statements are supported in stored routines like IF, CASE, WHILE, REPEAT, LOOP, ITERATE, LEAVE.
    6. MySQL support row-based, mixed and statement-based replication modes.
    7. Stored routines are stored in the proc table of the mysql database.
    8. Stored routine backup can be done by using mysqldump, and during hot and cold backups.
    9. 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.
    10. 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.

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox