If you already use databases such as Oracle, Interbase / Firebird, PostgreSQL, sure heard from stored procedures. However, in MySQL this is a novelty and a huge step for this database will become a true management system databases.
Los procedimientos almacenados son un conjunto de instrucciones SQL más una serie de estructuras de control que nos permiten dotar de cierta lógica al procedimiento. Estos procedimientos están guardados en el servidor y pueden ser accedidos a través de llamadas, como veremos más adelante.
Para crear un procedimiento, MySQL nos ofrece la directiva CREATE PROCEDURE. Al crearlo éste es ligado o relacionado con la base de datos que se está usando, tal como cuando creamos una tabla, por ejemplo.
Para llamar a un procedimiento We protect the CALL statement. Since a procedure in turn can invoke other procedures or functions.
A stored procedure, like any of the procedures that we set in our applications using any language, is:
A name.
can have a list of parameters.
has a content (also called definition section of the case: here
specify what they will do and how.)
This content can consist of SQL statements, control structures, local variable declaration, error handling, etc..
- MySQL SQL syntax follows: 2003 for stored procedures, which also uses IBM DB2.
- In summary, the syntax of a stored procedure is as follows:
- CREATE PROCEDURE name (parameter)
[features] -
-
definition may be more than one parameter (separated by commas) or may not have any ( in this case must still be present parentheses, although there is nothing inside.)
The parameters have the following structure: name type mode
Where:
way: is optional and may be IN (the default value are the parameters that the procedure will) OUT (are the parameters that the procedure may change) INOUT (mixture of the two).
name is the name of the parameter.
type: any type of data provided by MySQL. Within
features may include comments or define whether the procedure will return the same results with equal inputs, among other things.
definition: it is the body of the procedure and consists of the procedure itself: here you define what it does, how it does and under what circumstances do.
- Just as there are procedures, there are also functions. To create a function, MySQL offers the CREATE FUNCTION directive.
- The difference between a function and a procedure is that the function return values. These values \u200b\u200bcan be used as arguments to SQL statements, as we normally do with other functions such as, for example, MAX () or COUNT (). Use
- RETURNS clause is required when defining a function and used to specify the type of data to be returned (only the data type, not the data).
- Its syntax is:
- CREATE FUNCTION name (parameter)
RETURNS type [characteristic]
definition may be more than one parameter (Separated by commas) or may not have any (in this case the brackets should still be present, although there is nothing inside.) The parameters have the following structure: name type
Where:
name is the name of the parameter.
type: any type of data provided by MySQL. Within
features may include comments or define whether the function will return the same results with equal inputs, among other things.
definition: it is the body of the procedure and consists of the procedure itself: here you define what it does, how it does and when.
To call a function we do it simply by invoking his name, as in many programming languages.
- Since a function in turn can invoke other functions or procedures.
-
mysql> delimiter / / - mysql> CREATE PROCEDURE procedure (cod IN INT)
-> - BEGIN -> SELECT * FROM table WHERE cod_t = cod;
-> END
-> / / Query
OK, 0 rows affected (0.00 sec)
mysql> delimiter;
mysql> CALL procedimento (4);
In the previous code first thing we do is set a delimiter. Using the MySQL command line we saw that the default delimiter is the semicolon (;) in stored procedures can define us.
The interesting thing is that we can write the previous anchor, without which the procedure ends. Later in this same code will return to the classical boundary. Then we create the procedure with the syntax seen above and locate the content between the keywords BEGIN and END.
The procedure takes one parameter and then work with him, so this parameter is of type IN. We define the parameter as OUT when he goes out to await the procedure. If the parameter had been in and out at the same time, it would be called INOUT type.
The procedure ends and is then called with the following statement:
mysql> CALL procedimento (4);
Another example: CREATE PROCEDURE
procedimiento2 (IN a INTEGER)
BEGIN DECLARE CHAR variable ( 20);
IF a> 10 THEN SET
variable = 'greater than 10';
ELSE SET variable = 'less than or equal to 10';
END IF;
INSERT INTO table VALUES (variable);
END
• The procedure takes one parameter which is called type integer.
• Declare a variable for internal use is called variable and is of type char.
• Implement a control structure and if a is greater than 10 variable is assigned a value. If it is not assigned another.
• Use the final value of variable in an SQL statement.
Recall that to implement the last example should use new anchors, as discussed above.
now observe a sample of functions:
mysql> delimiter / /
mysql> CREATE FUNCTION square (s SMALLINT) RETURNS SMALLINT
-> RETURN s * s;
-> / /
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter;
mysql> SELECT square (2);
Other databases such as PostgreSQL stored procedures implemented and provide the ability to be programmed using languages \u200b\u200bsuch as PHP or Java.
In MySQL is planning to implement the same and probably in future versions we will see, but more important to use language or another is to understand what might serve the stored procedures.
Ultimately we took a tour through the world of programming stored procedures in MySQL. It is important that this is a world that is under development and promises to evolve.
Let Link:
http://dev.mysql.com/doc/refman/5.0/es/stored-procedures.html
phrase 
Many times in our java projects can not do this, as the web framework does not. For these cases there URLRewrite, which allows our url rewriting as we want; eye not only be used for restful url but for any change we would like to make our urls. 
But what son en realidad los procedimientos almacenados? Luego de sumergirnos en este tema veremos que el nombre es plenamente identificatorio y casi explica lo que es un procedimiento almacenado. 
