27 Stored Procedures in MYSQL

Hiren Joshi

epgp books

 

 

Introduction

 

• A stored procedure or a function is a named Pl/SQL block which resides in the Database engine’s tables. A stored procedure can be invoked by other procedure, triggers or by other applications like PHP, java etc.

• It is a logically grouped set of SQL and Pl/SQL statements which are written to perform a specific task.

• Procedures and functions are also referred to as Database objects and such objects can be invoked or called by any Pl/SQL block within the application.

• The initial versions of MySQL did not have support for stored procedures, functions or triggers. It was only after version 5.0 that support for this objects was added to increase reusability.

• It is like defining an API for our application which helps in enhancing reusability in different applications

• Before they are actually stored, MySQL engine parses and compiles the procedure.

 

Why Stored Procedures

 

• Stored procedures provide security as the DBA can grant permission to the applications to access the stored procedure without giving permissions on the underlying table.

• Stored procedures enhances reusability so the developers can use once written code and multiple resources are not wasted for single task.

• Stored procedures are invoked by some calling routine which pass only the name and parameters hence reducing database traffic , which otherwise has to send multiple sql statements

• The caching and buffering of stored procedures in MySQL is on demand , they are compiled on demand. And after compilation MySQL puts it in cache

 

Limitations

 

• Expert skill set required

• MySQL does not provide with debugging facilities

• Memory consumption will be more if many procedures are used or many logical operations in a procedure.

 

  Types of Parameters in Stored Procedure

 

• Procedures can also be made dynamic. The dynamism for procedures can be achieved by passing parameters before execution. The behavior of the procedure changes depending on the parameters passed.

• The different types of parameters available with stored procedure are

• IN : IN is the default parameter passed to the procedure which can be changed in the procedure

• OUT : No value is passed to the procedure , but the value is returned out from the procedure.

• IN OUT : Here , both value can be accepted and can be returned.

 

Structure of Stored Procedure

 

• Stored procedure consists of

• Declarative part

• The declarative part contains the declarations about variables, constants, cursors, exceptions etc. The scope of these declarations is local to the procedure

• Executable part

• The executable part contains the actions procedure is expected to take.

 

 

Now, let us understand different types of examples, with reference to stored procedures.

 

All the below code blocks are taken from MySQL editor and displayed here as it is.

 

1.  Simple example to fetch data from employee table

 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_sal`()

BEGIN

select name,salary from emp;

END

 

To run on command prompt SQL > call get_sal();

The above statement will display the name and salary from emp table.

 

2. Example to demonstrate IN parameter

 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `raise_sal`(emp_no varchar(10), amount int)

BEGIN

UPDATE emp

SET salary = salary + amount WHERE empid = emp_no;

END

 

In the above example, emp_no and amount are passed as IN parameters, and the salary is updated based on the amount provided by the user. By default, parameters are IN.

 

To run on command prompt SQL > call raise_sal(1,100);

 

The above statement will update the salary with 100 of empid : 1 , Hence the old value was 5400 which will be updated to 5500.

 

3. Example to demonstrate OUT parameter

 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `fetch_name`(empno varchar(10), out v_name varchar(10))research

BEGIN

select name into v_name from emp where empid=empno;

END

 

The above example demonstrates the use of OUT parameter and IN parameter. Empno is passed as IN parameter and name is returned as OUT parameter.

 

4.  Example to demonstrate IN OUT parameter

 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `raise_sal1`(empno varchar(10), Inout amount int)

BEGIN

declare v_amt int; declare v_sal int;

select salary into v_sal from emp where empid = empno; set v_amt = v_sal + amount;

update emp set salary = v_amt where empid = empno; set amount=v_amt;

END

 

The above example display the functionality of InOUT parameter where the variable amount is accepting the value, it is then processed and the same amount is returned back the value.

 

5. Example to demonstrate creating and executing a stored procedure on SQL prompt , Example of IN OUT parameter

 

Creating the procedure

SQL> create procedure square (INOUT P int) set P=P*P;

To assign value to the variable SQL> set @number=10;

To call the procedure

SQL> call square (@number);

To display variable value

SQL> select @number ;

To drop a procedure

SQL> drop procedure square;

  • Procedures allow you to return multiple values, You can separate the parameters with “,”(comma).
  • Loops can also be used into Mysql. The three types of loops which exist in MySQL are
  • Syntax : While <expression> Do Statements
  • End While
  • Repeat

 

Statements

Until <expression>

End Repeat

 

MySQL also provides with Loop statement which executes code repeatedly by using Loop_label e.g. Loop _label: loop. You can use Leave and iterate statements to navigate in the loop.

 

6. Example to demonstrate creating and executing a stored procedure on SQL prompt , Example of IN OUT parameter

 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_loop`()

BEGIN

DECLARE cntr INT;

DECLARE str VARCHAR(255);

SET cntr = 1;

SET str = ”;

loop_label: LOOP

IF cntr > 10 THEN

LEAVE loop_label;

END IF;

SET cntr= cntr + 1;

IF (cntr mod 2) THEN

ITERATE loop_label;

ELSE

SET str = CONCAT(str,cntr,’,’);

END IF;

END LOOP;

SELECT str;

END

 

Output: 2,4,6,8,10

 

THE Procedures which are created can be viewed by issuing the following command

 

Show procedure status

 

It displays all the statistics about the procedure

 

Stored Functions

 

•  Stored functions are the stored programs that return a single value. We make use of stored functions when we want to use some common routines which increases reusability.

•  But unlike stored procedure, we can use functions with an expression.

•  Functions can have only IN parameters

•  The return statement is compulsory in the function with a valid SQL datatype

 

   Simple example to fetch data from employee table
     DELIMITER $$
     CREATE DEFINER=`root`@`localhost` FUNCTION `get_sal`(empno varchar(10)) RETURNS int(11)
     BEGIN
     Declare v_sal int;
     select sal into v_sal from emp where empid = empno;
     RETURN v_sal;
     END

 

To execute on command prompt

select empid,get_sal1(1) from emp where empid=1;

 

The function call is used as part of expression here. The query calls the function and fetches the salary for the empid passed.

 

The simple example is illustrated to demonstrate the simple understanding of functions, but functions can be used to perform complex calculations and return results from complex queries.

you can view video on Stored Procedures in MYSQL

References:

 

1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers

2) Learning PHP, MySQL & JavaScript , Robin Nixon , O’Reilly Publications

3) PHP and MySQL Web Development , Luke Welling , Laura Thomson , Pearson Publications, Fourth Edition

4) MySQL Documentation : https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

5) mysqltutorial.org

Additional Reading :

 

1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers

2) MysQL documentation : dev.mysql.com/doc/ Practice Questions

1) Create a Stored Procedure Named DisplayMessage which will display the message “Welcome to E-pg-Pathshala”

2) Create a Procedure named AddEmp which will add records in emp table.

3) Create a Procedure DisplayMarks, which will return the total marks of a particular student. (student id will be passed as parameter)

4) Delete records from emp table based on the empid passed as the parameter. Display number of records deleted.

5) Create a Procedure Raisesal which will update the employees salary based on following conditions. (you will have to add designation field in emp table)

  1. If employee is Director : increment will be 20%
  2. If employee is Manager : increment will be 15%
  3. If employee is Programmer : increment will be 10%

 

6) Create a function to :

  1. Return the maximum salary for department 10
  2. Return whether the number passed is prime or not
  3. Return the total salary of employee after adding 10% DA and deduction 2% tax (to be done for empid passed).