MySQL Procedure

Creating a procedure

In MySQL, a procedure can also be created. A procedure can return one or more than one value through parameters or may not return at all. The procedure can be used in SQL queries.

Syntax

snippet

CREATE PROCEDURE procedure_name[ (parameter datatype [, parameter datatype]) ]
BEGIN
	Declaration_section
	Executable_section
END;

Parameter

procedure_name: name of the procedure.

Parameter: number of parameters. It can be one or more than one.

declaration_section: all variables are declared.

executable_section: code for the function is written here.

Example 1

Table - 1

Creating procedure:

snippet

DELIMITER $$
CREATE PROCEDURE get_student()
BEGIN
SELECT * FROM table1;
END$$

Calling procedure:

Drop a procedure

In MySQL, a procedure can also be dropped. When a procedure id dropped, it is removed from the database.

Syntax:

snippet

Drop procedure[ IF EXISTS ] procedure_name;

Parameter:

procedure_name: name of the procedure to be dropped.

Example 1:

snippet

drop procedure get_student;