Wednesday, 24 July 2019

How to create procedure in MySQL database?

                 In this post, we will see how to create procedure in MySQL database. 
             
What is the need of procedure?

1. By creating procedure, we can run multiple SQL queries in a single call. 

2. Another advantage is that procedure is stored and can be called at any time. No need to write same SQL queries again and again.

                 Check following example:


Procedure in MySQL:
mysql> delimiter //
mysql> create procedure sample()
    -> begin
    -> insert into student values(3303,'TE','GCOE','Amravati');
    -> update student set city='Aurangabad' where college='GCOE';
    -> select * from student;
    -> end//

mysql> delimiter ;
               
Output:
mysql> call sample();
+--------+-------+---------+------------+
| rollno | class | college | city       |
+--------+-------+---------+------------+
|   1221 | SE    | PICT    | Pune       |
|   3221 | TE    | VJTI    | Mumbai     |
|   1201 | se    | vjti    | mumbai     |
|   3201 | te    | coep    | Pune       |
|   4201 | be    | wce     | Sangli     |
|   2301 | SE    | COEP    | Pune       |
|   3301 | TE    | SGGS    | Nanded     |
|   3303 | TE    | GCOE    | Aurangabad |
+--------+-------+---------+------------+
8 rows in set (0.17 sec)

Query OK, 0 rows affected (0.17 sec)

Explanation
             delimiter command is used to change the end of MySQL Query.
e.g. Generally end of SQL query is ; . Now, if we want to change it to //. Then execute delimiter //. If we want to make end of each sql query as $$, then execute delimiter $$.

             In above example, we have to add multiple SQL queries in procedure sample(), hence we have changed the end of SQL query as // by executing delimiter //. After defining procedure sample, again we have reset the end of SQL query as ; by executing delimiter ; .


Syntax for creating procedure in MySQL is as follows:

create procedure procedure_name
begin
     SQL Queries;
end

Syntax for calling procedure is as follows:

call procedure_name();

Syntax for displaying all procedures:

show procedure status;

Syntax for deleting any procedure:

drop procedure procedure_name;

Syntax for getting all options for show command:

help show;



Find how to use parameters IN, OUT, INOUT in MySQL procedure in next post  https://www.comrevo.com/2019/07/mysql-stored-procedure-parameters-in-out-inout.html.
                 

No comments:

Post a comment