Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What are the prerequisites for starting writing and using MySQL stored procedure?
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)
171 Views
