Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to Work with a Stored Procedure

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 217
    Comment on it

    How to Work with a Stored Procedure

    DELIMITER //
    
    CREATE PROCEDURE `p2` ()
    LANGUAGE SQL
    DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT 'A procedure'
    BEGIN
        SELECT 'Hello World !';
    END//
    

    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

    CALL stored_procedure_name (param1, param2, ....)   
    ex:
     CALL procedure1(10 , 'string parameter' , @parameter_var); 
    

    Delete a Stored Procedure

    DROP PROCEDURE IF EXISTS p2;  
    

    Parameters Lets examine how you can define parameters within a stored procedure.

    CREATE PROCEDURE proc1 () : Parameter list is empty CREATE PROCEDURE proc1 (IN varname DATA-TYPE) : One input parameter. The word IN is optional because parameters are IN (input) by default. CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) : One output parameter. CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) : One parameter which is both input and output.

    example 1) IN PARAM:

    DELIMITER //  
    CREATE PROCEDURE `proc_IN` (IN var1 INT)  
    BEGIN  
    SELECT var1 + 2 AS result;  
    END//  
    

    example 2) OUT PARAM:

    DELIMITER //  
    CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))  
    BEGIN  
    SET var1 = 'This is a test';  
    END //
    

    example 3) INOUT PARAM:

    DELIMITER //  
    CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)  
    BEGIN  
    SET var1 = var1 * 2;  
    END //  
    

    Working with variables:

    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 //  
    

    Flow Control Structures 1) IF statement

    DELIMITER //  
    
    CREATE PROCEDURE `proc_IF` (IN param1 INT)  
    BEGIN  
    DECLARE variable1 INT;  
    SET variable1 = param1 + 1;  
    
    IF variable1 = 0 THEN  
        SELECT variable1;  
    END IF;  
    
    IF param1 = 0 THEN  
        SELECT 'Parameter value = 0';  
    ELSE  
        SELECT 'Parameter value <> 0';  
    END IF;  
    END //  
    

    2) CASE statement

    DELIMITER //  
    
    CREATE PROCEDURE `proc&#95;CASE` (IN param1 INT)  
    BEGIN  
    DECLARE variable1 INT;  
    SET variable1 = param1 + 1;  
    
    CASE   
    WHEN variable1 = 0 THEN  
    INSERT INTO table1 VALUES (param1);  
    WHEN variable1 = 1 THEN  
    INSERT INTO table1 VALUES (variable1);   
    ELSE  
    INSERT INTO table1 VALUES (99);  
    END CASE;  
    
    END //  
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: