PL/SQL - Generalitati

PL/SQL(Procedural Language SQL) este un limbaj procedural, extensie la SQL, care este limbajul standard de acces la date pentru baze de date obiect-relationale. PL/SQL ofera metode software noi, cum ar fi incapsularea datelor, exceptia, si orientarea pe obiect. PL/SQL permite ca manipularea datelor si interogarile sa fie incluse in stuctura bloc precum si unitatile de cod procedurale, facand din PL/SQL un limbaj de tranzactii puternic

PL/SQL joaca un rol central in Oracle Server (prin proceduri stocate, functii stocate, declansatori, si pachete) si in Oracle development tools (prin declansatorii din Developer/2000).

Developer/2000 consta din Oracle Forms, Oracle Reports, si Oracle Graphics.

PL/SQL poate fi folosit in grupul instructiunilor SQL intr-un singur bloc si trimis blocului de intrare al serverului intr-un singur apel, ducand la reducerea traficului retelei. Fara PL/SQL, declaratile SQL vor fi procesate una cite una.

PL/SQL poate coopera cu uneltele de dezvoltare a aplicatiilor Oracle Server, cum ar fi Developer/2000 Forms, Reports.

Fiecare unitate PL/SQL contine una sau mai multe blocuri. Aceste blocuri pot fi complet separate sau incluse una in alta. Unitatile de baza (procedurile, functiile si blocurile anonime) fac din PL/SQL un program cu blocuri logice, ce contine orice numar de subblocuri.

Deoarece PL/SQL apartine lui Oracle Server, putem muta programele pe orice calculator gazda ce suporta Oracle Server si PL/SQL. Pot fi declarate variabile, cursoare, constante si exceptii care pot fi folosite in SQL si in declaratii procedurale.

Structura bloc PL/SQL

PL/SQL este un limbaj structurat pe blocuri, ceea ce inseamna ca programele pot fi impartite in blocuri logice. Un bloc PL/SQL contine trei sectiuni: declarativa (optionala), executabila (obligatorie), si tratarea exceptiilor (optionala). Numai cheile BEGIN si END sunt obligatorii, variabilele locale putand fi declarate in blocul in care sunt folosite. Conditiile de eroare pot fi tratate specific in fiecare bloc unde sunt aplicate.

Structura bloc PL/SQL :

DECLARE - Optional

- Variabile, cursoare, exceptii definite de utilizator

BEGIN - Obligatoriu

Declaratii SQL

Declaratii PL/SQL

EXCEPTII - Optional

Actiuni cand apar erori

END; - Obligatoriu

Exemplul unei structuri bloc:

DECLARE

v_variable VARCHAR2(5)

BEGIN

SELECT column_name

INTO v_variable

FROM table_name;

EXCEPTII

WHEN exception_name THEN

.

END;

Blocurile pot fi de mai multe tipuri : anonim, procedura si functie. Blocurile anonime sunt blocuri fara nume, fiind declarate in punctele aplicatiei unde ele sunt executate. Declansatorii din Developer/2000 consta tocmai din aceste blocuri. Functia este similara procedurii, doar ca trebuie sa returneze o valoare.

Declararea variabilelor in PL/SQL

Toate variabilele PL/SQL trebuiesc declarate in sectiunea DECLARE, inaintea referirii lor in blocul PL/SQL, avand optiunea de a-le atribui o valoare initiala. Sintaxa definiri unei variabile este:

identifier [CONSTANT] datatype [NOT NULL]

[:= | DEFAULT expr];

unde:

identifier este numele variabilei;

CONSTANT defineste o constrangere prin care valoarea variabilei nu poate fi modificata (constantele trebuiesc initializate);

datatype tipul variabilei;

NOT NULL defineste o constrangere prin care variabila trebuie sa contina o valoare diferita de NULL;

expr este orice expresie PL/SQL, care poate contine o alta variabila sau o expresie care contine operatori si functii.

Exemplu:

Declare

v_hiredate DATE;

v_deptno NUMBER(2) NOT NULL := 10;

v_location VARCHAR2(13) := 'Atlanta';

c_comm CONSTANT NUMBER := 1400;

Doua variabile pot sa aiba acelasi nume, cu conditia ca ele sa fie definite in blocuri diferite. Nu se va alege numele unei variabile (identificator) la fel cu numele unei coloane a tabelei folosita in bloc.

Folosim v_ ca prefix pentru reprezentarea variabilelor, si g_ pentru reprezentarea variabilelor globale. Identificatorii nu trebuie sa fie mai lungi de 30 de caractere.

Atribuirea de valori unei variabile se realizeaza prin sintaxa:

identifier := expr

unde:

identifier este numele variabilei scalare.

expr poate fi o variabila, apelul unei functii, dar nu o coloana a unei baze.

Exemplu:

v_hiredate := '31-DEC-98';

v_name := 'Maduro';

Implicit, variabila este initializata NULL, dar poate fi introdusa constrangerea NOT NULL, ceea ce inseamna ca variabila trebuie sa contina o valoare.

Tipuri de date scalare

Exemplu:

v_job VARCHAR2(9);

v_count BINARY_INTEGER := 0;

v_total_sal NUMBER(9,2) := 0;

v_orderdate DATE := SYSDATE + 7;

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

v_valid BOOLEAN NOT NULL := TRUE;

Atributul %TYPE este folosit pentru declararea unei variabile de acelasi tip cu al unei variabile declarate anterior sau cu a unei coloane dintr-o baza de date.

Exemplu:

v_ename emp.ename%TYPE;

