MySQL - CREATE PROCEDURE Statement



CREATE PROCEDURE Statement

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures.

Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.

You can create a procedure using the MySQL CREATE PROCEDURE statement.

Syntax

Following is the syntax of the CREATE PROCEDURE statement −

CREATE PROCEDURE procedure_name (proc_parameter[,...]) routine_body

Where, procedure_name is the name of the function you need to create, input_arguments are the input values of the function and output_parameter is the return value of the function.

Example

Assume we have created a table named Employee in MySQL database using the CREATE TABLE as shown below −

CREATE TABLE Employee(
   Name VARCHAR(255), 
   Salary INT NOT NULL, 
   Location VARCHAR(255)
);

Following is an example of a MySQL stored procedure. Here we are inserting a column in to the Employee table taking values from user. (through input parameters)

DELIMITER //
Create procedure myProcedure (
   IN name VARCHAR(30),
   IN sal INT,
   IN loc VARCHAR(45))
      BEGIN
      INSERT INTO Employee(Name, Salary, Location) 
	  VALUES (name, sal, loc);
      END //
DELIMITER ;

Calling a stored procedure

Following statement calls the above created stored procedure.

CALL myProcedure ('Raman', 35000, 'Bangalore');

If you retrieve the contents of table, you can observe the newly inserted row as shown below −

select * from employee;

Output

The above query produces the following output −

Name Salary Location
Raman 35000 Bangalore
Advertisements