Changing the Default Delimiter
In this tutorial, you will learn how to change the default delimiter by using DELIMITER command.
In MySQL program uses the delimiter semicolon(;) to separate the MySQL statements and execute each statement separately. However, a stored procedure contains multiple statement which are separated by a semicolon(;).
In creating stored procedure, you need to redefine the delimiter temporarily so that you can pass the complete stored procedure to the server as a single statement.
Syntax to redefine the delimiter.
The delimiter_character may be a single character or multiple characters like ‘//’ or ‘$$’. But you should not use backslash(\) as it is used to escape characters in MySQL.
A stored procedure contains multiple statements separated by semicolon(;). In order to compile the complete stored procedure as a single statement, you need to temporarily modify the delimiter from the semicolon(;) to another delimiter such as $$ or //.
DELIMITER $$ CREATE PROCEDURE stored_procedure_name() BEGIN //statements //statements END $$ DELIMITER ;
In the above example, Firstly delimiter has been changed to $$.
And $$ after the END keyword to end the stored procedure.
And at the end, the default delimiter set back to a semicolon(;).