5.2.1. Inserción de datos¶

El comando INSERT de SQL permite introducir tatos en una tabla o en una vista de la base de datos. La sintaxis del comando es la siguiente:

INSERT INTO {nombre_tabla | nombre_vista } [(columna1 [, columna2]...)]
VALUES (valor1 [, valor2] ... );

Indicando la tabla se añaden los datos que se especifiquen tras el apartado VALUES en un nuevo registro. Los valores deben corresponderse con el orden de las columnas. Si no es así se puede indicar tras el nombre de la tabla y entre paréntesis.

Ejemplos:

Supongamos que tenemos el siguiente diseño físico de una tabla:

CREATE TABLE EMPLEADOS (
  COD       NUMBER(2)    PRIMARY KEY,
  NOMBRE    VARCHAR2(50) NOT NULL,
  LOCALIDAD VARCHAR2(50) DEFAULT 'Écija',
  FECHANAC  DATE
);

La forma más habitual de introducir datos es la siguiente:

INSERT INTO EMPLEADOS VALUES (1, 'Pepe', 'Osuna', '01/01/1970');
INSERT INTO EMPLEADOS VALUES (2, 'Juan', DEFAULT, NULL);
INSERT INTO EMPLEADOS VALUES (3, 'Sara', NULL, NULL);

Es obligatorio introducir valores para los campos COD y NOMBRE. Dichos campos no pueden tener valor NULL. Podemos insertar sólo el valor de ciertos campos. En este caso hay que indicar los campos a insertar y el orden en el que los introducimos:

INSERT INTO EMPLEADOS(NOMBRE, COD) VALUES ('Ana', 5);

Inserción de datos obtenidos de una consulta

También es posible insertar datos en una tabla que hayan sido obtenidos de una consulta realizada a otra tabla/vista u otras tablas/vistas. Su forma es:

INSERT INTO tabla
SELECT ...

Debe respetarse lo dicho anteriormente respecto a los campos. La consulta SELECT debe devolver la misma cantidad y tipo de campos que los definidos en la tabla.

Por ejemplo, suponiendo que disponemos de una tabla SOLICITANTES con el siguiente diseño:

CREATE TABLE SOLICITANTES (
  NUM        NUMBER(2) PRIMARY KEY,
  NOMBRE     VARCHAR2(50),
  CIUDAD     VARCHAR2(50),
  NACIMIENTO DATE,
  ESTUDIOS   VARCHAR2(50)
);

INSERT INTO EMPLEADOS
SELECT NUM, NOMBRE, CIUDAD, NACIMIENTO
FROM SOLICITANTES
WHERE ESTUDIOS='CFGS ASIR';

También podemos indicar los campos a insertar, teniendo en cuenta que, en este caso los campos COD y NOMBRE de la tabla EMPLEADO no aceptan valores NULL, por tanto es obligatorio introducir valores para ellos:

INSERT INTO EMPLEADOS(FECHANAC, NOMBRE, COD)
SELECT NACIMIENTO, NOMBRE, NUM
FROM SOLICITANTES
WHERE ESTUDIOS='CFGS ASIR';

5.2.2. Modificación de datos¶

Para la modificación de registros dentro de una tabla o vista se utiliza el comando UPDATE. La sintaxis del comando es la siguiente:

UPDATE {nombre_tabla | nombre_vista}
SET columna1=valor1 [, columna2=valor2] ...
[WHERE condición];

Se modifican las columnas indicadas en el apartado SET con los valores indicados. La cláusula WHERE permite especificar qué registros serán modificados.

Ejemplos:

-- Ponemos todos los nombres a mayúsculas
-- y todas las localidades a Estepa
UPDATE EMPLEADOS
SET NOMBRE=UPPER(NOMBRE), LOCALIDAD='Estepa';


-- Para los empleados que nacieron a partir de 1970
-- ponemos nombres con inicial mayúscula y localidades Marchena
UPDATE EMPLEADOS
SET NOMBRE=INITCAP(NOMBRE), LOCALIDAD='Marchena'
WHERE FECHANAC >= '01/01/1970';

Actualización de datos usando una subconsulta

También se admiten subconsultas. Por ejemplo:

UPDATE empleados
SET sueldo=sueldo*1.10
WHERE id_seccion = (SELECT id_seccion FROM secciones
                    WHERE nom_seccion='Producción');

Esta instrucción aumenta un 10% el sueldo de los empleados que están dados de alta en la sección llamada Producción.

5.2.3. Eliminación de datos¶

Es más sencilla que el resto, elimina los registros de la tabla que cumplan la condición indicada. Se realiza mediante la instrucción DELETE:

DELETE [ FROM ] {nombre_tabla|nombre_vista}
[WHERE condición] ;

Ejemplos:

-- Borramos empleados de Estepa
DELETE EMPLEADOS
WHERE LOCALIDAD='Estepa';

-- Borramos empleados cuya fecha de nacimiento sea anterior a 1970
-- y localidad sea Osuna
DELETE EMPLEADOS
WHERE FECHANAC < '01/01/1970' AND LOCALIDAD = 'Osuna';

-- Borramos TODOS los empleados;
DELETE EMPLEADOS;

Hay que tener en cuenta que el borrado de un registro no puede provocar fallos de integridad y que la opción de integridad ON DELETE CASCADE (clave secundaria o foránea) hace que no sólo se borren los registros indicados sino todos los relacionados. En la práctica esto significa que no se pueden borrar registros cuya clave primaria sea referenciada por alguna clave foránea en otra tabla, a no ser que dicha tabla secundaria tenga activada la clausula ON DELETE CASCADE en su clave foránea, en cuyo caso se borraría el/los registro/s de la tabla principal y los registros de tabla secundaria cuya clave foránea coincide con la clave primaria eliminada en la tabla primera.

Eliminación de datos usando una subconsulta

Al igual que en el caso de las instrucciones INSERT o SELECT, DELETE dispone de cláusula WHERE y en dicha cláusula podemos utilizar subconsultas. Por ejemplo:

DELETE empleados
WHERE id_empleado IN (SELECT id_empleado FROM operarios);

En este caso se trata de una subconsulta creada con el operador IN, se eliminarán los empleados cuyo identificador esté dentro de la tabla operarios.

5.2.4. Ejecución de comandos DML sobre vistas.¶

Las instrucciones DML ejecutadas sobre las vistas permiten añadir o modificar los datos de las tablas relacionados con las filas de la vista. Ahora bien, no es posible ejecutar instrucciones DML sobre vistas que:

• Utilicen funciones de grupo (SUM, AVG,…)
• Usen GROUP BY o DISTINCT
• Posean columnas con cálculos (P. ej: PRECIO * 1.16)

Además no se pueden añadir datos a una vista si en las tablas referencias en la consulta SELECT hay campos NOT NULL que no aparecen en la consulta (es lógico ya que al añadir el dato se tendría que añadir el registro colocando el valor NULL en el campo).

Si tenemos la siguiente vista:

CREATE VIEW resumen (id_localidad, localidad, poblacion,
                     n_provincia, provincia, superficie,
                     id_comunidad, comunidad)
