Why specify delimiter for stored procedure when there's BEGIN and END?

user_18389384 0
Viewed 294

In order to pass a stored procedure to the server as a whole, we declare a new delimiter that won't allow MySQL to interpret statements one at a time. So, our procedure would look something like:

delimiter $$
create procedure some_procedure()
begin
insert into table1 select * from table2;
select * from table1;
end $$
delimiter ;

Notice that there are actually two "things" grouping our code base. They are BEGIN-END keywords and $$ delimeter. My question is why we need them both and isn't it redundant?

If someone plans on answering that we must specify BEGIN-END because of the stored procedure's syntax, they would be wrong as it is not mandatory if it contains a single query:

create procedure another_procedure()
select * from table2;

Can someone tell me what am I missing here?

stored-procedures mysql
2 Answers

Procedures

A stored procedure is a set of SQL statements that is stored in association with a database. It is an object that is created with the CREATE PROCEDURE statement and invoked with the CALL statement. A procedure can have zero or many input parameters and zero or many output parameters.

Syntax:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

DELIMITER

To define a stored procedure it is necessary to temporarily modify the separator character used to delimit SQL statements.

The default separator character used in SQL is the semicolon (;). In the examples we are going to perform we are going to use the characters $$ to delimit SQL statements, but it is possible to use any other character.

Example :

In this example we are setting the $$ characters as the separators between SQL statements.

DELIMITER $$

In this example we configure again that the separator character is the semicolon.

DELIMITER ;

Input, output and input/output parameters

In stored procedures we can have three types of parameters :

  • Input : They are indicated by putting the reserved word IN in front of the parameter name. These parameters cannot change their value within the procedure, that is, when the procedure ends these parameters will have the same value they had when the procedure call was made. In programming it would be equivalent to passing by value of a parameter

  • Output : They are indicated by placing the reserved word OUT in front of the parameter name. These parameters change their value within the procedure. When the procedure call is made, they start with an initial value and when the procedure execution ends, they can end with a different value. In programming it would be equivalent to passing a parameter by reference.

  • Input/Output : It is a combination of IN and OUT types. These parameters are indicated by putting the reserved word IN/OUT in front of the parameter name.

Anonymous PL/SQL blocks

We will start with anonymous blocks, characterized by the fact that they have no name and are usually created and executed from PL/SQL.

I will explain what each one does in detail :

  • DECLARE : in this zone we will make the declaration of the variables that we will use in begin. If there are no variables to declare, it is not necessary to put it.

  • BEGIN : this area contains the PL/SQL code to be executed.

  • END : indicates the closing of the anonymous block.

I hope all this has helped you, best regards.

user_124648800

In the docs, 25.1 Defining Stored Programs it says:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

So, the semi-colon is always (including when it's used inside a BEGIN/END block) seen as a statement delimiter.

user_7240390
Related