Laboratorio Di Basi Di Dati 04 P L S Q L E P Lpg S Q L

1

A.A. 2005/2006 Basi di Dati e Laboratorio 1

Corso di Basi di Dati e Laboratorio

PL/SQL e PLpgSQLAlfio Ferrara - Stefano Montanelli


Estensioni procedurali di SQL

• Necessità– Disporre di linguaggi con cui costruire

applicazioni nel contesto di una base di dati SQL

• Soluzioni– Linguaggio nativo– Linguaggio ospite con procedure esterne– Linguaggio ospite con precompilatore

(embedded SQL)


Embedded SQL

• Per inserire statement SQL in un programmascritto in linguaggio ospite sono necessari:

• strumenti per descrivere lo stato delleesecuzioni SQL

• SQL offre la variabile SQLCODE che si riferisceallo stato di esecuzione di uno statement:

• SQLCODE = 0 - esecuzione corretta• SQLCODE < 0 - errore• SQLCODE = 100 - risultato vuoto

2


Embedded SQL

• Strumenti per scambiare valori tra variabili diprogramma e SQL

• ciò implica:• 1)una sintassi per referenziare variabili di

programma in statement SQL, e per assegnarevalori singoli a singole variabili– si aggiunge ad una selezione la clausola INTO se il

risultato della selezione è una singola tupla:SELECT nome, cognomeINTO :n, :cFROM …WHERE …


Embedded SQL

• 2)Uno strumento per trasferire un risultatocostituito da più tuple nelle variabili del programma si fa ricorso al concetto dicursore

• Un cursore può essere visto come unavariabile che contiene un insieme di datiche é possibile referenziare


Embedded SQL

• Un cursore viene dichiarato associandoload uno statement SELECT:DECLARE nomecursore CURSOR FOR select

• L’esecuzione dello statement avviene peròsolo all’apertura del cursore:OPEN nomecursore

3


Embedded SQL

• Ciò posiziona il cursore prima della prima tupla:FETCH nomecursore INTO lista

• questo provoca l’avanzamento del cursoree la copia dei valori della tupla correntenelle variabili di ‘lista’

• Successivamente il cursore è posizionatodopo l’ultima tupla e SQLCODE assume valore 100

• L’istruzione: CLOSE nomecursoredisattiva il cursore


Oracle PL/SQL

Procedural Language/SQL• Estensione procedurale di SQL• Struttura del linguaggio:

– PL/SQL è diviso in unità dette blocks– I programmi sono divisi dunque in unità che

eseguono una azione logica e possono essereinserite le une nelle altre


I blocchi

I singoli blocchi hanno la seguente struttura:DECLARE/* dichiarazione di variabili, tipi e strutture di

programma */

BEGIN/* esecuzione: le procedure e i comandi SQL sono

specificati in questa sezione si tratta dell’unicasezione indispensabile di un blocco */

EXCEPTION/* dichiarazione di procedure di gestione degli

errori */

END;

4


Elementi SQL in PL/SQL

• Gli elementi SQL ammessi in un bloccoPLSQL sono:SELECT, INSERT, UPDATE, DELETE

• Non sono ammessi:CREATE, DROP, ALTER

• Per eseguire un programma PLSQL ad END deve seguire un punto e il comandoRUN;


Variabili e tipi

• Le informazioni sono condivise dalprogramma PLSQL e dal database attraverso variabili

• Ad ogni variabile è associato un tipo• I tipi ammessi sono:

– Uno dei tipi ammessi per gli attributi SQL– Un tipo PLSQL (ad esempio number)– Si possono dichiarare variabili che siano dello

stesso tipo assegnato ad un attributo


Esempio di dichiarazione

DECLAREx NUMBER;y VARCHAR(20);

DECLAREx tabella.campo%TYPE;

DECLAREx tabella%ROWTYPE;

• Una variabile dichiarata ha sempre valoreNULL fino ad una assegnazione

• Operatore di assegnazione :=

Dichiarazione dei tipi

X assume il tipo del campo “campo”

X assume il tipo dei campi della tabella “tabella”

5


Uso di una query SQL

• Un programma PLSQL può far ricorso ad unaQuery SQL, con una sintassi leggermentediversa:

• La clausola SELECT deve essere seguita da unaclausola INTO che specifichi le variabili entro cui vanno inseriti i risultati della query

• E’ dunque necessaria una variabile per ogniattributo:

SELECT e,fINTO a,b FROM T1WHERE e>1;


Esempio di programmaCREATE TABLE T1(

e INTEGER,f INTEGER );

INSERT INTO T1 VALUES(1, 3);INSERT INTO T1 VALUES(2, 4);

DECLAREa NUMBER;b NUMBER;

BEGINSELECT e,fINTO a,b FROM T1 WHERE e>1;INSERT INTO T1 VALUES(b,a);

END;.run;


Limiti nell’uso di query SQL

• Un programma PLSQL che fa uso diuna query SQL risulta corretto solo se la query produce come risultato unasola tupla

• Per lavorare altrimenti occorre ricorreread un cursore

6


Controllo di flusso - IF

Sintassi:IF <condizione_1> THEN ...ELSIF <condizione_2> THEN ...... ...ELSIF <condizione_n> THEN ...ELSE ...END IF;


Controllo di flusso - LOOP

Sintassi:LOOP

<istruzioni>END LOOP;

Almeno una delle istruzioni del loop deve essere unaistruzione di uscita, come la seguente:EXIT WHEN <condizione>;


Esempio

DECLAREi NUMBER := 1;

BEGINLOOP

INSERT INTO T1 VALUES(i,i);i := i+1;

