Apuntes de MySQL #2

Published by danilo04 on July 30th, 2008 in Uncategorized

Siguiendo con los estractos que tomo del manual de MySQL, les dejo la información que obtuve sobre Funciones y Procedimientos Almacenados (Store Procedures). Los Triggers los voy a dejar para la entrega #3.

Procedimientos almacenados y funciones

Los procedimientos almacenados y funciones son nuevas funcionalidades de la versión de MySQL 5.0. Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse en el servidor. Una vez que se hace, los clientes no necesitan relanzar los comandos individuales pero pueden en su lugar referirse al procedimiento almacenado. Los procedimientos almacenados requieren la tabla proc en la base de datos mysql. Esta tabla se crea durante la isntalación de MySQL 5.0. Si está actualizando a MySQL 5.0 desde una versión anterior, asegúrese de actualizar sus tablas de permisos para asegurar que la tabla proc existe. Desde MySQL 5.0.3, el sistema de permisos se ha modificado para tener en cuenta los procedimientos almacenados como sigue:

El permiso CREATE ROUTINE se necesita para crear procedimientos almacenados.
El permiso ALTER ROUTINE se necesita para alterar o borrar procedimientos almacenados. Este permiso se da automáticamente al creador de una rutina.
El permiso EXECUTE se requiere para ejecutar procedimientos almacenados. Sin embargo, este permiso se da automáticamente al creador de la rutina. También, la característica SQL SECURITY por defecto para una rutina es DEFINER, lo que permite a los usuarios que tienen acceso a la base de datos ejecutar la rutina asociada.

Sintaxis de los Procedimientos Almacenados

Los procedimientos almacenados y rutinas se crean con comandos CREATE PROCEDURE y CREATE FUNCTION . Una rutina es un procedimiento o una función. Un procedimiento se invoca usando un comando CALL , y sólo puede pasar valores usando variables de salida. Una función puede llamarse desde dentro de un comando como cualquier otra función (esto es, invocando el nombre de la función), y puede retornar un valor escalar. Las rutinas almacenadas pueden llamar otras rutinas almacenadas. Desde MySQL 5.0.1, los procedimientos almacenadoso funciones se asocian con una base de datos. Esto tiene varias implicaciones:

Cunado se invoca la rutina, se realiza implícitamente USE db_name ( y se deshace cuando acaba la rutina). Los comandos USE dentro de procedimientos almacenados no se permiten.
Puede calificar los nombres de rutina con el nombre de la base de datos. Esto puede usarse para referirse a una rutina que no esté en la base de datos actual. Por ejemplo, para invocar procedimientos almacenados p o funciones f esto se asocia con la base de datos test , puede decir CALL test.p() o test.f().
Cuando se borra una base de datos, todos los procedimientos almacenados asociados con ella también se borran.

Nota: En MySQL 5.0.0 los procedimiento almacenados son globales y no están asociados a una base de datos específica. Por defecto, la rutina se asocia con la base de datos actual. Para asociar la rutina explícitamente con una base de datos, especifique el nombre como db_name.sp_name al crearlo. La cláusula RETURNS puede especificarse sólo con FUNCTION, donde es obligatorio. Se usa para indicar el tipo de retorno de la función, y el cuerpo de la función debe contener un comando RETURN value. Cada parámetro es un parámetro IN por defecto. Para especificar otro tipo de parámetro, use la palabra clave OUT o INOUT antes del nombre del parámetro. Especificando IN, OUT, o INOUT sólo es valido para una PROCEDURE. Un procedimiento o función se considera “determinista” si siempre produce el mismo resultado para los mismos parámetros de entrada, y “no determinista” en cualquier otro caso. Si no se da ni DETERMINISTIC ni NOT DETERMINISTIC por defecto es NOT DETERMINISTIC. MySQL permite a las rutinas que contengan comandos DDL (tales como CREATE y DROP) y comandos de transacción SQL (como COMMIT). Esto no lo requiere el estándar, y por lo tanto, es específico de la implementación. Los procedimientos almacenados no pueden usar LOAD DATA INFILE. Los comandos que retornan un conjunto de resultados no pueden usarse desde una función almacenada. Esto incluye comandos SE- LECT que no usan INTO para tratar valores de columnas en variables, comandos SHOW y otros comandos como EXPLAIN. El siguiente es un ejemplo de función que toma un parámetro, realiza una operación con una función SQL, y retorna el resultado:

