Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni...

of 79 /79

Transcript of Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni...

Page 1: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql
Page 2: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Manuale PL/Sql - Emanuele Tertulliani 2017 2

Page 3: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Informazioni di base

• PL / SQL è un linguaggio procedurale estensione del linguaggio SQL

• SQL statement sono “nativamente” integrati nel linguaggio PL/SQL ed è possibile chiamare PL / SQL direttamente dalla linea di comando con l’ interfaccia SQL * Plus. Oracle Database 11g ha anche evoluto PL / SQL da linguaggio interpretato ad linguaggio nativamente compilato

• È uno strumento robusto con molte opzioni. Permette di scrivere codice una volta e poi effettuare il deploy nel database insieme con i dati. Può semplificare lo sviluppo di applicazioni, ottimizzare esecuzioni e migliorare l'utilizzo delle risorse nel database. È un linguaggio di programmazione case-insensitive, come SQL. Questo ha comportato nel tempo a creare stili e regole personali e/o a seconda di direttive aziendali.

• Plsql è un linguaggio di programmazione procedurale standard e quindi ha funzioni, procedure, dichiarazioni di variabili, loop, ricorsione, etc. È simile ad altri linguaggi di programmazione procedurale non-object-oriented. Ciò che rende Plsql unico è la sua stretta integrazione con SQL. È facile e più naturale incorporare Sql in Plsql piuttosto che farlo in qualsiasi altro linguaggio di programmazione. Questo lo rende ideale per la scrittura di programmi complessi e di grandi dimensioni che devono interagire con un database Oracle.

Manuale PL/Sql - Emanuele Tertulliani 2017 3

Page 4: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Motore di PL / SQL

• Esegue le porzioni procedurali del codice ma invia al server oracle i comandi SQL

Pl/Sql Block Pl/Sql Block

Pl/Sql (procedural)

Pl/Sql EngineProceduralStatementExecutor

Sql

Oracle Server

SQL Statement Executor

Manuale PL/Sql - Emanuele Tertulliani 2017 4

Page 5: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Vantaggi - 1

• Può essere posizionato sia sul lato client, sia sul lato server

• Si integra perfettamente con gli altri tool Oracle (Es.: Developer) e molti di essi (es: Oracle Forms, Oracle Reports) dispongono di un proprio motore PL/SQL

• Server-side

• I blocchi PL/SQL sono processati dal motore PL/SQL che fa parte del Server Oracle

• Client-side

• Il motore PL/SQL filtra i comandi SQL e li invia SQL al server Oracle mentre esegue direttamente i comandi procedurali

• Diminuisce il carico della rete

• Raggruppa tutte le richieste in un blocco Pl/Sql che va in esecuzione causando una sola chiamata

Manuale PL/Sql - Emanuele Tertulliani 2017 5

Page 6: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Vantaggi - 2

• Struttura a blocchi• Sviluppo modulare

• Condivisione del codice Pl/Sql• Inserendolo in «librerie» condivise

• Portabilità

• Dichiarazione di identificatori• Variabili

• Cursori

• Costanti

• ….

• Gestione degli errori

Manuale PL/Sql - Emanuele Tertulliani 2017 6

Page 7: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Manuale PL/Sql - Emanuele Tertulliani 2017 7

Page 8: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Blocchi PL/Sql

• I blocchi PL/SQL (Block)• Rappresentano l’unità elementare di codice PL/SQL

• Normalmente contengono i comandi sufficienti a eseguire uno specifico compito

• Esistono due tipi di blocchi PL/SQL• Anonymous

• Named: Si tratta di blocchi PL/SQL precompilati che vengono memorizzati nel database

• stored procedure• Function• Trigger• package

Manuale PL/Sql - Emanuele Tertulliani 2017 8

Page 9: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Struttura di un blocco - 1

• Sezione di dichiarazione• Per dichiarare, variabili, costanti, cursori,ecc.• E’ opzionale

• Sezione di esecuzione• Descrive la logica dei comandi• Può contenere istruzioni SQL• E’ obbligatoria

• Sezione di gestione delle eccezioni• Viene eseguita quando si presentano degli errori• E’ opzionale

Attenzione: nella definizione delle procedure e funzioni la clausola DECLARE è implicita

Manuale PL/Sql - Emanuele Tertulliani 2017 9

Page 10: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Struttura di un blocco - 2

N.B.:Ogni istruzione va terminata con un ";", così come la parola chiave END, mentre non si mette per DECLARE, BEGIN ed EXCEPTION.Indentare il codice aumenta la leggibilità e quindi facilita la modifica dello stesso

Manuale PL/Sql - Emanuele Tertulliani 2017 10

Page 11: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Costrutti di programma

• Blocco Anonimo: blocco Pl/Sql senza nome, eseguito all’interno di una applicazione o in modo interattivo

• Store procedure / Function: blocco Pl/Sql memorizzato all’interno di Oracle Server che ammette parametri di Input; Le Functionprevedono il ritorno di un Output

• Package: modulo Pl/Sql che raggruppa funzioni e procedure

• Trigger: blocco Pl/Sql che viene eseguito al verificarsi di un evento DML

Manuale PL/Sql - Emanuele Tertulliani 2017 11

