¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Oracle 12c
  3. PL/SQL
Extrait - Oracle 12c SQL, PL/SQL, SQL*Plus
Extractos del libro
Oracle 12c SQL, PL/SQL, SQL*Plus Volver a la página de compra del libro

PL/SQL

Introducción

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:

  • Integración de SQL: se pueden utilizar las instrucciones DML, las de control de transacciones y las funciones SQL prácticamente con la misma sintaxis.

  • Procesamiento procedimental: la gestión de variables y las estructuras de control (condiciones y bucles) incrementan las posibilidades de gestión de los datos.

  • Funcionalidades suplementarias: la gestión de cursores y el tratamiento de errores ofrecen nuevas posibilidades de procesamiento.

  • Mejora del rendimiento: se pueden agrupar varias instrucciones en una misma unidad (bloque) que dará lugar a un único "acceso" a la base de datos (en lugar de un acceso por cada instrucción).

  • Integración en los productos de Oracle: los bloques o procedimientos PL/SQL son compilados y ejecutados por el "motor" de PL/SQL. Este motor está...

Gestión de variables

Las variables son zonas de memoria nominadas que permiten almacenar un valor.

En PL/SQL, permiten almacenar valores procedentes de la base de datos o de cálculos, que se emplearán para comprobar condiciones, realizar cálculos o asignar valores a otras variables o datos de la base de datos.

Las variables se caracterizan por:

  • su nombre, compuesto por letras, números y los caracteres $, _ o #.

    El nombre puede tener hasta un máximo de 30 caracteres y no debe ser una palabra reservada.

  • su tipo, que determina el formato de almacenamiento y de uso de la variable.

Es obligatorio declarar las variables antes de poder usarlas.

Al igual que el lenguaje SQL, PL/SQL no distingue entre mayúsculas y minúsculas. Los nombres de las variables pueden especificarse indistintamente en mayúsculas o minúsculas.

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

El valor de la variable no puede modificarse en el código de la sección BEGIN.

NOT NULL

Impide que se asigne el valor NULL a la variable, debiendo proporcionarse una expresión.

expresión

Valor inicial asignado a la variable durante la ejecución del bloque.

images/04FIG01.PNG

2. Tipos predefinidos

a. Tipos de caracteres

CHAR[(n)]

Cadena de caracteres de longitud fija, con n comprendido entre 1 y 32767. Si no se especifica un tamaño máximo, entonces se usa el valor predeterminado de 1. También hay que tener en cuenta que la longitud máxima de una columna de tipo CHAR es 2000 y, por tanto, es imposible insertar un valor de más de 2000 caracteres en una columna de dicho tipo.

VARCHAR2[(n)]

Cadena de caracteres de longitud variable, con n comprendido entre 1 y 32767 y que representa...

Estructuras de control

Las estructuras de control permiten elegir la forma en que se van a ejecutar las diferentes instrucciones.

Las tres posibles estructuras de control son:

  • secuencial: las instrucciones se ejecutan una a continuación de otra.

images/04fig03.png
  • alternativa: las instrucciones se ejecutan en función de una condición.

images/04fig04.png
  • repetitiva: las instrucciones se ejecutan varias veces en función de una condición.

images/04fig05.png

1. Procesamientos condicionales

images/fleche.PNGIf

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;
 
images/fleche.PNGCASE

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];
 

La condición ELSE es opcional y solo se ejecuta si ninguna...

Uso de cursores

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:

  • Cursor implícito: cursor SQL generado y gestionado por Oracle para cada sentencia SQL.

  • Cursor explícito: cursor SQL generado y gestionado por el usuario para tratar una instrucción SELECT de varias líneas.

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

a. Declaración

Cualquier cursor explícito utilizado en un bloque PL/SQL debe declararse en la sección DECLARE del bloque, especificando:

  • su nombre,

  • la instrucción SELECT asociada.

Sintaxis

CURSOR nombre_cursor IS instrucción_select;
 

b. Apertura

