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?
- Periodic updating 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 started
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 change the default delimiter to dollar ($) like the following example :
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
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;
In the next post, I will explain the variable, condition, loop, and cursor how to use in, store procedures.