Programmazione PL-SQL

Vai all'indice

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

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 evento

  
CREATE 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.sql

L'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

Eseguo una Update sul ruolo di un singolo record ed ottengo il risultato atteso.
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 Specification è la l'interfaccia del package. Essa dichiara i tipi, variabili, costanti, eccezioni, cursori e sottoprogrammi che possono essere referenziate al di fuori del pacchetto. Il altre parole contiene tutte le informazioni sul contenuto del pacchetto, ma esclude il codice per i sottoprogrammi.
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!