EXIT WHEN i>100;END LOOP;

END;.run;

7


Altri controlli di flusso

• L’istruzione EXIT senza condizione permettel’uscita incondizionata da un ciclo

While – sintassi:WHILE <condizione>

LOOP<istruzioni>

END LOOP;


Altri controlli di flusso

For – sintassi:FOR <var> IN <inizio>..<fine>LOOP<istruzioni>END LOOP;

• var è una variabile locale che non richiede diessere dichiarata


Cursori

• Un cursore è una variabile che scorre sui valoridelle tuple di una relazione (tabella o risultato diquery)

• Esempio di uso di un cursore:1) DECLARE2) a T1.e%TYPE;3) b T1.f%TYPE; /* dichiarazione delcursore:*/

4) CURSOR T1Cursor IS5) SELECT e, f6) FROM T17) WHERE e < f8) FOR UPDATE;

8


Cursori

9) BEGIN10)OPEN T1Cursor;11)LOOP12)FETCH T1Cursor INTO a, b;13)EXIT WHEN T1Cursor%NOTFOUND;14)DELETE FROM T1 WHERE CURRENT OF T1Cursor;15)INSERT INTO T1 VALUES(b, a);16)END LOOP;17)CLOSE T1Cursor;18)END;19).20)run;


Procedure

Caratteristiche:ogni parametro può essere seguito da un modo e

da un tipoi modi ammessi sono:

– IN sola lettura– OUT sola scrittura– INOUT lettura e scrittura

per eseguire una procedura occorre invocarla entroun comando PLSQL:BEGIN aggiungitupla(1);END;


Esempio

• Esempio di procedura di inserimento a dueparametri:

CREATE OR REPLACE PROCEDURE at2(x T2.a%TYPE,y T2.b%TYPE

)ASBEGININSERT INTO T2(a, b) VALUES(x, y);END at2;

BEGINat2(10, 'abc');END;

9


Funzioni

Caratteristiche:• la principale differenza rispetto ad una procedura

consiste nel definire un valore di RETURNsintassi:CREATE FUNCTION nomefunzione(parametri) RETURN<tipo_di_return> AS …

nel corpo della funzione si usa la seguenteespressione per terminare l’esecuzione ed ottenere il valore di ritorno:

RETURN <espressione>;


Verificare e cancellare

Controllare le procedure create:SELECT object_type, object_nameFROM user_objectsWHERE object_type = 'PROCEDURE'OR object_type = 'FUNCTION';

Cancellare una procedura:DROP PROCEDURE nomeprocedura;DROP FUNCTION nomefunzione;


PLpgSQL

• SQL procedurale su Postgres• Attivazione del linguaggio

– host:~ postgres$ createlang --dbname=nomedb plpgsql

• PlpgSLQ offre molte delle funzionalità di PL/SQL con alcune differenze

• Lucidi tratti da:– http://www.php-editors.com/postgres_manual/p_plpgsql-porting.html– http://www.postgresql.org/docs/7.4/interactive/plpgsql.html

10


Differenze fra PLpgSQL e PL/SQL

• In PostgreSQL non ci sono valori di defaultper i parametri

• Le funzioni PostgreSQL possono essere sovraccaricate

• Assegnamenti, cicli and valori condizionali sono simili


Esempio 1

• Incrementare numeri interi (per esempio per definire una propria sequenza)

CREATE FUNCTION incrementsix(integer) RETURNS INTEGER AS '

BEGINRETURN $1 + 6;

END;' LANGUAGE 'plpgsql';


Esempio 2

• Concatenazione di stringhe:CREATE FUNCTION concat_text(TEXT, TEXT) RETURNS TEXT AS '

BEGINRETURN $1 || $2;

END;

' LANGUAGE 'plpgsql';

11


Esempio 3

• Uso delle funzioni coi valori delle tabelleCREATE FUNCTION concat_selected_fields(users) RETURNS text AS '

DECLAREin_t ALIAS FOR $1;

BEGINRETURN in_t.first_name || ‘‘ ’’ ||

in_t.second_name;END;' LANGUAGE plpgsql;


Esempio 3

• SELECT concat_selected_fields(users) from users;

• Restituisce il risultato della funzione


Uso delle funzioni nei triggerCREATE OR REPLACE FUNCTION w_users() RETURNS TRIGGER

AS 'BEGIN

NEW.name = NEW.first_name || '' '' ||NEW.second_name;

RETURN NEW;END;' LANGUAGE plpgsql;

CREATE TRIGGER users_t BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE w_users();

12


Esempio 4CREATE FUNCTION emp_stamp () RETURNS TRIGGER AS '

BEGIN-- Check that empname and salary are givenIF NEW.empname ISNULL THEN

RAISE EXCEPTION ''empname cannot be NULL value'';END IF;IF NEW.salary ISNULL THEN

RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;END IF;

-- Who works for us when she must pay for?IF NEW.salary < 0 THEN

RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;END IF;

-- Remember who changed the payroll whenNEW.last_date := ''now'';NEW.last_user := current_user;RETURN NEW;

END;' LANGUAGE 'plpgsql';


Esempio 4

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp

FOR EACH ROW EXECUTE PROCEDURE emp_stamp();


Altri linguaggi

• Oltre a plpgsql postgres offre altri linguaggi embedded nel DBMS

• Ad esempio:– PL/Tcl– PL/Perl– PL/Python

Laboratorio Di Basi Di Dati 04 P L S Q L E P Lpg S Q L

Technology

Transcript of Laboratorio Di Basi Di Dati 04 P L S Q L E P Lpg S Q L