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Â
- 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()
