[MySQL] DELIMITER

ALEX T. SILVERSTEIN'S BLOG에서.....

MYSQL: THE REASON FOR USING THE “DELIMITER” STATEMENT IN MYSQL ROUTINES (STORED PROCEDURES, FUNCTIONS, TRIGGERS)

This short article illustrates how to format your MySQL routines (stored procedures, functions, triggers) for release. We’ll use the following code block to discuss.

DROP PROCEDURE IF EXISTS mytestproc; -- Don't use "dbname.mytestproc"

 

DELIMITER $$ -- Change delimiter from semicolon

 

CREATE PROCEDURE mytestproc() -- Again, don't use "dbname.mytestproc"

BEGIN

# ATS Created test for dbname data 06/20/2013

SELECT 1;

END$$ -- Use of new $$ delimiter

DELIMITER ; -- Change delimiter back to the default (semicolon). Note that the spacing used here is important!

1) The first step in the script is to drop the existing routine, if it exists. Notice how the procedure name is not prefixed with database name (e.g.,dbname.mytestproc). This is so that we can run this same script against multiple databases.

2) The next step is to change the default MySQL script parser’s delimiter from semicolon (;) to double-dollar sign ($$). The reason you do this is so that the semicolons after each statement in the body of the routine are not interpreted by the parser as meaning the end of the CREATE PROCEDURE statement. This is because the entire CREATE PROCEDURE block, from CREATE PROCEDURE to END is actually a single statement that must be executed by itself. Were it not for the delimiter change, the script would break, since there each statement inside BEGIN and END would execute individually. Note that you can use a variety of non-reserved characters to make your own custom delimiter.

3) The next step is to create the routine.

4) Next is the BEGIN statement, indicating the start of the routine body

5) Next are the statements inside the routine, each terminated with semicolon (which will NOT be interpreted as end-of-CREATE PROCEDURE, since you changed the delimiter to $$).

6) Next is the END$$ statement. Notice how END is followed by $$. This actually ends the CREATE PROCEDURE statement.

7) Finally, the default delimiter (semicolon) is restored