19.2. Sintaxis de procedimientos almacenados

MySQL 5.0

19.2. Sintaxis de procedimientos almacenados

Los procedimientos almacenados y rutinas se crean con comandos y . Una rutina es un procedimiento o una función. Un procedimiento se invoca usando un comando , 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 ( y se deshace cuando acaba la rutina). Los comandos 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 o funciones esto se asocia con la base de datos , puede decir o .

  • Cuando se borra una base de datos, todos los procedimientos almacenados asociados con ella también se borran.

(En MySQL 5.0.0, los procedimientos almacenados son globales y no asociados con una base de datos. Heredan la base de datos por defecto del llamador. Si se ejecuta desde la rutina, la base de datos por defecto original se restaura a la salida de la rutina.)

MySQL soporta la extensión muy útil que permite el uso de comandos regulares (esto es, sin usar cursores o variables locales) dentro de los procedimientos almacenados. El conjunto de resultados de estas consultas se envía diractamente al cliente. Comandos múltiples generan varios conjuntos de resultados, así que el cliente debe usar una biblioteca cliente de MySQL que soporte conjuntos de resultados múltiples. Esto significa que el cliente debe usar una biblioteca cliente de MySQL como mínimos desde 4.1.

La siguiente sección describe la sintaxis usada para crear, alterar, borrar, y consultar procedimientos almacenados y funciones.

19.2.1. CREATE PROCEDURE y CREATE FUNCTION

CREATE PROCEDURE  ([[,...]])
    [ ...] 

CREATE FUNCTION  ([[,...]])
    RETURNS 
    [ ...] 

:
    [ IN | OUT | INOUT ]  

:
    

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

:
    

Estos comandos crean una rutina almacenada. Desde MySQL 5.0.3, para crear una rutina, es necesario tener el permiso , y los permisos y se asignan automáticamente a su creador. Si se permite logueo binario necesita también el permisos como se describe en Sección 19.3, “Registro binario de procedimientos almacenados y disparadores”.

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 al crearlo.

Si el nombre de rutina es el mismo que el nombre de una función de SQL, necesita usar un espacio entre el nombre y el siguiente paréntesis al definir la rutina, o hay un error de sintaxis. Esto también es cierto cuando invoca la rutina posteriormente.

La cláusula puede especificarse sólo con , 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 .

La lista de parámetros entre paréntesis debe estar siempre presente. Si no hay parámetros, se debe usar una lista de parámetros vacía . Cada parámetro es un parámetro por defecto. Para especificar otro tipo de parámetro, use la palabra clave o antes del nombre del parámetro. Especificando , , o sólo es valido para una .

El comando se usa en versiones anteriores de MySQL para soportar UDFs (User Defined Functions) (Funciones Definidas por el Usuario). Consulte Sección 27.2, “Añadir nuevas funciones a MySQL”. UDFs se soportan, incluso con la existencia de procedimientos almacenados. Un UDF puede tratarse como una función almacenada externa. Sin embargo, tenga en cuenta que los procedimientos almacenados comparten su espacio de nombres con UDFs.

Un marco para procedimientos almacenados externos se introducirá en el futuro. Esto permitira escribir procedimientos almacenados en lenguajes distintos a SQL. Uno de los primeros lenguajes a soportar será PHP ya que el motor central de PHP es pequeño, con flujos seguros y puede empotrarse fácilmente. Como el marco es público, se espera soportar muchos otros lenguajes.

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 ni por defecto es .

Para replicación, use la función (o su sinónimo) o no hace una rutina no determinista necesariamente. Para , el log binario incluye el tiempo y hora y replica correctamente. también replica correctamente mientras se invoque sólo una vez dentro de una rutina. (Puede considerar el tiempo y hora de ejecución de la rutina y una semilla de número aleatorio como entradas implícitas que son idénticas en el maestro y el esclavo.)

Actualmente, la característica se acepta, pero no la usa el optimizador. Sin embargo, si se permite el logueo binario, esta característica afecta si MySQL acepta definición de rutinas. Consulte Sección 19.3, “Registro binario de procedimientos almacenados y disparadores”.