mysql&gt; delimiter //
mysql&gt; CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
-&gt; RETURN CONCAT('Hello, ',s,'!');
-&gt; //
Query OK, 0 rows affected (0.00 sec)
mysql&gt; delimiter ;
mysql&gt; SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)

Si el comando RETURN en un procedimiento almacenado retorna un valor con un tipo distinto al especificado en la cláusula RETURNS de la función, el valor de retorno se coherciona al tipo apropiado. Por ejemplo, si una función retorna un valor ENUM o SET, pero el co- mando RETURN retorna un entero, el valor retornado por la función es la cadena para el miembro de ENUM correspondiente de un con- junto de miembros SET .

ALTER PROCEDURE y ALTER FUNCTION

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

Este comando puede usarse para cambiar las características de un procedimiento o función almacenada. Debe tener el permiso ALTER ROUTINE para la rutina desde MySQL 5.0.3.

DROP PROCEDURE y DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Este comando se usa para borrar un procedimiento o función almacenado. Esto es, la rutina especificada se borra del servidor. Debe tener el permiso ALTER ROUTINE para las rutinas desde MySQL 5.0.3.

SHOW CREATE PROCEDURE y SHOW CREATE FUNCTION

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

Este comando es una extensión de MySQL . Similar a SHOW CREATE TABLE, retorna la cadena exacta que puede usarse para recrear la rutina nombrada.

Sentencia CALL

CALL sp_name([parameter[,...]])

El comando CALL invoca un procedimiento definido préviamente con CREATE PROCEDURE. CALL puede pasar valores al llamador usando parámetros declarados como OUT o INOUT . También “retorna” el número de registros afectados, que con un programa cliente puede obtenerse a nivel SQL llamando la función ROW_COUNT() y desde C llamando la función de la API C mysql_affected_rows() .

Sentencias BEGIN ... END

[etiqueta_inicio:] BEGIN
[lista_sentencias]
END [etiqueta_fin]