AS SELECT L.IdLocalidad, L.Nombre, L.Poblacion,
           P.IdProvincia, P.Nombre, P.Superficie,
           C.IdComunidad, C.Nombre
   FROM LOCALIDADES L JOIN PROVINCIAS P ON L.IdProvincia=P.IdProvincia
                      JOIN COMUNIDADES C ON P.IdComunidad=C.IdComunidad;

Si realizamos la siguiente inserción

INSERT INTO resumen (id_localidad, localidad, poblacion)
VALUES (10000, 'Sevilla', 750000);

Se producirá un error, puesto que estamos insertando un registro dentro de la vista donde muchos de sus campos no tienen especificado un valor y por tanto serán insertados a NULL. El problema es que no puede insertarse un NULL en n_provincia ni id_comunidad puesto que son claves primarias de las tablas subyacentes PROVINCIAS y COMUNIDADES. La solución al problema anterior se soluciona creando un disparador (trigger) de sustitución, que veremos en el apartado de triggers.

5.3.1. COMMIT¶

La instrucción COMMIT hace que los cambios realizados por la transacción sean definitivos, irrevocables. Se dice que tenemos una transacción confirmada. Sólo se debe utilizar si estamos de acuerdo con los cambios, conviene asegurarse mucho antes de realizar el COMMIT ya que las instrucciones ejecutadas pueden afectar a miles de registros. Además el cierre correcto de la sesión da lugar a un COMMIT, aunque siempre conviene ejecutar explícitamente esta instrucción a fin de asegurarnos de lo que hacemos.

5.3.2. ROLLBACK¶

Esta instrucción regresa a la instrucción anterior al inicio de la transacción, normalmente el último COMMIT, la última instrucción DDL o DCL o al inicio de sesión. Anula definitivamente los cambios, por lo que conviene también asegurarse de esta operación. Un abandono de sesión incorrecto o un problema de comunicación o de caída del sistema dan lugar a un ROLLBACK implícito.

5.3.3. SAVEPOINT¶

Esta instrucción permite establecer un punto de ruptura. El problema de la combinación ROLLBACK/COMMIT es que un COMMIT acepta todo y un ROLLBACK anula todo. SAVEPOINT permite señalar un punto intermedio entre el inicio de la transacción y la situación actual. Su sintaxis es:

-- ... instrucciones DML ...

SAVEPOINT nombre

-- ... instrucciones DML ...

Para regresar a un punto de ruptura concreto se utiliza ROLLBACK TO SAVEPOINT seguido del nombre dado al punto de ruptura. También es posible hacer ROLLBACK TO nombre de punto de ruptura. Cuando se vuelve a un punto marcado, las instrucciones que siguieron a esa marca se anulan definitivamente.

Ejemplo de uso:

SET AUTOCOMMIT OFF;

CREATE TABLE T (FECHA DATE);

INSERT INTO T VALUES ('01/01/2017');
INSERT INTO T VALUES ('01/02/2017');

SAVEPOINT febrero;

INSERT INTO T VALUES ('01/03/2017');
INSERT INTO T VALUES ('01/04/2017');

SAVEPOINT abril;

INSERT INTO T VALUES ('01/05/2017');

ROLLBACK TO febrero;
-- También puede escribirse ROLLBACK TO SAVEPOINT febrero;
-- En este ejemplo sólo se guardan en la tabla
-- los 2 primeros registros o filas.

5.3.4. Estado de los datos durante la transacción¶

Si se inicia una transacción usando comandos DML hay que tener en cuenta que:

• Se puede volver a la instrucción anterior a la transacción cuando se desee.
• Las instrucciones de consulta SELECT realizadas por el usuario que inició la transacción muestran los datos ya modificados por las instrucciones DML.
• El resto de usuarios ven los datos tal cual estaban antes de la transacción, de hecho los registros afectados por la transacción aparecen bloqueados hasta que la transacción finalice. Esos usuarios no podrán modificar los valores de dichos registros.
• Tras la transacción todos los usuarios ven los datos tal cual quedan tras el fin de transacción. Los bloqueos son liberados y los puntos de ruptura borrados.

5.3.5. Concurrencia de varias transacciones (Bloqueos)¶

Cuando se realizan varias transacciones de forma simultánea, pueden darse diversas situaciones en el acceso concurrente a los datos, es decir, cuando se accede a un mismo dato en dos transacciones distintas. Estas situaciones son:

• Lectura sucia (Dirty Read). Una transacción lee datos que han sido escritos por otra transacción que aún no se ha confirmado.
• Lectura no repetible (Non-repeateable Read). Una transacción vuelve a leer los datos que ha leído anteriormente y descubre que otra transacción confirmada ha modificado o eliminado los datos.
• Lectura fantasma (Phantom Read). Una transacción vuelve a ejecutar una consulta que devuelve un conjunto de filas que satisface una condición de búsqueda y descubre que otra transacción confirmada ha insertado filas adicionales que satisfacen la condición.

Para una mejor gestión de estas situaciones debemos indicar el nivel de aislamiento que deseamos. De las cuatro propiedades de ACID de un SGBD, la propiedad de aislamiento es la más laxa. Un nivel de aislamiento bajo aumenta la capacidad de muchos usuarios para acceder a los mismos datos al mismo tiempo, pero también aumenta el número de efectos de concurrencia (como lecturas sucias). Un mayor nivel de aislamiento puede dar como resultado una pérdida de concurrencia y el aumento de las posibilidades de que una transacción bloquee a otra.

Podemos solicitar al SGBD cuatro niveles de aislamiento. De menor a mayor nivel de aislamiento, tenemos:

• READ UNCOMMITTED (Lectura no confirmada). Las sentencias SELECT son efectuadas sin realizar bloqueos, por tanto, todos los cambios hechos por una transacción pueden verlos las otras transacciones. Permite que sucedan las 3 situaciones indicadas previamente: lecturas fantasma, no repetibles y sucias.
• READ COMMITTED (Lectura confirmada). Los datos leídos por una transacción pueden ser modificados por otras transacciones. Se pueden dar lectuas fantasma y lecturas no repetibles.
• REPEATEABLE READ (Lectura repetible). Consiste en que ningún registro leído con un SELECT se puede cambiar en otra transacción. Solo pueden darse lecturas fantasma.
• SERIALIZABLE. Las transacciones ocurren de forma totalmente aislada a otras transacciones. Se bloquean las transacciones de tal manera que ocurren unas detrás de otras, sin capacidad de concurrencia. El SGBD las ejecuta concurrentemente si puede asegurar que no hay conflicto con el acceso a los datos.

Nivel de aislamiento y Lecturas

Internamente el SGBD proporciona dicho nivel de aislamiento mediante bloqueos en los datos.

En Oracle, el nivel por defecto es READ COMMITED. Además de éste, solo permite SERIALIZABLE. Se puede cambiar ejecutando el comando:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

5.4.1. Conceptos básicos¶

