More

    Additional Examples for Stored Procedures and Stored Functions in MySQL

    Here we will discuss examples for different use cases for stored procedures and stored functions. Please refer to the note on ‘Stored Procedures and Stored Functions in MySQL’ for more theory, details and explanations.

    Example cases discussed here are:

    1. A Stored Procedure that Accept No Parameters
    2. A Stored Procedure that Accept Parameters (IN, OUT, INOUT)
    3. A Stored Procedure that Accept Parameters, Return ResultSet
    4. A Stored Function that Accept No Parameters
    5. A Stored Function that Accept Parameters

    PREREQUISITES

    Execute below commands first.

    DROP TABLE IF EXISTS emp;

    CREATE TABLE emp(`first name` VARCHAR(20), id INT PRIMARY KEY);

    insert into emp values(‘HJK’, 1);

    insert into emp values(‘ABC’, 2);

    insert into emp values(‘DEF’, 3);

    Verify Using:

    select * from emp;

    CASE 1: A STORED PROCEDURE THAT ACCEPT NO PARAMETERS

    DELIMITER |

    CREATE PROCEDURE sample_sp_no_param ()

    BEGIN

    UPDATE emp SET `first name`= ‘ChangedHJK’ where id = 1;

    END

    |

    DELIMITER ;

    Execute and Verify Commands

    CALL sample_sp_no_param;

    select * from emp;

    CASE 2: A STORED PROCEDURE THAT ACCEPT PARAMETERS (IN, OUT, INOUT)

    DELIMITER |

    CREATE PROCEDURE sample_sp_with_params (IN empId INT UNSIGNED, OUT oldName VARCHAR(20), INOUT newName VARCHAR(20))

    BEGIN

    SELECT `first name` into oldName FROM emp where id = empId;

    UPDATE emp SET `first name`= newName where id = empId;

    END

    |

    DELIMITER ;

    Execute and Verify Commands

    set @inout=’updatedHJK’;

    CALL sample_sp_with_params(1,@out,@inout);

    select @out,@inout;

    select * from emp;

    CASE 3: A STORED PROCEDURE THAT ACCEPT PARAMETERS, RETURN RESULTSET

    DELIMITER |

    CREATE PROCEDURE sample_sp_with_params_resultset (IN empId INT UNSIGNED, OUT oldName VARCHAR(20), INOUT newName VARCHAR(20))

    BEGIN

    SELECT `first name` into oldName FROM emp where id = empId;

    UPDATE emp SET `first name`= newName where id = empId;

    select * from emp;

    END

    |

    DELIMITER ;

    Execute and Verify Commands

    set @inout=’updatedHJKS’;

    CALL sample_sp_with_params_resultset (1,@out,@inout);

    You can verify the values of OUT and INOUT parameters as:

    select @out,@inout;

    CASE 4: A STORED FUNCTION THAT ACCEPT NO PARAMETERS

    DELIMITER |

    CREATE FUNCTION sample_fn_no_param ()

    RETURNS INT

    BEGIN

    DECLARE count INT;

    SELECT COUNT(*) INTO count FROM emp;

    RETURN count;

    END

    |

    DELIMITER ;

    Execute and Verify Commands

    select sample_fn_no_param ();

    CASE 5: A STORED FUNCTION THAT ACCEPT PARAMETERS

    DELIMITER |

    CREATE FUNCTION sample_fn_with_params (empId INT UNSIGNED, newName VARCHAR(20))

    RETURNS VARCHAR(20)

    BEGIN

    DECLARE oldName VARCHAR(20);

    SELECT `first name` into oldName FROM emp where id = empId;

    UPDATE emp SET `first name`= newName where id = empId;

    RETURN oldName;

    END

    |

    DELIMITER ;

    Execute and Verify Commands

    select sample_fn_with_params(2,’UpdatedABC’);

    CLEANUP: DROP COMMANDS

    DROP  PROCEDURE IF EXISTS sample_sp_no_param;

    DROP PROCEDURE IF EXISTS sample_sp_with_params;

    DROP PROCEDURE IF EXISTS sample_sp_with_params_resultset;

    DROP FUNCTION IF EXISTS sample_fn_no_param;

    DROP FUNCTION IF EXISTS sample_fn_with_params;

    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