Procedimientos Almacenados MySQL 5 - Capítulo IV: Sintaxis del procedimiento almacenado.

Capítulo IV: Sintaxis del procedimiento almacenado.


4.1.-Estándar SQL2003
4.2.-Permisos de ejecución y creación de procedimientos
4.3.-Instrucciones propias de procedimiento almacenado





Capítulo IV: Sintaxis del procedimiento almacenado.

En este capítulo se abordará todo lo relacionado a la sintaxis que se utiliza al momento de escribir los procedimientos almacenados, afortunadamente en la actualidad y desde hace tiempo todos lo DBMS se rigieron por los diferentes estándares de algunas organizaciones, claro ejemplo es el del estándar SQL que da convencionalidad a cada manejador de base de datos que puede jactarse de contar con él.

También en esta sección se comentarán las instrucciones SQL más significativas en los procedimientos almacenados y se abordarán algunos ejemplos prácticos de ellos, con la finalidad de que el lector se familiarice con ellos.

4.1.- Estándar SQL:2003.

Como es sabido un estándar es en cierto modo una guía que sirve como modelo con la finalidad de dar convencionalidad a los diferentes servicios, máquinas, tratados y en este caso instrucciones que propone. La gran ventaja que se rescata del apego a estándares es que si se habla de puertos de equipos de cómputo podemos decir que la manera en que trabaja el USB se ha vuelto un estándar y que se tiene la seguridad de que toda máquina que se compre que cuente con puerto USB funcionará de la misma manera, por que ya es un estándar. Esto pasa también con lo DBMS, como se ha venido mencionando, lo procedimientos almacenados son instrucciones SQL y como SQL está estandarizado todos los manejadores de datos utilizan prácticamente las mismas instrucciones, salvo pequeñas excepciones.

El estándar SQL:2003 es un conjunto de modelos a seguir en la sintaxis de las instrucciones SQL más significativas, es lógico pensar que cada DBMS tiene sobretodo por su funcionamiento excepciones a las instrucciones propuestas por el modelo, de la misma manera no son aisladas las situaciones en las cuales el DBMS tiene una buena cantidad mas de instrucciones que no se encuentran en el modelo.

Para llegar a la publicación de este estándar es importante mencionar que se vio enriquecido por las aportaciones de los representantes de las empresas más importantes en el ámbito de las bases de datos, dentro de los participantes más destacados en este modelo se encuentran personas de ORACLE, IBM, Microsoft etc. El SQL:2003 vino a sustituir el antiguo estándar SQL:1999 esto a raíz sobretodo de las instrucciones relacionadas con la implementación de procedimientos almacenados, disparadores y funciones definidas por el usuario.

4.2.- permisos de ejecución y creación de procedimientos.

Para comenzar a trabajar con la sintaxis de los procedimientos almacenados, es imperante tener claro que es necesario otorgar los permisos necesarios a los usuarios para la creación de los procedimientos CREATE ROUTINE, de modificación ALTER ROUTINE y de ejecución EXECUTE.
4.3.-Instrucciones propias del procedimiento almacenado.

Es claro que las instrucciones SQL que existían hasta antes de la implementación de lo procedimientos almacenados no fueron suficientes para satisfacer las necesidades que requerían estos nuevos elementos y por ello se tuvo que implementar un “lenguaje” propio del procedimiento almacenado donde sus principales característica consisten en la incorporación de algunas instrucciones de control y en algunos casos prácticamente un lenguaje de programación con el cual se puede manipular la información almacenada en la base de datos mediante consultas SQL desde el servidor.

En este capítulo se listan un grupo importante de instrucciones de control, ya que como se mencionó en las limitantes del documento no se pretende abordar a fondo las sentencias SQL.

4.3.1.- Delimitadores.

En el DBMS el delimitador es aquel símbolo el cual le indica tanto al DBMS o en su caso a la consola el envío de la instrucción que le antecede a él. En la mayoría de los casos el delimitador por default es el símbolo “;”. Este tipo de delimitador trae una consecuencia poco grata en los procedimientos almacenados, esto por que el procedimiento se compone regularmente de una serie de instrucciones SQL en donde se está acostumbrado a finalizar cada una de ellas con este símbolo, esto traería en consecuencia un error de sintaxis ya que faltaría la finalización del procedimiento almacenado.

