Categories
Technical

Overview Of SQL Stored Procedures

  • Post author Swaroop Das,Web Developer

Hello Everyone, This Blog is about MySQL Stored Procedure think of it as a beginner guide/ intro towards Stored Procedures. To follow along, you must at least know the basic of MYSQL operations like create a database/ table and simple DML(Data Manipulation Language) queries like INSERT, UPDATE AND DELETE And DQL (Data Query Language)DQL (Data Query Language) SELECT.

I’m not an expert just learned along the way, while I was tackling a problem to optimize my queries 😊

What ? is Stored Procedure?

In in a simple term, it’s just the bunch of SQL instructions (queries) run together, just like a function in other programming languages.

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure so that the stored procedure can act based on the parameter value(s) that is passed. – W3School

When ? to use it?

Let say, When you have a set of instructions that need to run one after another sequence and you miss something it’s might be troubling. Run a single query would be tedious. But creating a stored procedure for the mundane task, it would be easy to run in a single stored procedure call.

Where ? to use it?

  1. Periodic updating of data is necessary.

Why ? to use it?

  1. They allow modular programming.

  2. They allow faster execution.

  3. They can reduce network traffic.

  4. They can be used as a security mechanism

If you invoke the same stored procedure in the same session again, MySQL just executes the stored procedure from the cache without having to recompile it.

Below query will run on MariaDB MYSQL but for other SQL, some commands may differ.

Let’s get started

For below example, we consider the below Product table

id

name

sku

qty

status

1

Product Electric

10000034

50

1

2

Product IlluminateClick

10000035

0

0

3


 Product Giga

10000036

10

1

4

Product Giga

10000037

16

1

5

Product Giga

10000036

0

0







What is DeLimiter? How to change it?

A delimiter is a sequence of one or more characters for specifying the boundary between separate, independent regions in plain text or other data streams –wikipedia.org

When you write SQL query, you use the semicolon (;) to separate two statements like the following example:

SELECT name FROM Product;
 SELECT sku FROM Product;

In case of SQL Default Delimiter/End of SQL query is a semicolon (;) But change the default delimiter to dollar ($) like the following example :

DELIMITER $$

Delimiter Character may consist of single or double characters like // or $$ however you should avoid using backslash since it escapes sequence. When creating a stored procedure from the command-line, you will likely need to differentiate between the regular delimiter and a delimiter inside a BEGIN END block. To understand better, consider the following example:

CREATE PROCEDURE PROCEDURE_NAME()
BEGIN
SELECT name FROM customer;
 END;

If you enter above code line by line in command line then, MYSQL client will interpret the semicolon (;) as the end of the statement and will throw an exception. So, we temporarily change the delimiter other than the default

🎉How Write your own the Stored Procedure?🎉

We start with by changing the default Delimiter. All the SQL statements will be wrapped around BEGIN and END keyword as below

DELIMITER $$
CREATE PROCEDURE PROCEDURE_NAME()
BEGIN
/* Procedure code */
END $$
 DELIMITER ;

If a Stored Procedure with the same name already exits then it throws an exception procedure with the same name already exists

To Run Stored Procedure Run Below SQL Query

CALL PROCEDURE_NAME();

Drop a Stored Procedure

If a stored procedure is not present, it will throw an exception i.e why can add a Conditional check [IF EXISTS ] clause

DROP PROCEDURE PROCEDURE_NAME;

/* OR */

 DROP PROCEDURE IF EXISTS PROCEDURE_NAME;

Get the list of stored procedures

SHOW PROCEDURE STATUS;

Output : –

Db: testSP
Name: udpateStatus
Type: PROCEDURE
Definer: root@%
Modified: 2020-02-24 13:17:58
Created: 2020-02-24 13:17:58
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci


To Filter the result, you just need to add WHERE Clause as below.

SHOW PROCEDURE STATUS WHERE Db= DATABASE_NAME;

Variable

Unlike other programming languages, a Variable is just a placeholder for the actual value. As the meaning of variable is, not consistent or having a fixed pattern; liable to change. Every Variable has 2 things i.e Name and DataType. To define/declare a variable. As shown below :

