Overview Of SQL Stored Procedures

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

Feel Free to comment down below you find any mistake or suggestion to improve 😊

Let's start

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?

Periodic processing of data is necessary.

Why ? to use it?

• They allow modular programming.
• They allow faster execution.
• They can reduce network traffic.
• 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 querying !

For below example, we consider the below Product table

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 to change the default delimiter to the dollar ($) like the following exampl:

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 exists, it will throw 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. To prevent it,we 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;

Conclusion

In my next post, I will explain more about store procedures like variables, conditions, loop, and cursor.