Page 12: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Procedure - 1

• Una procedura è un blocco di codice PL/SQL dotato di un nome che viene mantenuto all’interno del database (stored procedure)

• La clausola IS sostituisce la clausola DECLARE

Es.:

CREATE PROCEDURE nome_procedura [(parametri)] IS

definizioni;

BEGIN

corpo_procedura;

END;

Manuale PL/Sql - Emanuele Tertulliani 2017 12

Page 13: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Procedure - 2

• Una procedura può essere richiamata utilizzando il comando call

CALL nome_procedura([parametri]);

• Parametri è una sequenza di

Nome_variabile TIPO_DATO

che specifica eventuali valori passati in input

• TIPO_DATO non deve specificare lunghezza, precisione o scala

number(3) non è un tipo di dato valido number si!

• Oracle deriva lunghezza, precisione o scala degli argomenti dall’ambiente da cui la procedura è chiamata

Manuale PL/Sql - Emanuele Tertulliani 2017 13

Page 14: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Function

• Le funzioni sono del tutto simili a procedure a meno della clausola RETURN che specifica il tipo di dato restituito

Es.:

CREATE FUNCTION nome_funzione [(parametri)] RETURN <tipo_dato> IS

Definizioni;

BEGIN

Corpo_procedura;

RETURN Variabile;

END;

Manuale PL/Sql - Emanuele Tertulliani 2017 14

Page 15: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Utilizzo delle variabili

• Memorizzazione temporanea dei dati

• Manipolazione dei valori memorizzati

• Riutilizzazione

• Ottimizzazione delle dichiarazioni• %TYPE

• %ROWTYPE

Manuale PL/Sql - Emanuele Tertulliani 2017 15

Page 16: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Gestione delle variabili

• Dichiarazioni ed inizializzazioni (DECLARE):

allocazione di memoria per un determinato tipo di valore

• Assegnare nuovi valori

• ACCEPT• Crea una variabile di sostituzione e permette all’utente di inserirne il valore

• SQL*Plus sostituisce il valore della variabile prima di inviare il blocco al motore PLSQL

• SET VERIFY [ON|OFF]• Permette di attivare|disattivare i messaggi di sostituzione

Manuale PL/Sql - Emanuele Tertulliani 2017 16

Page 17: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Variabili HOST

variable g_stip_mensile number -- non si definisce la dimensione

accept stip_annuale prompt "Inserire stipendio annuale: "; -- messaggio a video

declare

v_sal number(9,2) := &stip_annuale; -- implementazione dell’input

begin

:g_stip_mensile := v_sal/12; -- i : indicano la variabile Host; Queste variabili non hanno lunghezza

end;

/

print g_stip_mensile -- output video

Manuale PL/Sql - Emanuele Tertulliani 2017 17

Page 18: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Input Output con PL/SQL

• PL/SQL non consente di effettuare operazioni di Input/Output

• Per operazioni I/O si fa affidamento all’ambiente in cui il blocco viene eseguito

• SQL*PLUS e le variabili di sostituzione sono un ottimo compromesso

• DBMS_OUTPUT.PUT_LINE• Mostra l’output a video• La procedura scrive l’output su un buffer della SGA da cui può essere letto mediante

il comando .get_line• In SQL Plus l’output può essere visualizzato ponendo SET SERVEROUTPUT ON

(default OFF)• DBMS_OUTPUT è un package e PUT_LINE è una procedure all’interno del package he

accetta in Input una espressione da stampare

Manuale PL/Sql - Emanuele Tertulliani 2017 18

Page 19: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Esecuzione di PL/SQL

• Dato che SQL*Plus non è dotato di un motore PL/SQL deve inviare un blocco anonimo a Oracle

• Un blocco deve essere compilato prima che possa essere eseguito• Controllo sintattico

• Struttura del comando, parole riservate e variabili

• Binding• Controlla che gli oggetti referenziati esistano

• Generazione del p-code• Istruzioni che il motore PL/SQL può eseguire

Manuale PL/Sql - Emanuele Tertulliani 2017 19

Page 20: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Dichiarazione di una variabile PL/SQL

identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

• La dichiarazione deve essere effettuata nella sezione DECLARE

• Per default le variabili sono inizializzate a NULL (uhm …) a meno che non siano attive le «clausole» :=, DEFAULT o NOT NULL

• Se attiva CONSTANT, il valore deve essere assegnato subito in fase di DECLARE

• Le variabili sono dichiarate e inizializzate ogni volta che si accede al blocco

• Due variabili con lo stesso nome devono essere dichiarate in blocchi diversi

Manuale PL/Sql - Emanuele Tertulliani 2017 20

Page 21: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Regole di denominazione

• Naming Conventions• Fino a 30 caratteri, non case sensitive, cominciano sempre con una lettera e

non possono contenere spazi

• Non definire una variabile con il nome della colonna se queste vengono usate contemporaneamente

• Dichiarare una variabile per riga, aiuta la leggibilità

• Convenzioni consigliate• v_nome variabili

• c_nome costanti

• g_nome variabili globali

Manuale PL/Sql - Emanuele Tertulliani 2017 21

Page 22: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Assegnazione

