- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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?
152 Lectures 16 hours
87 Lectures 5.5 hours
We must have the following prerequisites before starting writing and using MySQL stored procedures −
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)
- What are the prerequisites before starting writing and using MySQL views?
- What are the limitations for replicating stored procedure and functions?
- What are the prerequisites for learning C#?
- What are the prerequisites for learning Java?
- What is the difference between MySQL stored procedure and function?
- What is stored procedure and how can we create MySQL stored procedures?
- What are the different modes of parameters used by MySQL stored procedure?
- How can a MySQL stored procedure call another MySQL stored procedure inside it?
- What are the advantages and disadvantages of using MySQL stored procedures?
- MySQL stored procedure return value?
- MySQL stored-procedure: out parameter?
- What are the prerequisites to learn Selenium?
- How to use FOR LOOP in MySQL Stored Procedure?
- MySQL Sum Query with IF Condition using Stored Procedure
- Display description of MySQL stored procedure