Stored Procedure in MYSQL using PHPMYADMIN

Pre-compiled collection of T-SQL statements stored with a name
Written by database developers or DBAs
Used to perform administrative tasks, or apply complex business rules
Contain DML statements

Benefits of Stored Procedures
—————————————-

• Increased execution speed
• Faster access to data
• Modular programming
• Consistency
• Enhanced security

 

Types of Stored Procedures
————————————-

• System stored procedures (can only be executed)
• User-defined stored procedures (can be created and executed)

 

User-defined Stored Examples 

1. CREATE PROCEDURE getallEmp()
   BEGIN
   SELECT * from emp;
   END

$$ : Delimiter

to execute, use this syntax: call getallEmp()

2) CREATE PROCEDURE InsEmp()
BEGIN
INSERT INTO emp (ename, bs, bn, ta, pf) VALUES
(‘Hari’,12000,2000,200,800);
END

call InsEmp()

3) CREATE PROCEDURE UpEmp()
BEGIN
UPDATE emp set bs=25000 WHERE ename=’Hari’;
END

call UpEmp()