Stored Procedure

Stored Procedure

Stored procedure is a block of code in database catalog and can be invoke later by a program.It just write the procedure in MySql then execute it.The procedure will save in catalog form that function name.You can see the procedure when you creating a backup file of  database schema in backup contain in Objecttype PROCEDURE. There are some advantages and disadvantages also.

Advantages

•                 Reusable and transparent to any application. Stored procedures are not in our programming file but there in database.An application of any programming language can invoke that procedure  by invoking the stored procedure name.
•                 It reduce the traffic between database server and application.
•                 It is secured.Data base Administrator can grant access level by using stored procedure.

Disadvantages

•                 It need a higher speed and higher capacity   database server.

Example:

DELIMITER //                                                         >>change the standard (;)delimiter to //

CREATE  PROCEDURE getAllUserDetaills()        >> name of the stored procedures

BEGIN                                                                   >> begin the actual query

SELECT * FROM USER                                       >>write the query

END//                                                                     >> end of the procedure

DELIMITER;                                                          >>change the delimiter to standard

 Can invoke the that procedure by using  CALL getAllUserDetails();.