Preparación para el examen 730 Fundamentos DB2 9, Parte 4: Trabajando con datos DB2

Procedimientos SQL y funciones definidas por usuario

Creando y llamando un procedimiento SQL

Un procedimiento SQL es un procedimiento almacenado cuyo cuerpo está escrito en SQL. El cuerpo contiene la lógica del procedimiento SQL. Este puede incluir declaraciones de variable, manejo de condiciones, enunciados de flujo de control y DML. Se pueden especificar múltiples enunciados SQL dentro de un enunciado compuesto, el cual agrupa varios enunciados en un bloque ejecutable.

Un procedimiento SQL es creado cuando usted invoca exitosamente un enunciado CREATE PROCEDURE (SQL) , que define el procedimiento SQL con un servidor de aplicaciones. los procedimientos SQL son una forma fácil de definir consultas más complejas o tareas que puedan llamarse cuando se necesiten.

Una forma fácil para crear un procedimiento SQL es codificar el enunciado CREATE PROCEDURE (SQL) en un script de procesador de línea de comandos (CLP). Por ejemplo, si el enunciado mostrado abajo estuviera en el archivo llamado createSQLproc.db2, ese archivo podría ejecutarse para crear el procedimiento SQL:

1. Conéctese a la base de datos SAMPLE.
2. Emita el siguiente comando:

   db2 -td@ -vf createSQLproc.db2

   
   

Este comando db2 especifica la banderilla de opción -td , la cual le dice al procesador de línea de comandos que defina y use @ como el caracter de terminación de enunciado (porque el punto y coma ya está siendo utilizado como caracter de terminación de enunciado dentro del cuerpo de procedimiento); la banderilla de opción -v , que le dice al procesador de línea de comandos que haga eco texto de comando a resultado estándar; y la banderilla de opción -f , que le dice al procesador de línea de comandos que lea un ingreso de comando desde el archivo especificado, en lugar de la entrada estándar.

CREATE PROCEDURE sales_status
(IN quota INTEGER, OUT sql_state CHAR(5))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE SQLSTATE CHAR(5);
  DECLARE rs CURSOR WITH RETURN FOR
  SELECT sales_person, SUM(sales) AS total_sales
    FROM sales
    GROUP BY sales_person 
    HAVING SUM(sales) > quota;
  OPEN rs;
  SET sql_state = SQLSTATE;
END @

Este procedimiento, llamado SALES_STATUS, acepta un parámetro de entrada llamado quota y retorna un parámetro de salida llamado sql_state. El cuerpo del procedimiento consiste en de un solo enunciado SELECT que retorna el nombre y las cifras de las ventas totales de cada vendedor cuyas ventas totales excedan la cuota especificada.

La mayoría de los procedimientos SQL aceptan por lo menos un parámetro de entrada. En nuestro ejemplo, el parámetro de entrada contiene un valor (quota) que se usa en el enunciado SELECT contenido en el cuerpo de procedimiento.

Muchos procedimientos SQL retornan por lo menos un parámetro de salida. Nuestro ejemplo incluye un parámetro de resultado (sql_state) que se utiliza para informar el éxito o la falla del procedimiento SQL. El DB2 retorna un valor SQLSTATE en respuesta a las condiciones que podrían ser resultado de un enunciado SQL. Como el valore retornado SQLCODE o SQLSTATE pertenece al último enunciado SQL emitido en el cuerpo de procedimiento, y acceder a los valores altera los valores subsiguientes de estas variables (porque se utiliza una enunciado SQL para acceder a ellos), el valor SQLCODE o SQLSTATE debe ser asignado a, y retornado a través de, una variable definida localmente (como la variable sql_state de nuestro ejemplo).

La lista de parámetros para un procedimiento SQL puede especificar cero o más parámetros, cada uno de los cuales puede tener uno de tres tipos posibles:

• Los parámetros IN pasan un valor de entrada hacia un procedimiento SQL, este valor no puede ser modificado dentro del cuerpo del procedimiento.
• Los parámetros OUT retornan un valor de resultado a partir de un procedimiento SQL.
• Los parámetros INOUT pasan un valor de entrada a un procedimiento SQL y retornan un valor de salida desde el procedimiento.