Es por ello que surge la instrucción “delimiter” que le indica tanto a la consola como al DBMS que el nuevo delimitador será el o los símbolos que le precede a este comando, en el ejemplo siguiente se usan los símbolos “” , con ello se consigue que se puedan escribir una serie de instrucciones y que se ejecuten como si fuera una sola.

01:mySQL> delimiter
02:
03:mySQL> CREATE PROCEDURE simpleproc (IN nombre string[])
04: -> BEGIN
05: -> insert into doctores(nombre) values('Huidobro');
06: -> END
07: ->
08: mySQL> delimiter ;

En el ejemplo se puede ver que en la primera línea se establece como delimitador el símbolo “”, ya que si no se hiciera de esta manera solamente se ejecutaría como una instrucción unitaria hasta la línea 5, es a consecuencia de establecer este delimitador que el conjunto de líneas que se ejecutarán como una sola será desde la 1 hasta la 7, y finalmente en la línea 8 se vuelve a establecer como delimitador el símbolo utilizado comúnmente “;”.

4.3.2.-Create procedure.

Como se pudo deducir observando el ejemplo anterior la instrucción create procedure sirve para la creación de un procedimiento almacenado, en el siguiente ejemplo se puede observar la estructura general de un procedimiento almacenado.

CREATE PROCEDURE sp_name ([parametros[,...]])
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| { DEFINER | INVOKER }
| COMENTARIO 'string'

Donde:

Parámetros:

• IN* : indica que son parámetros de entrada
• OUT: parámetros de salida.
• INOUT parámetros de entrada y de salida

Características:

Características 1:
• DETERMINISTIC: Se utiliza cuando los mismos parámetros de entrada tienen como resultado los mismos datos de salida.

• NO DETERMINISTIC*:Es utilizada en los casos en los que los parámetros de entrada pueden tener diferentes parámetros de salida.


Características 2:

• CONTAINS SQL*: El cuerpo del procedimiento cuenta con instrucciones SQL que pueden modificar datos y leerlos.

• NO SQL:El cuerpo del procedimiento no cuenta con instrucciones SQL

• READS SQL DATA: Las instrucciones SQL que conforman el procedimiento solamente leen datos

• MODIFIES SQL DATA:Las instrucciones que contiene el procedimiento solamente modifican los datos.

Características 3: SQL SECURITY

• DEFINER*:Esta opción indica que el procedimiento almacenado se ejecutará con los privilegios del usuario quien lo definió, no quien lo está invocando.

• INVOKER: Al contrario de la opción anterior esta le hace saber al DBMS que el procedimiento se ejecutará con los permisos de quien está invocando el procedimiento.


* Estos valores son los que el procedimiento almacenado tomará en el caso de que el usuario no le haga saber que requiere otros.

comment: es un espacio reservado en el procedimiento para la posible colocación de algún comentario, estos comentarios son visibles mediante el comando SHOW CREATE PROCEDURE

Cuerpodelarutina: En esta parte del procedimiento es donde se almacena las diferentes instrucciones de control y SQL que forman el cuerpo del procedimiento.

4.3.3.- Alter procedure.

ALTER {PROCEDURE | FUNCTION} sp_name [caracteristicas..]

caracteristicas:
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

Esta instrucción es útil en el momento de realizar alguna modificación en el procedimiento, especialmente si la modificación se realiza desde la consola, aunque en que la mayoría de las aplicaciones administrativas del DBMS primero eliminan el procedimiento con la instrucción drop y posteriomente lo vuelven a crear, es lógico y acertado pensar que para la utilización de esta rutina es necesario tener el privilegio de alter rutine, como se observa en el ejemplo los parámetros iniciales son los mismos que en la instrucción create procedure.




4.3.4.- Drop procedure.

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

No es difícil deducir cual es la utilidad de esta instrucción, esta sentencia tiene como finalidad el eliminar el procedimiento almacenado el cual se le pasa como parámetro.

4.3.5.- Sentencia compuesta begin y end.

BEGIN
END

La utilización de estas dos instrucciones en conjunto dan lugar al cuerpo del procedimiento, donde se almacenan la o las diferentes instrucciones que componen a esta rutina, es semejante al los símbolos{} en el tan conocido lenguaje C o C++.

4.3.6.- Sentencia declare.

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