Varias características proporcionan información sobre la naturaleza de los datos usados por la rutina. indica que la rutina no contiene comandos que leen o escriben datos. indica que la rutina no contiene comandos SQL . indica que la rutina contiene comandos que leen datos, pero no comandos que escriben datos. indica que la rutina contiene comandos que pueden escribir datos. es el valor por defecto si no se dan explícitamente ninguna de estas características.

La característica puede usarse para especificar si la rutina debe ser ejecutada usando los permisos del usuario que crea la rutina o el usuario que la invoca. El valor por defecto es . Esta característica es nueva en SQL:2003. El creador o el invocador deben tener permisos para acceder a la base de datos con la que la rutina está asociada. Desde MySQL 5.0.3, es necesario tener el permiso para ser capaz de ejecutar la rutina. El usuario que debe tener este permiso es el definidor o el invocador, en función de cómo la característica .

MySQL almacena la variable de sistema que está en efecto cuando se crea la rutina, y siempre ejecuta la rutina con esta inicialización.

La cláusula es una extensión de MySQL, y puede usarse para describir el procedimiento almacenado. Esta información se muestra con los comandos y .

MySQL permite a las rutinas que contengan comandos DDL (tales como y ) y comandos de transacción SQL (como ). Esto no lo requiere el estándar, y por lo tanto, es específico de la implementación.

Los procedimientos almacenados no pueden usar .

Nota: Actualmente, los procedimientos almacenados creados con no pueden tener referencias a tablas. (Esto puede incluir algunos comandos que pueden contener referencias a tablas, por ejemplo , y por otra parte no pueden contener comandos , por ejemplo .) Esta limitación se elminará en breve.

Los comandos que retornan un conjunto de resultados no pueden usarse desde una función almacenada. Esto incluye comandos que no usan para tratar valores de columnas en variables, comandos y otros comandos como . Para comandos que pueden determinarse al definir la función para que retornen un conjunto de resultados, aparece un mensaje de error (). Para comandos que puede determinarse sólo en tiempo de ejecución si retornan un conjunto de resultados, aparece el error ().

El siguiente es un ejemplo de un procedimiento almacenado que use un parámetro . El ejemplo usa el cliente mysql y el comando para cambiar el delimitador del comando de a mientras se define el procedimiento . Esto permite pasar el delimitador usado en el cuerpo del procedimiento a través del servidor en lugar de ser interpretado por el mismo mysql.

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

Al usar el comando , debe evitar el uso de la antibarra ('') ya que es el carácter de escape de MySQL.

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> delimiter //

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

Si el comando en un procedimiento almacenado retorna un valor con un tipo distinto al especificado en la cláusula de la función, el valor de retorno se coherciona al tipo apropiado. Por ejemplo, si una función retorna un valor o , pero el comando retorna un entero, el valor retornado por la función es la cadena para el mienbro de correspondiente de un comjunto de miembros .

19.2.2. ALTER PROCEDURE y ALTER FUNCTION

ALTER {PROCEDURE | FUNCTION}  [ ...]

:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT ''

Este comando puede usarse para cambiar las características de un procedimiento o función almacenada. Debe tener el permiso para la rutina desde MySQL 5.0.3. El permiso se otorga automáticamente al creador de la rutina. Si está activado el logueo binario, necesitará el permiso , como se describe en Sección 19.3, “Registro binario de procedimientos almacenados y disparadores”.

Pueden especificarse varios cambios con o .

19.2.3. DROP PROCEDURE y DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 

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 para las rutinas desde MySQL 5.0.3. Este permiso se otorga automáticamente al creador de la rutina.

La cláusula es una extensión de MySQL . Evita que ocurra un error si la función o procedimiento no existe. Se genera una advertencia que puede verse con .

19.2.4. SHOW CREATE PROCEDURE y SHOW CREATE FUNCTION

SHOW CREATE {PROCEDURE | FUNCTION} 

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

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
       Function: hello
       sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')

19.2.5. SHOW PROCEDURE STATUS y SHOW FUNCTION STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE '']