• bloque PL/SQL Se trata de un trozo de código que puede ser interpretado por Oracle. Se encuentra inmerso dentro de las palabras BEGIN y END.
• programa PL/SQL Conjunto de bloques que realizan una determinada labor.
• procedimiento Programa PL/SQL almacenado en la base de datos y que puede ser ejecutado si se desea con solo saber su nombre (y teniendo permiso para su acceso).
• función Programa PL/SQL que a partir de unos datos de entrada obtiene un resultado (datos de salida). Una función puede ser utilizada en cualquier expresión desde cualquier otro programa PL/SQL e incluso desde una instrucción SQL.
• paquete Colección de procedimientos y funciones agrupados dentro de la misma estructura. Similar a las bibliotecas y librerías de los lenguajes convencionales.
• trigger (disparador) Programa PL/SQL que se ejecuta automáticamente cuando ocurre un determinado suceso a un objeto de la base de datos.

5.4.2. Bloques¶

Cada programa en PL/SQL está formado por grupos de órdenes SQL llamadas bloques. Cada bloque puede contener, a su vez, nuevos bloques.

ESTRUCTURA DE UN BLOQUE PL/SQL

La estructura más sencilla es la siguiente:

BEGIN
  Sentencias
  ...
END;
/

Ejemplo:

BEGIN
  DBMS_OUTPUT.PUT_LINE ('Hola Mundo');
END;
/

La barra / se utiliza para ejecutar el código.

SET SERVEROUTPUT ON

La estructura general es:

[ DECLARE
   constantes,
   variables,
   cursores,
   excepciones definidas por el usuario
   ...
]
BEGIN
  Sentencias
[ EXCEPTION
   Acciones a realizar cuando se produce alguna excepción  ]
END;
/

Ejemplo:

DECLARE
  fecha DATE;
BEGIN
  SELECT sysdate INTO fecha FROM dual;
  dbms_output.put_line
  (
    to_CHAR(fecha,'day", "dd" de "month" de "yyyy", a las "hh24:mi:ss')
  );
END;
/

SELECT campos INTO variable ...

Tipos de Bloques

• Anónimos (anonymous blocks): Se construyen normalmente de manera dinámica para un objetivo muy concreto y se ejecutan, en general, una única vez. Por eso no llevan nombre.
• Nominados (named blocks): Son bloques a los que se les pone un nombre. También se conocen como subprogramas. Los subprogramas pueden ser procedimientos o funciones.
  • Procedimientos: Se construyen para efectuar algún tipo de operación más o menos frecuente y se almacenan para ejecutarlos cuantas veces se desee. Se ejecutan con una llamada al procedimiento.
  • Funciones: Son similares a los procedimientos. Al igual que estos realizan algún tipo de operación, pero además las funciones devuelven un valor que puede ser usado en cualquier sentencia PL/SQL e incluso en sentencias SQL.
• Paquetes: Se usan para agrupar procedimientos y funciones. Facilitan la descomposición modular y el mantenimiento.
• Disparadores (triggers): Son bloques nominados que se almacenan en la BD. Su ejecución está condicionada a cierta condición, como por ejemplo usar una orden concreta del DML.
• Comentarios: Pueden incluirse siempre que se desee.
  • Monolínea: Empiezan con 2 guiones – y terminan a final de línea.
  • Multilínea: Empiezan con /* y terminan con */ (como en C).

Ejemplo de Bloque Anónimo

DECLARE
  fecha  DATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Salida de información');
  SELECT SYSDATE INTO fecha FROM DUAL;
  DBMS_OUTPUT.PUT_LINE ('Fecha:  ' || fecha);
END;
/

Los otros tipos de bloques los veremos con mayor detenimiento más adelante.

5.4.3. Ejecución selectiva: Condicionales¶

Para ejecutar un serie de instrucciones según se cumpla o no una condición tenemos dos estructuras:

• IF
• CASE

Estas estructuras necesitan que indiquemos la condición o condiciones que deseamos evaluar. Dicha condición se evalúa en la mayoría de los lenguajes de programación como TRUE o FALSE. En SQL se usa una lógica trivaluada (TRUE, FALSE y NULL), donde NULL tiene el significado de ”desconocido o no definido”. Cualquier expresión relacional con un operando nulo, devuelve NULL. Tablas de Verdad:

IF  ExpresiónBooleana1 THEN
  SecuenciaÓrdenes1;
[ ELSIF ExpresiónBooleana2 THEN
  SecuenciaÓrdenes2; ]
...
[ ELSE
  SecuenciaÓrdenes; ]
END IF;

IF  A < B THEN
  C := 1;
ELSE
  C := 2;
END IF;

-- NO ES EQUIVALENTE A LO SIGUIENTE, si  las variables A o B
-- pueden tener valores NULL:

IF  A >= B THEN
  C := 2;
ELSE
  C := 1;
END IF;

-- LO MEJOR ES COMPROBAR PRIMERO si las variables A o B
-- tienen valores NULL

IF  A IS NULL OR B IS NULL THEN
  C := 3;
ELSIF A < B THEN
  C := 1;
ELSE
  C := 2;
END IF;

SET SERVEROUTPUT ON

DECLARE
   A NUMBER := NULL;
   B NUMBER := 2;
   C NUMBER;

BEGIN

  IF  A IS NULL OR B IS NULL THEN
    C := 3;
  ELSIF A < B THEN
    C := 1;
  ELSE
    C := 2;
  END IF;
  DBMS_OUTPUT.PUT_LINE ('El valor de C es ' || C);

END;
/

DECLARE
  nota NUMBER(2);

BEGIN
  nota := 7;

  IF  nota = 10 OR nota = 9 THEN
    DBMS_OUTPUT.PUT_LINE('Sobresaliente');
  ELSIF nota = 8 OR nota = 7 THEN
    DBMS_OUTPUT.PUT_LINE('Notable');
  ELSIF nota = 6 THEN
    DBMS_OUTPUT.PUT_LINE('Bien');
  ELSIF nota = 5 THEN
    DBMS_OUTPUT. PUT_LINE('Suficiente');
  ELSIF nota < 5 AND nota >=0 THEN
    DBMS_OUTPUT.PUT_LINE('Insuficiente');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Nota no válida');
  END IF;

END;
/

CASE Expresion
  WHEN valor1 THEN
    Secuencia_de_Órdenes1;
  [ WHEN valor2 THEN
    Secuencia_de_Órdenes2; ]
  ...
  [ ELSE
    Secuencia_de_Órdenes; ]
END CASE;

DECLARE
  nota NUMBER(2);
