MySQL Stored Procedure and Stored Function Examples

Here we discuss examples for different cases of usage 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:

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

 

Prerequisites

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');

 

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;

 

Tags: 

Search the Web

Custom Search

Searches whole web. Use the search in the right sidebar to search only within javajee.com!!!

Quick Notes Finder Tags

Activities (1) advanced java (1) agile (3) App Servers (6) archived notes (2) ArrayLists (1) Arrays (2) Best Practices (12) Best Practices (Design) (3) Best Practices (Java) (7) Best Practices (Java EE) (1) BigData (3) Chars & Encodings (6) coding problems (2) Collections (15) contests (3) Core Java (All) (52) course plan (2) Database (12) Design patterns (8) dev tools (3) downloads (2) eclipse (9) Essentials (1) examples (14) Exception (1) Exceptions (4) Exercise (1) exercises (6) Getting Started (18) Groovy (2) hadoop (4) hibernate (77) hibernate interview questions (6) History (1) Hot book (4) http monitoring (2) Inheritance (4) intellij (1) java 8 notes (4) Java 9 (1) Java Concepts (7) Java Core (7) java ee exercises (1) java ee interview questions (2) Java Elements (16) Java Environment (1) Java Features (4) java interview points (4) java interview questions (4) javajee initiatives (1) javajee thoughts (3) Java Performance (6) Java Programmer 1 (11) Java Programmer 2 (8) Javascript Frameworks (1) Java SE Professional (1) JSP (1) Legacy Java (1) linked list (3) maven (1) Multithreading (16) NFR (1) No SQL (1) Object Oriented (9) OCPJP (4) OCPWCD (1) OOAD (3) Operators (4) Overloading (2) Overriding (2) Overviews (1) policies (1) programming (1) Quartz Scheduler (1) Quizzes (17) RabbitMQ (1) references (2) resources (1) restful web service (3) Searching (1) security (10) Servlets (8) Servlets and JSP (31) Site Usage Guidelines (1) Sorting (1) source code management (1) spring (4) spring boot (3) Spring Examples (1) Spring Features (1) spring jpa (1) Stack (1) Streams & IO (3) Strings (11) SW Developer Tools (2) testing (1) troubleshooting (1) user interface (1) vxml (8) web services (1) Web Technologies (1) Web Technology Books (1) youtube (1)