- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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 is stored procedure and how can we create MySQL stored procedures?
Stored procedure, in the context of regular computing language, may be defined as a subroutine like a subprogram that is stored in a database. In the context of MySQL, it is a segment of declarative SQL statements stored inside the database catalog. Before writing stored procedures in MySQL, we must have to check the version because MySQL 5 introduces stored procedure. Following is the syntax for creating a stored procedure −
Syntax
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type type: Any valid MySQL data type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement
Example
Following is an example in which we created a simple procedure to get all the records from the table ‘student_info’ which have the following data −
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)
Now, with the help of following queries we created the stored procedure named allrecords()
mysql> Delimiter // mysql> Create Procedure allrecords() -> BEGIN -> Select * from Student_info; -> END// Query OK, 0 rows affected (0.02 sec) mysql> DELIMITER ;
- Related Articles
- How can we create MySQL stored procedures without ‘BEGIN’ and ‘END’?
- How can we invoke MySQL stored procedure?
- How can we access tables through MySQL stored procedures?
- How can we create MySQL stored procedure to calculate the factorial?
- How can we alter a MySQL stored procedure?
- How can we drop a MySQL stored procedure?
- How can we see the list of stored procedures and stored functions in a particular MySQL database?
- How can a MySQL stored procedure call another MySQL stored procedure inside it?
- Call Stored Procedures within a Stored Procedure with IF Logic?
- Create a stored Procedures using MySQL Workbench?
- How can we write MySQL handler in a stored procedure?
- How can we perform START transactions inside MySQL stored procedure?
- How can we perform COMMIT transactions inside MySQL stored procedure?
- How can I create MySQL stored procedure with IN parameter?
- How can I create MySQL stored procedure with OUT parameter?

Advertisements