Programación en PL/SQL: Estructuras, Procedimientos y Funciones

Introducción a PL/SQL

PL/SQL es un lenguaje basado en ADA, procedimental y orientado a objetos. Cuenta con características propias de los lenguajes de tercera generación: variables, estructura modular, estructuras de control, control de excepciones y un completo soporte para la Programación Orientada a Objetos.

Bloques PL/SQL

Es la estructura básica característica de todos los programas PL/SQL. Consta de 3 partes:

• Zona de declaraciones: donde declaramos variables y constantes.
• Conjunto de instrucciones: empieza por BEGIN, es el cuerpo del programa.
• Zona de excepciones: empieza por EXCEPTION, es opcional.

Estructuras de Control

Alternativas

Alternativa simple

IF <condición> THEN    instrucciones;END IF;

Alternativa doble

IF <condición> THEN    instrucciones;ELSE    instrucciones;END IF;

Alternativa múltiple (ELSIF)

IF <condición1> THEN    instrucciones;ELSIF <condición2> THEN    instrucciones;ELSIF <condición3> THEN    instrucciones;...ELSE    instrucciones;END IF;

Repetitivas

Mientras

WHILE <condición> LOOP    Instrucciones;END LOOP;

Para

FOR <variable> IN <mínimo>..<máximo> LOOP    instrucciones;END LOOP;

Iterar... fin iterar salir si...

LOOP    instrucciones;    EXIT WHEN <condición>;    instrucciones;END LOOP;

Gestión de Excepciones

Se utilizan para tratar errores o mensajes de aviso predefiniéndolos en la zona EXCEPTION mediante el manejador WHEN. Existen excepciones predefinidas en Oracle:

• NO_DATA_FOUND: Una orden de tipo SELECT INTO no ha devuelto ningún valor.
• TOO_MANY_ROWS: Una orden SELECT INTO ha devuelto más de una fila.

Procedimientos y Funciones

Procedimiento

PROCEDURE <nombre_procedimiento> [(<parámetros>)]IS    [<declaraciones>];BEGIN    <instrucciones>;    [EXCEPTION        <gestión de excepciones>;]END [<nombre_procedimiento>];

Función

FUNCTION <nombre_función> [(<parámetros>)]RETURN <tipo_de_retorno> IS    [<declaraciones>];BEGIN    <instrucciones>;    RETURN <valor>;    [EXCEPTION        <gestión de excepciones>;]END [<nombre_función>];

Tipos de Datos

Escalares

• Carácter/cadena: CHAR, NCHAR, VARCHAR2, NVARCHAR2, LONG, RAW, LONG RAW, ROWID, UROWID.
• Numérico: NUMBER, BINARY_INTEGER, PLS_INTEGER, BINARY_DOUBLE, BINARY_FLOAT.
• Booleano: BOOLEAN.
• Fecha/hora: DATE, TIMESTAMP, INTERVAL.
• Otros: ROWID, UROWID.

Compuestos

Son tipos compuestos por otros simples: tablas indexadas, tablas anidadas, VARRAYS, objetos.

Referencias

Se distinguen por su manejo y almacenamiento: REF CURSOR, REF.

LOB

Almacenan objetos grandes.

Atributos %TYPE y %ROWTYPE

Se utilizan para declarar variables del mismo tipo de otras ya declaradas.

• %TYPE: declara una variable del mismo tipo que otra o que una columna.

nombre_variable nombre_objeto%TYPE;

• %ROWTYPE: declara una variable de registro cuyos campos serán columnas de una tabla o vista de la base de datos.

nombre_variable nombre_objeto%ROWTYPE;

FOR <variable> IN .. LOOP    instrucciones;END LOOP;

En este caso, comenzará por el valor especificado en segundo lugar e irá restando una unidad en cada iteración:

SQL> BEGIN  2  FOR i IN REVERSE 1..3 LOOP  3  DBMS_OUTPUT.PUT_LINE(i);  4  END LOOP;  5  END;

Ejemplo de inversión de cadena:

SQL> DECLARE  2  r_cadena VARCHAR2(10);  3  BEGIN  4  FOR i IN REVERSE 1..LENGTH('HOLA') LOOP  5  r_cadena := r_cadena||SUBSTR('HOLA',i,1);  6  END LOOP;  7  DBMS_OUTPUT.PUT_LINE(r_cadena);  8* END;SQL> /ALOHProcedimiento PL/SQL terminado con éxito.

