43 3. IL DATABASE ORACLE
In questo capitolo si descrivono i concetti base inerenti al database Oracle. Sono illustrate le operazioni da compiere per preparare un ambiente di test con il quale poter sviluppare codice in sicurezza, quali la creazione del database e l’importazione dei dati dall’istanza di
“produzione”. Questa è l’istanza attualmente caricata sul server per la gestione dell’azienda.
Successivamente è presentata una breve panoramica sul linguaggio di programmazione della piattaforma Oracle : il linguaggio PL/SQL. Tutto ciò è essenziale per la comprensione sia della parte di codice preesistente in azienda, sia di quella scritta per realizzare l’automa dei prelievi.
Tutte le procedure illustrate in questo capitolo sono state testate sotto ambienti che supportano Microsoft Windows.
3.1 Preparazione dell’ambiente di test
Le operazioni per la preparazione di un ambiente di test Oracle sono due:
• Creazione del database privo di dati e dell’istanza Oracle server
• Importazione dei dati: per popolare l’istanza appena creata 3.1.1 Creazione del database e dell’istanza
Si fa notare che esiste una differenza fra database e istanza del server. Il database è l’involucro, all’interno del quale, si conservano tutte le tabelle con i relativi dati. L’istanza del server è il processo che gira all’interno del database che andrà a leggere e scrivere nel database.
Possiamo avere, quindi, più istanze che si riferiscono allo stesso database. Nel nostro caso avremo una sola istanza e quindi la differenza fra istanza e database potrebbe non risultare così marcata.
3.1.1.1 Prerequisiti
L’installazione del pacchetto software server Oracle versione 9.2i è necessaria per la creazione del database e dell’istanza. E’ possibile scaricare in maniera del tutto gratuita, dopo una breve registrazione, dal sito Oracle Technology Network [8] la versione completa dell’Oracle server.
3.1.1.2 Procedura di creazione del database assistita
La creazione del database e dell’istanza è realizzata mediante l’Assistente alla configurazione del database presente all’interno della suite Oracle Server 9.2i. Una volta lanciato ci troviamo di fronte alla schermata in Fig. 29. Fare click su avanti e selezionare, nella schermata successiva crea un database e fare click su avanti. Selezionare come modello del database New Database e fare click su avanti.
Fig. 29 Assistente alla configurazione del database
44 Nella schermata successiva sono richiesti il nome globale del database e il SID (ID di sistema). Come nome globale del database si può inserire un identificativo univoco come ad esempio un dominio (server.miodominio.it)
Il SID, invece, identifica in modo univoco l’istanza che stiamo creando rispetto a tutte le altre presenti all’interno del nostro sistema. Inseriamo come valore del SID ad esempio TEST e facciamo click su avanti.
Fra le funzioni del database togliamo Example schemas e facciamo click su avanti.
Selezionare come modalità di funzionalità del database Modalità server dedicato e fare click su avanti.
Adesso ci troviamo di fronte alla schermata di Fig. 30 nella quale dobbiamo configurare i parametri di inizializzazione:
quantitativi di memoria da associare all’istanza, set di caratteri del database ecc. Una possibile configurazione su un pc con 2 GB di memoria ram installata è: Shared Pool 100 MB, Cache Buffer 300 MB, Pool Java 32 MB, Large Pool 8 MB e PGA 50 MB. Come set di caratteri scegliere, dal menu a schede set di caratteri, WE8ISO8859P1.
Come ultimi due passi premere due volte avanti e attendere che l’assistente crei il database.
3.1.1.3 Configurazione per il supporto al gestionale BMS
Il passo successivo è andare a personalizzare l’installazione fatta con l’Assistente di configurazione affinché il gestionale BMS funzioni correttamente sul database di test.
Per far ciò, apriamo in modalità standalone il programma Enterprise Manager Console, presente nella suite Oracle Server, questo permette la gestione e la configurazione dei database presenti su una macchina.(Fig. 31) Andando ad accedere al database TEST inseriamo come credenziali di accesso: utente system, password system e come ruolo SYSDBA.
Dobbiamo andare a creare tre utenti. La creazione degli utenti si realizza posizionandosi sotto sicurezza Æ utenti. Fare click con il tasto destro del mouse e selezionare crea. Ci troveremo di fronte alla schermata di configurazione dei parametri dell’utente di Fig. 32.
Inseriamo come Nome: CONT_AMM e come password CONT_AMM. Specifichiamo come ruolo, nell’apposito menù a schede DBA e premiamo Crea. Ripetere la stessa procedura per creare l’utente CONT_SVI e CONT_INT.
Come ultimo passo dobbiamo creare le tablespace che andranno a contenere le tabelle da importare. Per fare ciò posizionarsi sotto MemoriaÆ Tablespace. Fare click con il tasto destro del mouse e selezionare Crea. Nel campo Nome inserire BMS_DATI e nel riquadro datafile assegnare come dimensioni 8000 MB. Ripetere la procedura per creare le rimanenti tablespace BMS_INDICI (2000 MB), TMP(10 MB), MEDIA_SPOOL (1000 MB), TEMP2 (10 MB). Tra parentesi sono state indicate le dimensioni da impostare nel riquadro datafile per ogni tablespace.
Fig. 30 Parametri di inizializzazione
45 3.1.2 Importazione dei dati
L’istanza appena creata è solo uno scheletro che contiene informazioni sulla struttura del database creato: gli utenti con i relativi permessi, le aree del disco dove andare a memorizzare i dati, le quantità di memoria ram di sistema da dedicare all’istanza ecc.
Si deve andare, quindi, ad importare tutte le tabelle necessarie per il corretto funzionamento del gestionale e i dati in esse contenuti.
Tutto ciò si ricava andando a creare un file immagine che è un’esportazione della banca dati contenuta nel database attualmente in linea.
Questa immagine si crea digitando il seguente comando dalla prompt dei comandi nella macchina server che contiene l’istanza di produzione:
exp CONT_AMM/password@istanza_di_produzione file= nome_del_file.dmp, full = Y
dove CONT_AMM è l’utente con privilegi di amministratore all’interno dell’istanza di produzione, password è la password dell’utente, file specifica il nome dell’immagine che sarà creata e l’opzione full crea un esportazione completa di tutte le tabelle del database.
Una volta ricavato questo file è possibile andare a importarlo all’interno dell’istanza di test con il seguente comando, lanciato dal prompt dei comandi della macchina dove è stata creata l’istanza test:
imp CONT_AMM/password@istanza_di_test
L’esecuzione di questo comando richiede una serie di parametri all’utente. L’operatore confermerà tutti i parametri di default premendo il tasto invio a eccezione di:
• nome file da importare: inserire il nome del file usato per l’esportazione
• il nome del’user : inserire CONT_AMM questo provocherà l’importazione delle sole tabelle appartenenti al tablespace di questo utente. Queste sono le uniche necessarie per il corretto funzionamento del gestionale.
Fig. 31 Enterprise manager console
46 3.2 Linguaggio PL/SQL
Il linguaggio PL/SQL è l’estensione di programmazione procedurale, ed a volte ad oggetti, di SQL, fornita da Oracle, esclusivamente per Oracle.
In PL/SQL, PL significa procedural language. PL/SQL è un linguaggio proprietario non disponibile al di fuori di Oracle Database. È un linguaggio di terza generazione (3GL) con costrutti simili a quelle di altri linguaggi 3GL compresi dichiarazioni di variabili, cicli, gestione di errori e così via.
In PL/SQL, SQL significa structured query language. Si utilizza SQL per effettuare le operazioni di SELECT, INSERT, UPDATE e DELETE sui dati; lo si utilizza per creare e mantenere oggetti e utenti e per controllare i diritti di accesso alle proprie istanze.
SQL è la porta di accesso ai dati al database. È un linguaggio di quarta generazione (4GL) creato in modo da essere facile da utilizzare e veloce da imparare. La sintassi fondamentale di SQL, non è una creazione di Oracle; in realtà è nato dal lavoro effettuato dal Dr. Codd e da IBM all’inizio degli anni settanta. L’American National Standards Institute (ANSI) riconosce SQL e pubblica standard per il linguaggio.
Oracle supporta SQL standard ANSI. [9]
Nel proseguo di questo paragrafo sono illustrati i concetti base per quanto riguarda la parte del linguaggio procedurale (PL) per facilitare la lettura e la comprensione del codice.
Si trascurano la descrizione dei concetti base sia dell’SQL sia della struttura di un database relazionale in quanto ritenuti concetti ampiamente conosciuti dalla maggioranza degli sviluppatori.
Per ogni eventuale approfondimento sul database Oracle e il linguaggio PL/SQL consiglio la letture di [9] e [10]. Queste sono state due fonti molto utili durante lo sviluppo del codice PL/SQL.
3.2.1 L’unità base: il blocco
In PL/SQL, l’unità fondamentale di programma viene chiamata blocco. I blocchi contengono set di istruzioni che Oracle deve eseguire. I programmi PL/SQL sono costituiti come minimo da un blocco.
La struttura minima di un blocco PL/SQL è costituita da un BEGIN e un END con almeno un comando eseguibile tra di essi. Ad esempio il Listato 1 rappresenta la dichiarazione minima possibile.[9]
Fig. 32 Creazione di un utente
47
begin
--commento singola linea /*commento
multilinea*/
null end
Nel Listato 1 sono messi in evidenza anche i due tipi di commenti al codice ammessi in PL/SQL: quello singola linea e quello multilinea.
La struttura completa di un blocco è data nel Listato 2. La parola chiave declare è opzionale e nella sua sezione devono essere dichiarate tutte le variabili utilizzate all’interno della sezione begin. Nella sezione exception si possono gestire le eventuali eccezioni.
declare --opzionale
--Elenco delle variabili
begin --obligatorio
--Istruzioni di processo
exception --opzionale --Gestione degli errori
end;
I blocchi si dividono in due tipi: quelli anonimi e quelli con nome. Il blocco nel Listato 1 è un blocco anonimo. Questo viene compilato ad ogni invio al database e non può essere memorizzato all’interno del database e non può essere chiamato da altri blocchi.
In PL/SQL esistono molte tipologie di blocchi con nome. Nei prossimi paragrafi si presentano i due tipi di blocchi con nome più utilizzati in questo lavoro:
• Funzioni
• Store procedure 3.2.2 Blocchi con nome
Le store procedure e le funzioni PL/SQL si comportano in maniera molto simile a procedure e funzioni di altri linguaggi di terza generazione e ne condividono molte proprietà.
Questi blocchi sono scritti all’interno di un editor di testo o ad un ambiente di sviluppo, come PL/SQL Developer, e poi inviati al database per essere compilati. Una volta compilati questi risiedono nel database sia sotto forma di codice compilato sia come codice sorgente.
Un blocco con nome deve iniziare con una dichiarazione di creazione poiché, all’atto dell’invio al database, questo deve essere creato, o aggiornato se già esiste, come un qualsiasi altro oggetto presente all’interno del database.
3.2.2.1 Store procedure
In PL/SQL una procedura prende il nome di store procedure. La struttura di una dichiarazione di creazione di una procedura è la seguente:
create or replace procedure procedura_1(Name1 in out integer, Name2 in out varchar2, ...) is
begin
Listato 1 Blocco PL/SQL minimo
Listato 2 Blocco PL/SQL completo
48
--istruzioni della procedura end procedura_1;
Con questa dichiarazione create or replace procedure si dice al compilatore Oracle di creare se non esiste già all’interno del database, o di sostituire la procedura di nome procedura_1. I valori indicati tra parentesi tonde sono i parametri formali della procedura.
La sintassi per la definizione dei parametri formali è: nome del parametro modalità del parametro tipo del parametro.
Le modalità del parametro ammesse sono:
• In: il valore del parametro reale viene passato alla procedura quando questa viene chiamata. All’interno della procedura, il parametro formale agisce come una costante.
• Out: qualsiasi valore abbia il parametro reale quando viene chiamata la procedura viene ignorato. All’interno della procedura, il parametro formale agisce come una variabile non inizializzata e di valore pari a null. Si può leggere e scrivere in questa variabile. Al termine della procedura il valore del parametro formale viene copiato all’interno del parametro reale del chiamante.
• In Out: questa modalità è una combinazione di In e Out. Il valore del parametro reale viene passato alla procedura quando viene chiamata. All’interno della procedura, il parametro formale agisce come una variabile inizializzata e si può leggere e scrivere in questa. Al termine della procedura il valore del parametro formale viene copiato all’interno del parametro reale del chiamante.
Se non viene specificata nessuna modalità per un parametro, per impostazione predefinita è In.
3.2.2.2 Funzioni
Una funzione è molto simile ad una procedura. Entrambe prendono parametri, che possono essere in qualsiasi modalità. Entrambe sono forme diverse di blocchi PL/SQL con sezioni di dichiarazione, begin-end e di eccezione. Entrambe possono essere memorizzate all’interno del database. Tuttavia la chiamata di una procedura è una dichiarazione PL/SQL in sé, mentre una chiamata ad una funzione è parte di un’espressione in quanto una funzione ritorna sempre un valore. [9]
La sintassi per la creazione di una funzione è molto simile alla sintassi per una procedura come è visibile dal Listato 4.
create or replace function funzione_test(Name1 in integer, Name2 in out varchar2,....) return date is
--dichiarazione della variabile di ritorno data date;
begin
return data;
end funzione_test;
Con questa dichiarazione create or replace function si comunica al compilatore Oracle di creare se non esiste già all’interno del database, o di sostituire la funzione di nome procedura_1. I valori indicati tra parentesi tonde sono i parametri formali della funzione. La sintassi per la definizione dei parametri è nome del parametro modalità del parametro tipo del parametro. Dopo le parentesi tonde deve essere specificata la
Listato 3 Dichiarazione store procedure
Listato 4 Dichiarazione funzione PL/SQL
49 parola chiave return seguita dal tipo del valore di ritorno, in questo caso la funzione ritorna una data.
3.2.2.3 Parametri di default
Sia per le store procedure che per le funzioni è possibile andare a specificare dei parametri con valori di default utilizzando la seguente sintassi:
nome_parametro modalità_parametro tipo_parametro := valore_di_default
3.2.2.4 Eliminazione di procedure e funzioni
Le store procedure e le funzioni, come già detto, sono da considerarsi oggetti di un database, una volta compilati.
Se vogliamo eliminare una funzione, quindi, dobbiamo inviare il seguente comando al database
drop function nome_funzione
Se invece vogliamo eliminare una store procedure inviamo questo:
drop procedure nome_provedura
3.2.3 Package
Un package è un costrutto PL/SQL che consente ad oggetti in relazione tra di loro di essere memorizzati insieme. Un package ha due parti distinte: la specifica e il corpo, ciascuna memorizzata separatamente all’interno del database. A differenza di procedure e funzioni, che possono essere contenute localmente in un blocco o memorizzate nel database, un package può essere solo memorizzato nel database. Oltre a consentire di raggruppare oggetti in relazione tra di loro i package sono utili perché rispetto alle dipendenze sono meno limitativi dei sottoprogrammi. Hanno anche vantaggi nelle prestazioni.
In sostanza un package è una sezione di dichiarazione con nome. qualsiasi cosa possa andare nella parte di dichiarazione di un blocco può stare in un package. Un vantaggio di mettere tali oggetti in un package è la capacità di fare riferimento ad essi da altri blocchi PL/SQL, quindi i package forniscono anche variabili globali per PL/SQL. [9]
3.2.3.1 Specifica di un package
La specifica di un package (nota anche come header di un package) contiene informazioni sui contenuti del package. Tuttavia non contiene il codice per alcun sottoprogramma. Si consideri l’esempio nel Listato 5.
create or replace package PKG_GALPESP_RUN is
procedure SP_MAIN(PC_CSOCI in VARCHAR2, pi_ncocg in number,
pc_cstag in varchar2, pd_dscad_i in date, pd_dscad_f in date, pi_oelen out integer,
pi_nprog_ielen out integer);
procedure SP_INIZ_LGGALP(pi_oelen number,pi_nprog_ielen out integer, pc_stag in varchar2);
procedure SP_CRE_LPA(pi_oelen in integer, pi_nprog_ielen in integer,
50
pi_test_ldp in integer);
procedure SP_DISP_P3(pi_oelen in integer,
pi_nprog_ielen in integer);
procedure SP_DISP_P2(pi_oelen in integer,
pi_nprog_ielen in integer);
end PKG_GALPESP_RUN;
Il package PKG_GALPESP_RUN contiene cinque procedure. La sintassi per la creazione di un pacchetto è molto semplice: create or replace nome_del_package is seguite dalle varie dichiarazioni di funzioni, procedure, tipi ecc. e terminata dalla parola chiave end seguita dal nome del package.
3.2.3.2 Corpo del package
Il corpo del package è un oggetto del database separato dall’header del package. Non può essere compilato se l’header non è già stato compilato con successo. Il corpo del package contiene il codice dei sottoprogrammi dichiarati nell’header. Un esempio di corpo di package è nel Listato 6.
create or replace package body PKG_GALPESP_RUN is procedure SP_MAIN(PC_CSOCI in VARCHAR2, pi_ncocg in number,
pc_cstag in varchar2, pd_dscad_i in date, pd_dscad_f in date, pi_oelen out integer,
pi_nprog_ielen out integer) is
--dicharazione variabili locali li_oelen number;
li_prog_ielen number;
continua condice…
--aggiorno la disponibilità in p2
spu_update_col_flag_lggalp(pi_oelen,ls_csoci,ls_ccaus_sorcl,li_ycale_
sorcl,li_nprot_dorcl,'DISP_P2',li_paia_disp_p2);
commit;
end;
end PKG_GALPESP_RUN;
Nel Listato 6 è visibile la sintassi per la dichiarazione del corpo del package: create or replace package body nome_del_pacchetto is, codice dei vari sottoprogrammi, end nome_del_package.
Listato 5 Esempio specifica di un package
Listato 6 Esempio di un corpo di un package
51 3.2.4 Cursori
Un altro oggetto molto utilizzato nella programmazione PL/SQL è il cursore. Il cursore offre un sottoinsieme di dati, definiti da una query, recuperati in memoria quando il cursore viene aperto e residenti in memoria fino a quando il cursore non viene chiuso. Un cursore non è semplicemente un puntatore ai dati richiamati dalla query; punta, infatti, a una zona di memoria nella Process Global Area (PGA), chiamata area di contesto, che contiene:
• Righe restituite dalla query
• Numero di righe elaborate dalla query
• Un puntatore alla query
Quindi il puntatore è sulla memoria non direttamente sui dati. Poiché i record sono recuperati in memoria al momento in cui viene aperto il cursore, è garantita una vista coerente dei dati durante tutta la transazione. Se vengono aggiunti, cancellati o modificati dati dopo l’apertura del cursore, i dati nuovi o modificati non vengono riflessi nel set di risultati del cursore. L’apertura del cursore è letteralmente come prendere una istantanea dei dati esistenti in quel momento.[9]
3.2.4.1 Dichiarazione di un cursore
Un esempio di dichiarazione di un cursore è riportato in Listato 7 nella quale è dichiarato un cursore di nome author_curl con un parametro di tipo number che andrà a formare la condizione della clausola where della query associata.
cursor author_curl (i_id in number) is select rowid
from authors where id > i_id;
3.2.4.2 Apertura e chiusura di un cursore
Una volta dichiarato, un cursore deve essere aperto prima del suo utilizzo e una volta terminato il suo uso deve essere chiuso. La chiusura di un cursore è importante in quanto viene liberata la parte di memoria della PGA allocata all’atto della apertura.
L’apertura del cursore si realizza con il seguente codice:
open nome_cursore [(valori_parametri)];
e la chiusura con
close nome_cursore;
3.2.4.3 Recupero di record dal cursore
Una volta aperto un cursore, i dati, specificati dalla query associata, sono disponibili nella area di contesto. Il comando utilizzato per recuperare i dati dalla area di contesto è fetch. Il comando fetch opera solo sul record corrente e procede attraverso il set di risultati un record per volta.
La sintassi per fetch è:
fetch nome_cursore into nome(i)_variabile(i)
Listato 7 Esempio dichiarazione di un cursore
52 nome_cursore è il nome del cursore aperto, nome(i)_variabile(i) può essere una o più variabili separate da virgole, corrispondenti al numero e al tipo delle colonne comprese nel set di risultati.
Un recupero in cui il cursore restituisca più righe può essere:
fetch author_cur into v_first_name, v_last_name;
3.2.4.4 Attributi di un cursore
Oracle offre diversi attributi per i cursori. I più utilizzati sono elencati qui di seguito:
• %FOUND: controlla se un fetch ha restituito un record. Il valore restituito è di tipo booleano. Se è true, fetch ha restituito una riga; se è false non è stata restituita nessuna riga
• %ISOPEN: controlla se un cursore è già aperto. Se è true il cursore è aperto; se è false non è aperto
• %NOTFOUND: è l’opposto di %FOUND
• %ROWCOUNT: controlla il numero di righe riportate dal cursore in qualsiasi momento e restituisce il numero.
L’utilizzo di tali attributi è utile all’interno dei cicli per scorrere i dati contenuti nel cursore attraverso un ciclo.
3.2.4.5 Scorrimento nei cursori con i cicli
I cursori sono spesso utilizzati con i cicli in modo da fornire un modo per scorrere nel set di record restituito dal cursore. Ci sono tre tipi di cicli applicabili ai cursori:
• Ciclo semplice
• Ciclo while
• Ciclo for 3.2.4.5.1 Ciclo semplice
Il ciclo semplice ha la seguenti sintassi:
loop
--codice ciclo End loop;
All’interno del ciclo, ciascun record viene recuperato con il comando fetch e utilizzato. Un esempio di ciclo semplice è riportato nel Listato 8.
open lc_righe_oc;
loop --ciclo righe di ordine fetch lc_righe_oc into ls_ccaus_smaga, li_nprot_dmoma, li_nriga_ddocu;
exit when lc_righe_oc%notfound;
--operazioni sui singoli record end loop;
close lc_righe_oc;
Listato 8 Esempio di ciclo semplice
53 La clausola exit when è necessaria all’interno del ciclo per assicurarsi che questo si concluda una volta che sia stato recuperato l’ultimo record.
3.2.4.5.2 Ciclo while
Il ciclo while è simile al ciclo semplice per quanto riguarda la sua funzione, anche se il metodo di esecuzione è leggermente diverso. Di seguito si riporta l’esempio di Listato 8 ma utilizzando il ciclo while al posto del ciclo semplice.
open lc_righe_oc;
fetch lc_righe_oc into ls_ccaus_smaga, li_nprot_dmoma, li_nriga_ddocu;
while lc_riche_oc%FOUND loop;
--operazioni sui singoli record fetch lc_righe_oc into
ls_ccaus_smaga, li_nprot_dmoma, li_nriga_ddocu;
end loop;
close lc_righe_oc;
Questo blocco, sebbene utilizzi una sintassi diversa, restituisce gli stessi risultati dell’esempio del Listato 8. Inoltre non è necessario inserire una dichiarazione exit when, poiché %FOUND è integrato nella sintassi del ciclo while .[9]
3.2.4.5.3 Ciclo for
Il ciclo for è unico per il fatto che non richiede open, fetch e close espliciti.
Inoltre il ciclo for utilizza una variabile mai dichiara nella sezione declaration del blocco.
Sempre usando lo stesso esempio si riporta il codice del Listato 8 utilizzando il ciclo for.
For ls_ccaus_smaga, li_nprot_dmoma,
li_nriga_ddocu in lc_righe_oc Loop
--operazioni sui singoli record End loop;
Come si può vedere, questo ciclo è il più compatto dei tre.
Listato 9 Esempio di ciclo while
Listato 10 Esempio di ciclo for