• Sintassi – 1 >>> Assegnazione sempliceidentificatore := expr;

Esempio: v_name := ‘Emanuele’;

c_tax_rate CONSTANT NUMBER(3,2) := 8.25;

• Sintassi – 2 >>> Assegnazione tramite SELECT INTOselect <valore> into <identificatore> from <tabella>;

Esempio: select 1 into v_num from dual;

Indicazioni:• Le SELECT devono restituire un singolo valore• Stringe e date devono essere racchiuse tra apici singoli

• Se la stringa che devo memorizzare contiene un apice, questo deve essere preceduto da un apice singoloEsempio: v_name := ‘Sant’’Antonio’;

Manuale PL/Sql - Emanuele Tertulliani 2017 22

Page 23: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Tipi di variabili

• Scalar• Possono contenere un singolo valore• Corrispondono ai tipi di dati previsti per le tabelle Oracle più poche altre (es:

Boolean)

• Composite• Permettono di manipolare gruppi di campi (record e tabelle)

es: una variabile di tipo %ROWTYPE memorizza un’intera riga

• Reference• Puntatori

• LOB (Large OBjects)• Contengono elementi, chiamati locators, che specificano la posizione di oggetti di

grosse dimensioni (es. immagini) che sono memorizzati separatamente

Manuale PL/Sql - Emanuele Tertulliani 2017 23

Page 24: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Tipi di dati SCALARI

• VARCHAR2 (length): stringhe a lunghezza variabile fino a max 32.767 byte

• CHAR [(lung. max.)]: stringhe a lunghezza fissa fino a max 32.767 byte

• NUMBER [(precisione, scala)]: numeri con virgola mobile e non, dove• precisione: 0-38• scala: -84 to 127Esempio: NUMBER(5,2) -> ddd.dd

• DATE: tipo data, include anche l’ora• Da: January 1, 4712 BC A: December 31, 9999 AD

• BOOLEAN: tipo booleano• TRUE o FALSE o NULL• Non ha nessun tipo corrispondente nei tipi degli attributi

Manuale PL/Sql - Emanuele Tertulliani 2017 24

Page 25: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Attributo %TYPE

• Quando si definisce una variabile PL/SQL per memorizzare il valore di una colonna è necessario assicurarsi la corrispondenza tra i due tipi di dato

• In caso contrario si verificherà un errore PL/SQL durante l’esecuzione

• Un tipo di dato “anchored” evita questo problema• Se cambia la definizione della colonna, cambia anche runtime la definizione della variabile. Si

realizza così l’indipendenza dei dati e si permette ai programmi di adattarsi ai cambiamenti del database

• %TYPE dichiara una variabile in base a:• La definizione di una colonna del database• Un’altra variabile definita precedentemente

• Sintassi:

identificatore_1 tabella.campo%type;

identificatore_2 identificatore_1%type;

Manuale PL/Sql - Emanuele Tertulliani 2017 25

Page 26: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Manuale PL/Sql - Emanuele Tertulliani 2017 26

Page 27: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Delimitatori

Simbolo Descrizione Simbolo Descrizione

+ Addizione >= Maggiore o uguale

- Sottrazione <= Minore o uguale

* Moltiplicazione <> Diverso

/ Divisione != Diverso

= Uguale || Concatenazione

@ Accesso remoto -- Commento

; Fine istruzione /* Inizio commento

> Maggiore */ Fine commento

< Minore := Assegnazione

Manuale PL/Sql - Emanuele Tertulliani 2017 27

Page 28: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Commenti

• Commento su singola riga-- questo è un commento

• Commento su riga multipla/*

questo è un commento

su più righe

*/

Manuale PL/Sql - Emanuele Tertulliani 2017 28

Page 29: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Funzioni SQL in PL/SQL

• Molte funzioni disponibili in SQL possono essere utilizzate nelle espressioni in PL/SQL:

• Funzioni numeriche• Funzioni di stringa• Funzioni di conversione• Funzioni di data

Possono essere assegnate a variabili

• Non sono supportate (direttamente …):• DECODE• Funzioni di gruppo

A causa dei dati scalari ma posso aggirare l’ostacolo utilizzando SQL nel codice seguendo alcuni accorgimenti

Manuale PL/Sql - Emanuele Tertulliani 2017 29

Page 30: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Conversione tra tipi di dato

• PL/SQL tenta inizialmente una conversione dinamica tra tipi di dato, se non ci riesce ritorna un errore

• Effettuare un casting (conversione) esplicito laddove ce ne fosse bisogno, come in SQL

Esempio:DECLAREv_date date;

BEGINv_date := ‘Agosto 01, 2017’;

END;

DECLAREv_date date;

BEGINv_date := to_date(‘01082017’,’DDMMRRRR’);

END;

Manuale PL/Sql - Emanuele Tertulliani 2017 30

Page 31: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Blocchi nidificati – Scope - Lifetime

• PL/SQL permette la creazione di blocchi annidati

• Un blocco annidato diventa un’istruzione

• Un identificatore è visibile nel blocco in cui viene creato e in tutti i suoi sottoblocchi

• Lifetime: Indica l’intervallo durante il quale una variabile esiste in memoria e può contenere un valore