La sintaxis BEGIN ... END se utiliza para escribir sentencias compuestas que pueden aparecer en el interior de procedimientos almacenados y triggers. Una sentencia compuesta puede contener múltiples sentencias, encerradas por las palabras BEGIN y END. lista_sentencias es una lista de una o más sentencias. Cada sentencia dentro de lista_sentencias debe terminar con un punto y como (;) delimitador de sentencias. lista_sentencias es opcional, lo que significa que la sentencia compuesta vacía (BEGIN END es correcta. La cláusula opcional [NOT] ATOMIC no está soportada todavía. Esto significa que no hay un punto transaccional al inicio del bloque de instrucciones y la cláusula BEGIN usada en este contexto no tiene efecto en la transacción actual.

Variables dentro de los procedimientos almacenados

Declare

DECLARE var_name[,...] type [DEFAULT value]

Este comando se usa para declarar variables locales. Para proporcionar un valor por defecto para la variable, incluya una cláusula DEFAULT . El valor puede especificarse como expresión, no necesita ser una constante. Si la cláusula DEFAULT no está presente, el valor inicial es NULL. La visibilidad de una variable local es dentro del bloque BEGIN ... END donde está declarado. Puede usarse en bloques anidados excepto aquéllos que declaren una variable con el mismo nombre.

SET

SET var_name = expr [, var_name = expr] ...

El comando SET en procedimientos almacenados es una versión extendida del comando general SET. Las variables referenciadas pueden ser las declaradas dentro de una rutina, o variables de servidor globales. El comando SET en procedimientos almacenados se implementa como parte de la sintaxis SET pre-existente. Esto permite una sintaxis extendida de SET a=x, b=y, ... donde distintos tipos de variables (variables declaradas local y globalmente y variables de sesión del servidor) pueden mezclarse. Esto permite combinaciones de variables locales y algunas opciones que tienen sentido sólo para variables de sistema; en tal caso, las opciones se reconocen pero se ignoran.

SELECT ... INTO

SELECT col_name[,...] INTO var_name[,...] table_expr

Esta sintaxis SELECT almacena columnas seleccionadas directamente en variables. Por lo tanto, sólo un registro puede retornarse. SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

Conditions and Handlers

DECLARE handlers

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
CONTINUE
| EXIT
| UNDO
condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code

Para un handler CONTINUE , continúa la rutina actual tras la ejecución del comando del handler. Para un handler EXIT , termina la ejecución del comando compuesto BEGIN...END actual. El handler de tipo UNDO todavía no se soporta.

SQLWARNING es una abreviación para todos los códigos SQLSTATE que comienzan con 01.
NOT FOUND es una abreviación para todos los códigos SQLSTATE que comienzan con 02.
SQLEXCEPTION es una abreviación para todos los códigos SQLSTATE no tratados por SQLWARNING o NOT FOUND.

Cursores

Se soportan cursores simples dentro de procedimientos y funciones almacenadas. La sintaxis es la de SQL empotrado. Los cursores no son sensibles, son de sólo lectura, y no permiten scrolling. No sensible significa que el servidor puede o no hacer una copia de su tabla de resultados. En bases de datos, el término cursor se refiere a una estructura de control utilizada para el recorrido (y potencial procesamiento) de los registros del resultado de una consulta. Un cursor se utiliza para el procesamiento individual de las filas devueltas por el sistema gestor de base de datos para una consulta. Es necesario debido a que muchos lenguajes de programación sufren de lo que en inglés se conoce como impedance mismatch. Por norma general los lenguajes de programación son procedurales y no disponen de ningún mecanismo para manipular conjuntos de datos en una sola instrucción. Debido a ello, las filas deben ser procesadas de forma secuencial por la aplicación. Un cursor puede verse como un iterador sobre la colección de filas que habrá en el set de resultados. Ejemplo:

CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
811
Procedimientos almacenados y funciones
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b &lt; c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END

Constructores de Flujo

Sentencia IF

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

IF implementa un constructor condicional básico. Si search_condition se evalúa a cierto, el comando SQL correspondiente listado se ejectua. Si no coincide ninguna search_condition se ejecuta el comando listado en la cláusula ELSE. statement_list puede consistir en varios comandos. Tenga en cuenta que también hay una función IF(), que difiere del commando IF descrito aquí.

Sentencia CASE

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

El comando CASE para procedimientos almacenados implementa un constructor condicional complejo. Si una search_condition se evalúa a cierto, el comando SQL correspondiente se ejecuta. Si no coincide ninguna condición de búsqueda, el comando en la cláusula ELSE se ejecuta.

Sentencia LOOP

[begin_label:] LOOP
statement_list
END LOOP [end_label]

LOOP implementa un constructor de bucle simple que permite ejecución repetida de comandos particulares. El comando dentro del bucle se repite hasta que acaba el bucle, usualmente con un comando LEAVE.

Sentencia LEAVE

LEAVE label Este comando se usa para abandonar cualquier control de flujo etiquetado. Puede usarse con BEGIN ... END o bucles.

Sentencia ITERATE

ITERATE label ITERATE sólo puede aparecer en comandos LOOP, REPEAT, y WHILE . ITERATE significa “vuelve a hacer el bucle.” Por ejemplo:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 &lt; 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END

Sentencia REPEAT

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

El comando/s dentro de un comando REPEAT se repite hasta que la condición search_condition es cierta. Un comando REPEAT puede etiquetarse. end_label no puede darse a no ser que begin_label esté presente, y si lo están, deben ser el mismo. Por ejemplo:

mysql&gt; delimiter //
mysql&gt; CREATE PROCEDURE dorepeat(p1 INT)
-&gt; BEGIN
-&gt;    SET @x = 0;
-&gt;    REPEAT SET @x = @x + 1; UNTIL @x &gt; p1 END REPEAT;
-&gt; END
-&gt; //
Query OK, 0 rows affected (0.00 sec)
mysql&gt; CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
mysql&gt; SELECT @x//
+------+
| @x     |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

Sentencia WHILE

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

El comado/s dentro de un comando WHILE se repite mientras la condición search_condition es cierta. Un comando WHILE puede etiquetarse. end_label no puede darse a no ser que begin_label también esté presente, y si lo están, deben ser el mismo. Por ejemplo:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 &gt; 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END

danilo04's blog

carlos (no verificado) on May 04th 2010

Gracias, buenísimo!!!!!!!!!!!!!!

responder

Danilo Domínguez P. Site