Los procedimientos SQL pueden retornar cero o más conjuntos de resultados. En nuestro ejemplo, el procedimiento SALES_STATUS retorna un conjunto de resultados. Esto se ha llevado a cabo:

1. Declarando el número de conjuntos de resultados que ese procedimiento SQL retorna en la cláusula DYNAMIC RESULT SETS .
2. Declarando un cursor en el cuerpo del procedimiento (usando la cláusula WITH RETURN FOR ) para cada conjunto de resultados que se retorna. Un cursor es una estructura de control con nombre, que es usada por un programa de aplicaciones para apuntar a una fila específica dentro de un conjunto ordenado de filas. Los cursores se utilizan para recuperar filas de un conjunto.
3. Abrir el cursor para cada conjunto de resultados que se retorna.
4. Dejar el cursor(es) abierto(s) cuando retorne el procedimiento SQL.

Las variables se deben declarar al comienzo del cuerpo del procedimiento SQL. Para declarar una variable, asígnele un identificador único y especifique un tipo de datos SQL para la variable y, opcionalmente, asigne un valor inicial a la variable.

La cláusula SET en nuestro procedimiento SQL de muestra es un ejemplo de cláusulaflow-of-control . Los siguientes enunciados, estructuras y cláusulas flow-of-control pueden usarse para procesamiento condicional dentro de un cuerpo de procedimiento SQL:

• La estructura CASE selecciona una ruta de ejecución con base en la evaluación de una o más condiciones.
• La estructura FOR ejecuta un bloque de código para cada fila de una tabla.
• El enunciado GET DIAGNOSTICS retorna información sobre el enunciado SQL anterior en una variable SQL.
• El enunciado GOTO transfiere control hacia un bloque etiquetado (una sección de uno o más enunciados identificados por un nombre SQL único seguido por dos puntos).
• La estructura IF selecciona una ruta de ejecución con base en la evaluación de condiciones. Las cláusulas ELSEIF y ELSE le permiten ramificar o especificar una acción predeterminada si otras condiciones son falsas.
• La cláusula ITERATE pasa el flujo de control al comienzo del bucle etiquetado.
• La cláusula LEAVE transfiere control de programa hacia afuera del bucle o bloque de código.
• La cláusula LOOP ejecuta un bloque de código múltiples veces hasta que un enunciado LEAVE, ITERATE o GOTO transfiere el control hacia afuera del bucle.
• La cláusula REPEAT ejecuta un bloque de código hasta que de una condición de búsqueda específica retorne verdadero.
• La cláusula RETURN retorna control desde el procedimiento SQL hacia quien hace el llamado.
• La cláusula SET asigna un valor a un parámetro de salida o variable SQL.
• La cláusula WHILE ejecuta repetidamente un bloque de código mientras una condición especificada retorne verdadero.

Para crear exitosamente procedimientos SQL, usted debe tener instalado el DB2 Application Development Client en el servidor de base de datos. (Vea el primer tutorial de esta serie para más información sobre el Application Development Client.) La dependencia en un compilador C para crear procedimientos SQL fue eliminada en el DB2 Universal Database Version 8. Todas las operaciones que dependían de un compilador C son ahora efectuadas por código de byte generado por el DB2, que es hospedado en una máquina virtual. Para más información sobre esta mejora, vea Recursos.

Use el enunciado SQL CALL para llamar procedimientos SQL desde la línea de comandos DB2. El procedimiento que se esté llamando debe estar definido en el catálogo de sistema. Las aplicaciones de cliente escritas en cualquier lenguaje soportado pueden llamar procedimientos SQL. Para llamar al procedimiento SQL SALES_STATUS, siga estos pasos:

1. Conéctese a la base de datos SAMPLE.
2. Emita el siguiente enunciado:

   db2 CALL sales_status (25, ?)

   
   

Como los paréntesis tienen un significado especial para el shell de comandos en sistemas basados en UNIX, en esos sistemas estos deben estar precedidos por un caracter de barra invertida (\) o estar encerrados por comillas dobles:

db2 "CALL sales_status (25, ?)"