• Lo spazio in memoria è allocato quando la variabile viene dichiarata, è deallocato quando il programma raggiunge il comando END del blocco in cui stata creato

• Scope: La regione del programma in cui referenziare una variabile; Le variabili dichiarate in una blocco PL/SQL sono locali al blocco e sono considerate globali per tutti i sotto blocchi

• La visibilità è inibita se nel blocco viene dichiarata una variabile con lo stesso nome.• Un blocco può fare riferimento a variabili dichiarate nei blocchi padre• Un blocco NON può fare riferimento a variabili dichiarate nei blocchi figli

Manuale PL/Sql - Emanuele Tertulliani 2017 31

Page 32: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Manuale PL/Sql - Emanuele Tertulliani 2017 32

Page 33: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Statement SQL in PL/SQL

Non sono supportati nativamente comandi di tipo:• DDL non è permesso

• CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP VIEW

• DCL non è permesso• GRANT, REVOKE, CREATE USER, DROP ROLE, ALTER USER

Sono supportati:• DML è permesso

• INSERT, UPDATE, DELETE

• TCL è permesso• COMMIT, ROLLBACK, SAVEPOINT

• Comandi SELECT (select … into) per l’assegnazione delle variabili

Manuale PL/Sql - Emanuele Tertulliani 2017 33

Page 34: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Controllo di flusso

Per controllare il flusso logico delle istruzioni si fa uso di due tipologie di costrutto:

• Controllo condizionale

• IF … THEN … END IF;

• IF … THEN … ELSE … END IF;

• IF … THEN … ELSIF … END IF;

• Controllo iterativo

• LOOP -- infinito

• FOR LOOP -- determinato

• WHILE LOOP -- determinato fino ad una condizione

Manuale PL/Sql - Emanuele Tertulliani 2017 34

Page 35: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

IF … THEN … e le sue varianti

� IF condition THEN

statement(s);

END IF;

� IF condition THEN

statement(s);

ELSE

statement(s);

END IF;

� IF condition THEN

statement(s);

ELSIF

… … … … … …

END IF;

Manuale PL/Sql - Emanuele Tertulliani 2017 35

Page 36: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Gestione NULL

• I valori nulli si gestiscono tramite IS NULL, oppure IS NOT NULL

• Il risultato tra NULL +-*/ (operando) è sempre NULL

• Il risultato tra NULL < > <= >= = <> != (valore) è sempre NULL

Manuale PL/Sql - Emanuele Tertulliani 2017 36

Page 37: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Tabella di confronto logico (AND – OR – NOT)

A B A AND B A OR B

TRUE TRUE TRUE TRUE

TRUE FALSE FALSE TRUE

FALSE TRUE FALSE TRUE

FALSE FALSE FALSE FALSE

Manuale PL/Sql - Emanuele Tertulliani 2017 37

A NOT A

TRUE FALSE

FALSE TRUE

Page 38: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Controllo iterativo

• Ripetere una o più istruzioni più volte

• Tre tipologie di LOOP:

• LOOP di base

• FOR LOOP

• WHILE LOOP

• L’istruzione EXIT termina un loop (forzatamente)

Manuale PL/Sql - Emanuele Tertulliani 2017 38

Page 39: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

LOOP di base o «Infinito»

• Racchiude una sequenza di istruzioni tra le parole chiave LOOP e END LOOP

• Termina con l’istruzione EXIT• Può essere presente più volte nel ciclo• Può essere inserita in una IF• Può essere seguita dalla parola chiave WHEN• In sua assenza il ciclo diventa infinito!!!

• Sintassi

LOOP

[istruzioni;]

EXIT [WHEN condizione];

[istruzioni;]

END LOOP;

Manuale PL/Sql - Emanuele Tertulliani 2017 39

Page 40: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

EXIT

• EXIT svolge un ruolo fondamentale nel loop di base

• Se viene inserita:• subito dopo la parola chiave LOOP e prima di ogni altra istruzione, il ciclo

potrebbe non eseguire alcuna istruzione se la condizione di EXIT viene immediatamente verificata; Ovviamente, senza condizione (WHEN o all’interno di una IF) non ha molto senso metterla qui

• alla fine, subito prima della parola chiave END LOOP, il ciclo eseguirà tutte le istruzioni almeno una volta

Manuale PL/Sql - Emanuele Tertulliani 2017 40

Page 41: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

FOR LOOP (numerici)

• Presentano una istruzione di controllo prima della parola chiave LOOP• Il controllo è basato su due limiti, inferiore (lower bound) e superiore (upper

bound)

• Il contatore che computa il numero di iterazioni non viene definito nella DECLARE in quanto è definito implicitamente come numerico e si incrementa di 1 ad ogni iterazione

• Lower e upper bound possono essere numeri, variabili, o espressioni che essere sempre valutati come interi

• Il counter è definito e può essere referenziato solo all’interno del ciclo

• Può essere utilizzato il comando EXIT per terminare in maniera anticipata il ciclo

Manuale PL/Sql - Emanuele Tertulliani 2017 41

Page 42: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

FOR LOOP

• SintassiFOR counter IN [REVERSE] lower..upper LOOP

statement1;statement2;. . .

END LOOP;