Después de haber declarado el cursor, es preciso "abrirlo" para ejecutar la instrucción SELECT.

La apertura desencadena las siguientes acciones:

  • la asignación de memoria del cursor,

  • la identificación del resultado,

  • la activación de los posibles bloqueos (si SELECT ... FOR UPDATE).

La apertura del cursor se realiza en la sección BEGIN del bloque.

Sintaxis

OPEN nombre_cursor;
 

c. Tratamiento de las filas

Después de ejecutar la instrucción SELECT, las filas devueltas se tratan una por una; el valor de cada columna especificada en la instrucción SELECT debe almacenarse en una variable.

Sintaxis

FETCH nombre_cursor INTO lista_variables;
 

La instrucción FETCH extrae una única fila cada vez; para tratar n filas es necesario utilizar un bucle.

d. Cierre

Después de tratar las filas, se cierra el cursor para liberar el espacio en memoria.

Sintaxis

CLOSE nombre_cursor;
 

e. Cursor for

En los casos en los que el uso principal de un cursor es recorrer un conjunto de filas extraídas por la ejecución de una instrucción SELECT asociada puede resultar interesante utilizar una sintaxis más simple para abrir el cursor y recorrer el bucle.

Oracle proporciona una variante para el bucle FOR que declara implícitamente la variable de recorrido, abre el cursor, ejecuta las extracciones (FETCH) sucesivas y cierra el cursor.

Sintaxis

FOR variable IN cursor LOOP   
--instrucciones  
END LOOP;
 

Ejemplo

Definición y recorrido de un CURSOR sobre la tabla CLIENTES. Presentación del último...

Gestión de errores

Durante la ejecución pueden producirse muchos errores. Dichos errores pueden ser consecuencia del hardware, de fallos de programación o pueden tener cualquier otro origen. En determinados lenguajes de programación no es posible gestionar cualquier clase de error, como por ejemplo la división por cero.

El lenguaje PL/SQL proporciona un mecanismo de detección de errores que permite dar una respuesta desde el software a cualquier tipo de error. Por supuesto, no todos los errores podrán ser tratados, pero es posible prever una salida correcta del programa cuando se producen.

El tratamiento de errores tiene lugar en la sección EXCEPTION del bloque PL/SQL. Esta sección es opcional y solo debe definirse cuando el bloque deba capturar los errores.

Estructura de un bloque PL/SQL:

images/04fig06.png

La sección EXCEPTION permite definir un tratamiento apropiado para los posibles errores que puedan producirse durante la ejecución del bloque PL/SQL.

Se distingue entre dos tipos de errores:

  • errores internos de Oracle,

  • anomalías debidas al programa.

Después de ejecutar el código correspondiente al tratamiento de la excepción, el bloque en curso de ejecución se termina y la siguiente instrucción que se ejecuta es la que sigue a la llamada a este bloque PL/SQL en el bloque maestro.

Las ventajas de este modo de tratamiento de errores son muchas. La más importante de ellas es que, para gestionar un determinado tipo de error, solo tiene que escribirse el código una vez. En los lenguajes de programación en los que no existe este mecanismo de detección de errores, la llamada a la función que trata el error debe especificarse cada vez que el error pueda producirse.

Esquema que resume la principal ventaja de un mecanismo de captura de errores:

images/04fig07.png

Reglas

  • Definir y proporcionar un nombre a cada error (diferente para los errores de usuario y los errores de Oracle).

  • Asociar una entrada en la sección EXCEPTION para cada nombre de error definido en la sección DECLARE.

  • Definir el tratamiento que hay que realizar en la sección EXCEPTION.

La activación de excepciones no permite proseguir normalmente el tratamiento de las operaciones.

Sin embargo, al basarse en la definición de subbloques dentro de los que se administran las excepciones, es posible ejecutar una serie de instrucciones incluso si se activa...

Ejemplo resumen

El siguiente ejemplo muestra la sintaxis utilizada para la gestión de variables, cursores y estructuras de control en un programa completo.

Este programa se ejecuta en SQL*Plus.

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;  ...