BEGIN
  nota := 7;
  CASE nota
    WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('Sobresaliente');
    WHEN 9  THEN DBMS_OUTPUT.PUT_LINE('Sobresaliente');
    WHEN 8  THEN DBMS_OUTPUT.PUT_LINE('Notable');
    WHEN 7  THEN DBMS_OUTPUT.PUT_LINE('Notable');
    WHEN 6  THEN DBMS_OUTPUT.PUT_LINE('Bien');
    WHEN 5  THEN DBMS_OUTPUT.PUT_LINE('Suficiente');
    WHEN 4  THEN DBMS_OUTPUT.PUT_LINE('Insuficiente');
    WHEN 3  THEN DBMS_OUTPUT.PUT_LINE('Insuficiente');
    WHEN 2  THEN DBMS_OUTPUT.PUT_LINE('Insuficiente');
    WHEN 1  THEN DBMS_OUTPUT.PUT_LINE('Insuficiente');
    WHEN 0  THEN DBMS_OUTPUT.PUT_LINE('Insuficiente');
    ELSE DBMS_OUTPUT.PUT_LINE('Nota no válida');
  END CASE;
END;
/

DECLARE
  nota NUMBER(2);
BEGIN
  nota := 7;
  CASE
    WHEN nota=10 OR nota=9  THEN DBMS_OUTPUT.PUT_LINE('Sobresaliente');
    WHEN nota=8  OR nota=7  THEN DBMS_OUTPUT.PUT_LINE('Notable');
    WHEN nota=6             THEN DBMS_OUTPUT.PUT_LINE('Bien');
    WHEN nota=5             THEN DBMS_OUTPUT.PUT_LINE('Suficiente');
    WHEN nota<5 AND nota>=0 THEN DBMS_OUTPUT.PUT_LINE('Insuficiente');
    ELSE DBMS_OUTPUT.PUT_LINE('Nota no válida');
  END CASE;
END;
/

5.4.4. Ejecución repetitiva: Bucles¶

Para realizar una operación un número elevado de veces utilizamos bucles. Un bucle es una estructura del lenguaje que nos permite indicar que determinado código se repetirá en su ejecución. Existen 3 formas de hacerlo que pasamos a ver a continuación:

• LOOP
• WHILE
• FOR

LOOP
  Sentencias;
END LOOP;

EXIT [WHEN condición];

DECLARE
  i  BINARY_INTEGER := 1;
BEGIN
  LOOP
    INSERT INTO Tabla_Temp VALUES (i*10);
    EXIT WHEN i>=10;
    i:= i+1;
  END LOOP;
END;
/

WHILE condición LOOP
  Sentencias;
END LOOP;

DECLARE
  i  BINARY_INTEGER := 1;
BEGIN
  WHILE i<=10 LOOP
    INSERT INTO Tabla_Temp VALUES (i*10);
    i:= i+1;
  END LOOP;
END;
/

FOR i IN [REVERSE] min..max LOOP
  Sentencias;
END LOOP;

BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO Tabla_Temp VALUES (i*10);
  END LOOP;
END;
/

5.4.5. Variables¶

Una variable es el nombre que se da a una zona de memoria donde se guardarán ciertos datos. PL/SQL soporta todos los tipos de SQL más algunos más que veremos a continuación. Formato: La variables PL/SQL se declaran con el siguiente formato:

Nombre Tipo  [[CONSTANT|NOT NULL] := Valor_Inicial ];

Conversiones de Tipo:

• Explícita: Usando funciones de conversión: TO_CHAR, TO_NUMBER…
• Implícita: PL/SQL realiza conversiones automáticas si es posible, incluso entre datos de distintas familias (numéricos, caracteres, fechas…). Esto es desaconsejado para evitar problemas.

Tipos de datos:

Puede ser cualquier tipo válido en una columna de tabla (vistos anteriormente) y otros tipos adicionales. El valor por defecto es NULL, excepto que se especifique un valor como Valor_Inicial (puede sustituirse := por DEFAULT). Con NOT NULL se requiere la inicialización.

• Tipos Escalares:
  • Numéricos Reales.
    • NUMBER(p,e) y sus subtipos totalmente equivalentes definidos por cuestiones de compatibilidad: DEC, DECIMAL, DOUBLE PRECISION, INT, INTEGER, NUMERIC, SMALLINT y REAL.Se almacenan en formato decimal: Para operaciones aritméticas deben traducirse a binario.
  • Numéricos Enteros.
    • BINARY_INTEGER, que es un entero en binario (complemento a 2) con rango ±2147483647, ideal para variables sobre las que se efectuarán operaciones (contadores…). Tiene definidos subtipos restringidos en su rango: NATURAL [0, 2147483647], NATURALN (igual que NATURAL pero NOT NULL), POSITIVE [1, 2147483647], POSITIVEN, SIGNTYPE (–1, 0 y 1).
    • PLS_INTEGER es similar a BINARY_INTEGER, pero más rápido en las operaciones aritméticas y que genera un error si se produce un desbordamiento (ORA-1426) al asignarlo a un NUMBER.
• Carácter:
  • VARCHAR2(max_tam), con max_tam<=32676 bytes (como columna de tabla admite 4000 → Cuidado con los errores). Si se usa un código distinto al código ASCII, el número total de caracteres puede ser menor.
  • CHAR (tam_fijo) con 1 por defecto y 32767 como máximo (como columna de tabla admite 255), se rellena siempre con blancos. LONG es una cadena de longitud variable con un máximo de 32760 (como columna de tabla admite 2GB).
  • NCHAR y NVARCHAR2 permiten almacenar cadenas en un conjunto de caracteres nacional distinto al propio de PL/SQL.
• Binarios:
  • RAW(max_tam), con max_tam<=32676 bytes.
  • LONG RAW admite un máximo de 32760 bytes.
  • ROWID es un tipo para almacenar identificadores de fila (pseudocolumna ROWID).
• Fecha:
  • DATE, como en SQL de Oracle almacena siglo, año, mes, día, hora, min. y segs. (7 bytes).
• Lógico:
  • BOOLEAN, con los siguientes valores posibles: TRUE, FALSE y NULL.
• Tipos Referencias (punteros):
  • REF CURSOR y REF TipoObjeto.
• Tipos LOB (Large OBject):
  • BFILE, LOB, CLOB y NLOB (se usan con el paquete DBMS_LOB) .
• Tipos Compuestos:
  • RECORD, TABLE y VARRAY. El tipo RECORD o REGISTRO lo veremos en detalle en el siguiente apartado.
• Notaciones especiales:
  • Tabla.Columna%TYPE: el tipo que tenga asignado una columna de una tabla, independientemente de cómo esté definida ésta.
  • Tabla%ROWTYPE: el tipo que tenga asignado una fila de una tabla, independientemente de cómo esté definida ésta.

Ejemplos:

CODIGO         HOTEL.ID%TYPE;
HABS        HOTEL.NHABS%TYPE;
DEP       DEPARTAMENTOS%ROWTYPE;
HOTEL             HOTEL%ROWTYPE;

Esto hace los programas más robustos frente a cambios de tipo. Ejemplo completo: Supongamos que disponemos de una tabla llamada Hotel con el siguiente diseño físico:

CREATE TABLE HOTEL (ID NUMBER(2) PRIMARY KEY, NHABS NUMBER(3) );

Si insertamos los siguientes valores:

INSERT INTO HOTEL VALUES (1, 10);
INSERT INTO HOTEL VALUES (2, 60);
INSERT INTO HOTEL VALUES (3, 200);
INSERT INTO HOTEL VALUES (99, NULL);