v_balance NUMBER (7,2);

v_min_balance v_balance%TYPE :=10;

Cu variabilele de tip LOB se pot stoca blocuri de date nestructurate (cum ar fi text, imagini grafice, clipuri video si forme de sunete wave) pana la 4 gigabytes, suportand acces aleator la date.

BFILE (binary file) este folosit pentru stocarea obiectelor binare intr-un fisier al sistemului de operare inafara bazei de date.

O variabila legata este o variabila ce poate fi declarata in mediul gazda, ea poate fi folosita pentru transmiterea valorilor, in interiorul sau exteriorul unuia sau mai multor programe PL/SQL, ea poate fi folosita ca oricare alta variabila. In SQL Plus, pentru declararea variabilelor legate, putem folosi comanda VARIABILE. De exemplu, putem declara o variabila legata de tip NUMBER dupa cum urmeaza:

VARIABLE return_code NUMBER

Afisarea variabilei legate se face prin folosirea comenzii PRINT:

SQL> VARIABLE n NUMBER

SQL> PRINT n

Referirea variabilelor Non-PL/SQL se face prin prefixarea lor cu : , pentru distingerea lor fata de variabilele PL/SQL.

Exemple:

: host_var1 := v_sal;

: global_var1 := 'YES';

Scrierea declaratiilor executabile

Deoarece PL/SQL este o extensie a lui SQL, regulile generale de sintaxa aplicate in SQL sunt aplicate si in PL/SQL. Unitatile lexicale pot fi separate prin unul sau mai multe spatii sau prin alti delimitatori. Identificatorii sunt folositi pentru denumirea campurilor si unitatilor program PL/SQL, ce contin constante, variabile, exceptii, cursoare, variabile cursor, subprograme si pachete.

Identificatorii pot contine pana la 30 de caractere, dar obligatoriu trebuie sa inceapa cu un caracter alfabetic. Nu putem alege acelasi nume pentru un identificator la fel cu numele unei coloane din tabelul folosit in blocul program.

Cuvintele rezervate nu pot fi folosite ca identificatori daca nu sunt incluse intre ghilimele.

Comentarea codului PL/SQL se face cu doua liniute daca comentariul este intr-o singura linie, sau inclusa intre simbolurile /* si */ daca comentariul este pe mai multe linii. Comentariul este strict informational si nu influenteaza in nici un fel codul.

Exemplu: Calcularea salariului anual pe baza salariului lunar.

.

v_sal NUMBER(9, 2);

BEGIN

/* Calcularea salariului anual pe baza salariului lunar intodus de utilizator */

v_sal := v_sal * 12;

END; -- Acesta este sfarsitul tranzactiei.

Functiile SQL care sunt valabile si in PL/SQL sunt: functii numerice single-row, functii caracter single-row, functii de conversie a datelor.

Functiile care nu sunt valabile in PL/SQL sunt: GREATEST, LEAST, DECODE si functiile pe grup: AVG, MIN, MAX, COUNT, SUM, STDDEV si VARIANCE.

Exemplu: Calcularea sumei numerelor stocate in tabela PL/SQL NUMBER_TABLE. Acest exemplu produce o eroare de compilare.

v_total := SUM(number_table);

Una din avantajele lui PL/SQL este abilitatea de declarare incuibata. Putem incuiba blocurile oriunde este permisa declaratia executabila. Domeniul unui obiect este regiunea programului ce poate referi obiectul. Putem referi variabila declarata in sectiunea executabila.

Un identificator este vizibil in bloc in care este declarat si in toate subblocurile, procedurile si functii continute in acel bloc. Daca blocul nu gaseste identificatorul declarat local, se verifica in sectia declarativa al blocului parinte. Domeniul de vizibilitate se aplica la toate obiectele declarate, inclusiv variabile, cursoare, exceptii definite de utilizator si constante.

Exemplu:

.

x BINARY_INTEGER;

BEGIN domeniul lui x

.

DECLARE

y NUMBER;

BEGIN domeniul lui y

.

END;

.

END;

Variabila y poate referi variabila x, iar invers nu este posibil.

Exemplu:

.

DECLARE

V_SAL NUMBER(7,2) := 60000;

V_COMM NUMBER(7,2) := V_SAL / .20;

V_MESSAGE VARCHAR2(255):='comision';

BEGIN .

DECLARE

V_SAL NUMBER(7,2) := 50000;

V_COMM NUMBER(7,2) := 0;

V_TOTAL NUMBER(7,2):=V_SAL+ V_COMM;

BEGIN

V_MESSAGE := 'CLERK nu are' || V_MESSAGE;

END;

V_MESSAGE := 'BLAKE are' || V_MESSAGE;

END;

Interactionarea cu Serverul Oracle.

Cand dorim sa extragem informatii sau sa aplicam schimbari asupra unei baze de date, trebuie sa folosim SQL. PL/SQL suporta limbajul de manipulare a datelor si comenzile de control a tranzactiei in SQL. Comenzile DML (data manipulation language) pot procesa coloane multiple.

Compararea tipurilor de instructiuni SQL si PL/SQL:

Un bloc PL/SQL nu este o unitate de tranzactie. Commit-urile, savepoint-urile si rollback-urile sunt independente de blocuri, dar putem apela aceste comenzi intr-un bloc.

PL/SQL nu suporta limbajul de definire a datelor (DDL), cum ar fi CREATE TABLE, ALTER TABLE sau DROP TABLE.

PL/SQL nu suporta limbajul de control a datei (DCL), cum ar fi GRANT sau REVOKE.