Delimiters in SQL
Last Updated :
08 Nov, 2022
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 (;).
Share your thoughts in the comments
Please Login to comment...