Dove:• Counter, variabile di conteggio, dichiarato implicitamente• REVERSE, parola chiave per il conteggio inverso• Lower, limite iniziale• Upper, limite finale

Manuale PL/Sql - Emanuele Tertulliani 2017 42

Page 43: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

WHILE LOOP

• Esegue una serie di istruzioni finché la condizione di controllo risulta vera (TRUE)

• Il controllo della condizione avviene prima dell’elaborazione• Può essere utilizzato il comando EXIT per terminare in maniera anticipata il

ciclo

Sintassi 1: Sintassi 2:WHILE condition LOOP WHILE condition1 LOOP

statement1; statement1;statement2; statement2;. . . EXIT WHEN condition2

END LOOP; . . .END LOOP;

Manuale PL/Sql - Emanuele Tertulliani 2017 43

Page 44: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

LOOP NIDIFICATI e LABEL

• È possibili nidificare i loop in più livelli

• L’interruzione di un loop interno non termina il loop più esterno a meno che non sia stata sollevata un’eccezione

• È possibile associare delle LABEL (etichette) ai loop

• Il nome della LABEL va definito prima della parola chiave LOOP ed inserito tra << >>

• È possibile ripetere il nome della LABEL dopo la chiusura dl loop (END LOOP) ma senza inserirlo tra << >>

Esempio:<<Outer_loop>>

WHILE v_outercounter <= 3 LOOP<<Inner_loop>>

FOR v_innercounter IN 1.. 5 LOOP

v product := v outercounter* v innercounter;

DBMS_OUTPUT.PUT_LINE(v_outercounter || ' x ' ||v_innercounter || ' = ' || v_product);

END LOOP Inner_loop;

DBMS_OUTPUT.PUT_LINE('------------------------');

v_outercounter := v_outercounter + 1;

END LOOP Outer_loop;

Manuale PL/Sql - Emanuele Tertulliani 2017 44

Page 45: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Manuale PL/Sql - Emanuele Tertulliani 2017 45

Page 46: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Tipi di dati composti

• I tipi di dati composti memorizzano un insieme (collection) di elementi

• Sono caratterizzati da elementi (in genere scalari) interni

• Sono riutilizzabili

• Sono mantenuti in memoria

• Tipo RECORD

• Tipo TABELLA

Manuale PL/Sql - Emanuele Tertulliani 2017 46

Page 47: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

RECORD

• Un record PL/SQL è un gruppo di attributi correlati memorizzati in una struttura, ognuno col proprio nome e tipo

• Un record PL/SQL è quindi un tipo composto in cui i singoli campi sono trattati come un’unità logica

• Un record può essere NOT NULL ma i campi senza un valore iniziale sono inizializzati a NULL (Attenzione alla gestione!!!)

• I RECORD vengono definiti nella sezione DECLARE

• Un record può essere componente di un altro record

• Sono convenienti per gestire le righe dell’ active set, poiché permettono di eseguire il FETCH di un’intera un intera riga.

• I valori della riga vengono caricati nei campi corrispondenti

Manuale PL/Sql - Emanuele Tertulliani 2017 47

Page 48: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

RECORD – Dichiarazione 1

• I campi si dichiarano come fossero delle variabili ed è possibile utilizzare il %TYPE

• Non esiste un tipo predefinito e quindi va sempre dichiarato

• Si può costruire un RECORD con la stessa struttura di una tabella utilizzando il nometabella%ROWTYPE al posto della definizione della struttura

• L’accesso ai campi avviene con la sintassi nome_record.nome_campo

• Il campo può essere utilizzato come una qualunque variabile

Manuale PL/Sql - Emanuele Tertulliani 2017 48

Page 49: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

RECORD – Dichiarazione 2

Sintassi 1:TYPE nome_record IS RECORD (

nome_campo { tipo_campo|variabile%TYPE

«struttura» |tabella.colonna%TYPE }[[NOT NULL]

{ := | DEFAULT } espressione ][,nome_campo … ]. . .

);

Identificatore nome_record; -- variabile del tipo RECORD

Sintassi 2:

identificatore nometabella%ROWTYPE;

Manuale PL/Sql - Emanuele Tertulliani 2017 49

Page 50: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

ROWTYPE

• Dichiara una variabile di tipo record basandosi su un insieme di campi appartenenti a una tabella, vista o cursore

• I campi nel record assumono il nome e il tipo di quelli nella tabella, vista o cursore

• Ci si riferisce a un membro di un campo utilizzando la sintassi

recordvariable_name.fieldname

• Il tipo e il numero delle colonne nel database può cambiare

• E’ necessario anteporre a %ROWTYPE il nome della tabella, vista o cursore a cui il record è associato

Manuale PL/Sql - Emanuele Tertulliani 2017 50

Page 51: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

TABELLA - 1

• Contiene una chiave primaria con tipo BINARY_INTEGER che indicizza la tabella PL/SQL, utilizzata per un accesso diretto alla stessa

• Contiene una colonna con un campo scalare oppure un Record, entrambi al fine di memorizzare un dato

• Sono dinamiche, cioè non hanno una dimensione fissa

• Di fatto, sono un insieme di Record

• L’accesso ai dati di una tabella «base» avviene con la sintassi nometabella(valorechiave)

