A stored procedure in MySQL is created using below syntax.
A procedure is created with a
CREATE PROCEDUREstatement 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
ENDstatements 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
DELIMITERstatement followed by the delimiter we want.
After the procedure ends by using
ENDstatement, 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
$$is just an example.
Executing Stored Procedure
A stored procedure can be invoked using
CALLstatement 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
— invoke procedure fetchprice with item code as parameter
— invoke the same procedure with a variable name
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.0