Basi di dati: PL SQL e PLpgSQL

Esistono due sistemi per estendere le funzionalità dei RDBMS:

1. Estensioni native al linguaggio SQl (caso di PL SQL (Oracle) e PLpgSQL (Postgre))
   
2. Linguaggio "ospitato" (ad esempio un linguaggio come PYTON che viene interpretato dal motore del DB a cui viene aggiunta la possibilità di avere statement SQL
   

PLSQL

SELECT INTO

: per scrivere il valore di una select che ritorna un solo record in variabili utilizzabili:
SELECT
Campo1, Campo2
INTO
Variabile1, variabile2 FROM….

CURSORE

: Una struttura dati che ci permette di navigare tra i record ritornati dalla SELECT (navigare solo in avanti).
DECLARE CURSOR
NomeCursore
FOR SELECT ….
OPEN
NomeCursore -> Apre il cursore e lo posiziona sul primo record
FETCH
NomeCursore
INTO
Var1, Var2, ecc.-> Scrive il record attuale dentro a delle variabili e passa al record successivo
CLOSE
NomeCursore -> Chiude il cursore (e dealloca le risorse)
Una variabile mostra lo stato del cursore (SQLCODE)

Programmi PLpgSQL

Un programma PL SQL è diviso in blocchi

Un header (con dichiarazione variabili, ecc.)

Un corpo con i comandi PL SQL e SQL-DML (SELECT INSERT UPDATE DELETE (CRUD)). Ha una gestione degli errori

DECLARE
Header
BEGIN
Corpo del programma
EXCEPTION
Gestione dell'eccezione
END;

Gestione Eccezioni

La gestione dell'errore può essere puntuale per tipo di errore:
BEGIN
Istruzioni
EXCEPTION
WHEN
TipoException
THEN
IstruzioniDiGestioneErrore
[WHEN AltroTipoException
THEN
IstruzioniDiGestioneErrore]
END

Per avviare una funzione posso usare RUN
nomeFunzione oppure SELECT
nomeFunzione

Il PLSQL è organizzato a blocchi

Sono ammessi tutti i tipi di dati SQL + tipi estesi

Sono ammessi tutti i comandi DML (DDL no, ma posso farli come DML sul catalog)

Posso dichiarare una variabile "come un certo attributo di una certa tabella" (il tipo sarà: NomeTabella.NomeCampo%TYPE) oppure come un record di una certa tabella (il tipo sarà: NomeTabella%ROWTYPE)

Esempi:

DECLARE
x NUMBER;
y VARCHAR(20);

oppure:

DECLARE
x tabella.campo%TYPE;

DECLARE
x tabella%ROWTYPE; (tipo di dati "riga": come riga della tabella specificata)

Le variabili dichiarate sono nulle fino ad un'assegnazione. L'operatore di assegnazione è :=

Cicli di controllo

IF
condizione1
THEN
istruzioni
[ELSEIF
condizione2
THEN
istruzioni]
[ELSE
istruzioni]
END IF

LOOP
istruzioni
EXIT WHEN
condizione (uscita dal loop)
END LOOP

WHILE
condizione
LOOP
istruzioni
END LOOP

FOR
var
IN
inizio fine
LOOP
istruzioni
END LOOP

Esempio (con tabella T1):

DECLARE

a T1.e%TYPE;
b T1.f%TYPE;
CURSOR
T1Cursor
IS
SELECT
e, f
FROM
T1
WHERE
e < f
FOR UPDATE; /*Con FOR UPDATE il cursore é aggiornabile*/

BEGIN

OPEN
T1Cursor;

LOOP
FETCH
T1Cursor
INTO
a, b;
EXIT WHEN
T1Cursor%NOTFOUND;
DELETE FROM
T1Cursor
WHERE CURRENT OF
T1Cursor; /*permesso solo se ho FOR UPDATE nel CURSORE*/
INSERT INTO
T1
VALUES (b, a); /*Li ho ri-scritti invertiti*/
END LOOP;

CLOSE
T1Cursor;

END;
.
run;

Posso incapsulare in procedure o funzioni (le funzioni hanno un valore di ritorno)

Posso passare parametri (anche con IN o OUT (o INOUT default) per definire se sono parametri passati o solo che ritornano)

Posso non specificare nome per i parametri (in tal caso si chiameranno $1, $2, …, $n)

Posso associare le procedure a dei trigger

CREATE OR REPLACE PROCEDURE
NomeProcedure (
param1 tipoParam1,
…
paramN tipoParamN
) AS
BEGIN
istruzioni
END

Per richiamarla:

BEGIN
NomeProdecure(param1,…paramN);
END;

Le funzioni hanno valore di ritorno: CREATE FUNCTION
NomeFunzione
RETURN
Tipo
AS…

Si può fare l'OVERLOAD di funzioni, non si può dare valori di default ai parametri

Operatore di concatenamento di stringhe é: ||

TRIGGER

Le funzioni invocate da un trigger hanno uno specifico tipo di ritorno (TRIGGER). Es:
CREATE OR REPLACE FUNCTION
NomeProcedure
RETURN TRIGGER AS …
Questo mi permette di avere all'interno del corpo le due variabili di tipo riga NEW (in caso di "ON INSERT" o "ON UPDATE") e OLD (in caso di UPDATE o DELETE). Se la funzione è per INSERT o UPDATE alla fine dovrò fare "RETURN NEW" (oppure "RETURN OLD" oppure "RETURN AltraRiga" oppure "RETURN NULL" (=nessuna riga (in caso di insert non inserisce niente!))

Per attivare la funzione come un trigger basterà poi la sintassi già vista. Es di trigger per riga su INSERT:
CREATE TRIGGER
NomeTrigger
BEFORE INSERT ON NomeTabella
FOR EACH ROW EXECUTE PROCEDURE
NomeProcedure();

Al posto di BEFORE INSERT posso mettere UPDATE o DELETE (oppure INSERT OR UPDATE, ecc.)

Dopo AS dovrei specificare il linguaggio. Es.: CREATE PROCEDURE
NomeProcedure
AS
$$
BEGIN
…
END $$ LANGUAGE plpgsql

La lezione bdd_2005112401 perde audio dal 33° minuto. Si vede un esempio d'uso di refcursor che non approfondisco.

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.