• L’accesso ai dati di una tabella basata su Record avviene con la sintassi nometabella(valorechiave).nomecampo

• Il valore della chiave può essere anche negativo e l’indicizzazione può anche non partire dal valore «1» perché BINARY_INTEGER ha un range che va da -2.147.483.647 a + 2.147.483.647

Manuale PL/Sql - Emanuele Tertulliani 2017 51

Page 52: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

TABELLA - 2

Sintassi:

TYPE nome_tab IS TABLE OF tipo_campo

|variabile%TYPE

«struttura» |tabella.colonna%TYPE

[[NOT NULL]

[INDEX BY BINARY INTEGER];

Identificatore nome_tab; -- variabile del tipo TABELLA

Manuale PL/Sql - Emanuele Tertulliani 2017 52

Page 53: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

TABELLA - METODI

• Un metodo è una procedura built-in o una funzione che opera su una tabella Pl/Sql

• Non tutti i metodi accettano parametri

Sintassi: Nome_tabella.nome_metodo[(parametri)]

Manuale PL/Sql - Emanuele Tertulliani 2017 53

METODO DESCRIZIONE

EXISTS(n) Restituisce TRUE se esiste l’elemento n della tabella

COUNT Numero di elementi contenuti in tabella

FIRST - LAST Restituisce il primo e l’ultimo elemento, NULL se la tabella è vuota

PRIOR(n) Restituisce l’indice che precede l’indice n

NEXT(n) Restituisce l’indice che segue l’indice n

TRIM(n) Rimuove n elementi dalla fine della tabella

DELETE Rimuove tutti gli elementi di una tabella; DELETE(n) rimuove l’elemento n; DELETE(m,n) rimuove gli elementi compresi tra m a n

Page 54: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Manuale PL/Sql - Emanuele Tertulliani 2017 54

Page 55: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori – Panoramica

• Ogni volta che si sottopone al sistema un comando SQL, Oracle alloca un’area di memoria in cui il comando viene analizzato ed eseguito. Tale area è detta context area.

• Un cursore è un puntatore alla locazione di memoria di una contextarea

• Ogni comando SQL eseguito da Oracle ha associato un proprio cursore

Manuale PL/Sql - Emanuele Tertulliani 2017 55

Page 56: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori – Tipologia

Esistono 2 tipologie di Cursori:

• Impliciti :• sono dichiarati implicitamente da Pl/Sql ogni volta che viene eseguita una istruzione

DML e/o una SELECT. Il cursore è gestito automaticamente, non si può utilizzare OPEN, FETCH, CLOSE per controllarlo

• Espliciti :• Sono dichiarati e maneggiati direttamente dal codice (programmatore)

• Sono utilizzati per processare le singole righe restituite da un comando SQL multiple-row

• Puntano alla riga corrente nell’active set

Manuale PL/Sql - Emanuele Tertulliani 2017 56

Page 57: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Impliciti - Attributi

• E’ possibile utilizzare gli attributi del cursore per verificare il risultato di un comando

Manuale PL/Sql - Emanuele Tertulliani 2017 57

Attributo Descrizione

SQL%ROWCOUNT Numero di righe coinvolte dal più recente comando SQL

SQL%FOUND Attributo Booleano che è TRUE se l’ultimo comando SQL ha coinvolto almeno una riga

SQL%NOTFOUND Attributo Booleano che è TRUE se l’ultimo comando SQL non hacoinvolto nemmeno una riga

SQL%ISOPEN E’ sempre FALSE poiché PL/SQL chiude i cursori impliciti immediatamente dopo l’esecuzione

Page 58: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti• Utilizzati per elaborare singolarmente ogni riga restituita da un’istruzione SELECT

• Il set di righe restituite dall’istruzione SELECT è detto ACTIVE SET

• Operazioni sui cursori:

• Dichiarazione : SELECT di estrazione dei dati di interesse e tenuti in un area di memoria

• Apertura : Oracle può elaborare i dati estratti aprendo l’ara di memoria

• Fetch : elaborazione dei dati estratti

• Chiusura : Oracle chiude l’area di memoria

• Gestione anche tramite attributi

Manuale PL/Sql - Emanuele Tertulliani 2017 58

1001 Mario Rossi

1002 Antonio Bianchi

1003 Marco Blu

1004 Luigi Verdi

Active set

CursoreRiga corrente

Page 59: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti

Elaborazione

NO

YES

Crea la context area Esegue la query Carica la riga Controlla l’esistenza di Rilascia l’active set

Definisce la query che Identifica l’active set corrente nelle variabili ulteriori righe Disabilita il

produce i dati Posiziona il cursore prima Fa avanzare il cursore alla Esegue nuovamente il cursore e rilascia

della riga iniziale prossima riga FETCH se è stata trovata una riga le risorse

Manuale PL/Sql - Emanuele Tertulliani 2017 59

DECLARE OPEN FETCH CLOSEEMPTY?

Page 60: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti - Dichiarazione

Sintassi

CURSOR cursor_name ISselect_statement;

• select_statement è un qualsiasi comando SELECT• Può includere join, operatori di set e subquery

• Se è necessario processare le righe in una determinata sequenza si può utilizzare la clausola ORDER BY nella query

• E’ possibile fare riferimento a variabili all’interno della query, ma queste devono essere definite anticipatamente (parametri)

Nota: l’istruzione select NON deve contenere la clausola INTO, perché la «variabile» è il cursore stesso

Manuale PL/Sql - Emanuele Tertulliani 2017 60

Page 61: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti - Apertura

SintassiOPEN cursor_name;

• Alloca la memoria necessaria per memorizzare il risultato della query

• Esegue l’analisi sintattica e semantica della query

• Esegue l’interrogazione e identifica l’active set

• Posiziona il puntatore prima della prima riga nell’active set• Le righe non vengono caricate nelle variabili fino all’esecuzione all’esecuzione del

comando FETCH

• Non si verifica alcuna eccezione se la query non restituisce valori

Manuale PL/Sql - Emanuele Tertulliani 2017 61

Page 62: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti - FETCH

SintassiFETCH cursor name INTO [ variable1, variable2, ...]

| record_name];

