Procedimientos, Cursores, Excepciones, Triggers y Paquetes en PL/SQL: Guía Práctica con Ejemplos

Procedimientos y funciones en PL/SQL

En PL/SQL se pueden crear procedimientos almacenados con parámetros de entrada (IN), de salida (OUT) y de entrada/salida (IN OUT). A continuación se muestra un ejemplo básico:

CREATE PROCEDURE imp(ven NUMBER, com IN OUT NUMBER, imp_co OUT NUMBER) IS
BEGIN
  IF ven > 1 THEN
    com := 2;
  END IF;
  imp_co := (com / 1) * ven;
END imp;
/

DECLARE
  com NUMBER := 5;
  pag NUMBER;
BEGIN
  imp(1, com, pag);
END;

Procedimientos con cursores

Un cursor permite recorrer un conjunto de filas. En el siguiente ejemplo se actualizan los salarios de los empleados cuyo salario está por debajo de la media de su oficio:

CREATE PROCEDURE a_s IS
  CURSOR c IS
    SELECT salario, oficio, ROWID FROM emple ORDER BY oficio;
  ofi VARCHAR2(10) := 'SIN';
  medio NUMBER := 0;
BEGIN
  FOR var IN c LOOP
    IF ofi = 'SIN' THEN
      ofi := var.oficio;
      SELECT AVG(NVL(salario, 0)) INTO medio FROM emple WHERE oficio = ofi;
    END IF;
    IF ofi != var.oficio THEN
      ofi := var.oficio;
      SELECT AVG(NVL(salario, 0)) INTO medio FROM emple WHERE oficio = ofi;
    END IF;
    IF medio > var.salario THEN
      UPDATE emple SET salario = salario + 2 WHERE ROWID = var.ROWID;
    END IF;
  END LOOP;
END a_s;
/

Funciones

CREATE FUNCTION a(f1 DATE, f2 DATE) RETURN INTEGER IS
BEGIN
  RETURN MONTHS_BETWEEN(f1, f2) / 12;
END a;
/

Parámetros con valor por defecto

CREATE OR REPLACE PROCEDURE p(n1 NUMBER DEFAULT 0) ...

Cursores explícitos e implícitos

Cursor explícito con OPEN, FETCH y CLOSE

CREATE PROCEDURE emp(n NUMBER) IS
  CURSOR c IS SELECT apellido FROM emple WHERE dept_no = n;
  var emple.apellido%TYPE;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO var;
    IF c%NOTFOUND THEN EXIT; END IF;
    DBMS_OUTPUT.PUT_LINE(var);
  END LOOP;
  CLOSE c;
END emp;
/

EXECUTE emp(30);

Cursor con bucle FOR

CREATE PROCEDURE emp(n NUMBER) IS
  CURSOR c IS SELECT apellido FROM emple WHERE dept_no = n;
BEGIN
  FOR var IN c LOOP
    DBMS_OUTPUT.PUT_LINE(var.apellido);
  END LOOP;
END emp;
/

Cursor implícito

DECLARE
  ape VARCHAR2(10);
BEGIN
  SELECT apellido INTO ape FROM emple WHERE emp_no = 5;
  DBMS_OUTPUT.PUT_LINE(ape);
END;

Gestión de excepciones

Excepciones predefinidas

CREATE PROCEDURE Div_0 IS
  total REAL;
BEGIN
  total := 5 / 0;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('oo');
END Div_0;
/

Excepciones definidas por el usuario

CREATE PROCEDURE Depto(d NUMBER) IS
  CURSOR c IS SELECT apellido FROM emple WHERE dept_no = d;
  n_50 EXCEPTION;
BEGIN
  IF d = 50 THEN RAISE n_50; END IF;
  FOR var IN c LOOP
    DBMS_OUTPUT.PUT_LINE(var.apellido);
  END LOOP;
EXCEPTION
  WHEN n_50 THEN
    DBMS_OUTPUT.PUT_LINE('NO EN 50');
END Depto;
/

Excepciones asociadas a códigos de error de Oracle

DECLARE
  a EXCEPTION;
  PRAGMA EXCEPTION_INIT(a, cod_oracle);
BEGIN
  ...
EXCEPTION
  WHEN a THEN ...