Este comando es una extensión de MySQL . Retorna características de rutinas, como el nombre de la base de datos, nombre, tipo, creador y fechas de creación y modificación. Si no se especifica un patrón, le lista la información para todos los procedimientos almacenados, en función del comando que use.

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
           Db: test
         Name: hello
         Type: FUNCTION
      Definer: testuser@localhost
     Modified: 2004-08-03 15:29:37
      Created: 2004-08-03 15:29:37
Security_type: DEFINER
      Comment:

También puede obtener información de rutinas almacenadas de la tabla en . Consulte Sección 22.1.14, “La tabla .

19.2.6. La sentencia CALL

CALL ([[,...]])

El comando invoca un procedimiento definido préviamente con .

puede pasar valores al llamador usando parámetros declarados como o . También “retorna” el número de registros afectados, que con un programa cliente puede obtenerse a nivel SQL llamando la función y desde C llamando la función de la API C .

19.2.7. Sentencia compuesta BEGIN ... END

[:] BEGIN
    []
END []

Los procedimientos almacenados pueden contener varios comandos, usnado un comando compuesto .

Un comando compuesto puede etiquetarse. no puede darse a no ser que también esté presente , y si ambos lo están, deben ser el mismo.

Tenga en cuenta que la cláusula opcional no está soportada. Esto significa que no hay un punto transaccional al inicio del bloque de instrucciones y la cláusula usada en este contexto no tiene efecto en la transacción actual.

Usar múltiples comandos requiere que el cliente sea capaz de enviar cadenas de consultas con el delimitador de comando . Esto se trata en el cliente de línea de comandos mysql con el comando . Cambiar el delimitador de final de consulta end-of-query (por ejemplo, a ) permite usar en el cuerpo de la rutina.

19.2.8. Sentencia DECLARE

El comando se usa para definir varios iconos locales de una rutina: las variables locales (consulte Sección 19.2.9, “Variables en procedimientos almacenados”), condiciones y handlers (consulte Sección 19.2.10, “Conditions and Handlers”) y cursores (consulte Sección 19.2.11, “Cursores”). Los comandos y no se soportan en la actualidad.

puede usarse sólo dentro de comandos compuestos y deben ser su inicio, antes de cualquier otro comando.

Los cursores deben declararse antes de declarar los handlers, y las variables y condiciones deben declararse antes de declarar los cursores o handlers.

19.2.9. Variables en procedimientos almacenados

Pude declarar y usar una variable dentro de una rutina.

19.2.9.1. Declarar variables locales con

DECLARE [,...]  [DEFAULT ]

Este comando se usa para declarar variables locales. Para proporcionar un valor por defecto para la variable, incluya una cláusula . El valor puede especificarse como expresión, no necesita ser una constante. Si la cláusula no está presente, el valor inicial es .

La visibilidad de una variable local es dentro del bloque donde está declarado. Puede usarse en bloques anidados excepto aquéllos que declaren una variable con el mismo nombre.

19.2.9.2. Sentencia para variables

SET  =  [,  = ] ...

El comando en procedimientos almacenados es una versión extendida del comando general . Las variables referenciadas pueden ser las declaradas dentro de una rutina, o variables de servidor globales.

El comando en procedimientos almacenados se implemnta como parte de la sintaxis pre-existente. Esto permite una sintaxis extendida de 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.

19.2.9.3. La sentencia

SELECT [,...] INTO [,...] 

Esta sintaxis 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;

19.2.10. Conditions and Handlers

Ciertas condiciones pueden requerir un tratamiento específico. Estas condiciones pueden estar relacionadas con errores, así como control de flujo general dentro de una rutina.

19.2.10.1. Condiciones

DECLARE  CONDITION FOR 

:
    SQLSTATE [VALUE] 
  | 

Este comando especifica condiciones que necesitan tratamiento específico. Asocia un nombre con una condición de error específica. El nombre puede usarse subsecuentemente en un comando . Consulte Sección 19.2.10.2, “ handlers”.

Además de valores SQLSTATE , los códigos de error MySQL se soportan.

19.2.10.2. handlers