• I dati possono essere inseriti in un record o in un insieme di variabili• La FETCH recupera le righe dell’active set una alla volta• Dopo un FETCH, il cursore avanza alla prossima riga dell’active set e la riga

letta può essere manipolata per ulteriori elaborazioni• Dopo ogni FETCH è necessario verificare se il cursore contiene delle righe

• Se un cursore non acquisisce valori l’active set è stato completamente elaborato• Non vengono create delle eccezioni• Le variabili/record mantengono i valori precedenti

Manuale PL/Sql - Emanuele Tertulliani 2017 62

Page 63: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti - Chiusura

Sintassi

CLOSE cursor name;

• Chiude il cursore dopo aver completato l’elaborazione

• Disabilita il cursore rendendo indefinito l’active set

• Non è possibile eseguire FETCH su un cursore chiuso• Provocherebbe una eccezione di tipo INVALID_CURSOR

• La riapertura del cursore provocherà la riesecuzione dell’interrogazione

Manuale PL/Sql - Emanuele Tertulliani 2017 63

Page 64: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti - Attributi

Attributo Tipo Descrizione

%ISOPEN Boolean Restituisce TRUE se il cursore è open

%NOTFOUND Boolean Restituisce TRUE se il FETCH più recente non ha restituito righe

%FOUND Boolean Restituisce TRUE se il FETCH più recente ha restituito righe

%ROWCOUNT Number Restituisce il numero totale di righe restituite (ossia fetched)

Manuale PL/Sql - Emanuele Tertulliani 2017 64

Page 65: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti – Attributo %ISOPEN

Esempio

IF NOT cursor_name%ISOPEN THEN

OPEN cursor_name;

END IF;

LOOP

FETCH cursor_name . . . . . .

EXIT WHEN cursor_name%ROWCOUNT > 10

OR

cursor_name%NOTFOUND;

. . .

. . .

END LOOP;

CLOSE cursor_name;

Manuale PL/Sql - Emanuele Tertulliani 2017 65

Page 66: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori Espliciti – FOR LOOP

• Elabora le righe di un cursore in modo rapido• Si evita di:

• Dichiarare il record• Aprire il cursore• Effettuare la fetch dei dati• Chiudere il cursore

Sintassi

FOR record_name IN cursor_name LOOPstatement1;statement2;. . .

END LOOP;

Manuale PL/Sql - Emanuele Tertulliani 2017 66

Page 67: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Manuale PL/Sql - Emanuele Tertulliani 2017 67

Page 68: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori FOR LOOP - SUBQUERY

BEGIN

FOR rec_cursor IN (SELECT . . . FROM . . .

) LOOP

. . .

. . .

END LOOP;

END;

Manuale PL/Sql - Emanuele Tertulliani 2017 68

Page 69: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Cursori con Parametri

• Passare i valori dei parametri ad un cursore quando esso viene aperto e la query viene eseguita

• Aprire un cursore esplicito più volte, utilizzando ogni volta un activeset diverso

CURSOR cursor_name [(parameter_name datatype, ...)] IS

select_statement;

Manuale PL/Sql - Emanuele Tertulliani 2017 69

Page 70: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Clausola FOR UPDATE

• Nella dichiarazione del cursore si può utilizzare la clausola come i seguito

SELECT ...FROM...FOR UPDATE [OF column_reference][NOWAIT];

• Applica un lock alle righe selezionate dal cursore in modo che sia possibile modificare o cancellare i valori all’interno del codice

• Il lock è applicato al momento dell’apertura del cursore non durante la fase di fetch

• Il lock è rilasciato al momento del COMMIT o ROLLBACK da eseguire al termine del ciclo• L’esecuzione di COMMIT o ROLLBACK per ogni riga provoca errore (ORA-01002)

• Se il cursore applica una selezione su più tabelle tramite FOR UPDATE è possibile limitare il lock a una sola tabella. Il lock è applicato solo alle righe delle tabelle di cui è citato un campo nella clausola FOR UPDATE.

• La clausola FOR UPDATE è l’ultima di ogni query di SELECT

• NOWAIT indica al server di non attendere se sulle tabelle è attivo un lock di un’altra sessione.• Si verifica una exception• Il controllo è restituito al programma che può eseguire altre operazioni prima di tentare di riacquisire il lock

