Oracle básico (IV): Programación en PL/SQL

El lenguaje de programación de Oracle, llamado PL/SQL, es un lenguaje portable, procedural y de transacción muy potente y de fácil manejo, con las siguientes características fundamentales:

1.    Incluye todos los comandos de SQL estudiados en el artículo Oracle Básico I y II (ver revista Algoritmo números 8 y 9, respectivamente):

       -    SELECT

       -    INSERT

       -    UPDATE

       -    DELETE.

2.    Es una extensión de SQL, ya que este es un lenguaje no completo dado que no incluye las herramientas clásicas de programación. Por eso, PL/SQL amplia sus posibilidades al incorporar las siguientes sentencias:

       -    Control condicional

                IF ... THEN ... ELSE ... ENDIF  

       -    Ciclos

                FOR ... LOOP
       WHILE ... LOOP 

-      3.    Incorpora opciones avanzadas en:

-       Control y tratamiento de errores llamado excepciones.

-      Manejo de cursores.

-      Variedad de  procedimientos y  funciones empaquetadas incorporadas  en el módulo SQL*Forms para la programación de disparadores (Trigger) y procedimientos del usuario (Procedure).

Estructura del bloque de código

Veamos a continuación la organización del bloque de código de PL/SQL, compuesto por cuatro secciones DECLARE, BEGIN, EXCEPTION y END como se detalla en el fuente 1:

/* --- Fuente 1 -----------------------------------------------------------

[<< nombre del bloque >>]
         Etiqueta que identifica al Bloque. 

[DECLARE]
         Declaración de
                   Variable
                   Constante                         Se inicializa con un valor que no se puede modificar.                   Cursor                              Area de trabajo que contiene los datos de la fila de la tabla en uso. El cursor es el resultado de una sentencia SELECT.         ExcepciónVariables                    para control de errores.
BEGIN
         Código.
[EXCEPTION]
         Control y tratamiento de errores.         Es el punto al que se transfiere el control del programa siempre que exista un problema. Los indicadores de excepción pueden ser definidos por el usuario o por el sistema, como es por ejemplo la excepción ZERO_DIVIDE. Las excepciones se activan automáticamente al ocurrir un error, existiendo la definición de la excepción OTHERS que considera aquellos errores no definidos y que siempre se ubica al final de todas las excepciones.
END [nombre del bloque];
         Fin del Bloque. 

Con el ejemplo del fuente 2 ilustraremos las distintas secciones que componen un bloque de código en PL/SQL. En este caso deseamos calcular la venta promedio del día y, en caso que la misma sea menor a lo esperado, se debe registrar en la tabla VENTABAJA.

/* --- Fuente 2 -----------------------------------------------------------

DECLARE
         esperada          CONSTANT NUMBER(5) := 500;
         xtotal            NUMBER;
         xcant             NUMBER;
         xprom             NUMBER;
BEGIN
         /*Asigna a la variable xtotal el TOTAL de las ventas
         y a la variable xcant la cantidad de ventas del día.
         */
         SELECT SUM(valor),COUNT(valor) INTO xtotal,xcant
                   FROM ventas WHERE fecha=sysdate;
         xprom:=xtotal/xcant;
         IF xprom >= esperada THEN
                   message('Ventas por encima de la esperada');
                   pause;
         ELSE
                   /*Se registra en la tabla ventabaja las ventas por debajo
                   del promedio esperado */
                   INSERT INTO ventabaja VALUES (sysdate,xprom);
         END IF;
EXCEPTION
         WHEN ZERO_DIVIDE THEN
                   message('No se realizaron ventas en el día');
         pause;
         WHEN OTHERS THEN
                   message('Error Indefinido');
         pause;
END; 

Asignación de valores

Las dos formas que existen para asignar valores a variables de memoria, vistas en el ejemplo anterior, son:

-      Con el operador de asignación :=, como cuando calculamos el promedio de las ventas asignándole valor a la variable xprom con la siguiente sentencia:

          xprom:=xtotal/xcant; 

-      Con la sentencia SELECT que contiene la orden INTO, como se muestra, es la asignación de valores a las variables xtotal y xcant con el siguiente código:

          SELECT SUM(valor),
                   COUNT(valor)
                   INTO xtotal,xcant
                   FROM ventas
                   WHERE fecha=sysdate; 

Veamos a continuación, con la creación del procedimiento FECHAALTA, la asignación de valores a una variable de registro llamada Client_Rec, que va a contener la estructura de una fila de la tabla CLIENTES y que estará formada por todos los campos correspondientes a la tabla. Para esto usaremos el atributo de variable %ROWTYPE que declara una variable de registro que contiene la estructura de la tabla, y después, con el uso de la sentencia SELECT * INTO, se asigna a la variable de registro los valores de la fila. La referencia a un dato contenido en la variable de registro se hace de la forma variable_registro.campo, como por ejemplo cliente_rec.fecha hace referencia a la fecha del alta del cliente.

Pasemos a mostrar lo anteriormente expuesto a través del código del fuente 3.

/* --- Fuente 3 -----------------------------------------------------------

PROCEDURE FECHAALTA IS
         BEGIN
                DECLARE
                         cliente_rec  clientes%ROWTYPE;
                BEGIN
                         SELECT * INTO cliente_rec
                                   FROM clientes
                                   WHERE codigo = 5;
                         IF cliente_rec.fecha>sysdate-10
                         THEN
                                   message(        cliente_rec.nombre||
                                                         ' Dado de alta en los últimos 10 días');
                                   pause;
                         ELSE
                                   message(        cliente_rec.nombre||
                                                         ' Dado de alta hace más de 10 días');
                                   pause;
                         END IF;
         END;
END; 

SELECT con control de excepciones

La sentencia SELECT en PL/SQL no muestra en pantalla las filas resultantes de la consulta, como ocurre en SQL (el cual trabaja en forma interactiva) sino que, según sea la acción a realizar, así será la cantidad de filas devueltas por la consulta, existiendo en este caso una de las tres posibles situaciones recogidas en la tabla 1: