DELIMITER is a MySQL-specific command for temporarily changing the marker that signals the end of a SQL statement. It matters when defining a stored procedure because the procedure contains several statements, each normally ending with a semicolon. Without a different marker, MySQL could treat the first internal semicolon as the end of the entire procedure. section 20.2.1
Creating a stored procedure
Change the delimiter to double slashes, define the procedure with its normal internal semicolons, close the procedure with //, and restore the semicolon afterward. The resulting GetTitleCategory procedure joins title and category data; CALL GetTitleCategory executes it. section 20.2.1
DELIMITER // #1
CREATE PROCEDURE GetTitleCategory() #2
BEGIN #3
SELECT #4
t.TitleName,
c.CategoryName
FROM title t
INNER JOIN category c
ON t.CategoryID = c.CategoryID;
END // #5
DELIMITER ; #6
This listing shows the complete delimiter sequence: switch to //, create the procedure, terminate it with //, and switch back to ;. section 20.2.1
Use the pattern consistently:
- Issue DELIMITER // before the CREATE PROCEDURE statement. section 20.2.1
- Keep semicolons inside the procedure for its individual SQL statements. section 20.2.1
- End the procedure with END //, then restore DELIMITER ; for ordinary queries. section 20.2.1
- Apply the same pattern to conditional procedures such as AddPromotion, whose IF block contains an INSERT statement. section 21.2.1
DELIMITER changes how the procedure definition is terminated; it does not replace the semicolons that separate statements inside the procedure. Procedure syntax and execution vary among database products, so this command should not be treated as portable SQL. section 20.2.1 section 21.2.1
