Mysql – Stored Procedures Using MySQL Workbench

delimiterMySQLstored-procedures

Very new to the environment, I have a question about a line that's added to the end of my code. The guide I'm following is:

http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/

If anyone has a better one regarding MySQL stored procedures, I'm all ears.

Before I ask, this is the environment I'm using:
OS: Windows 7 / WAMP (MySQL 5.5.24) / MySQL Workbench

I'm instructed to define a delimiter; in my case I'm sticking with the default '$$.'

The stored procedure I created is:

DELIMITER $$
CREATE PROCEDURE test.`p2` ()
LANGUAGE SQL
DETERMINISTIC
COMMENT 'Adds "nson" to first and last names in the record.'
BEGIN
SELECT 'Hello World';
END $$

When I apply this stored procedure and I get the review screen, I see a new line of code added;

At the bottom:

DELIMITER ;

This lats line; is it added because the DELIMITER statement announces a block within which the defined delimiters ($$) can be used and thus closes the block in the end?

Best Answer

When using the builtin procedure editor, MySQL Workbench adds a few extra commands:

USE `test`; // <----------
DROP procedure IF EXISTS `p2`;  // <----------

DELIMITER $$
USE `test`$$ // <----------
CREATE PROCEDURE test.`p2` ()
LANGUAGE SQL
DETERMINISTIC
COMMENT 'Adds "nson" to first and last names in the record.'
BEGIN
SELECT 'Hello World';
END $$

DELIMITER ; // <----------

Those commands are not strictly related to the stored procedures syntax, they're merely a commodity—other MySQL clients (such as HeidiSQL or the official command line utility) will not add them. The last delimiter change is probably a reset to avoid problems in future statements on the same connection.

You need to change the delimiter in order to instruct the client about where the procedure code starts and end. The problem is that the procedure body is normally a collection of SQL statements so omitting the delimiter change would make MySQL think that you are attempting to run a series of statements, the first of which would be this:

CREATE PROCEDURE test.`p2` ()
LANGUAGE SQL
DETERMINISTIC
COMMENT 'Adds "nson" to first and last names in the record.'
BEGIN
SELECT 'Hello World';

With DELIMITER $$ you are telling MySQL that your full statement goes from CREATE to END. It's just syntactic sugar: DELIMITER is not even a SQL keyword. HeidiSQL, for instance, provides a GUI with a text box where you write the procedure body, thus you don't need the DELIMITER workaround.

Related Topic