Oracle - PL/SQL
• Introduzione ad Oracle
• PL/SQL: Elementi di base
• Tecniche di programmazione
–
–
–
–
uso delle variabili
uso dei cursori
uso delle funzioni/procedure
I/O
Oracle: Prodotti
• SQL*Plus - Interprete di comandi come DDL,
DML.
• Developer/2000 and Developer. Suite di tool di
sviluppo
– Oracle*Forms - Data entry e manipulation a schermo
– Oracle*Reports - Tool di gestione dei report
– Oracle*Graphics - Tool grafico per la sintesi dei dati
(charts e grafici)
• Oracle*Book - Tool grafico per lo sviluppo online di documentazione ipertestuale
1
Oracle: Prodotti
• SQL*TextRetrieval and Oracle Context - Una
suite di strumenti e API per la ricerca
bibliografica.
• Programmer/2000 - include Pro * precompilatori
e librerie di procedure e programmi di utilita’ per
il linking ``C' ' , C++, FORTRAN, Java, ADA,
COBOL che garantiscono l’accesso agli Oracle
DB
Oracle: Utilities
• Enterprise Manager - Tool grafico di gestione di
una Base Dati.
• SQL*Loader - Caricatore di dati esternni (ASCII
o binary) in un Oracle DB.
2
Oracle: Connectivity e Middleware
• SQL*Net e Net8 - Driver di comunicazione che
supporta accesso client-server dei tool ad un
Oracle DB
• ORACLE Server - Parte di un DBMS Oracle che
e’ attivo su un database server: riceve richieste da
macchine client e le trasmette all’ Oracle RDBMS
e viceversa.
• Oracle ODBC Drivers - Open DataBase
Connectivity drivers per la connessione dei
database Oracle in accordo con lo standard
ODBC
Core Database Engine
– ORACLE RDBMS
• Oracle Web Applications Server - WWW (HTTP)
Server collegato all’Oracle RDBMS.
• Spatial Data Cartridge (GIS), Video Cartridge
• ConText Cartridge - Provides storage and retrieval
of text documents.
• OLAP Option - On-Line Analytical Processing
• Objects Option (OO features)
– Integrated Data Dictionary.
– SQL e PL/SQL
3
Oracle: Ambienti di sviluppo
• Sviluppo di applicazioni Oracle:
– Oracle RDBMS (o un Oracle RDBMS server),
– uno o piu’ tool di sviluppo.
– Tool di sviluppo esterni (PowerBuilder, Visual Basic
or Java Appl Dev).
• Sviluppo stand-alone (Personal Oracle o Personal
Oracle Lite RDBMS + Oracle Developer)
• Sviluppo Multi-user (in ambiente condiviso):
Oracle RDBMS server su macchina server, clienti
distribuiti su PCs.
PL/SQL: Elementi di Base
• Login
Host String:
per Personal Oracle8
beq-local.
4
PL/SQL: Elementi di Base
• Attivazione
PL/SQL:Obbiettivi
• Inserire, modificare, cercare ed invocare
comandi SQL
• Accedere alle definizioni di tabelle, colonne
e dati
• Formattare, effettuare calcoli, memorizzare
e stampare i risultati di interrogazioni
• Accedere e copiare dati tra Oracle DB
diversi
5
Comando CREATE
CREATE TABLE impiegato
(nome
VARCHAR2(8),
cognome
VARCHAR2(8),
cf
VARCHAR2(9) NOT NULL,
salary
NUMBER(7) NOT NULL,
supercf
VARCHAR2(9),
deptno
NUMBER(1) NOT NULL) ;
Domini, Tipi di Dato
• VARCHAR2
– caratteri , taglia variabile, max=4,000
• NUMBER
– Numerici, NUMBER(precision, scale) con
NUMBER(6,2) in [-999.99, 999.99].
• DATE
– data e ora, TO_DATE(), TO_CHAR().
• RAW - binary data. <=255 char, 1 solo attr
LARGE RAW (<= 2 Gb) per tabella
• LOB, BLOB e CLOB
6
Comando DESCRIBE
SQL> CREATE TABLE imp_dip_1
AS SELECT nome, cognome
FROM impiegato
WHERE deptno = 1 ;
Table created.
SQL> DESCRIBE imp_dip_1
Name
Null?
Type
-------------------------- -------- ---NOME
VARCHAR2(8)
COGNOME
VARCHAR2(2)
Ouput
• Formattazione dell’output
column <nome colonna> <opz 1> opz 2>
• Es.
column desc heading “Descrizione”
7
Formattazione
• FORMAT <formato>
• Formati
– A<n> fissa la ampiezza della colonna a n
caratteri
– 99,999.99 fissa la ampiezza ed i decimali di
un numero
Formattazione
• HEADING <text>
– fornisce il nome <text> per l’intestazione della
colonna
• NULL <text>
– fornisce il default <text> per la stampa dei
valori NULL
8
Formattazione
• COLUMN <nome colonna> CLEAR
– annulla la formattazione vigente sulla
colonna <nome colonna>
Report (0)
CREATE TABLE Prodotto(
Pid
integer,
Descrizione CHAR(120),
Fornitore
CHAR(20),
Costo
integer,
PRIMARY KEY (PId));
9
Report (1)
TTitle 'Tabella dei Prodotti - Esempio di Report'
BTitle 'by R. Basili'
Column PId heading 'CODE' format 999990
Column Descrizione Heading 'Descrizione' format
a20 word_wrapped
Column Fornitore Heading 'Prodotto '
format a10 trunc
Column Costo Heading 'Costo al Forn '
format 999990.99
Break on
Column Pr_Cons Heading 'Prezzo '
format 99999990.99
Report (2)
set pagesize 20
spool Report1.sql
select PID, Descrizione,
Fornitore, Costo,
(Costo * 2.5) as Pr_Cons
from Prodotto
order by Costo, PId;
10
Report (3)
Mer Nov 08
pagina
1
Tabella dei Prodotti - Esempio di Report
CODE
------3
1
Descrizione
-------------------Cravatta Uomo
Scarpe Mocassini
Uomo
2 Scarpe Mocassini
Donna
4 Giacca Uomo
Prodotto
Costo al Forn
Prezzo
---------- -------------- -----------Missoni
30000.00
75000.00
Valleverde
50000.00
125000.00
Valleverde
Armani
60000.00
150000.00
250000.00
625000.00
by R. Basili
PLSQL: Struttura di un blocco
[Header]
[DECLARE
<Costanti>
<Variabili>
<Cursori>
<Gestori di eccezioni/errori> ]
BEGIN
<Istruzioni PL/SQL>
[ EXCEPTIONS
<Gestione eccezioni/errori> ]
END
/
11
PLSQ: Dichiarazioni
DECLARE
Ricavo number (38);
counter number(9) := 0;
ProdID
De
Forn
Cst
Prd
Prodotto.PId%TYPE;
Prodotto.Descrizione%TYPE;
Prodotto.Fornitore%TYPE;
Prodotto.Costo%TYPE;
cname%ROWTYPE;
cursor cname( Thresh Prodotto.Costo%TYPE ) is
select *
from Prodotto
where Costo >= Thresh;
…
BEGIN
commit;
open cname(2000);
counter := 0;
loop
fetch cname into Prd;
exit when cname%NOTFOUND;
ProdId := Prd.PId;
Cst := Prd.Costo;
De := Prd.Descrizione;
Ricavo := Cst * 1.25;
counter := counter + 1;
end loop;
close cname;
END;
/
PLSQL: blocco
12
BEGIN
…
loop
PLSQL: Output
fetch cname into Prd;
exit when cname%NOTFOUND;
ProdId:=Prd.PId; Cst:=Prd.Costo; De:=Prd.Descrizione;
Ricavo := Cst * 1.25;
DBMS_OUTPUT.PUT_LINE('
Il Prodotto: '
|| De
|| ' ha un prezzo di ' || to_char(Ricavo) );
counter := counter + 1;
end loop;
close cname;
DBMS_OUTPUT.PUT_LINE('
Sono stati analizzati '
|| to_char(counter) || ' prodotti ' );
END;
/
Gestione dell’Input
PROMPT Seleziona il dipartimento
ACCEPT dpn NUMBER PROMPT
"Dipartimento n.:"
SELECT nome, cognome, deptno
FROM
impiegati
WHERE deptno = &dpn ;
13
Gestione dell’Input (2)
SQLprompt>
Seleziona il dipartimento
Dipartimento n.: 3
Nome
-------Mario
Massimo
...
COGNOME
DEPTNO
-------- --------ROSSI
3
BIANCHI
3
Gestione dell’Input (3)
PROMPT
ACCEPT
SELECT
FROM
WHERE
Digita Cognome
cgnme PROMPT ”Cognome Impiegato: "
Nome, Cognome, deptno
impiegati
UPPER(cognome) like UPPER('%&cgnme%');
14
Attivazione di un programma PL/SQL
• Dato un programma PL/SQL, memorizzato in un
file PLSQLprog.sql, la sua attivazione e' possibile
mediante il seguente comando SQLplus:
SQLplusPrompt> start PLSQLprog
PLSQL: Cursori Annidati
cursor cname( Thresh Prodotto.Costo%TYPE ) is
select *
from Prodotto
where Costo >= Thresh
ORDER BY Costo;
cursor Tutti_Prodotti is
select *
from Prodotto;
Prd1
cname%ROWTYPE;
Prd2
Tutti_Prodotti%ROWTYPE;
Cost_Diff number(10,2);
15
for Prd in cname(30000)
loop
Cst := Prd.Costo;
if Cst*1.25 > 50000 then
De := Prd.Descrizione;
Ricavo := Cst * 1.25;
ProdId := Prd.PId;
counter2 := 0;
for Prd2 in Tutti_Prod
loop
Cost_Diff := abs(Prd2.Costo - Cst)/Prd2.Costo;
if Cost_Diff <= 0.20 then
counter2 := counter2 + 1;
end if;
end loop; /* su Tutti_Prod */
end if;
counter := counter + 1;
end loop; /* su CNAME() */
PLSQL: Cursori Annidati
(2)
File I/O - Preliminari
FILE init.ora
…
utl_file = c:\Database\datiUtente
utl_file_dir = c:\Database\datiScambio
FILE ProvaFILE.sql
Outdir char(200);
Outfilename char(25);
Outf UTL_FILE.FILE_TYPE;
…
Outdir := 'C:\Documenti\Didattica\BasidiDati\Esempio';
Outfilename := 'Mio.dat';
DBMS_OUTPUT.PUT_LINE ('Trying to open <’ || Outdir ||
'/’ || Outfilename );
Outf := utl_file.fopen(Outdir, Outfilename,'w');
16
PLSQL: File I/O
…
Outf := utl_file.fopen(Outdir, Outfilename,'w');
DBMS_OUTPUT.PUT_LINE ('File Opened !!' );
loop
fetch cname into Prd;
exit when cname%NOTFOUND;
ProdId := Prd.PId;Cst := Prd.Costo;De := Prd.Descrizione;
Ricavo := Cst * 1.25;
utl_file.putf( Outf,
'Il prodotto %s ha un prezzo di %s\n',
trim(De), to_char(Ricavo));
utl_file.new_line(Outf);
counter := counter + 1;
end loop;
close cname;
utl_file.fflush(Outf); utl_file.fclose(Outf);
Triggers
CREATE OR REPLACE TRIGGER check_eta
BEFORE INSERT OR UPDATE ON impiegati
FOR EACH ROW
DECLARE
eta NUMBER;
error_msg CHAR(180);
BEGIN
eta := ( (sysdate - :new.nascita) / 365);
IF (eta < 16) THEN
error_msg := 'Attenzione: '||:new.fname||' '||
:new.lname || ' ha solo ' ||
TO_CHAR(eta, '99.9') || ' anni.';
RAISE_APPLICATION_ERROR ( -20601, error_msg);
END IF;
END;
/
17
Triggers (2)
• Compilazione OK
– trigger memorizzato nello schema utente
• Alcuni errori minori in BEGIN … END
– msg: “Trigger created with compilation errors”.
• Errori importanti (CREATE OR REPLACE TRIGGER)
– trigger e’ respinto e non memorizzato
SQL> SELECT trigger_name, trigger_body
FROM
user_triggers
WHERE trigger_name = 'CHECK_AGE';
Funzioni o Procedure
CREATE OR REPLACE PROCEDURE elimina_da_inventario (
locid_corr IN NUMBER,
prodid_corr IN VARCHAR,
quantita_da_eliminare IN NUMBER) AS
quantita_corr NUMBER;
error_msg CHAR(180);
BEGIN
quantita_corr := 0;
...
END;
/
18
Funzioni o Procedure (1)
BEGIN
quantita_corr := 0;
-- Cerca nella localizzazione
-- SE la quantita' corrente e' non sufficiente
attiva EXCEPTION
-- Altrimenti aggiorna
SELECT
INTO
FROM
WHERE
AND
quant
quantita_corr
inventario
inventario.locationid = locid_corr
inventario.productid = prodid_corr;
Funzioni o Procedure (2)
-- materiale in inventario sufficiente
IF (quantita_corr - quantita_da_eliminare > 0)
THEN
UPDATE inventario
SET
quant = quant - quantita_da_eliminare
WHERE inventario.locationid = locid_corr
AND inventario.productid = prodid_corr;
END IF;
19
Funzioni o Procedure (3)
-- materiale in inventario appena sufficente
IF (quantita_corr - quantita_da_eliminare = 0)
THEN
DELETE FROM inventario
WHERE inventario.locationid = locid_corr
AND inventario.productid = prodid_corr;
END IF;
Funzioni o Procedure (4)
-- poco materiale in inventario
IF (quantita_corr - quantita_da_eliminare < 0) THEN
error_msg := 'ERRORE: Quantita insufficiente !!';
RAISE_APPLICATION_ERROR (-20602, error_msg);
END IF;
20
Funzioni o Procedure (5)
EXCEPTION
-- nessun materiale in inventario
WHEN NO_DATA_FOUND THEN
BEGIN
error_msg := 'ERROR: Prodotto ' ||
prodid_corr ||
' non presente!!';
RAISE_APPLICATION_ERROR (-20603, error_msg);
END; -- fine eccezioni
END;
/
-- fine procedura elimina_da_inventario/3
Invocazione
• EXECUTE elimina_da_inventario(106,'P500', 10)
CREATE OR REPLACE FUNCTION data_nascita
(impid in number)
return date
as nascita
begin
… -- aggiorna nascita
return nascita;
end;
• if ( data_nascita(234)>01-GEN-98 ) then
21
