Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

  • How to Work with a Stored Procedure

    @dinesh.rawat
    Recommendations Offline Message

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 385
    Comment on it

    How to Work with a Stored Procedure

    1. DELIMITER //
    2.  
    3. CREATE PROCEDURE `p2` ()
    4. LANGUAGE SQL
    5. DETERMINISTIC
    6. SQL SECURITY DEFINER
    7. COMMENT 'A procedure'
    8. BEGIN
    9.     SELECT 'Hello World !';
    10. 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

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

    Delete a Stored Procedure

    1. 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:

    1. DELIMITER //  
    2. CREATE PROCEDURE `proc_IN` (IN var1 INT)  
    3. BEGIN  
    4. SELECT var1 + 2 AS result;  
    5. END//

    example 2) OUT PARAM:

    1. DELIMITER //  
    2. CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))  
    3. BEGIN  
    4. SET var1 = 'This is a test';  
    5. END //

    example 3) INOUT PARAM:

    1. DELIMITER //  
    2. CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)  
    3. BEGIN  
    4. SET var1 = var1 * 2;  
    5. END //

    Working with variables:

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

    Flow Control Structures 1) IF statement

    1. DELIMITER //  
    2.  
    3. CREATE PROCEDURE `proc_IF` (IN param1 INT)  
    4. BEGIN  
    5. DECLARE variable1 INT;  
    6. SET variable1 = param1 + 1;  
    7.  
    8. IF variable1 = 0 THEN  
    9.     SELECT variable1;  
    10. END IF;  
    11.  
    12. IF param1 = 0 THEN  
    13.     SELECT 'Parameter value = 0';  
    14. ELSE  
    15.     SELECT 'Parameter value <> 0';  
    16. END IF;  
    17. END //

    2) CASE statement

    1. DELIMITER //  
    2.  
    3. CREATE PROCEDURE `proc&#95;CASE` (IN param1 INT)  
    4. BEGIN  
    5. DECLARE variable1 INT;  
    6. SET variable1 = param1 + 1;  
    7.  
    8. CASE   
    9. WHEN variable1 = 0 THEN  
    10. INSERT INTO table1 VALUES (param1);  
    11. WHEN variable1 = 1 THEN  
    12. INSERT INTO table1 VALUES (variable1);   
    13. ELSE  
    14. INSERT INTO table1 VALUES (99);  
    15. END CASE;  
    16.  
    17. END //
    Stored Procedure- Working mysql
    Comment on it

 0 Comment(s)

Sign In
Forgot Password?
                           OR                           
Create an account !!
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Reset Password
Fill out the form below and reset your password: