MySQL Procedure is often called stored procedure. It is a pre-compiled SQL statement, which is stored in the database. So we can say, It is a subprogram or subroutine. So, stored procedure code can be used over and over again.
Syntax:-
CREATE PROCEDURE procedure_name
AS SQL statement
GO;
Advantage:-
Normally, the stored procedure is useful for:-
- increase the performance of the application
- reduce the traffic between the database and the query
- As mentioned earlier. we can reuse it.
- It is a secure process because for stored procedure database administrator is not required to grant access to the database Table.
Disadvantage:-
- A stored procedure is not useful for complex and flexible business logic.
- It increases memory use.
- Debugging is hard
- Maintenance will be difficult.
On the basis of writing the stored procedure we can categorize it in four-part:-
- Without Parameter:- There are no parameters defined.
Syntax:- DELIMITER&&
CREATE PROCEDURE procedure_name()
begin
SQL statement
end
DELIMITER;
For call:- CALL procedure_name();
- IN Parameter:- It takes input parameters.
Syntax:- DELIMITER&&
CREATE PROCEDURE procedure_name(IN para1 datatype, para2 datatype, ..)
begin
SQL statement
end
DELIMITER;
For call:- CALL procedure_name();
- OUT Parameter:- It takes output parameters.
Syntax:- DELIMITER&&
CREATE PROCEDURE procedure_name(OUT para1 datatype, para2 datatype, ..)
begin
SQL statement
end
DELIMITER;
For call:- CALL procedure_name();
- INOUT Parameter:- It takes both input and output parameters.
Syntax:- DELIMITER&&
CREATE PROCEDURE procedure_name(INOUT para1 datatype, para2 datatype, ..)
begin
SQL statement
end
DELIMITER;
For call:- CALL procedure_name();
Example 1:- For creating a procedure first, we need to select a database.
Like :- USE database_name
In my case database name is PayrollSys, So
USE ParollSys;
Suppose In our database have a table name pay_emp_info
This table contains fields “empID, emp_name, emp_code, emp_department, emp_salary, emp_phone”
Suppose we want to display all employees whose salary is greater than 25000 and count all table rows.
DELIMITER&&
CREATE PROCEDURE emplyee_salary()
begine
SELECT * FROM Pay_emp_info WHERE emp_salary > 25000
SELECT COUNT(emp_code) AS total_emp FROM Pay_emp_info
end
DELIMITER;
For call:- CALL emplyee_salary ();
Example2:- Suppose we want to change the department name from Account to Finance
DELIMITER&&
CREATE PROCEDURE update_department(IN tmp_ empID INT(11), IN tmp_update_dept VARCHAR(11))
begin
UPDATE Pay_emp SET emp_department = tmp_update_dept WHERE empID = tmp_ empID;
end;
DELIMITER;
CALL update_department(1, Finance);
Example3:- If we want to get the highest salary for the employee
DELIMITER&&
CREATE PROCEDURE highest_salary(OUT highest_salary INT)
begin
SELECT MAX(emp_salary) INTO highest_salary FROM Pay_emp;
end;
DELIMITER;
CALL highest_salary (@M);
SELECT @M;
Example 4:- If We want to find specific employee salaries on the basis of empID.
DELIMITER&&
CREATE PROCEDURE find_salary(INOUT find_salary INT)
begin
SELECT emp_salary INTO find _salary FROM Pay_emp WHERE empID = find_salary;
end;
DELIMITER;
SET @M = 5;
CALL find_salary (@M);
If we want to show procedure on the basis of a specific pattern
SHOW PROCEDURE STATUS [LIKE ‘pattern’ | WHERE search_condition]
If we want to show the procedure in a specific database
SHOW PROCEDURE STATUS WHERE db = ‘mystudentdb’;
From the above way, we can get all list of procedure
For the DROP/DELETE procedure
DROP PROCEDURE [ IF EXISTS ] procedure_name
Creating procedures in PHPMyAdmin is a part of the Routine
First log in to PHPMyAdmin
Then select database
Then select the ROUTINE menu
Then select New a popup form will open Here you can able to create procedures and function
Read Also:–
MySQL Store Function
MySQL Function
MySQL Triggers
MySQL View
For more detail about MySQL Create function please read MySQL manual
