What are the prerequisites for starting writing and using MySQL stored procedure?

MySQLMySQLi Database

We must have the following prerequisites before starting writing and using MySQL stored procedures −

MySQL Version

As we know that MySQL 5 introduced stored procedures, hence first of all we need to check for the version of MySQL before staring writing and using stored procedures. It can be done with the following query −

mysql> Select VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.20    |
+-----------+
1 row in set (0.10 sec)

Privileges for the current user

Actually, CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable. Privileges for the current user can be checked with the following query −

mysql> SHOW PRIVILEGES

Selecting a database

Before creating a procedure we must have to select a database from the available databases. It can be done with the help of the following query −

mysql> USE query;
Database changed

From the above query, it is clear that we are using a database named ‘query’.

Picking a DELIMITER

The delimiter is the character or string of characters that is used to complete an SQL statement. By default, we use a semicolon (;) as a delimiter. But this causes a problem in stored procedures because a procedure can have many statements, and everyone must end with a semicolon. So for your delimiter, pick a string that rarely occurs within a statement or within the procedure. We can choose double backslash // or double dollar signs $$ or any of the other delimiter of our choice. It can be done with the help of the following query −

mysql> DELIMITER // ;

Now, the default delimiter has been changed to // sign and it can be checked from the following query −

mysql> Select * from student_info //
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 100  | Aarav   | Delhi      | Computers  |
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Jaipur     | Literature |
| 110  | Rahul   | Chandigarh | History    |
+------+---------+------------+------------+
4 rows in set (0.00 sec)
raja
Published on 21-Feb-2018 11:56:02
Advertisements