Delimiters in SQL

Pre-requisites: Different types of Procedures in MySQL

Delimiters are used when we need to define the stored procedures as well as to create triggers. Default delimiter is semicolon.

Step 1: Create and insert a value in a database.

But, if we are considering multiple statements, then we need to use different delimiters like $$ or //. The delimiter is a type of function in SQL. Using another delimiter which is //, we will get an error like this:

Output:

Now, by default, we are getting an error. How to make this work? In place of semicolon being used as the default delimiter, we would like to use //. Is it possible in SQL?

Yes, it is possible. The Syntax for this is given below:

Query:

Delimiter//
select*from world//

Output:

Now, in this case, there would be no output because in the delimiter statement we have written //, and then in the select statement we have used the delimiter $$. The delimiter in the delimiter statement as well as in the select statement should be the same to make it run.

Now we can also use the delimiter $$. The syntax is given below:

Query:

Delimiter$$
select * from world$$

When we are writing SQL statements, we use the semicolon to separate two different statements like in the example below:

Query:

SELECT * FROM employees; 
SELECT * FROM players;

For Example, MySQL workbench uses the delimiter semicolon (;) to separate two statements and then execute each statement separately.

But, a stored procedure contains multiple statements segregated by a semicolon (;).

If we are using the MySQL client program to define a stored procedure that consists of semicolon characters, the program will not treat the whole stored procedure as a single statement, but as many statements.

The command which we can use to redefine the delimiter is:

DELIMITER delimiter_character

The delimiter_character consists of a single character or multiple characters like for example: // or $$.

Once changing the delimiter, we can use the new delimiter to end a statement as shown below:

DELIMITER // 
SELECT * FROM employees // 
SELECT * FROM players //

To change the delimiter to the default one, which is a semicolon (;), we can use the following statement:

DELIMITER ;

Usually, a stored procedure contains multiple statements which are separated by semicolons. To compile the procedure as a single statement, we need to temporarily change the delimiter from semicolon (;) to another delimiter such as $$ or //:

DELIMITER $$ 
CREATE PROCEDURE customers() 
BEGIN  -- 
statements END $$ DELIMITER ;

Code Explanation:

• Change the default delimiter to $$.
• Now, use a semicolon (;) in the body and $$ after the END keyword to end the procedure.
• Again change the default delimiter back to a semicolon (;).