Nel precedente articolo ho introdotto PL/SQL il linguaggio di programmazione proprietario di Oracle. Eseguire operazioni pesanti direttamente nel database tramite PL/SQL rende l'elaborazione dell'applicazione più leggera e stabile, tuttavia, nel caso le operazioni da eseguire nel DB non siano particolarmente onerose, conviene mantenere le query all'interno dell'applicazione, per facilità di manutenzione e per la portabilità verso altri datasource. Nel precedente articolo vi ho mostrato il funzionamento di una semplice stored procedure, tuttavia esistono altri costrutti utili ed interessanti.
Le function
Le function sono dei blocchi di codice che restituiscono un valore in output, di seguito un semplice esempio:
-- Nota: mai creare i propri oggetti nel SYS schema.
CREATE OR REPLACE FUNCTION UtentiTotali
RETURN number IS
totali number(2) := 0;
BEGIN
SELECT count(*) into totali
FROM Utente;
RETURN totali;
END;
/
CREATE OR REPLACE FUNCTION UtentiTotali
RETURN number IS
totali number(2) := 0;
BEGIN
SELECT count(*) into totali
FROM Utente;
RETURN totali;
END;
/
La funzione UtentiTotali esegue un conteggio dei record all'interno della tabella Utente e lo memorizza nella variabile numerica totali, la quale viene restituita come risultato della funzione. Eseguo la funzione in SQLPLUS.
Definisco una variabile itot di tipo numerico,
SQL>var itot number;inserisco il codice all'interno di un file e genero la function
SQL>@D:path/file.sql;Eseguo la function assegnando il risultato alla variabile itot appena definita.
SQL>exec :itot := utentiTotali; Infine stampo a video il risultato.
SQL>print itot; I trigger
I trigger sono programmi che vengono eseguiti al verificarsi di un eventoCREATE OR REPLACE TRIGGER display_ruolo_changes
BEFORE DELETE OR INSERT OR UPDATE ON myuser.myUtente
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
BEGIN
dbms_output.put_line('Old ruolo: ' || :OLD.ruolo);
dbms_output.put_line('New ruolo: ' || :NEW.ruolo);
dbms_output.put_line('il ruolo è stato modificato');
END;
/
Il Trigger esegue il blocco di codice definito all'interno delle istruzioni
begin..end prima dell'esecuzione di operazioni di insert, delete o update nella tabella Utente. La clausula "for each row" indica che viene eseguito una volta per ogni riga interessata dall'evento. I riferimenti OLD e NEW indicano il valore del campo prima e dopo l'esecuzione dell'operazione di DML. Per testare il trigger eseguo l'istruzione:SQL>@D:path/trigger.sqlL'operazione genera un messaggio di errore.
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYSERROR at line 1:Nello specifico Oracle impedisce di generare i trigger tra gli oggetti di sistema. In realtà è buona norma definire un proprio schema in cui generare tutti i propri oggetti.
In quest'ottica genero un tablespace personale:
SQL> create tablespace mytabspace
2 datafile 'mytabspace.dat'
3 size 10m autoextend on;Creo un'utenza personale a cui associo il tablespace appena creato.
SQL> create user myuser
2 identified by passwd
3 default tablespace mytabspace;Associo all'utenza i permessi per la creazione di sessioni, tabelle, e trigger
SQL> grant create session to myuser;
SQL> grant create table to myuser;
SQL> grant create trigger to myuser;
SQL> grant unlimited tablespace to myuser;Accedo all'utenza appena creata e genero gli oggetti di cui ho bisogno compreso il trigger che voglio testare.
SQL> conn myuser;
SQL> CREATE TABLE myUtente("ID" NUMBER(*,0) NOT NULL ENABLE, "NOME" VARCHAR2(30), "COGNOME" VARCHAR2(30), "RUOLO" VARCHAR2(30), PRIMARY KEY ("ID"));
SQL> insert into myUtente (ID, NOME, COGNOME, RUOLO) VALUES (1, 'FRANCO', 'NERI', 'CAPOUFFICIO');
SQL> insert into myUtente (ID, NOME, COGNOME, RUOLO) VALUES (2, 'MARIO', 'BIANCHI', 'IMPIEGATO');
SQL> insert into myUtente (ID, NOME, COGNOME, RUOLO) VALUES (3, 'MARCO', 'ROSSI', 'APPRENDISTA');Rigenero il trigger dopo avere corretto il riferimento dalla tabella Utente a myUtente
SQL>@D:path/trigger.sql
SQL> set serveroutput on;
SQL> update myUtente set ruolo = 'Impieganto' where id = 3;
Old ruolo: Apprendista
New ruolo: Impieganto
il ruolo è stato modificato.
I pacchage
I package raggruppano oggetti PL/SQL logicamente correlati come variabili e sottoprogrammi.Un pacchetto ha due parti obbligatorie:
- Package Specification
- Package Body
Il Package Body contiene il codice dei vari metodi dichiarati nello specifico pacchetto e altre dichiarazioni private che non vengono mostrate all'esterno al pacchetto.
Riporto di seguito un esempio pratico.
Genero il Package Specification.
CREATE OR REPLACE PACKAGE c_package AS
-- Aggiunta nuovo utente
PROCEDURE addUtente(c_id myutente.id%type,
c_nome myutente.nome%type,
c_cognome myutente.cognome%type,
c_ruolo myutente.ruolo%type);
-- Elimina utente
PROCEDURE delUtente(c_id myutente.id%TYPE);
--Lista utenti
PROCEDURE listaUtenti;
END c_package;
/
Genero il Package Body.
CREATE OR REPLACE PACKAGE BODY c_package AS
PROCEDURE addUtente(c_id myUtente.id%type,
c_nome myUtente.nome%type,
c_cognome myUtente.cognome%type,
c_ruolo myUtente.ruolo%type)
IS
BEGIN
INSERT INTO myUtente (id,nome,cognome,ruolo)
VALUES(c_id, c_nome, c_cognome, c_ruolo);
END addUtente;
PROCEDURE delUtente(c_id myUtente.id%type) IS
BEGIN
DELETE FROM myUtente
WHERE id = c_id;
END delUtente;
PROCEDURE listaUtenti IS
CURSOR c_utente is
SELECT nome FROM myUtente;
TYPE c_list is TABLE OF myUtente.nome%type;
name_list c_list := c_list();
counter integer :=0;
BEGIN
FOR n IN c_utente LOOP
counter := counter +1;
name_list.extend;
name_list(counter) := n.nome;
dbms_output.put_line('Utente(' ||counter|| ')'||name_list(counter));
END LOOP;
END listaUtenti;
END c_package;
/
Il blocco di codice che riporto di seguito utilizza i metodi dichiarati e definiti nel pacchetto c_package.
DECLARE
code myUtente.id%type:= 8;
BEGIN
c_package.addUtente(7, 'Maria', 'Viola', 'Impiegata');
c_package.addUtente(8, 'Gianni', 'Verdi', 'Apprendita');
c_package.listaUtenti;
c_package.delUtente(code);
c_package.listaUtenti;
END;
/
Per testare i programmi appena creati su SQLPLUS è sufficiente inserirli su dei file .sql e mandarli in esecuzione con le stesse modalità già viste in precedenza.
Hai apprezzato questo post? Conferma le mie competenze o scrivi una segnalazione sul mio profilo Linkedin!
