Overview Of SQL Stored Procedures (part 1 )

Overview Of SQL Stored Procedures (part  1 )

Hello Everyone, This Blog is about MYSQL Stored Procedure think of it as a beginner guide/ intro to Stored Procedures. To follow along, you must at least know the basics 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.

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

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

idnameskuqtystatus
1Product Electric10000034501
2Product IlluminateClick1000003500
3Product Giga10000036101
4Product Giga10000037161
5Product Giga1000003600

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: [email protected]%
            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 the next post, I will explain the variable, condition, loop, and cursor how to use in, store procedures.

Swarup Das

Swarup Das

Web Developer, Tech Enthusiast. Passionate about new technology and programming languages.

Leave a Reply

Your email address will not be published. Required fields are marked *