Un
bloc anonim PL/SQL are urmatoarea structura:
declaratii de
variabile, cursori, exceptii definite de utilizator
cereri SQL
instructiuni PL/SQL
EXCEPTION - optional
Actiuni executate in
caz de ridicare exceptii
Portiunile unui bloc sunt deci urmatoarele:
Intre DECLARE si BEGIN:
o
Sunt
declarate variabilele, cursoarele si exceptiile utilizator necesare blocului.
Aceasta parte poate lipsi, un bloc putand incepe direct cu cuvantul cheie BEGIN
(care este obligatoriu).
o
Tipurile
de date din SQL pot fi folosite (cu unele diferente) si in PL/SQL.
Intre BEGIN si EXCEPTION:
o
Corpul
blocului, format din cereri SQL si instructiuni PL/SQL care descriu procesarea
datelor in cadrul acestuia. Cuvantul cheie EXCEPTION poate lipsi, in care caz
blocul se termina cu END.
Intre EXCEPTION si END:
o
Actiuni
executate in caz de eroare. Este o parte optionala a unui bloc (dar cuvantul
cheie END care marcheaza sfarsit de bloc este obligatoriu.
Exemplu:
DECLARE
v_dname VARCHAR2(10);
BEGIN
SELECT DNAME
INTO v_dname
FROM DEPT
WHERE DEPTNO=10;
EXCEPTION
WHEN nume_exceptie THEN
<tratare exceptie>
In cazul executiei un bloc din SQL*Plus:
§
Fiecare
cerere SQL si instructiune PL/SQL se incheie cu punct si virgula (;)
§
DECLARE,
BEGIN si EXCEPTION nu se termina cu punct si virgula
§
Dupa
END se pune punct si virgula
§
Terminarea
blocului se face cu punct (.)
§
Rularea
unui bloc se face cu /
§
In
caz de rulare cu succes apare mesajul:
A.
Variabile PL/SQL:
§
Scalare:
Aceste variabile pot contine o singura valoare. Ipurile principale corespund celor care se pot asocia coloanelor unei tabele Oracle.
§
Compuse:
Contin mai multe valori, de exemplu o inregistrare (linie) din rezultatul unei cereri SQL
§
Referinta:
Contin pointeri (referinte) catre alte elemente de program. Nu sunt tratate in acest curs (cu exceptia tipului REF CURSOR)
§
LOB (obiecte mari):
Acestea contin valori numite
locatori (locators) care specifica locatia unor obiecte mari (imagini de
exemplu).
B.
Variabile externe (non PL/SQL):
§
Variabile
care provin din limbajul gazda in care este scrisa aplicatia (C, PHP, etc).
§
Campuri
ale unei forme in Oracle Forms
§
Variabile
SQL*Plus
Sintaxa unei declaratii de variabile este:
Identificator [CONSTANT] tipdedate [NOT NULL]
[:= |
DEFAULT expresie]
unde:
[CONSTANT]
Variabila nu-si poate
schimba valoarea. Constantele trebuiesc initializate la declarare.
tipdedate
Tipul variabilei (un tip
scalar, compus, referinta sau LOB).
[NOT NULL]
Variabila nu poate fi nula.
Aceste variabile trebuiesc initializate la declarare.
[:= expresie]
Initializare cu o expresie.
Variabilele neinitializare contin initial valoarea NULL.
[DEFAULT expresie]
Valoare implicita data de o
expresie (initializeaza variabila).
Exemplu:
DECLARE
v_nume VARCHAR2(10)
:= 'ION';
v_datanasterii
DATE;
v_deptno NUMBER(2)
NOT NULL := 30;
v_comision CONSTANT
NUMBER := 2000;
Instructiunea de atribuire este := (ca in limbajul Pascal). Caracterul = este
folosit in PL/SQL ca operator de comparatie (egal cu).
Sintaxa atribuirii este:
Identificator := expresie;
Exemplu:
v_nume := 'ION';
v_datanasterii := '12-APR-89';
v_data2 := to_date('14-04-79', 'DD-MM-YY');
v_numar := v_n1 + v_n2 * 1230;
§
NUMBER[(numar de cifre, numar de zecimale)]:
Numar intreg sau real. Fara
parametri sau cu 2 parametri: numar real. Cu un parametru: numar intreg.
Magnitudine: 10-130 - 10125 (+/-).
Subtipuri ale acestui tip
sunt:
o
DEC,
DECIMAL, NUMERIC – numere in virgula fixa cu 38 de cifre semnificative.
o
DOUBLE
PRECISION, FLOAT: numere in virgula mobila cu 38 de cifre semnificative.
o
REAL:
numere in virgula fixa cu 18 de cifre semnificative.
o
INTEGER,
INT, SMALLINT: numere intregi cu maxim cu 38 de cifre semnificative.
§
BINARY_INTEGER:
Intregi intre -231
si 231. Subtipuri ale acestuia sunt:
o
NATURAL
si POSITIVE pentru intregi non-negativi sau pozitivi.
o
NATURALN
si POSITIVEN ca mai sus dar nu pot avea valori nule
o
SIGNTYPE:
doar valorile -1, 0 si 1.
§
PLS_INTEGER:
Intregi intre -231
si 231. E asemanator cu tipul anterior dar operatiile sunt mai
rapide. In plus, in caz de depasire se ridica o exceptie (la BINARY_INTEGER si
NUMBER nu se intampla asta). Este recomandat in aplicatii noi in loc de
BINARY_INTEGER.
§
CHAR[(lungime_maxima [CHAR | BYTE])]:
Sir de caractere de lungime
fixa (maxim 32737 octeti, implicit 1). Optional CHAR | BYTE spune in ce este
exprimata lungimea maxima (un caracter putand fi memorat pe mai multi octeti in
functie de setul de caractere folosit).
Un subtip al acestui tip
este CHARACTER, identic cu tipul de baza (introdus pentru compatibilitatea cu
alte sisteme).
Nota: acest tip difera de tipul
CHAR pentru coloane care poate avea maxim 2000 de octeti.
§
VARCHAR2(lungime_maxima [CHAR | BYTE]):
Sir de caractere de lungime
variabila, maxim 32737 octeti. Aceleasi observatii ca mai sus in legatura cu CHAR
| BYTE.
Subtipuri ale acestui tip
sunt: STRING si VARCHAR, identice cu tipul de baza (introduse pentru
compatibilitatea cu alte sisteme).
Nota: acest tip difera de tipul
VARCHAR2 pentru coloane care poate avea maxim 4000 de octeti.
§
LONG
Similar cu VARCHAR2 dar
lungimea maxima este de 32760 de octeti. Incepand cu Oracle 9i acest tip este
compatibil cu CLOB si se recomanda folosirea acestuia din urma.
Nota: acest tip difera de tipul
LONG pentru coloane care poate avea maxim 231 octeti (2 GB).
§
LONG RAW
Similar cu LONG dar se pot
stoca si date binare (nu doar caractere). In plus, PL/SQL nu interpreteaza date
de acest tip.
Incepand cu Oracle 9i acest
tip este compatibil cu BLOB si se recomanda folosirea acestuia din urma.
Nota: acest tip difera de tipul
LONG RAW pentru coloane care poate avea maxim 231 octeti.
§
RAW(dimensiune_maxima)
Similar cu VARCHAR2 contine
date binare. Dimensiunea maxima este de 32767 octeti. PL/SQL nu interpreteaza
astfel de date.
Nota: acest tip difera de tipul
LONG RAW pentru coloane care poate avea maxim 2000 de octeti.
§
ROWID
Poate stoca un identificator
fizic de linie intr-o tabela. Pentru conversia la/de la sir de caracter (18
caractere) se pot folosi functiile SQL ROWIDTOCHAR respectiv CHARTOROWID.
§
UROWID
(Universal ROWID) Poate
stoca un identificator logic de linie intr-o
tabela, indexata sau nu precum si un identificator de linie extern
(non-Oracle). Nu este necesara folosirea functiilor de conversie la/de la sir
de caractere (conversie automata).
§
NCHAR(dimensiune_maxima)
Echivalent cu CHAR dar
pentru a stoca date in setul de caractere national specificat la crearea bazei
(un caracter pe 2-3 octeti).
§
NVARCHAR2(dimensiune maxima)
Echivalent cu VARCHAR2 dar
pentru a stoca date in setul de caractere national specificat la crearea bazei
(un caracter pe 2-3 octeti).
Aceste tipuri permit stocarea de date nestructurate (text, imagini, video, audio) de dimensiune maxima 4 Gbytes. Este posibil accesul direct la continut (la LONG doar acces secvential). Obiectele de acest tip contin de fapt referinte (locatori) pentru date, datele propriu-zise fiind stocate fie in baza de date fie in fisiere externe din sistemul de operare. Sunt de urmatoarele tipuri:
§
BFILE:
Date binare (pana la 4GB)
stocate in fisiere externe, din sistemul de operare. Sunt date read/only (nu se
pot modifica). Nu participa la tranzactii si nici la restaurarea bazei de date
(ele nu sunt stocate in aceasta) si nu pot fi replicate.
§
BLOB:
Pentru date binare de
dimensiuni mari. Apartin bazei de date, participa la tranzactii si se pot
restaura cu baza de date, sunt replicabile. Dimensiunea maxima este de 4GB.
§
CLOB:
Similar cu BLOB dar pentru
date tip caracter si nu binare. Aceleasi caracteristici ca mai sus.
§
NCLOB:
Similar cu CBLOB dar pentru
siruri de caractere din setul national setat la crearea bazei de date.
§
Se
pot stoca valori booleene: TRUE, FALSE si NULL. Sunt permisi doar operatorii
booleeni.
Nota: acest tip nu exista ca tip
pentru coloanele unei tabele.
§
DATE:
Data calendaristica (zi,
luna, an, ora, minut, secunda). Se pot folosi operatorii + numar si - numar
pentru a incrementa si decrementa cu un numar de zile o data. Diferenta intre
doua date da numarul de zile dintre acestea (real, cu zecimale in cazul in care
ora-minutul-secunda difera).
§
TIMESTAMP[(precizie)]
Extinde tipul DATE. Contine
data calendaristica (zi, luna, an, ora, minut, secunda si fractiuni de
secunda). Precizia (intre 0 si 9) specifica numarul de zecimale pentru fractiunile
de secunda. Valoarea implicita a preciziei este 6.
Exemplu:
DECLARE
moment TIMESTAMP(4);
BEGIN
moment := '2004-04-26 15:52:33.1592';
...
END;
§
TIMESTAMP [(precizie)] WITH TIME ZONE
Extinde tipul TIMESTAMP
continand si o diferenta intre ora locala si ora universala (GMT).
Exemplu:
DECLARE
moment TIMESTAMP(4) WITH TIME ZONE;
BEGIN
moment := '2004-04-26 15:52:33.1592 +2.00';
...
END;
In acest caz diferenta este
de 2 ore.
Doua date de acest timp sunt
identice daca reprezinta aceeasi data in timp universal.
§
TIMESTAMP [(precizie)] WITH LOCAL TIME ZONE
Similar cu tipul anterior
dar la stocarea pe o coloana de tip DATE se face conversia la timpul local,
deci diferenta fata de GMT nu este stocata.
§
INTERVAL YEAR [(precizie)] TO MONTH
Se stocheaza intervale de
ani si luni. Precizia specifica numarul de cifre pentru an (intre 0 si 4,
implicit 2). De exemplu, un interval de 2 ani si 3 luni se poate stoca intr-o
variabila de acest tip astfel:
DECLARE
perioada INTERVAL YEAR(1) TO MONTH;
BEGIN
perioada := INTERVAL '2-3' YEAR TO MONTH;
sau:
lifetime := '101-3';
. . . .
Se poate stoca numai un
numar de ani sau de luni:
perioada := INTERVAL '2' YEAR;
perioada := INTERVAL '3' MONTH;
§
INTERVAL DAY [(precizie_zi)] TO SECOND [(precizie_sec)]
Similar cu tipul anterior,
dar pentru intervale de zile si secunde.
Nota: Datele si/sau intervalele
se pot aduna, scadea, inmulti rezultand date sau intervale, dupa caz:
Interval1 + Interval2 = Interval3
Interval1 - Interval2 = Interval3
Data1 + Interval = Data2
Data1 - Interval = Data2
Interval1 * numar = Interval3
Interval1 / numar = Interval3
Putem defini tipul unei variabile in functie de
tipul altei variabile sau a unei coloane de tabela folosind constructia:
Variabila%TYPE sau Tabela.Coloana%TYPE.
Exemplu:
DECLARE
v_numar NUMBER(5,2);
v_altnumar v_numar%TYPE;
v_nume emp.ename%TYPE;
- - - - -
Nota: Daca o variabila este definita pe baza tipului unei
coloane de tip NOT NULL ea nu mosteneste acest atribut, putand contine si
valoarea NULL.
Acestea se prefixeaza cu doua puncre (:). Exemplu:
:g_salariu := v_salanual /
12;
Astfel de variabile provin din mediul extern, de
exemplu variabile din programele C, PHP, etc. din care se face acces la baza de
date, campuri ale formelor sau variabile SQL*Plus.