DECLARE VARIABLE_NAME DATATYPE DEFAULT VALUE

Example :

DECLARE done INT DEFAULT FALSE;

Conditional Statement

To handle the Logical behavior of the SQL statement, we use the conditional statement. They allow us to handle the condition based upon a particular scenario. As shown below :

IF CONDITION THEN
/* Statement */
 ENDIF;

If the condition is satisfied, then the code surrounded by the IF block is executed. if the code has else block then the ELSE block is executed. You also have nested block as shown below :

IF CONDITION THEN
/* Statement */
IF CONDITION THEN
/* Statement */
ELSE
/* Statement */
ENDIF;
ELSE
/* Statement */
 ENDIF;

To handle cases where the logical behavior many outcomes. You can also have if-else-if-else or IF-ELSE-IF ladder. As shown below.

IF CONDITION THEN
/* Statement */
ELSE IF CONDITION THEN
/* Statement */
ELSE
/* Statement */
 ENDIF;

Example :

SELECT id,
IF(`qty` > 0 && `stock_status` = 1,’ENABLE’,’DISABLE’) as product_enable
 FROM products;

Switch Statement

An Alternate to IF-ELSE-IF ladder is SWITCH CASE, were operation is performed based upon the input value. As shown below.

WHEN
CASE CONDITION THEN /* Statement */
CASE CONDITION THEN /* Statement */
ELSE RESULT
 END CASE;

Example :

SELECT id,
(
CASE
WHEN `qty` > 0 && `stock_status` = 1 THEN ‘ENABLE’
WHEN `qty` <= 0 && `stock_status` = 0 THEN ‘DISABLE’
END
) as product_enable
 FROM products;

Loop Statement

Loop is crucial to any programming language as it allows us to perform the same task iteratively if the condition is true. There are different variant of LOOPS, WHILE and just a simple LOOP. As shown below :

WHILE LOOP

WHILE expression DO
/* Statement */
 END WHILE;

Loop will go-on until the condition is satisfied. when false the execution of block stop

LOOP

LOOP_NAME: LOOP
/* Statement */
 END LOOP;

You can leave LOOP if a certain case is true. As shown below : ##### LEAVE LOOP

LOOP_NAME: LOOP
IF CONDITION THEN
LEAVE LOOP_NAME;
END IF;
/* Statement */
 END LOOP;

Cursors

What is Cursors ?

A cursor allows you to iterate a set of rows returned by a query and process each row individually.

For any MySQL cursor, there are 5 parts , 1. Declaration Statement

DECLARE CURSOR_NAME CURSOR FOR SELECT_SQL_STATEMENT

  1. Handler Statement

Handler allows us to handle the scenario when the cursor hits the end of the rows.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  1. Open Statement

OPEN CURSOR_NAME;

  1. Fetch Statement

FETCH CURSOR_NAME INTO VAR1

  1. Close Statement

CLOSE CURSOR_NAME;

Complete Syntax :

DECLARE VAR1 DATA_TYPE;
DECLARE CURSOR_NAME CURSOR FOR SELECT FIELD FROM TABLE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN CURSOR_NAME;
FETCH CURSOR_NAME INTO VAR1;
 CLOSE CURSOR_NAME;

Example of Loop & Cursor :

DELIMITER $$
CREATE PROCEDURE updateStatus()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id INT;
DECLARE cursor_products CURSOR FOR SELECT Id FROM products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor_products;

read_loop: LOOP
FETCH cursor_products INTO id;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE products set stock_status=IF(qty>0,1,0) WHERE Id=id;
END LOOP;

CLOSE cursor_products;
END$$
 DELIMITER ;

Conclusion

To conclude the overview of the stored procedure, we have gone through the basics of store procedure to handle conditional, switch case, loop and even cursor to iterate over a row of a table. To learn more about store procedure visit the below reference links as a starting point. > All the Best. To embark on the journey towards the stored procedure. 😊 😊 ### Happy Coding!

Reference

Stored Procedure Definition W3SCHOOL

Delimiter Definition WIKIPEDIA

Why ? SQLSERVERTUTORIAL

Learn Material mysqltutorial

WHERE ? TECHREPUBLIC