 |
|
 |  |
|
|
| |
 | Biologie | Chimie | Didactica | Fizica | Geografie | Informatica |
| Istorie | Literatura | Matematica | Psihologie |
Obiective
Dupa parcurgerea acestei lectii, veti putea sa:
Recunosteti semnificatia sectiunii
executabile
Scrieti declaratii in sectiunea executabila
Descrieti regulile blocurilor incluse
Executa si testa un bloc PL/SQL
Folosi conventiile de programare
Scopul lectiei
In aceasta lectie, veti invata sa scrieti cod executabil intr-un PL/SQL bloc. Veti mai invata regulile de includere a blocurilor, precum si cum se executa si testeaza aceste blocuri.
Sintaxa blocurilor PL/SQL
Declaratiile pot continua peste cateva
linii
Unitatile lexicale pot fi separate de spatii:
-delimitatori
-identificatori
-constante
-comentarii
Deoarece PL/SQL este o extensie SQL, regulile de sintaxa
SQL se pot aplica si limbajului PL/SQL.
Unitatile lexicale (de exemplu identificatori sau valori)
pot fi separate de unul sau mai multe spatii sau de alti delimitatori care nu apartin unitatii lexicale. Nu pot fi incluse spatii in unitatile lexicale exceptie facand constantele de tip sir de caractere sau comentariile.
Declaratiile pot fi "taiate" de linii, dar nu si cuvintele rezervate.
Delimitatori
Delimitatorii sunt simboluri simple sau compuse care au o semnificatie speciala pentru PL/SQL.
Simboluri simple Simboluri compse
|
simbol |
semnificatie |
simbol |
semnificatie |
|
^ |
operator adunare |
<> |
operator relational |
|
- |
operator scadere/negare |
operator relational |
|
|
* |
operator multiplicare |
operator concaternare |
|
|
/ |
operator divizare |
indicator comentariu linie |
|
|
# |
operator relational |
inceput comentariu |
|
|
@ |
indicator acces |
sfarsit comentariu |
|
|
; |
indicator sfarsit |
operator asignare |
|
|
** |
operator putere |
Sintaxa blocurilor PL/SQL
Identificatorii
pot contine pana la 30 caractere
nu pot contine cuvinte rezervate
exceptand cazul cand acestea sunt incluse
in ghilimele duble
trebuie sa inceapa cu un caracter alfabetic
nu trebuie sa aiba acelasi nume ca al
unei coloane dintr-o baza de date folosita
Identificatori
Identificatorii sunt folositi pentru a denumi unitati si obiecte de program PL/SQL, adica constante, variabile, exceptii, cursoare, variabile tip cursor, subprograme, si pachete.
Identificatorii pot contine pana la 30 caractere, dar trebuie sa inceapa cu un caracter alfabetic.
Nu trebuie alese aceleasi nume pentru identificatori ca cele ale coloanelor bazelor de date folosite in bloc. Daca apare acest caz si identificatorii se gasesc in aceeasi parte declarativa SQL, atunci se considera ca se face referire la coloana.
Cuvintele rezervate nu pot fi folosite ca identificatori, mai putin cand sunt incluse intre ghilimele duble( de exemplu, "SELECT").
Cuvintele rezervate ar trebuie scrise cu litere mari pentru o mai buna lizibilitate a codului.
Sintaxa blocurilor PL/SQL
Constante
Constantele de tip caracter si data trebuie
sa fie incluse intre ghilimele simple
v_ename:='
Numerele pot fi simple valori sau notatii
stiintifice
Constantele
O
Constantele de tip caracter contin toate caracterele tiparibile din setul de caractere PL/SQL: litere, numere, spatii si simboluri speciale.
Constantele numerice pot fi o valoare simpla (de exemplu, -32.5) sau o notatie stiitifica (de exemplu,2E5, reprezinta 2*10 la puterea 5 adica 200000).
Comentarea codului
O linie de comentariu incepe cu "- -"
Comentariile multilinie se includ intre /* si */
Exemplu
.
v_sal NUMBER(9,2);
BEGIN
/* Compute the annual salary based on
the monthly salary input from the user*/
v_sal:=v_sal * 12;
END; -- This is the end of the transaction
Comentarea codului
Este indicata comentarea fiecarei faze de programare pentru a usura depanarea. Pentru a comenta o singura linie de cod se insereaza la incepul ei doua caractere minus (--), iar in cazul a mai multor linii acestea se incadreaza intre /* si */.
Exemplu
Calculul salariului anual pe baza celor lunare:
.
v_sal NUMBER(9,2);
BEGIN
/* Compute the annual salary based on
the monthly salary input from the user*/
v_sal:=v_sal * 12;
END; -- This is the end of the transaction
Functii SQL in PL/SQL
Disponibile:
-de tip numeric
-de tip sir de caractere
-de tip conversie data
-de tip data
Nedisponibile:
-GREATEST
-LEAST
-DECODE
-de tip grup
Functii SQL in PL/SQL
Majoritatea functiilor din SQL se regasesc si in PL/SQL:
functii numerice
functii siruri de caractere
functii conversie data
altele
Urmatoarele functii nu mai sunt disponibile:
GREATEST, LEAST si DECODE.
Functiile grup: AVG, MIN, MAX, COUNT, SUM,
STDDEV si VARIANCE. Functiile grup aplicate
grupurilor de linii a unei baze de date pot fi folosite
numai intr-o instructiune de tip SQL.
Exemplu
Calculul sumei tuturor numerelor memorate intr-o baza NUMBER_TABLE PL/SQL.Acest exemplu va da o eroare la compilare.
v_total := SUM(number_table);
Functii PL/SQL
Exemple
Completarea unei liste cu adresele
angajatilor
v_mailing_address:=v_name||CHR(10)||v_address
||CHR(10)||v_state||CHR(10)||v_zip;
Convertirea in litere mici a numelor
angajatilor
v_ename:= LOWER(v_ename);
Functii PL/SQL
PL/SQL pune la dispozitie utilizatorilor multe functii puternice pentru manipularea datelor. Acestea se pot grupa:
de tip error-reporting
de tip numeric
de tip caracter
de tip conversie
de tip data
altele
CHR este o functie SQL ce converteste un cod ASCII in caracterul corespunzator lui; 10 este codul pentru linie noua.
Conversii de tipuri de date
Conversia din tipul data in tipuri comparabile
Mixarea tipurilor de date poate conduce la
erori
Functii de conversie
BEGIN
-TO_CHAR SELECT TO_CHAR(hiredate,
-TO_DATE 'MON, DD, YYYY')
-TO_NUMBER FROM emp;
END;
Conversii de tipuri de date
PL/SQL incearca sa converteasca dinamic tipurile de date daca intalneste intr-o atribuire tipuri diferite. De exemplu, daca se atribuie o valoare numerica uneia de tip caracter, atunci PL/SQL automat converteste numarul in tip caracter, pentru ca sa-l poata retina intr-o variabila caracter. La fel este tratata si situatia inversa.
Analog, se poate asigna o variabila caracter uneia de tip data, precum si invers.
Intr-o expresie trebuie avut grija ca tipurile de date sa fie aceleasi. Daca totusi apar astfel de situatii, trebuie folosite functiile de conversie corespunzatoare.
Sintaxa
TO_CHAR (value, fmt)
TO_DATE (value, fmt)
TO_NUMBER (value, fmt)
unde: value este un sir de caractere, numar sau data.
fmt este un model de conversie.
Conversii de tipuri de date
Aceasta linie de cod va da o eroare de compilare
v_comment:=USER||': '||SYSDATE;
Pentru a corecta aceasta eroare, se foloseste
functia TO_CHAR
v_comment:=USER||': '||TO_CHAR(SYSDATE);
Conversii de tipuri de date
Conversiile date ca exemplu mai sus se definesc astfel:
se atribuie variabilei v_comment o valoare ce este
compusa din numele utilizatorului si data curenta.
Aceasta atribuire va cauza o eroare de sintaxa.
pentru a corecta aceasta eroare, se converteste SYSDATE la un sir de caractere cu ajutorul functiei TO_CHAR.
PL/SQL incearca conversia daca este posibil, dar succesul ei depinde de operatia ce se executa. Este indicat a se realiza explicit conversii de tip, deoarece ele maresc performanta softului ramanand corecte chiar daca se face o modificare ulterioara.
Blocuri incluse si domeniul de
valabilitate al variabilelor
O secventa de cod poate fi inclusa oriunde
o secventa de cod executabila este permisa.
Un bloc inclus devine o secventa de cod.
O sectiune exception poate include blocuri.
Aria de valabilitate a unui obiect este
regiunea de program care poate referi acel
obiect.
Blocuri incluse
Unul din avantajele pe care PL/SQL le are asupra SQL este posibilitatea de a include sectiuni de cod. Se pot include blocuri oriunde o sectiune de cod executabila este permisa, blocurile incluse devenind astfel sectiuni de cod. De aceea, partile executabile ale unui bloc pot fi impartite in blocuri mai mici. Sectiunea exception poate include deasemenea blocuri.
Aria de valabilitate a variabilelor
Aria de valabilitate a unui obiect este regiunea de program
care poate referi acel obiect. Se pot referi variabilele declarate in
cadrul sectiunilor executabile.
Blocuri incluse si domeniul de
valabilitate a variabilelor.
Domeniul de vizibilitate a unui identificator
in cadrul unui bloc se stabileste dupa
urmatoarele reguli:
un identificator declarat intr-un bloc inclus
nu este vizibil in exteriorul acelui bloc
un identificator declarat in interiorul unui
bloc este vizibil in orice bloc inclus
Identificatori
Un identificator este vizibil in blocul in care este declarat si in toate subblocurile, procedurile si functiile incluse. Daca blocul nu gaseste indentificatorul declarat local atunci cauta in
blocurile in care este inclus( blocurile parinte ) , si niciodata in
blocurile incluse( blocurile fiu ).
Aceste reguli se aplica tuturor obiectelor declarate, incluzand variabile, cursoare, exceptii definite de utilizator si constante.
Blocuri incluse si domeniul de
valabilitate al variabilelor
Exemplu
x BINARY_INTEGER;
BEGIN Scope of x
DECLARE
y NUMBER;
BEGIN Scope of y
END;
END;
Blocuri incluse si domeniul de valabilitate a variabilelor
In cadrul blocurilor incluse de mai sus, variabila y poate referi variabila x. Variabila x nu poate referi variabila y. Valoarea unei variabile este valida doar in interiorul blocutilor in care este inclusa si in interiorul blocurilor incluse, nu si in afara acestora.
Domeniu
Domeniu unui identificator este acea regiune a unui program ( bloc, subprogram sau sectiune) din care se poate referi identificatorul.
Vizibilitate
Un identificator este vizibil numai in regiunile din care acesta se poate referi nefolosind un nume specific.
Operatori in PL/SQL
Logici
Aritmetici
De concatenare
Paranteze de control al ordinii operatiilor
-- aceasi ca in SQL
Operatorul exponential(**)
Ordinea operatiilor
Operatiile din cadrul unei expresii sunt executate intr-o ordine particulara depinzand de precedenta (prioritatea) lor. Urmatorul tabel arata ordinea operatiilor de sus in jos:
|
Operator |
Operation |
|
**,NOT |
exponenta,negare logica |
|
identitate,negare |
|
|
inmultire,impartire |
|
|
adunare,scadere,concatenare |
|
|
a,!a,<,>,<a,>a,ISNULL, LIKE,BETWEEN,IN |
comparare |
|
AND |
conjunctie |
|
OR |
incluziune |
Nota: Nu este necesar sa folosim paranteze in cadrul expresiilor booleene, dar sunt mai usor de citit daca le folosim.
Operatori in PL/SQL
Exemple
Incrementarea unui index pentru un loop
v_count :=v_count+1;
Setarea valorii unui flag boolean
v_equal :=(v_n1av_n2);
Verificarea nulitatii unui camp
v_valid :=(v_empno IS NOT NULL);
Operatori in PL/SQL
Lucrand cu valori nule, se pot evita cateva greseli comune respectand urmatoarele reguli:
Compararea cu valori nule are intotdeauna rezultat NULL;
Aplicarea operatorului NOT unei valori nule are ca rezultat NULL;
In cadrul instructiunilor de test daca o conditie este NULL, secventa asociata respectivei instructiuni nu se executa;
Reguli de respectat in programare
Faceti codul mai usor de inteles prin:
Documentarea codului cu comentarii
Stabilirea unor conventii case pentru cod
Stabilirea unor conventii de denumire a
identificatorilor si celorlalte obiecte
Alinierea liniilor de cod
Reguli de respectat in programare
Urmati aceste reguli de programare pentru a produce un cod clar si pentru a reduce timpul de depanare in dezvoltarea blocurilor PL/SQL.
Conventii de cod
Urmatorul tabel da reguli pentru scrierea codului cu caractere mari sau mici pentru a ajuta la distingerea cuvintelor cheie de celelalte obiecte.
|
Categorie |
Case Convention |
Exemple |
|
Blocuri SQL |
Uppercase |
SELECT,INSERT |
|
Cuvinte cheie PL/SQL |
Uppercase |
DECLARE,BEGIN,IF |
|
Tipuri de date |
Uppercase |
VARCHAR2, BOOLEAN |
|
Identificatori si variabile |
Lowercase |
v_sal,emp_cursor, g_sal,p_empno |
|
Baze de date si coloane |
Lowercase |
emp,orderdate, deptno |
Conventii de notare
Pentru a se evita ambiguitatile de notare:
Denumirile variabilelor locale si a
parametrilor formali sunt prioritare celor a
bazelor de date.
Denumirile coloanelor sunt prioritare
variabilelor locale.
Conventii de notare
Tabelul urmator prezinta un de prefixe si sufixe utile pentru deosebirea identificatorilor de alti identificatori, a obiectelor de tip baza de date, precum si a altor obiecte.
|
Identificator |
Conventie de notare |
Exemplu |
|
Variabila |
v_name |
v_sal |
|
|
c_name |
c_company_name |
|
Cursor |
name_cursor |
emp_cursor |
|
Exceptie |
e_name |
e_too_many |
|
Tip baza de date |
name_table_type |
amount_table_type |
|
Baza de date |
name_table |
order_total_table |
|
Tip inregistrare |
name_recor_type |
emp_record_type |
|
Inregistrare |
name_record |
customer_record |
|
SQL*Plus parametru substitutie |
p_name |
p_sal |
|
SQL*Plus variabila globala |
g_name |
g_year_sal |
Alinierea codului
Pentru claritate, este indicata alinierea fiecarui nivel de cod.
Exemplu:
DECLARE
v_deptno
NUMBER(2);
BEGIN v_location VARCHAR2(13);
IF xa0 THEN BEGIN
ya1; SELECT deptno,
END IF; location
END; INTO v_deptno,
v_location
FROM dept
WHERE dnamea'SALES';
END;
Alinierea codului
Pentru o mai buna lizibilitate a codului este indicata alinierea codului. Pentru a evidentia structurile, liniile de cod se "rup" folosind carriage return si apoi se insereaza spatii sau caractere tab. Comparatii urmatoarea structura IF din punct de vedere al lizibilitatii:
IF x>y THEN max:=x;ELSE max:=y;END IF;
IF x > y THEN
max := x;
ELSE
max := y;
END IF;
Determinarea domeniilor de
vizibilitate a variabilelor
Exercitiu
DECLARE
v_sal NUMBER(7,2):=60000;
v_comm NUMBER(7,2):=v_sal / .20;
v_message VARCHAR@(255):=' eligible for
commission';
BEGIN .
DECLARE
v_sal NUMBER(7,2):=50000;
v_comm NUMBER(7,2):=0;
v_total_comp NUMBER(7,2):=v_sal+v_comm;
BEGIN
v_message:='CLERK not'||v_message;
END;
v_message:='SALESMAN'||v_message;
END;
Exercitiu
Analizati blocul PL/SQL de mai sus. Determinati urmatoarele valori conform regulilor de vizibilitate:
1.valoarea v_message in blocul inclus
2.valoarea v_total_com in blocul principal
3.valoarea v_comm in blocul inclus
4.valoarea v_comm in blocul principal
5.valoarea v_message in blocul principal
Sumar
structura blocurilor PL/SQL:
-blocuri incluse si domenii de vizibilitate
programarea PL/SQL:
-functii
-conversii de tipuri de date
-operatori
-variabile bind
-conventii
Exercitii recapitulative
Reguli de includere si domenii de vizibilitate
Dezvoltarea si testarea blocurilor PL/SQL
Exercitii recapitulative
Aceasta recapitulare are ca scop aprofundarea conceptelor de baza PL/SQL prezentate in aceasta lectie, adica regulile de includere ale blocurilor, de testare si executare a lor.
1.Blocuri PL/SQL:
DECLARE
v_weight NUMBER(3):=600;
v_message VARCHAR2(255):='Product 11002';
BEGIN
SUB-BLOCK
DECLARE
v_weight NUMBER(3):=1;
v_message VARCHAR@(255):='Product 11001';
v_new_locn VARCHAR(50):='
BEGIN
v_weight:=v_weight+1;
v_new_locn:='Wstern '||v_new_locn;
END;
v_weight:=v_weight+1;
v_message:=v_message||' is in stock';
v_new_locn:='Western '||v_new_locn;
END;
Analizati acest bloc si determinati urmatoarele valori:
a. valoarea v_weight in sub-bloc
b. valoarea v_new_locn in sub-bloc
c. valoarea v_weight in blocul principal
d. valoarea v_message in blocul principal
e. valoarea v_new_locn in blocul principal
2.Domenii de vizibilitate
DECLARE
v_customer VARCHAR2(50):='Womansport';
v_credit_rating VARCHAR(50):='EXCELLENT';
BEGIN
DECLARE
v_customer NUMBER(7):=201;
v_name VARCHAR2(25):='Unisports';
BEGIN
v_customer v_name v_credit_rating
END;
v_customer v_name v_credit_rating
END;
Fie structura de mai sus. Variabilele v_customer si v_credit_rating sunt declarate in blocul principal, iar v_name si o alta variabila v_customer in sub-bloc. Determinati:
a.valoarea v_customer in subbloc este:
b.valoarea v_name in subbloc este:
c.vaoarea v_credit_rating in subbloc este:
d.valoarea v_customer in blocul principal este:
e.valoarea v_name in blocul principal este:
f.valoarea v_credit_rating in blocul principal este:
Creati si executati un bloc PL/SQL care accepta doua numere prin intermediul a doua variabile de substitutie SQL*Plus. Primul numar trebuie impartit la al doilea iar rezultatul adunat la cel deaal doilea numar. Rezultatul trebuie scris intr-o variabila PL/SQL si afisat pe ecran.
Please enter the first number: 2
Please enter the second number: 4
PL/SQL procedure successfully completed.
V_RESULT
--------
4.5
Construiti un bloc PL/SQL care calculeaza compensatia totala pentru un an. Salariul anual si bonusul anual sunt trimise blocului PL/SQL prin doua variabile de substitutie SQL*Plus bonusul trebuind sa fie convertit dintr-un numar intreg in zecimal ( de exemplu 15 in .15). Daca salariul este nul, dati-i valoarea zero inainte de a calcula valoarea compensatiei. Folositi functia NVL pentru valorile nule.
Nota: Pentru a test functia NVL datI NULL la prompt; apasand <return> rezulta un mesaj de eroare (missing expression error).
Please enter the salary amount: 50000
Please enter the bonus percentage: 10
PL/SQL procedure successfully completed.
G_TOTAL
-------
55000
Copyright © 2024 - Toate drepturile rezervate