Ejemplos de Procedimientos

Cambiar Oficio de Empleado

Crearemos un procedimiento que reciba un número de empleado y una cadena correspondiente a su nuevo oficio. El procedimiento deberá localizar el empleado, modificar el oficio y visualizar los cambios realizados.

CREATE OR REPLACE PROCEDURE cambiar_oficio (    num_empleado NUMBER, -- En los parámetros ..    nuevo_oficio VARCHAR2) -- ..no se especifica tamañoAS    v_anterior_oficio emple.oficio%TYPE;BEGIN    SELECT oficio INTO v_anterior_oficio FROM emple    WHERE emp_no = num_empleado;    UPDATE emple SET oficio = nuevo_oficio    WHERE emp_no = num_empleado;    DBMS_OUTPUT.PUT_LINE(num_empleado||'*Oficio Anterior:'||v_anterior_oficio||    '*Oficio Nuevo :'||nuevo_oficio );END cambiar_oficio;

Visualizar Precio de Producto

En el siguiente procedimiento se visualiza el precio de un producto cuyo número se pasa como parámetro.

SQL> CREATE OR REPLACE  2  PROCEDURE ver_precio(v_num_producto NUMBER)  3  AS  4  v_precio NUMBER;  5  BEGIN  6  SELECT precio_actual INTO v_precio  7  FROM productos  8  WHERE producto_no = v_num_producto;  9  DBMS_OUTPUT.PUT_LINE('Precio = '||v_precio);  10 END;  11 /Procedimiento creado.SQL> EXECUTE VER_PRECIO(50);Precio = 1050Procedimiento PL/SQL terminado con éxito.

Modificar Precio de Producto

Escribiremos un procedimiento que modifique el precio de un producto pasándole el número del producto y el nuevo precio. El procedimiento comprobará que la variación de precio no supere el 20 por 100:

SQL> CREATE OR REPLACE  2  PROCEDURE modificar_precio_producto  3  (numproducto NUMBER, nuevoprecio NUMBER)  4  AS  5  v_precioant NUMBER(5);  6  BEGIN  7  SELECT precio_actual INTO v_precioant  8  FROM productos  9  WHERE producto_no = numproducto;

Consultar Datos de Departamento

Introduciendo estas líneas desde el indicador de SQL*Plus dispondremos de un procedimiento PL/SQL sencillo para consultar los datos de un cliente:

SQL> CREATE OR REPLACE  2  PROCEDURE ver_depart (numdepart NUMBER)  3  AS  4  v_dnombre VARCHAR2(14);  5  v_localidad VARCHAR2(14);  6  BEGIN  7  SELECT dnombre, loc INTO v_dnombre, v_localidad  8  FROM depart  9  WHERE dept_no = numdepart;  10 DBMS_OUTPUT.PUT_LINE('Num depart:'||numdepart|| ' * Nombre dep:'|| v_dnombre ||  11 ' * Localidad:'||v_localidad);  12 EXCEPTION  13 WHEN NO_DATA_FOUND THEN  14 DBMS_OUTPUT.PUT_LINE('No encontrado departamento ');  15 END ver_depart;

Consultar Nombre de Cliente

El siguiente programa solicitará la introducción de un número de cliente y visualizará el nombre del cliente correspondiente con el número introducido. Para introducir el número de cliente recurriremos a las variables de sustitución de SQL*Plus.

SQL> DECLARE  2  v_nom CLIENTES.NOMBRE%TYPE; --(ejemplo uso %TYPE)  3  BEGIN  4  SELECT nombre INTO v_nom  5  FROM clientes  6  WHERE CLIENTE_NO=&vn_cli;  7  DBMS_OUTPUT.PUT_LINE(v_nom);  8  END;

Consultar Precio de Producto

El siguiente programa muestra el precio de un producto especificado:

SQL> DECLARE  2  v_precio NUMBER;  3  BEGIN  4  SELECT precio_actual INTO v_precio  5  FROM productos  6  WHERE PRODUCTO_NO = 70;  7  DBMS_OUTPUT.PUT_LINE(v_precio);  8  END;