In this blog, I will explain about stored procedures and their advantages and how to create and use them.
What is Store Procedure in MySQL
A stored procedure is a collection of SQL statements that are stored in the database. It contains business logic, which is one of the key aspects that distinguish stored procedures from views. It can accept parameters and you can set variables like other programming languages.
Advantages With Stored Procedures
Stored procedures help to increase the performance of the application and reduce the traffic between the application and database server.
Syntax
DELIMITER//
CREATE PROCEDURE sp_name(p_1 INT)
BEGIN
DECLARE VARIABLES
....code goes here ...
END//
DELIMITER;
Create Stored Procedure
Here I create simple sp to print ‘HELLO WORLD’. For creating sp first open Smart workbench tool and then select stored procedure option present under database and then right-click on STORED_PROCS then select create stored procedure option.
DELIMITER $$
DROP PROCEDURE IF EXISTS `Helloworld` $$
CREATE PROCEDURE Helloworld ()
BEGIN
SELECT "HELLO WORLD" ;
END $$
DELIMITER ;
Execute Stored Procedure
You will execute SP by click on run option and then use by invoking call statement.
call Helloworld ( );
In Smart Workbench we will get by simply drag the stored procedure name.
Alter Stored Procedure
You can alter Sp by using alter stored procedure option presented under database -> STORED_PROCS -> right-click on stored procedure -> choose Alter Stored procedure.
DELIMITER $$
DROP PROCEDURE IF EXISTS `Helloworld_Name` $$
CREATE PROCEDURE Helloworld_Name (v_name VARCHAR (100))
BEGIN
SELECT CONCAT("HELLO WORLD", v_name) ;
END $$
DELIMITER ;powerful tools to execute Stored Procedures
- Mysql’s WorkBench
- Smart workbench SmartMySQL product
- SQLYog
SQlYog,Workbench create basic Stored procedures.But in one click you can create stored procedure with cursor by using Smart workbench tool.You can create stored procedure with cursor by using create stored procedure with cursor option presented under database->STORED_PROCS->right click on stored procedure->choose stored procedure with cursor.
Parameters
In MySQL, we use three types of parameters
In,Out,Inout
- When you define In parameter in stored procedure you must pass as an argument to the stored procedure.
- When you define OUT parameter in stored procedure it must be passed to the calling program as output.
- When you define INOUT parameter in stored procedure it is a combination of IN, OUT parameters.
Stored Procedure with cursor
Below MySQL query returns the list of employee number, hire_date , salary , department_name details.
SELECT
e.emp_no,
e.hire_date,
e.salary,
d.dept_name
FROM
employee e JOIN departments d
ON e.dept_id=d.dept_id;
Variable Declaration
variable is a database object whose value will change during Stored Procedure execution. You can specify variable name after declare keyword
after that database type with length then assign variable default value
/* variables declaration */
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_emp_no INT (11);
DECLARE v_hire_date DATE;
DECLARE v_salary DECIMAL (17);
DECLARE v_dept_name VARCHAR (40);Error Handing
Inside Stored Procedure in between begin, end declare continue handlers when getting an error inside Stored Procedure need to handle it properly like continue or exit execution.
/*declare NOT FOUND handler */
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;Cursor
In MySQL Stored Procedures we will use the cursor to handle set of rows returned by a query and process them. You can declare cursor name after declare keyword.
/* declare cursor for store procedure */
DEClARE EmpList CURSOR FOR
SELECT
e.emp_no,
e.hire_date,
e.salary,
d.dept_name
FROM
employee e join departments d
on e.dept_id=d.dept_id; LOOP
Loop used to execute a group of commands repeatedly one or more times terminate the loop execution after v_finished=1.
SmartMySQLLoop: LOOP
IF v_finished = 1 THEN
LEAVE SmartMySQLLoop;
END IF;
/* Fetch cursor statment */
FETCH EmpList INTO v_emp_no, v_hire_date, v_salary, v_dept_name;
/* Write your logic for each record of the cursor. Cursor query each record
values are saved in new Variables with are starting for v_ variables + column name. */
IF v_dept_name='marketing' AND v_hire_date < CURDATE() - INTERVAL 3 YEAR
THEN
UPDATE employee SET salary=salary+salary<em>20/100 WHERE emp_no=v_emp_no;
ELSEIF v_hire_date < CURDATE() - INTERVAL 3 YEAR
THEN
UPDATE employee SET salary=salary+salary</em>10/100 WHERE emp_no=v_emp_no;
ELSE
UPDATE employee SET salary=salary+salary*5/100 WHERE emp_no=v_emp_no;
END IF;
END LOOP SmartMySQLLoop;Sample Stored Procedure with cursor updating employee table for
- Increase 20% salary for employees who ha been working in ‘Marketing ‘Department’ for the last 3 years.
- Increase 10% salary for employees who have been working in other than marketing departments for the last 3 years.
- Rest of the employees increase 5% salary.
DELIMITER $$
CREATE PROCEDURE EmpHike ()
BEGIN
/* variables declaration */
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_emp_no INT (11);
DECLARE v_hire_date DATE;
DECLARE v_salary DECIMAL (17);
DECLARE v_dept_name VARCHAR (40);
/* declare cursor for store procedure */
DECLARE EmpList CURSOR FOR
SELECT
e.emp_no,
e.hire_date,
e.salary,
d.dept_name
FROM
employee e JOIN departments d
ON e.dept_id=d.dept_id;
/* declare NOT FOUND handler */
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
/* open Cursor*/
OPEN EmpList;
/* open loop */
SmartMySQLLoop: LOOP
IF v_finished = 1 THEN
LEAVE SmartMySQLLoop;
END IF;
/* Fetch cursor statment */
FETCH EmpList INTO v_emp_no, v_hire_date, v_salary, v_dept_name;
/* Write your logic for each record of the cursor. Cursor query each record
values are saved in new Variables with are starting for v_ variables + column name. */
IF v_dept_name='marketing' AND v_hire_date < CURDATE() - INTERVAL 3 YEAR
THEN
UPDATE employee SET salary=salary+salary<em>20/100 WHERE emp_no=v_emp_no;
ELSEIF v_hire_date < CURDATE() - INTERVAL 3 YEAR
THEN
UPDATE employee SET salary=salary+salary</em>10/100 WHERE emp_no=v_emp_no;
ELSE
UPDATE employee SET salary=salary+salary*5/100 WHERE emp_no=v_emp_no;
END IF;
END LOOP SmartMySQLLoop;
CLOSE EmpList;
END$$SmartMySQL Workbench has advance options for writing stored procedure with cursor.you can choose stored procedure with cursor option from stored_procs present on left-hand tree structure.there you enter stored procedure name,cursor name ,cursor select query it will automatically create stored procedure with cursor .
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE EmpHIKE()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
/* variables declaration */
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_emp_no INT (11);
DECLARE v_hire_date DATE;
DECLARE v_salary DECIMAL (17);
DECLARE v_dept_name VARCHAR (40);
/* declare cursor for store procedure */
DEClARE EmpList CURSOR FOR
SELECT
e.emp_no,
e.hire_date,
e.salary,
d.dept_name
FROM
employee e JOIN departments d
ON e.dept_id=d.dept_id;
/* declare NOT FOUND handler */
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
/* open Cursor */
OPEN EmpList;
/* open loop */
SmartMySQLLoop: LOOP
IF v_finished = 1 THEN
LEAVE SmartMySQLLoop;
END IF;
/* Fetch cursor statment */
FETCH EmpList INTO v_emp_no, v_hire_date, v_salary, v_dept_name;
/* Write your logic for each record of the cursor. Cursor query each record
values are saved in new Variables with are starting for v_ variables + column name. */
SELECT v_emp_no, v_hire_date, v_salary, v_dept_name; /* remove this select statement before starting your code */
END LOOP SmartMySQLLoop;
CLOSE EmpList;
END$$
DELIMITER ;write your logic inside the stored procedure.you can invoke stored procedure by simply drag stored procedure name it will create call stored procedure name.
call EmpHike;The SmartMySQL Workbench is free and you can download. If any problem contacts support@smartmysql.com.
