• Stored Procedure with PHP & MYSQL

    A stored procedure is a method to encapsulate repetitive tasks. They allow for variable declarations, flow control and other useful programming techniques.

    Step 1 – Picking a Delimiter

    The delimiter is the character or string of characters that you’ll use to tell the mySQL client that you’ve finished typing in an SQL statement. For ages, the delimiter has always been a semicolon. That, however, causes problems, because, in a stored procedure, one can have many statements, and each must end with a semicolon. In this tutorial I will use “//”

    Step 2 – How to Work with a Stored Procedure
    Creating a Stored Procedure

    View Code MYSQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    
    DELIMITER //
     
    CREATE PROCEDURE `p2` ()
     
    LANGUAGE SQL
     
    DETERMINISTIC
     
    SQL SECURITY DEFINER
     
    COMMENT 'A procedure'
     
    BEGIN
     
        SELECT 'Hello World !';
     
    END//

    The first part of the statement creates the procedure. The next clauses defines the optional characteristics of the procedure. Then you have the name and finally the body or routine code.

    Stored procedure names are case insensitive, and you cannot create procedures with the same name. Inside a procedure body, you can’t put database-manipulation statements.

    The four characteristics of a procedure are:

    Language : For portability purposes; the default value is SQL.
    Deterministic : If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value is NOT DETERMINISTIC.
    SQL Security : At call time, check privileges of the user. INVOKER is the user who calls the procedure. DEFINER is the creator of the procedure. The default value is DEFINER.
    Comment : For documentation purposes; the default value is “”

    Calling a Stored Procedure

    To call a procedure, you only need to enter the word CALL, followed by the name of the procedure, and then the parentheses, including all the parameters between them (variables or values). Parentheses are compulsory.

    View Code MYSQL
    1
    2
    3
    
    CALL stored_procedure_name (param1, param2, ....)
     
    CALL procedure1(10 , 'string parameter' , @parameter_var);

    Modify a Stored Procedure

    MySQL provides an ALTER PROCEDURE statement to modify a routine, but only allows for the ability to change certain characteristics. If you need to alter the body or the parameters, you must drop and recreate the procedure.
    Delete a Stored Procedure

    View Code MYSQL
    1
    
    DROP PROCEDURE IF EXISTS p2;

    This is a simple command. The IF EXISTS clause prevents an error in case the procedure does not exist.
    Step 3 – Parameters

    Let’s examine how you can define parameters within a stored procedure.

    ·

    View Code MYSQL
    1
    
     CREATE PROCEDURE proc1 ()

    : Parameter list is empty

    ·

    View Code MYSQL
    1
    
    CREATE PROCEDURE proc1 (IN varname DATA-TYPE)

    : One input parameter. The word IN is optional because parameters are IN (input) by default.

    ·

    View Code MYSQL
    1
    
    CREATE PROCEDURE proc1 (OUT varname DATA-TYPE)

    : One output parameter.

    ·

    View Code MYSQL
    1
    
    CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE)

    : One parameter which is both input and output.

    Of course, you can define multiple parameters defined with different types.
    IN example

    View Code MYSQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    
     
    DELIMITER //
     
    CREATE PROCEDURE `proc_IN` (IN var1 INT)
     
    BEGIN
     
        SELECT var1 + 2 AS result;
     
    END//

    OUT example

    View Code MYSQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    
    DELIMITER //
     
    CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
     
    BEGIN
     
        SET var1 = 'This is a test';
     
    END //

    INOUT example

    View Code MYSQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    
    DELIMITER //
     
    CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
     
    BEGIN
     
        SET var1 = var1 * 2;
     
    END //

    Step 4 – Variables

    The following step will teach you how to define variables, and store values inside a procedure. You must declare them explicitly at the start of the BEGIN/END block, along with their data types. Once you’ve declared a variable, you can use it anywhere that you could use a session variable, or literal, or column name.

    Declare a variable using the following syntax:

    View Code MYSQL
    1
    
    DECLARE varname DATA-TYPE DEFAULT defaultvalue;

    Let’s declare a few variables:

    View Code MYSQL
    1
    2
    3
    4
    5
    6
    7
    
    DECLARE a, b INT DEFAULT 5;
     
    DECLARE str VARCHAR(50);
     
    DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
     
    DECLARE v1, v2, v3 TINYINT;

    Working with variables

    Once the variables have been declared, you can assign them values using the SET or SELECT command:

    View Code MYSQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    
    DELIMITER //
     
    CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
     
    BEGIN
     
        DECLARE a, b INT DEFAULT 5;
     
        DECLARE str VARCHAR(50);
     
        DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
     
        DECLARE v1, v2, v3 TINYINT;   
     
        INSERT INTO table1 VALUES (a);
     
        SET str = 'I am a string';
     
        SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;
     
    END //

Comments on this post

Leave a Reply

  • Security Code :


    5 + nine =