Y definimos el siguiente procedimiento:

CREATE OR REPLACE
PROCEDURE TAMHOTEL (cod Hotel.ID%TYPE)
AS
  NumHabitaciones  Hotel.Nhabs%TYPE;
  Comentario       VARCHAR2(40);
BEGIN
  -- Número de habitaciones del Hotel cuyo ID es cod
  SELECT Nhabs INTO NumHabitaciones
  FROM Hotel WHERE ID=cod;

  IF NumHabitaciones IS NULL THEN
    Comentario := 'de tamaño indeterminado';
  ELSIF NumHabitaciones < 50 THEN
    Comentario := 'Pequeño';
  ELSIF NumHabitaciones < 100 THEN
    Comentario := 'Mediano';
  ELSE
    Comentario := 'Grande';
  END IF;

  DBMS_OUTPUT.PUT_LINE ('El hotel con ID '|| cod ||' es '|| Comentario);
END;
/

Si ejecutamos:

BEGIN
   TAMHOTEL(1);
   TAMHOTEL(2);
   TAMHOTEL(3);
   TAMHOTEL(99);
END;
/

Los resultados serán:

El hotel con ID 1 es Pequeño
El hotel con ID 2 es Mediano
El hotel con ID 3 es Grande
El hotel con ID 99 es de tamaño indeterminado

5.4.6. Registros¶

Son agrupaciones de datos relacionados. Permite crear estructuras que albergan un conjunto de tipos de datos. Por ejemplo, podemos crear el registro PERSONA con los campos código, nombre y edad, cada uno de estos campos con diferentes tipos de datos. En PL/SQL su importancia proviene de su similitud a la fila (registro) de tabla. Es necesario definir un Tipo de Dato Registro, para declarar variables.

Formato:

TYPE Tipo_Registro IS RECORD
(
  Campo1 Tipo1 [[NOT NULL] :=Expr1],
  Campo2 Tipo2 [[NOT NULL] :=Expr2],
  . . .
  CampoN TipoN [[NOT NULL] :=ExprN]
);

Igual que en los lenguajes de programación, para acceder a un campo se usa la Notación Punto: VariableRegistro.Campo. Se permite asignar registros si son del mismo tipo. Si son de tipos distintos no se pueden asignar, aunque estén definidos igual. En ese caso, se pueden asignar campo a campo.

También se pueden asignar los campos de un SELECT en un registro compatible.

DECLARE
  TYPE RegPersona IS RECORD
  (
    CODIGO NUMBER(2),
    NOMBRE VARCHAR2(40),
    EDAD   NUMBER
  );
  Pepe RegPersona;
BEGIN
  Pepe.CODIGO := 1;
  Pepe.NOMBRE := 'Pepe';
  Pepe.EDAD   := 30;
  DBMS_OUTPUT.PUT_LINE ('Código: ' || Pepe.CODIGO);
  DBMS_OUTPUT.PUT_LINE ('Nombre: ' || Pepe.NOMBRE);
  DBMS_OUTPUT.PUT_LINE ('Edad  : ' || Pepe.EDAD);
  INSERT INTO PERSONAS VALUES Pepe;
END;
/

CREATE TABLE PERSONAS
(
   CODIGO  NUMBER(2),
   NOMBRE  VARCHAR2(40),
   EDAD    NUMBER
);

DECLARE

  TYPE RegHotel IS RECORD
  (
    COD   Hotel.ID%TYPE,
    HABS  Hotel.NHABS%TYPE
  );

  Hotel99 RegHotel;

BEGIN
  SELECT ID, NHABS INTO Hotel99 FROM HOTEL WHERE ID=99;
  DBMS_OUTPUT.PUT_LINE ('Cód. Hotel  : ' || Hotel99.COD);
  DBMS_OUTPUT.PUT_LINE ('Habitaciones: ' || Hotel99.HABS);
END;
/

DECLARE
  Hotel99 Hotel%ROWTYPE;
BEGIN
  SELECT * INTO Hotel99 FROM HOTEL WHERE ID=99;
  DBMS_OUTPUT.PUT_LINE ('Cód. Hotel  : ' || Hotel99.ID);
  DBMS_OUTPUT.PUT_LINE ('Habitaciones: ' || Hotel99.NHABS);
END;
/

5.4.7. Cursores¶

PL/SQL utiliza cursores para gestionar las instrucciones SELECT. Un cursor es un conjunto de registros devuelto por una instrucción SQL. Técnicamente los cursores son fragmentos de memoria reservados para procesar los resultados de una consulta SELECT.

Hay dos tipos de cursores: Implícitos y Explícitos.

Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de acuerdo a los mismos convenios que cualquier otra variable. Los cursores implícitos no necesitan declaración como tales. Los cursores explícitos debemos declararlos con la palabra CURSOR.

• IMPLÍCITOS: Este tipo de cursores se utiliza para operaciones SELECT INTO. Se usan cuando la consulta devuelve un único registro y no es necesario declararlos como tales.

Ejemplo:

DECLARE
  Hotel99 Hotel%ROWTYPE;
BEGIN
  SELECT * INTO Hotel99    -- Hotel99 es un cursor implícito
  FROM HOTEL WHERE ID=99;  -- Almacena un solo registro

  DBMS_OUTPUT.PUT_LINE ('Cód. Hotel  : ' || Hotel99.ID);
  DBMS_OUTPUT.PUT_LINE ('Habitaciones: ' || Hotel99.NHABS);
END;
/

• EXPLÍCITOS: Se utilizan cuando la consulta devuelve un conjunto de registros. Ocasionalmente también se utilizan en consultas que devuelven un único registro por razones de eficiencia. Son más rápidos.

Ejemplo:

DECLARE
  CURSOR Hoteles IS        -- Hoteles es un cursor explícito
   SELECT * FROM Hotel;    -- Almacena varios registros
BEGIN
  FOR registro IN Hoteles  -- No es necesario declarar registro
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Cód. Hotel  : ' || registro.ID);
    DBMS_OUTPUT.PUT_LINE ('Habitaciones: ' || registro.NHABS);
    DBMS_OUTPUT.PUT_LINE ('       ----      ');
  END LOOP;
END;
/

La forma complicada de hacerlo sería así:

DECLARE
  CURSOR Hoteles IS         -- Hoteles es un cursor explícito
   SELECT * FROM Hotel;     -- Almacena varios registros
  registro Hoteles%ROWTYPE; -- Es necesario declarar esta variable
BEGIN
  OPEN Hoteles;   -- Abrimos cursor
  LOOP
    FETCH Hoteles INTO registro;  -- Recuperamos un registro
    EXIT WHEN Hoteles%NOTFOUND;   -- Salimos si no hay más registros
    DBMS_OUTPUT.PUT_LINE ('Cód. Hotel  : ' || registro.ID);
    DBMS_OUTPUT.PUT_LINE ('Habitaciones: ' || registro.NHABS);
    DBMS_OUTPUT.PUT_LINE ('       ----      ');
  END LOOP;
  CLOSE Hoteles;  -- Cerramos cursor
