PL/SQL

1. ¿Qué es el PL/SQL?

PL/SQL es el lenguaje procedimental de Oracle. Es una extensión de SQL, el cual es, por su parte, un lenguaje estándar basado en la teoría de conjuntos.

El interés del lenguaje PL/SQL reside en poder mezclar la potencia de las instrucciones SQL con la flexibilidad de un lenguaje procedimental en un mismo programa.

Estos programas pueden ser ejecutados directamente por las herramientas de Oracle (bloques anónimos) o a partir de objetos de la base de datos (procedimientos almacenados y paquetes).

Las ventajas del lenguaje PL/SQL son diversas:

1. Variables locales

Declaración

PL/SQL dispone de un conjunto de tipos que pueden utilizarse en la definición de las columnas de las tablas con el fin de facilitar los intercambios de datos entre las tablas y los bloques de código. Sin embargo, el ámbito de los posibles valores para cada uno de estos tipos puede ser diferente del ámbito en SQL.

También dispone de un determinado número de tipos propios, principalmente para gestionar los datos numéricos.

Por último, PL/SQL permite definir tipos complejos basados en estructuras basadas en las tablas o en descripciones proporcionadas por el usuario.

Sintaxis

nombre-de-variable [CONSTANT]  
type [[NOT NULL]:=expresión];
 

CONSTANT

NOT NULL

expresión

2. Tipos predefinidos

1. Procesamientos condicionales

If

Permite ejecutar instrucciones en función del resultado de una condición.

Sintaxis

IF condición1 THEN procesamiento1;  
   [ELSIF condición2 THEN procesamiento2;]  
[ELSE  
   procesamiento3;]  
[END IF;]
 

Los operadores utilizados en las condiciones son los mismos que en SQL: =; >, <, !, >=, <=, IS NULL, IS NOT NULL, BETWEEN, LIKE, AND, OR, etc.

Ejemplo

Si se trata del cliente número 10, se actualiza; en caso contrario, la transacción se anula:

if  Vnumcli = 10 THEN  
    UPDATE CLIENTES SET NOMCLI = 'Torres' where NUMCLI = Vnumcli;  
    COMMIT;  
else  
     ROLLBACK;  
end if;
 

CASE

La instrucción CASE permite realizar una ejecución condicional, al igual que la instrucción IF. Sin embargo, la instrucción CASE se adapta especialmente bien a las condiciones que implican numerosas opciones diferentes. Proporciona una presentación que hace que el código se lea mejor y, por tanto, disminuye el riesgo de cometer errores. Además, el uso de CASE puede mejorar el rendimiento durante la ejecución.

Sintaxis

[<<etiqueta>>]  
CASE elemento_de_selección  
  WHEN valor1 THEN instrucciones1;  
  WHEN valor2 THEN instrucciones2;  
 ... 
  [ELSE instrucciones;]  
END CASE [etiqueta];  
 
[<<etiqueta>>]  
CASE  
  WHEN condición1 THEN instrucciones1;  
  WHEN condición2 THEN instrucciones2;  
 ... 
  [ELSE instrucciones;]  
END CASE [etiqueta];
 

1. Definición

El cursor es una zona de memoria de tamaño fijo, utilizada por el motor de la base de datos de Oracle para analizar e interpretar cualquier comando SQL.

Los estados de ejecución del comando se almacenan en el cursor.

Existen dos tipos de cursores:

2. Etapas en el uso de un cursor explícito

CURSOR nombre_cursor IS instrucción_select;
 

OPEN nombre_cursor;
 

FETCH nombre_cursor INTO lista_variables;
 

CLOSE nombre_cursor;
 

FOR variable IN cursor LOOP   
--instrucciones  
END LOOP;
 

1. Especificación del programa

Se desea actualizar la cantidad que hay en almacén de la tabla ARTICULOS (REFART, DESCRIPCION, PRECIOSINIVA, CANTALM) a partir de los pedidos en curso (ESTADOPED = ’EC’) almacenados en las tablas PEDIDOS (NUMPED, NUMCLI, FECHAPED, ESTADOPED) y LINEASPED (NUMPED, NUMLIN, REFART, CANTPED).

El proceso debe actualizar la columna CANTALM de la tabla ARTICULOS, restando la cantidad pedida (CANTPED de la tabla LINEASPED) para el artículo. También debe actualizar el estado del pedido (ESTADOPED) a ’SU’ (suministrado) si todas las cantidades especificadas en el pedido pueden ser suministradas (CANTALM > 0 después de realizar la operación de sustracción).

En el caso de que la cantidad en almacén del artículo pase a ser negativa, las actualizaciones para dicho pedido se anulan.

Para cada artículo para el que existan problemas (que no pueda ser suministrado) y para cada pedido que se entregue totalmente se actualiza una tabla testigo.

2. Ejemplo

Script actsum.sql:

rem Suministro de pedidos y actualización de almacenes  
rem Creación de la tabla TESTIGO  
create table TESTIGO(numped number(6), texto char(60));  
 
rem Bloque PL/SQL de actualización  
DECLARE  
  cursor cped is select p.numped, refart, cantped  
    from linped l,pedidos p  
    where p.numped=l.numped  
      and p.estadoped='EC'  
    order by p.numped;  
  vped cped%rowtype;  
  vcantalm articulos.cantalm%type;  
  vnvcant vcantalm%type;  
  vtexto testigo.texto%type;  
  verr boolean;  ...