Esta sentencia se utiliza para la declaración de variables dentro del procedimiento almacenado, así como handlers y cursores, esta instrucción puede usarse dentro de las etiquetas begin y end las cuales engloban el cuerpo de instrucciones del procedimiento, también es posible asignar un tipo de variable y un valor default.

4.3.7.-Sentencia “select ... into”.

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

Esta sentencia es especialmente útil ya que inserta en las variables que se le indican los resultados del select, es importante tener en cuenta que no almacena una estructura bidimensional, si no solamente una o varias variables, es por ello que es necesario que las consultas solamente tengan un solo registro como el ejemplo siguiente.

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

4.3.8.-Sentencia “open” del cursor.

OPEN cursor_name

Este comando abre un cursor declarado previamente.

4.3.9.-Sentencia de cursor fetch.

FETCH cursor_name INTO var_name [, var_name] ...

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

Ya asignado previamente un estructura de información a un cursor esta instrucción le asigna una variable a cada columna del recordset, con esto hace que avance un registro cada vez que es llamada esta sentencia.

4.3.10.- Sentencia de cursor close.

CLOSE cursor_name

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

4.3.11.- Sentencia “if”.

IF condicion THEN
Sentencias1
ELSE
Sentencias2

END IF

IF implementa un constructor condicional básico. Si condicion se evalúa a cierto, el comando SQL correspondiente sentencias1 se ejecuta. Si no coincide ninguna condición se ejecuta el comando listado en la cláusula ELSE. Sentencias2 puede consistir en varios comandos.

4.3.12.- Sentencia “case”.

CASE condicion THEN
ELSE
END CASE

El comando CASE para procedimientos almacenados implementa un constructor condicional complejo. Si una condición 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.

4.3.13.-Sentencia “while”.

Desde luego que los DBMS soportan varios tipo de bucles y con ellos muchos tipos de control sobre ellos, en este documento solamente se abordará el estudio de uno de ellos y ese es el bucle while y se omitirá todas las sentencias de control de bucles.

WHILE condición DO
END WHILE

Los comandos dentro del WHILE se repiten mientras la condición condition es cierta.

Por ejemplo:

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO
END WHILE;
END

4.3.14.-. declare “handlers”.

Este tipo de instrucciones son especialmente útiles en la captación de errores dentro de un procedimiento almacenado, es decir, se utiliza principalmente cuando en el interior de un conjunto de rutinas ocurre un error y a la ejecución de ese evento de error se ejecuta la o las instrucciones handler.

DECLARE handler_tipo HANDLER FOR valor_error [,...] sentencias

handler_tipo: Este parámetro le indica al handler que acción debe de realizar con el proceso de ejecución del procedimiento almacenado, las posibles acciones que puede realizar se listan a continuación.

• CONTINUE: Que el procedimiento se siga ejecutando aún cuando se haya presentado un advertencia o error.
• EXIT: Que al tiempo de que se presente el error dentro del procedimiento almacenado este de por terminado la rutina.
• UNDO: Esta instrucción actúa de manera similar a una transacción, en el caso de que se presente algún error en la ejecución de las rutinas se reviertan los posibles cambios que se realizaron en la base de datos a consecuencia de la ejecución del procedimiento almacenado.

valor_error: En esta instrucción hay algunos valores posibles que colocar en ella.

• SQLSTATE [VALUE]: En el caso de que se esté esperando un error del cual se tiene conocimiento del código de este, se pondría el valor y solamente se captaría la ejecución del error con el código fijado en el handler
• CONDITION_NAME :Esta opción engloba todos los posibles errores que conincidan son los siguientres tres apartados

• SQLWARNING: Si cierta sentencia SQL realiza alguna advertencia al tiempo de realizar alguna ejecución, tiene como característica que acapara todos los códigos de las advertencias, al igual que las dos opciones de abajo.
• NOT FOUND: Sentencia no encontrada.
• SQLEXCEPTION: Una excepción en el DBMS.

Sentencias: En este espacio se colocan las posibles acciones que desee que el handler realice durante su ejecución.

4.3.15.-Declaración de cursores.

DECLARE cursor_name CURSOR FOR select_statement

Como se observa no es difícil declarar un cursor, es importante mencionar que se pueden declarar varios cursores dentro del bengin end , pero ninguno debe tener el mismo nombre.
Y de la misma manera es necesario tener presente que las sentencias SQL deben ser de selección.