END;

Excepciones definidas con RAISE_APPLICATION_ERROR

IF d = 50 THEN
  RAISE_APPLICATION_ERROR(-20001, 'NO');
END IF;

Control de transacciones

Los SAVEPOINT permiten deshacer parcialmente una transacción:

SAVEPOINT uno;
INSERT ...;
SAVEPOINT dos;
INSERT ...;
IF ... THEN
  ROLLBACK TO uno;
ELSIF ...
  ROLLBACK TO dos;
ELSE
  COMMIT;
END IF;

Cursores con actualización

CREATE PROCEDURE act(num NUMBER, i NUMBER) IS
  CURSOR c IS
    SELECT salario, ROWID FROM emple WHERE dept_no = num;
    -- CURSOR c IS SELECT salario FROM emple WHERE dept_no = num FOR UPDATE;
  n NUMBER := 0;
BEGIN
  SELECT COUNT(*) INTO n FROM emple WHERE dept_no = num;
  IF n = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'no exist');
  END IF;
  FOR var IN c LOOP
    IF ... THEN
      UPDATE emple SET salario = salario + i WHERE ROWID = var.ROWID;
      -- UPDATE emple SET salario = salario + i WHERE CURRENT OF c;
    END IF;
  END LOOP;
END act;
/

Triggers

Trigger BEFORE UPDATE

CREATE TRIGGER e BEFORE UPDATE ON emple FOR EACH ROW
DECLARE
  no EXCEPTION;
BEGIN
  DBMS_OUTPUT.PUT_LINE('OK');
  IF :OLD.salario >= 200 THEN
    RAISE no;
  END IF;
END;
/

Trigger de auditoría en tabla de log

CREATE TABLE log (f DATE, m VARCHAR2(50));

CREATE TRIGGER log_e AFTER UPDATE OR INSERT OR DELETE ON emple FOR EACH ROW
DECLARE
  msj log.m%TYPE;
BEGIN
  IF INSERTING THEN
    msj := 'Insertado ' || :NEW.apellido;
  END IF;
  IF UPDATING THEN
    msj := 'Actualizado ' || :OLD.apellido || ' a ' || :NEW.apellido;
  END IF;
  IF DELETING THEN
    msj := 'Borrado ' || :OLD.apellido;
  END IF;
  INSERT INTO log_emple(f, m) VALUES (SYSDATE, msj);
END;
/

Trigger INSTEAD OF sobre una vista

CREATE TRIGGER v1t INSTEAD OF INSERT OR UPDATE ON vi1
DECLARE
BEGIN
  IF INSERTING THEN
    INSERT INTO clientes (cod, nom) VALUES (:NEW.cod, :NEW.nom);
    INSERT INTO pedidos (num, cli) VALUES (:NEW.num, :NEW.cod);
  END IF;
  IF UPDATING THEN
    INSERT INTO clientes (cod, nom) VALUES (:NEW.cod, :OLD.nom);
    UPDATE pedidos SET cli = :NEW.cod WHERE cli = :OLD.cod;
    DELETE FROM clientes WHERE cod = :OLD.cod;
  END IF;
END;
/

Paquetes

Especificación del paquete

CREATE PACKAGE g AS
  PROCEDURE s(i emple.salario%TYPE);
END;

Cuerpo del paquete

CREATE PACKAGE BODY g AS
  PROCEDURE s(i emple.salario%TYPE) IS
  BEGIN
    UPDATE emple SET salario = i;
  END;
END;
/

EXECUTE g.s(18);

Seguridad: roles, usuarios y perfiles

Roles y privilegios

CREATE ROLE e;
GRANT CREATE SESSION TO e;
GRANT INSERT ON emple TO e;
GRANT INSERT ON depart TO e;
GRANT SELECT ANY TABLE TO e;

Usuarios

CREATE USER fer IDENTIFIED BY perro;
GRANT e TO fer;

Perfiles

CREATE PROFILE dos LIMIT SESSIONS_PER_USER 2;
ALTER USER fer IDENTIFIED BY perro PROFILE dos;

Eliminación de objetos

DROP ROLE e;
DROP USER fer;
DROP PROFILE dos;
DROP TRIGGER t;