No incluya comillas dobles si está usando el procesador de línea de comandos (CLP) en modo de ingreso interactivo, caracterizado por la ventana de ingreso db2 => .

En este ejemplo, un valor de 25 para el parámetro de entrada quota es pasado al procedimiento SQL, así como un posicionador de signo de interrogación (?) para el parámetro de salida sql_state. El procedimiento retorna el nombre y las ventas totales de cada vendedor cuyas ventas totales excedan la cuota especificada (25). El siguiente es un resultado de ejemplo retornado por este enunciado:

SQL_STATE: 00000
SALES_PERSON            TOTAL_SALES
GOUNOT                       50
LEE                          91
 "SALES_STATUS" RETURN_STATUS: "0"

Volver arriba

Creando y usando funciones SQL definidas por usuario

Cree funciones definidas por usuario para extender el conjunto de funciones DB2 integradas. Por ejemplo, cree funciones que evalúen expresiones matemáticas complejas o que manipulen secuencias de caracteres, y luego referencie estas funciones en enunciados SQL como lo haría con cualquier otra función incorporada existente.

Suponga que usted necesita una función que retorne el área de un círculo cuando el radio de ese círculo sea especificado como argumento de la función. Tal función no está disponible como una función incorporada en DB2, pero usted puede crear una función escalar SQL definida por usuario para efectuar esta tarea y referenciar la función donde se soporten funciones escalares dentro de un enunciado SQL.

CREATE FUNCTION ca (r DOUBLE)
  RETURNS DOUBLE
  LANGUAGE SQL
  CONTAINS SQL
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN 3.14159 * (r * r);

La cláusula NO EXTERNAL ACTION especifica que la función no necesita realizar ninguna acción que cambie el estado de un objeto que no sea administrado por el gestor de base de datos. La palabra claveDETERMINISTIC especifica que la función siempre retorna el mismo resultado para un valor de argumento dado. Esta información es usada durante optimización de secuencia. Una manera conveniente de ejecutar la función es referenciarla en una consulta. En el siguiente ejemplo, la consulta es ejecutada (arbitrariamente) contra la vista de catálogo SYSIBM.SYSDUMMY1 , que solo tiene una fila:

db2 SELECT ca(96.8) AS area FROM sysibm.sysdummy1

AREA
------------------------
  +2.94374522816000E+004

  1 record(s) selected.

Usted también puede crear una función de tabla definida por usuario, la cual toma cero o más argumentos de entrada y retorna datos como una tabla. Una función de tabla solo puede usarse en la cláusula FROM como un enunciado SQL.

Suponga que usted necesita una función que retorne los nombres y números de empleado de todos los empleados que tengan un trabajo específico, con el título de ese trabajo especificado como un argumento de la función. El siguiente es un ejemplo de una función de tabla que efectúa la tarea:

CREATE FUNCTION jobemployees (job VARCHAR(8))
  RETURNS TABLE (
   empno CHAR(6),
   firstname VARCHAR(12),
   lastname VARCHAR(15)
  )
  LANGUAGE SQL
  READS SQL DATA
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN
   SELECT empno, firstnme, lastname
     FROM employee
     WHERE employee.job = jobemployees.job;

La siguiente consulta referencia la nueva función de tabla de la cláusula FROM y pasa el cargo 'CLERK' como el argumento para la función. Un nombre de correlación, introducido por la palabra clave AS, es requerido por la sintaxis:

db2 SELECT * FROM TABLE(jobemployees('CLERK')) AS clerk

EMPNO  FIRSTNAME    LASTNAME
------ ------------ ---------------
000120 SEAN         O'CONNELL
000230 JAMES        JEFFERSON
000240 SALVATORE    MARINO
000250 DANIEL       SMITH
000260 SYBIL        JOHNSON
000270 MARIA        PEREZ

  6 record(s) selected.

5 de 8 | Anterior | Siguiente

Volver arriba

Tabla de contenidos

• Antes de comenzar
• Structured Query Language (SQL)
• Data Manipulation Language (DML)
• Los enunciados COMMIT y ROLLBACK y los límites de transacción
• Procedimientos SQL y funciones definidas por usuario
• Resumen
• Recursos
• Sobre el autor

---

5 de 8 | Anterior | Siguiente