Manuale PL/Sql - Emanuele Tertulliani 2017 70

Page 71: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Clausola WHERE CURRENT OF

WHERE CURRENT OF cursor ;

• Referenzia la riga corrente di un cursore esplicito.

• Permette di eseguire UPDATE o DELETE della riga corrente utilizzando una clausola WHERE semplificata.

• Non richiede di creare la condizione che specifichi a quale riga applicare l’operazione poiché questa viene applicata alla riga corrente.

• E’ necessario utilizzare FOR UPDATE nella definizione del cursore in modo da applicare un lock sulla tabella

• In caso contrario si verificherà un errore

Manuale PL/Sql - Emanuele Tertulliani 2017 71

Page 72: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Esempio di WHERE CURRENT OF

CREATE PROCEDURE Esempio ISCURSOR cur IS

SELECT s.student_id, z.cityFROM student s, zipcode zWHERE z.city = 'Brooklyn'

AND s.zip = z.zipFOR UPDATE OF phone;

BEGINFOR rec IN cur LOOP

UPDATE studentSET phone = '718'|| substr(phone,4)

WHERE CURRENT OF cur;

END LOOP;COMMIT;

END;

Manuale PL/Sql - Emanuele Tertulliani 2017 72

Page 73: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Manuale PL/Sql - Emanuele Tertulliani 2017 73

Page 74: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Eccezioni – Concetti base 1

• Cosa è una exception?• Un identificatore PL/SQL che viene valorizzato durante l’esecuzione di un blocco• L’esecuzione viene trasferita al corrispondente gestore dell’eccezione nella sezione

exception del blocco

• Come avviene la valorizzazione?• Automaticamente (implicitamente) quando si verifica un errore runtime• Esplicitamente se nel codice è presente l’istruzione RAISE

• Come vengono gestite?• Includendo una routine corrispondente nella sezione exception

• Cosa avviene in caso contrario?• Il blocco PL/SQL termina con un errore• L’eccezione è propagata all’applicazione chiamante• SQL*Plus mostra il corrispondente messaggio di errore

Manuale PL/Sql - Emanuele Tertulliani 2017 74

Page 75: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Eccezioni – Concetti base 2

• Il rilevamento e la gestione delle eccezioni fanno capo alla sezione EXCEPTION

• Possono essere definiti molti tipi di eccezioni ognuno associato a un proprio insieme di comandi

• Ogni gestore è identificato da una clausola WHEN, che specifica una o più eccezioni, seguita da un insieme di comandi

• Si può verificare una sola eccezione per volta

• Il gestore OTHERS

• Controlla ogni eccezione non trattata esplicitamente

• Deve essere l’ultima eccezione nella lista

Manuale PL/Sql - Emanuele Tertulliani 2017 75

Page 76: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Eccezioni – Tipologia e Sintassi

Esistono 2 tipi di Eccezioni:• Predefinite da Oracle

• L’eccezione non deve essere dichiarata

• Definite dall’utente• L’eccezione deve essere dichiarata

EXCEPTIONWHEN exception1 [OR exception2 . . .] THEN

statement1;statement2;. . .

[WHEN exception3 [OR exception4 . . .] THENstatement1;statement2;

. . .][WHEN OTHERS THEN

statement1;statement2;

. . .]

Manuale PL/Sql - Emanuele Tertulliani 2017 76

Page 77: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Alcune Eccezioni Predefinite

Eccezione Descrizione

NO_DATA_FOUND (ORA-01403) SELECT NON ha tornato alcun valore (0 righe)

TOO_MANY_ROWS (ORA-01422) SELECT ha tornato più valori (1 o più righe)

VALUE_ERROR (ORA-06502) Si è verificato un errore aritmetico, numerico, di conversione o su un vincolo

ZERO_DIVIDE (ORA-01476) Tentativo di divisione per 0

DUP_VAL_ON_INDEX (ORA-00001) Tentativo di duplicare un valore in una colonna soggetta ad un vincolo di univocità

INVALID_NUMBER (ORA-01722) Conversione da stringa a numero non riuscita

Manuale PL/Sql - Emanuele Tertulliani 2017 77

Page 78: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Funzioni di gestione Eccezioni

• SQLCODE

• Restituisce il valore numerico del codice di errore

• SQLERRM

• Restituisce il messaggio associato al numero di errore

• Possono essere assegnate a variabili Pl/Sql e/o mandate in Output al programma

Manuale PL/Sql - Emanuele Tertulliani 2017 78

Page 79: Manuale PL/Sql -Emanuele Tertulliani 2017 2 · Motore di PL / SQL • Esegue le porzioni procedurali del codice ma invia al server oraclei comandi SQL Pl/Sql Block Pl/Sql Block Pl/Sql

Eccezioni definite dall’Utente

DECLARE

exception_utente EXCEPTION;

BEGIN

update table_name

set instruction

where condition;

IF SQL%NOTFOUND THEN

RAISE exception_utente;

END IF;

COMMIT;

EXCEPTION

WHEN exception_utente THEN

statement1;

. . .

END;

Manuale PL/Sql - Emanuele Tertulliani 2017 79