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();.
