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
id | name | sku | qty | status |
---|---|---|---|---|
1 | Apple | 10000034 | 50 | 1 |
2 | Pan | 10000032 | 10 | 0 |
3 | Desktop | 10000031 | 1 | 1 |
4 | mouse | 10000030 | 23 | 1 |
5 | graphic card | 10000021 | 56 | ! |
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.
Top comments (0)