END;
/

Ejemplo de cursor explícito con parámetros:

Un cursor puede aceptar parámetros. Por ejemplo cuando deseamos que los resultados de la consulta dependan de ciertas variables. Para hacer que el cursor varíe según esos parámetros, se han de indicar los mismos en la declaración del cursor. Para ello se pone entre paréntesis su nombre y tipo tras el nombre del cursor en la declaración. A continuación se muestra un cursor que admite un parámetro que luego será utilizado en la consulta para mostrar los hoteles cuyo ID sea menor que el valor de dicho parámetro.

DECLARE
  CURSOR Hoteles(num NUMBER) IS        -- Hoteles es un cursor explícito
    SELECT * FROM Hotel WHERE ID<num;  -- Almacena varios registros
BEGIN
  FOR registro IN Hoteles(10)  -- Hoteles con ID < 10
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Cód. Hotel  : ' || registro.ID);
    DBMS_OUTPUT.PUT_LINE ('Habitaciones: ' || registro.NHABS);
    DBMS_OUTPUT.PUT_LINE ('       ----      ');
  END LOOP;
END;
/

UPDATE empleados SET salar = salar * 1.05 WHERE numem = 120;
IF  SQL%NOTFOUND THEN
   INSERT INTO empleados VALUES (120, 'José', ...);
END IF;

DELETE empleados WHERE numhi > 0;
IF  SQL%ROWCOUNT > 5 THEN  -- más de 5 filas borradas
   RAISE borrado_masivo;
END IF;

DECLARE
   CURSOR c IS
   -- ordenamos por empleado con mayor salario
   SELECT nomem, numem, salar FROM empleados ORDER BY salar DESC;

   nombre  CHAR(10);
   numero  NUMBER(3);
   salario NUMBER(7,2);
BEGIN
   OPEN c;
   LOOP
      FETCH c INTO nombre, numero, salario;
      -- mostramos un máximo de 5 empleados
      EXIT WHEN (c%ROWCOUNT > 5) OR (c%NOTFOUND);
      DBMS_OUTPUT.PUT_LINE (c%ROWCOUNT
        || ' - ' || salario || ' - ' || numero || ' - ' || nombre);
   END LOOP;
   CLOSE c;
END;

5.4.8. Gestión de excepciones¶

Se llama excepción a todo hecho que le sucede a un programa que causa que la ejecución del mismo finalice. Lógicamente eso causa que el programa termine de forma anormal. Las excepciones se deben a:

Que ocurra un error detectado por Oracle (por ejemplo si un SELECT no devuelve datos ocurre el error ORA-01403 llamado NO_DATA_FOUND). Que el propio programador las lance (comando RAISE). Las excepciones se pueden capturar a fin de que el programa controle mejor la existencia de las mismas.

DECLARE
  sección de declaraciones
BEGIN
  instrucciones
  EXCEPTION
    WHEN excepción1 [OR excepción2 ...] THEN
      instrucciones que se ejcutan si suceden esas excepciones
    [WHEN excepción3 [OR...] THEN
      instrucciones que se ejcutan si suceden esas excepciones]
    [WHEN OTHERS THEN
      instrucciones que se ejecutan si suceden otras excepciones]
END;

DECLARE
  x NUMBER := 0;
  y NUMBER := 3;
  res NUMBER;
BEGIN
  res:=y/x;
  DBMS_OUTPUT.PUT_LINE(res);

  EXCEPTION
    WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('No se puede dividir por cero') ;

    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error inesperado') ;
END;
/

DECLARE
  Hotel99 Hotel%ROWTYPE;
BEGIN
 SELECT * INTO Hotel99 WHERE Nombre='Costanera';
 -- IF  SQL%NOTFOUND THEN ...     // Esto no tiene sentido aquí
 -- IF  SQL%ROWCOUNT > 1 THEN ... // Tampoco tiene sentido aquí

 EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- Cuando no se recupera ninguna fila
     INSERT INTO Hotel VALUES (99, 'Costanera', 110, 60, 'S', 3 );

   WHEN TOO_MANY_ROWS THEN  -- Cuando se recuperan varias filas
      DELETE Hotel WHERE Nombre='Costanera' AND HotelID<>99;
END;

EXCEPTION
  ...
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE
      ('Ocurrió el error ' ||  SQLCODE ||'mensaje: ' || SQLERRM);
END;

miExcepcion EXCEPTION;

RAISE miExcepcion;

EXCEPTION
  ...
  WHEN miExcepcion THEN
  ...

DECLARE
  error_al_eliminar EXCEPTION;
BEGIN
  DELETE piezas WHERE tipo='ZU' AND modelo=26;
  IF  SQL%NOTFOUND THEN
    RAISE error_al_eliminar;
  END IF;
  EXCEPTION
    WHEN error_al_eliminar THEN
      DBMS_OUTPUT.PUT_LINE ('Error -20001: No existe esa pieza');
END;
/

RAISE_APPLICATION_ERROR (noDeError, mensaje, [,{TRUE|FALSE}]);

DECLARE
BEGIN
  DELETE piezas WHERE tipo='ZU' AND modelo=26;
  IF  SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20001,'No existe esa pieza');
  END IF;
END;
/

5.4.9. Procedimientos¶

Un procedimiento es un bloque que puede recibir parámetros, lo cual permite trabajar con unos datos de entrada, realizar las operaciones deseadas con dichos datos y, en algunos casos guardar ciertos resultados como parámetros de salida. Se usa la palabra reservada PROCEDURE. Su estructura simplificada es:

PROCEDURE nombre IS
bloque sin palabra DECLARE

Su estructura en detalle es:

