Each stored program contains a body that consists of an SQL statement. This statement may be a compound statement made up of several statements separated by semicolon (';') characters. For example, the following stored procedure has a body made up of a note 'BEGIN ... END': begin-end. block that contains a note 'SET': set-variable. statement and a note 'REPEAT': repeat. loop that itself contains another note 'SET': set-variable. statement:
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;If you use the note 'mysql': mysql. client program to define a stored program containing semicolon characters, a problem arises. By default, note 'mysql': mysql. itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause *note 'mysql': mysql. to pass the entire stored program definition to the server.
To redefine the note 'mysql': mysql. delimiter, use the 'delimiter' command. The following example shows how to do this for the 'dorepeat()' procedure just shown. The delimiter is changed to '//' to enable the entire definition to be passed to the server as a single statement, and then restored to ';' before invoking the procedure. This enables the ';' delimiter used in the procedure body to be passed through to the server rather than being interpreted by note 'mysql': mysql. itself.
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)You can redefine the delimiter to a string other than '//', and the delimiter can consist of a single character or multiple characters. You should avoid the use of the backslash ('') character because that is the escape character for MySQL.
The following is an example of a function that takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use 'delimiter' because the function definition contains no internal ';' statement delimiters:
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)File: manual.info.tmp, Node: stored-routines, Next: triggers, Prev: stored-programs-defining, Up: stored-objects