BASES DE DADES ORACLE

UD2: ADMINISTRACIÓ DE LA BASE DE DADES

Tema 2.1: Administració remota amb l'Schema Manager

Activitat 2.1.2: Disparadors de bases de dades (Triggers)

El lenguatge PL/SQL

És un llenguatge propi d'Oracle, basat en ADA, que amplia les funcions d'SQL, afegint:

variables i tipus de dades
estructures de control
procediments i funcions

L'estructura d'un programa PL/SQL és:

Secció de declaracions (optativa): DECLARE
Secció d'execució (obligatòria): BEGIN .....
Secció de gestió d'errors d'execució (optativa): EXCEPTION
END;

Exemple:

El seguent codi modifica la taula EMP de SCOTT: actualitza el número de departament de l'empleat STEVE. Si aquest empleat no existeix, aleshores l'insereix a la taula:

DECLARE

depart NUMBER(2) := 10; /*declaracions de variables */
numemp NUMBER(4) := 8000;
nomemp VARCHAR2(10) := 'STEVE';

BEGIN /* codi SQL d'actualització */

UPDATE SCOTT.emp
SET DEPTNO = depart
WHERE ENAME = nomemp;

IF SQL%NOTFOUND THEN /* no hi es, inserir-lo */
INSERT INTO SCOTT.emp (EMPNO, ENAME, DEPTNO)
VALUES (numemp, nomemp, depart);
END IF;
COMMIT;

END;

Proveu-lo des de la finestra SQL Worksheet de l'OEM i comproveu que s'afegeix l'empleat STEVE.

Objectes de codi PL/SQL enmagatzemats: funcions, procediments, paquets i disparadors

Aquests objectes són rutines compilades escrites en el llenguatge PL/SQL i guardades com objectes en esquemes
Igual que en la resta d'objectes cal tenir drets d'execució sobre ells per poder-los utilitzar.
Alguns d'aquests objectes poden ser utilitzats directament, invocant-los des de SQL, o bé son funcions que es poden incloure dintre d'un programa PL/SQL.
Els procediments i les funcions són blocs de codi que poden cridar-se des de programes PL/SQL
Per exemple en la següent figura veiem la finestra d'edició d'una funció compilada i enmagatzemada en el servidor que retorna la data del sistema en format text.

Exemple de definició de funció d'ús general en l'esquema jcuesta

Exercici: Creeu un objecte nou en Schema =>Functions i copieu el codi de la imatge per crear la funcó TIME. Un cop creada, aneu al SQL Worksheet i executeu les següents sentències:
1r) set serveroutput on
2n) executeu el codi PL/SQL següent:
BEGIN
dbms_output.put_line(TIME);
END;

Observeu que es mostra la hora del servidor Oracle.

Sobre els drets dels objectes PL/SQL

L'únic dret d'accés que té sentit atorgar a la resta d'usuaris en el cas dels objectes PL/SQL es el dret d'execució. Per exemple:

grant execute on jcuesta.time to scott

donarà dret d'execució sobre la funció anterior a l'usuari scott.

Procediments

Són blocs de codi que normalment accpeten paràmetres i executen alguna acció. Per exemple el procediment SUBPTXT situat en SYS accepta 4 paràmetres i crida a un altre procediment anomenat SUBPTXT2:

Disparadors

Un disparador de base de dades (objecte trigger) és un programa escrit en PL/SQL que està associat a una taula, vista, esqeuam o bé a tota la base de dades. Són procediments que s'executen (es disparen) quan succeeix un esdeveniment, normalment quan efectuem alguna operació sobre una taula.
Quan dissenyem un disparador hem de decidir en quin moment s'executarà (abans o després de la transacció) i quina és l'acció sobre la taula que l'activarà, així com l'acció que efectuarà en resposta.
Les utilitats dels disparadors són:
- Validar les dades abans de guardar-les
- Generar automàticament registres en altres taules a partir de les dades que s'estan introduint
- Implementar regles complexes de restricció de integritat
- Auditoria sobre una taula, registrant les modificacions i l'usuari que les realitza
Normalment associarem un disparador amb les accions de insertar dades a la taula (insert), esborrar les (delete) o actualitzar les (update).
Per aprofitar tota la potència dels disparadors cal conèixer a fons el llenguatge PL/SQL. Aquí nomès veurem alguns exemples simples.

Exemple de creació d'un disparador amb l'Enterprise Manager

Anem a crear un disparador sobre la taula Publicacions que s'activarà al modificar qualsevol de les dades d'un fila. Seguiu les següents passes:

1. Entreu en l'OEM identificant vos amb l'usuari que veu emprar al crear la taula Publicacions
2. Botò dret sobre l'icona del vostre esquema; apareix la llista d'objectes Oracle:

Triem "disparador".
3. En la carpeta General donem un nom al disparador i escribim el codi PL/SQL que s'executarà:

4. En la targeta "Evento" decidim quan s'activarà el codi i en resposta a quin esdeveniment:

En l'exemple quan actualitzem les columnes "Marca" o "Model" de la taula ""Motos" de l'usuari JCUESTA s'executarà el codi PL/SQL. Fixeu-vos que està marcada la casella "Antes": abans de efectuar la transacció es dispararà el codi; d'aquesta forma podem executar accions complexes abans de modificar les dades, com ara verificacions o actualitzacions de dades relacionades. També podem triar que s'executi després de la transacció.

5. En la targeta "Avanzado" podem especificar que el disparador s'activi per a cada fila modificada en la taula. Per exemple si fem

UPDATE MOTOS SET MODEL =' ' WHERE MARCA='Honda';

modifiquem amb una única instrucció moltes files; per defecte el disparador només s'activa un sol cop, excepte si hem activat el disparador per files, llavors s'activarà per a cada fila modificada.

6. Acceptar el disparador. Comproveu que s'ha incorporat a la llista de disparadors de la taula, com mostra la figura:

6. Per provar el disparador, anem a PL/SQL. Modifiquem les dades de la fila actual (estem suposant que hi han dades):

update publicacions set any_publicacio = '01/01/2000';

Observeu que es mostra la data del sistema (variable d'entorn SYSDATE) abans (before) del missatge "1 fila actualitzada".

Un altre exemple més complex de disparador:

DECLARE
conta number;
cursor erronis is
select dades, count(*) conta
from taula
group by dades;

BEGIN
for registre in erronis loop
if registre.conta > 2 then
dbms_output.put_line('masses dades repes en ' || to_char(registre.dades));
else
null;
end if;
end loop;

END ;

En la secció de declaracions tenim dues variables: el contador conta i la variable erronis que es de tipus cursor. Un cursor representa un conjunt de registres (el resultat de la sentència SELECT).

En la secció executable recorrem el conjunt de registres erronis i verifiquem si algún d'ells té el contador > 2 (és a dir, si hi han duplicats). Si és així, mostra els registres que estan duplicats.

En la targeta "Events" tenim:

Per tant, després d'inserir un registre en la taula "TAULA", es dispara el trigger que detecta si hi han registres duplicats.