A stored procedure in MySQL is created using below syntax.
Stored procedure syntax
DELIMITER $$
CREATE PROCEDURE [procedure name]()
BEGIN
[Statements of procedure]
END $$
DELIMITER ;
CREATE PROCEDURE [procedure name]()
BEGIN
[Statements of procedure]
END $$
DELIMITER ;
A procedure is created with a
CREATE PROCEDURE statement followed by the name of procedure.Name of the procedure is followed by parenthesis which contain optional parameters accepted by the procedure. Stored procedure parameters will be discussed in the next section.
A procedure is enclosed between
BEGIN and END statements which contain the body of procedure or the set of SQL queries of the procedure.What is DELIMITER
Default delimiter of MySQL is a semi-colon which means that when MySQL encounters a semi-colon, it treats it as the end of a statement.
Now, when multiple SQL queries are written inside a procedure, they will also end with a semi-colon.
So, after the first SQL query, MySQL will consider it as the end of the procedure.
Hence, before starting the procedure, we change the default delimiter using
DELIMITER statement followed by the delimiter we want.After the procedure ends by using
END statement, we place the same delimiter just to indicate MySQL about the end of procedure.Finally, we reset the delimiter to default, that is, a semi-colon. Delimiter for a procedure can by anything such as
**, && etc. $$ is just an example.Executing Stored Procedure
A stored procedure can be invoked using
CALL statement followed by the name of the stored procedure and parenthesis.Parenthesis should contain the parameters expected by the stored procedure. If the stored procedure does not expect any parameters, then the parenthesis should be empty.
Examples of calling a stored procedure are given below.
— invoke procedure named getdata without any parameters
CALL getdata();
— invoke procedure fetchprice with item code as parameter
CALL fetchprice(123)
— invoke the same procedure with a variable name
SET itemcode=123;
CALL fetchprice(@itemcode);
Dropping a procedure
A stored procedure can be deleted using DROP PROCEDURE statement followed by the name of the procedure as shown below.
DROP PROCEDURE getdata;
If the procedure does not exist, then you will get an error stating the procedure does not exist.