DECLARE  HANDLER FOR [,...] 

:
    CONTINUE
  | EXIT
  | UNDO

:
    SQLSTATE [VALUE] 
  | 
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | 

Este comando especifica handlers que pueden tratar una o varias condiciones. Si una de estas condiciones ocurren, el comando especificado se ejecuta.

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

  • es una abreviación para todos los códigos SQLSTATE que comienzan con .

  • es una abreviación para todos los códigos SQLSTATE que comienzan con .

  • es una abreviación para todos los códigos SQLSTATE no tratados por o .

Además de los valores SQLSTATE , los códigos de error MySQL se soportan.

Por ejemplo:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->   SET @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

Tenga en cuenta que es , lo que muestra que MySQL se ha ejecutado al final del procedimiento. Si la línea no está presente, MySQL habría tomado la ruta por defecto () tras el segundo fallido debido a la restricción , y habría retornado .

19.2.11. 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.

Los cursores deben declararse antes de declarar los handlers, y las variables y condiciones deben declararse antes de declarar cursores o handlers.

Por ejemplo:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  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 < 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

19.2.11.1. Declarar cursores

DECLARE  CURSOR FOR 

Este comando declara un cursor. Pueden definirse varios cursores en una rutina, pero cada cursor en un bloque debe tener un nombre único.

El comando no puede tener una cláusula .

19.2.11.2. Sentencia del cursor

OPEN 

Este comando abre un cursor declarado préviamente.

19.2.11.3. Sentencia de cursor

FETCH  INTO  [, ] ...

Este comando trata el siguiente registro (si existe) usando el cursor abierto especificado, y avanza el puntero del cursro.

19.2.11.4. Sentencia de cursor

CLOSE 

Este comando cierra un cursor abierto préviamente.

Si no se cierra explícitamente, un cursor se cierra al final del comando compuesto en que se declara.

19.2.12. Constructores de control de flujo

Los constructores , , , , , y están completamente implementados.

Estos constructores pueden contener un comando simple, o un bloque de comandos usando el comando compuesto . Los constructores pueden estar anidados.

Los bucles no están soportados.

19.2.12.1. Sentencia

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

implementa un constructor condicional básico. Si se evalúa a cierto, el comando SQL correspondiente listado se ejectua. Si no coincide ninguna se ejecuta el comando listado en la cláusula . puede consistir en varios comandos.

Tenga en cuenta que también hay una función , que difiere del commando descrito aquí. Consulte Sección 12.2, “Funciones de control de flujo”.

19.2.12.2. La sentencia

CASE 
    WHEN  THEN 
    [WHEN  THEN ] ...
    [ELSE ]
END CASE

O:

CASE
    WHEN  THEN 
    [WHEN  THEN ] ...
    [ELSE ]
END CASE

El comando para procedimientos almacenados implementa un constructor condicional complejo. Si una 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 se ejecuta.

Nota: La sitaxis de un comando mostrado aquí para uso dentro de procedimientos almacenados difiere ligeramente de la expresión SQL descrita en Sección 12.2, “Funciones de control de flujo”. El comando no puede tener una cláusula y termina con en lugar de .

19.2.12.3. Sentencia

[:] LOOP
    
END 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 .

Un comando puede etiquetarse. no puede darse hasta que esté presente , y si ambos lo están, deben ser el mismo.

19.2.12.4. Sentencia

LEAVE 

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

19.2.12.5. La setencia

ITERATE 

sólo puede aparecer en comandos , , y . significa “vuelve a hacer el bucle.

Por ejemplo:

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

19.2.12.6. Sentencia

[:] REPEAT
    
UNTIL 
END REPEAT []

El comando/s dentro de un comando se repite hasta que la condición es cierta.

Un comando puede etiquetarse. no puede darse a no ser que esté presente, y si lo están, deben ser el mismo.

Por ejemplo:

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> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

19.2.12.7. Sentencia

[:] WHILE  DO
    
END WHILE []

El comado/s dentro de un comando se repite mientras la condición es cierta.

Un comando puede etiquetarse. no puede darse a no ser que 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 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END