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?
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.
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
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 ;
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.
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.