PROCEDURE nombre
[(parámetro1 [modo] tipoDatos[,parámetro2 [modo] tipoDatos [,...])]
{IS|AS}
bloque sin palabra DECLARE

Los procedimientos permiten utilizar parámetros para realizar su tarea. El modo, que es opcional, puede ser de 3 tipos: IN, OUT o IN OUT. Si no se indica nada, por defecto es IN.

• Parámetros IN. Son los parámetros que en otros lenguajes se denominan como parámetros por valor. El procedimiento recibe una copia del valor o variable que se utiliza como parámetro al llamar al procedimiento. Estos parámetros pueden ser: valores literales (18 por ejemplo), variables (v_num por ejemplo) o expresiones (como v_num+18). A estos parámetros se les puede asignar un valor por defecto.
• Parámetros OUT. Relacionados con el paso por variable de otros lenguajes. Sólo pueden ser variables y no pueden tener un valor por defecto. Se utilizan para que el procedimiento almacene en ellas algún valor. Es decir, los parámetros OUT son variables sin declarar que se envían al procedimiento de modo que si en el procedimiento cambian su valor, ese valor permanece en ellas cuando el procedimiento termina.
• Parámetros IN OUT. Son una mezcla de los dos anteriores. Se trata de variables declaradas anteriormente cuyo valor puede ser utilizado por el procedimiento que, además, puede almacenar un valor en ellas. No se las puede asignar un valor por defecto.

Para crear el procedimiento debemos anteponer la sentencia

CREATE [ OR REPLACE ]

La opción REPLACE hace que si ya existe un procedimiento con ese nombre, se reemplaza con el que se crea ahora. Los parámetros son la lista de variables que necesita el procedimiento para realizar su tarea. Para invocar al procedimiento o procedimientos definidos debemos hacerlo dentro de un bloque BEGIN … END; o también con la sentencia EXEC si lo ejecutamos desde SQL*Plus.

BEGIN
  procedimiento1;
  procedimiento2();
  procedimiento3(parametro1, parametro2);
  ...
END;
/

o también en SQL*Plus:

EXEC procedimiento1;
EXEC procedimiento2();
EXEC procedimiento3(parametro1, parametro2);

Cuando se invoca a un procedimiento, si éste no tiene parámetros, se pueden omitir los paréntesis (es decir la llamada al procedimiento procedimiento2() se puede hacer simplemente escribiendo procedimiento2, sin paréntesis)

Para eliminar un procedimiento utilizamos la sentencia DROP PROCEDURE.

DROP PROCEDURE procedimiento;

Ejemplo:

CREATE OR REPLACE
PROCEDURE muestra_fecha IS
  fecha  DATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Salida de información');
  SELECT SYSDATE INTO fecha FROM DUAL;
  DBMS_OUTPUT.PUT_LINE ('Fecha:  ' || fecha);
END muestra_fecha;
/

Para crear el procedimiento muestra_fecha sin parámetros.

Para invocar el procedimiento muestra_fecha:

BEGIN
  muestra_fecha;
END;
/

o también en SQL*Plus:

EXEC muestra_fecha;

Ejemplo de procedimiento con parámetros:

CREATE OR REPLACE
PROCEDURE escribe (texto VARCHAR2)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(texto);
END;
/

BEGIN
  ESCRIBE('HOLA');
END;
/

o también en SQL*Plus:

EXEC ESCRIBE('HOLA');

Al declarar cada parámetro se indica el tipo de los mismos, pero no su tamaño; es decir sería VARCHAR2 y no VARCHAR2(50).

5.4.10. Funciones¶

Una función es prácticamente idéntica a un procedimiento. También puede recibir parámetros de entrada y realizar operaciones con dichos datos. Lo que distingue a una función de un procedimiento es que la función siempre devuelve algún valor. Se usa la palabra reservada FUNCTION. Su estructura simplificada es:

FUNCTION nombre RETURN tipoDedatos IS
bloque sin palabra DECLARE

Su estructura en detalle es:

FUNCTION nombre
[(parámetro1 [modelo] tipoDatos
[,parámetro2 [modelo] tipoDatos [,...]])]
RETURN tipoDeDatos
{IS|AS}
bloque sin palabra DECLARE

Para crear la función debemos anteponer la sentencia

CREATE [ OR REPLACE ]

La opción REPLACE hace que si ya existe una función con ese nombre, se reemplaza con la que se crea ahora. Los parámetros son la lista de variables que necesita la función para realizar su tarea. Para invocar la función debemos hacerlo dentro de una expresión. Ejemplo:

SELECT ...función... FROM DUAL;

Para eliminar una función utilizamos la sentencia DROP FUNCTION.

DROP FUNCTION función;

Ejemplo:

CREATE OR REPLACE
FUNCTION SUMA (NUM1 NUMBER, NUM2 NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN NUM1+NUM2;
END SUMA;
/

Para invocar la función definida debemos hacerlo dentro de una expresión. Ejemplos:

SELECT SUMA(5.7, 9.3)           FROM DUAL;
SELECT SUMA(5.7, 9.3)*3         FROM DUAL;
SELECT 150/(SUMA(5.7, 9.3)*3)   FROM DUAL;
SELECT SYSDATE+SUMA(10,2)-2     FROM DUAL;

5.4.11. Paquetes¶

Los paquetes sirven para agrupar bajo un mismo nombre funciones y procedimientos. Facilitan la modularización de programas y su mantenimiento. Los paquetes constan de dos partes:

• Especificación. Que sirve para declarar los elementos de los que consta el paquete. En esta especificación se indican los procedimientos, funciones y variables públicos del paquete (los que se podrán invocar desde fuera del paquete). De los procedimientos sólo se indica su nombre y parámetros (sin el cuerpo).
• Cuerpo. En la que se especifica el funcionamiento del paquete. Consta de la definición de los procedimientos indicados en la especificación. Además se pueden declarar y definir variables y procedimientos privados (sólo visibles para el cuerpo del paquete, no se pueden invocar desde fuera del mismo).

-- PAQUETE ARITMETICA – Especificación
-- PACKAGE_ARITMETICA.SQL
CREATE OR REPLACE
PACKAGE aritmetica IS
  version NUMBER := 1.0;

  PROCEDURE mostrar_info;
  FUNCTION suma       (a NUMBER, b NUMBER) RETURN NUMBER;
  FUNCTION resta      (a NUMBER, b NUMBER) RETURN NUMBER;
  FUNCTION multiplica (a NUMBER, b NUMBER) RETURN NUMBER;
  FUNCTION divide     (a NUMBER, b NUMBER) RETURN NUMBER;
END aritmetica;
/

-- PAQUETE ARITMETICA – Cuerpo
-- PACKAGE_BODY_ARITMETICA.SQL
CREATE OR REPLACE
PACKAGE BODY aritmetica IS

  PROCEDURE mostrar_info IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE
      ('Paquete de operaciones aritméticas. Versión ' || version);
  END mostrar_info;

  FUNCTION suma       (a NUMBER, b NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN (a+b);
  END suma;

  FUNCTION resta      (a NUMBER, b NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN (a-b);
  END resta;

  FUNCTION multiplica (a NUMBER, b NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN (a*b);
  END multiplica;

  FUNCTION divide     (a NUMBER, b NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN (a/b);
  END divide;

END aritmetica;
/

Para utilizar el paquete debemos llamar al procedimiento y funciones deseadas.

Ejemplo de uso, sencillo:

BEGIN
  ARITMETICA.MOSTRAR_INFO;
END;
/

SELECT ARITMETICA.SUMA(4,3) FROM DUAL;
SELECT ARITMETICA.RESTA(4,3) FROM DUAL;
SELECT ARITMETICA.MULTIPLICA(4,3) FROM DUAL;
SELECT ARITMETICA.DIVIDE(4,3) FROM DUAL;

Ejemplo de uso, más elaborado:

DECLARE
  num1      NUMBER:= 2;
  num2      NUMBER:= 5;
  resultado NUMBER;
BEGIN
  ARITMETICA.MOSTRAR_INFO;

  SELECT ARITMETICA.SUMA(num1,num2) INTO resultado FROM DUAL;
  DBMS_OUTPUT.PUT_LINE
    ('La suma de ' || num1 ||' y '|| num2 ||' es '|| resultado);

  SELECT ARITMETICA.RESTA(num1,num2) INTO resultado FROM DUAL;
  DBMS_OUTPUT.PUT_LINE
    ('La resta de ' || num1 ||' y '|| num2 ||' es '|| resultado);
END;
/

Oracle incorpora una serie de paquetes para ser utilizados dentro del código PL/SQL. Es el caso del paquete DBMS_OUTPUT que sirve para utilizar funciones y procedimientos de escritura como PUT_LINE. Otro ejemplo es el paquete DBMS_RANDOM, que contiene diversas funciones para utilizar número aleatorios. Quizá la más útil es la función DBMS_RANDOM.RANDOM que devuelve un número entero (positivo o negativo) aleatorio (y muy grande). Ejemplos:

-- Si deseamos un número aleatorio entre 1 y 10
MOD(ABS(DBMS_RANDOM.RANDOM),10)+1

-- Entre 20 y 50
MOD(ABS(DBMS_RANDOM.RANDOM),31)+20

5.4.12. Disparadores (Triggers)¶

Es un bloque PL/SQL que se ejecuta de forma implícita cuando se ejecuta cierta operación DML: INSERT, DELETE o UPDATE. Contrariamente, los procedimientos y las funciones se ejecutan haciendo una llamada explícita a ellos. Un disparador NO admite argumentos.

Sus aplicaciones son inmensas, como por ejemplo:

• Mantenimiento de Restricciones de Integridad complejas. Ej: Restricciones de Estado (como que el sueldo sólo puede aumentar).
• Auditoría de una Tabla, registrando los cambios efectuados y la identidad del que los llevó a cabo.
• Lanzar cualquier acción cuando una tabla es modificada.

Su estructura general es:

CREATE [OR REPLACE]
TRIGGER Nombre
{ BEFORE | AFTER } Suceso_Disparo ON Tabla
[ FOR EACH ROW [ WHEN Condición_Disparo ]]
Bloque_del_TRIGGER;

Para borrar un disparador:

DROP TRIGGER Nombre ;

Para habilitar/deshabilitar un disparador:

ALTER TRIGGER Nombre { ENABLE | DISABLE };

Para desactivar o activar todos los triggers de una tabla:

ALTER TABLE nombreTabla { DISABLE | ENABLE } ALL TRIGGERS;

Eso permite en una sola instrucción operar con todos los triggers relacionados con una determinada tabla (es decir actúa sobre los triggers que tienen dicha tabla en el apartado ON del trigger).

INSERT
UPDATE
DELETE

BEFORE
AFTER
INSTEAD OF

-- TRIGGER para realizar auditoría sobre operaciones en Empleados
CREATE OR REPLACE
TRIGGER Control_Empleados
AFTER INSERT OR DELETE OR UPDATE ON Empleados
BEGIN
  INSERT INTO Ctrl_Empleados (Tabla,Usuario,Fecha)
  VALUES ('Empleados', USER, SYSDATE);
END Control_Empleados;
/

-- La información de auditoría se guardará en la siguiente tabla
CREATE TABLE CTRL_EMPLEADOS
(
  TABLA    VARCHAR2(50),
  USUARIO  VARCHAR2(50),
  FECHA    DATE
);

CREATE [ OR REPLACE ]
TRIGGER Nombre
{ BEFORE | AFTER } evento1 [OR evento2[, ...]] ON tabla
[ DECLARE
  declaraciones ]
BEGIN
  cuerpo
  [ EXCEPTION
    captura de excepciones ]
END;

{ INSERT | UPDATE [OF columna1 [,columna2, ...]] | DELETE}

CREATE OR REPLACE
TRIGGER ins_personal
BEFORE INSERT ON personal
BEGIN
  IF (TO_CHAR(SYSDATE,'HH24') NOT IN ('10','11','12') THEN
    RAISE_APPLICATION_ERROR
   (-20001,'Sólo se puede añadir personal entre las 10 y las 12:59');
  END IF;
END;

CREATE [ OR REPLACE ]
TRIGGER Nombre
{ BEFORE | AFTER } evento1 [OR evento2[, ...]] ON tabla
FOR EACH ROW [ WHEN condición ]
[ DECLARE
  declaraciones ]
BEGIN
  cuerpo
  [ EXCEPTION
    captura de excepciones ]
END;

CREATE TABLE PIEZAS_AUDITORIA
(
  precio_viejo NUMBER(11,4),
  precio_nuevo NUMBER(11,4),
  tipo         VARCHAR2(2),
  modelo       NUMBER(2),
  fecha        DATE
);

CREATE OR REPLACE
TRIGGER hacer_auditoria_piezas
BEFORE UPDATE OF precio_venta ON PIEZAS
FOR EACH ROW WHEN (OLD.precio_venta < NEW.precio_venta)

BEGIN

  INSERT INTO PIEZAS_AUDITORIA
  VALUES (
    :OLD.precio_venta,
    :NEW.precio_venta,
    :OLD.tipo,
    :OLD.modelo,
    SYSDATE );

END hacer_auditoria_piezas;

CREATE OR REPLACE
TRIGGER nombre
BEFORE INSERT OR DELETE OR UPDATE OF campo1 ON tabla
FOR EACH ROW

BEGIN
  IF  DELETING THEN
    instrucciones que se ejecutan si el TRIGGER saltó por borrar filas
  ELSIF INSERTING THEN
    instrucciones que se ejecutan si el TRIGGER saltó por insertar filas
  ELSE
    instrucciones que se ejecutan si el TRIGGER saltó por modificar fila
  END IF;
END;

CREATE VIEW existenciasCompleta (tipo,modelo,precio,almacen,cantidad)
AS
SELECT p.tipo, p.modelo, p.precio_venta,e.n_almacen, e.cantidad
FROM PIEZAS p JOIN EXISTENCIAS e ON p.tipo=e.tipo AND p.modelo=e.modelo
ORDER BY p.tipo,p.modelo,e.n_almacen;

INSERT INTO existenciasCompleta VALUES('ZA',3,4,3,200);

CREATE OR REPLACE
TRIGGER ins_piezas_exis
INSTEAD OF INSERT ON existenciascompleta

BEGIN
  INSERT INTO piezas(tipo,modelo,precio_venta)
  VALUES(:NEW.tipo,:NEW.modelo,:NEW.precio);

  INSERT INTO existencias(tipo,modelo,n_almacen,cantidad)
  VALUES(:NEW.tipo,:NEW.modelo, :NEW.almacen,:NEW.cantidad);
END;

SELECT * FROM USER_ERRORS;

SELECT * FROM USER_ERRORS
WHERE NAME='nombre_bloque' AND TYPE='tipo_bloque';

PROCEDURE
FUNCTION
PACKAGE
PACKAGE BODY
TRIGGER

CREATE OR REPLACE
TRIGGER Control_Empleados
AFTER INSERT OR DELETE OR UPDATE ON Empleados

BEGIN
  INSERT INTO Ctrl_Empleados (Tabla,Usuario,Fecha)
  VALUES ('Empleados', USER, SYSDATE);
END Control_Empleados;
/

SELECT name, type, text FROM user_errors
WHERE name='CONTROL_EMPLEADOS' AND type='TRIGGER';

SHOW ERRORS;