CAPITOLUL 20
REPREZENTAREA DETELOR
In acest unit vom discuta tipurile de date suportate de PL/SQL, variabile scalare, si cum pot fi manipulate datele si expresiile.
Acest unit introduce de asemenea modul de utilizare al PL/SQL cu SQL*Plus, care este o cale interactiva convenabila pentru testarea blocurilor pe care le veti scrie.
* Sintaxa de BAZA a PL/SQL
Din moment
+ instructiunile pot avea mai multe linii daca este necesar dar
cuvintele cheie nu pot avea mai multe linii.
+ Unitatile lexicale (identificatorii, numele de operatori) pot
fi separate de unul sau mai multe spatii, sau alt delimitator
care nu poate face parte din unitati lexicale
+ Cuvintele rezervate (Vezi apendizul B) nu pot fi folosite ca
identificatori decit marcate in ghilimele duble( de ex.
"SELECT"_
+ Identificatorii trebuie sa inceapa cu o litera si pot contine
pina la 30 de caractere.
+ Sirurile trebuie marcate cu ghilemele simple.
+ Numerele pot fi reprezentate prin valoarea lor (ex. -32.65)
sau scrierea stiintifica ( ex. 2E5 inseamna 2x10 la puterea 5
= 200000 ).
+ Comentariile pot fi marcate cu simbolurile /* si */, caz in
care comentariile se pot lungi pe mai multe linii ( tip 'C')
sau incepind cu '--' in care sfirsitul liniei marcheaza
sfirsitul comentariului.
Exemplu:
/* Acesta comentariu se prelungeste pe
mai multe linii. Orice secventa de cod
aflata aici este considerata comentariu. */
COMMIT; --Aici se termina transferul
* Delimitatori
Delimitatorii sunt simboluri sau simboluri compuse,care au o semnificatie speciala pentru PL/SQL. Veti recunoaste multe dintre acestia ca operatori pentru SQL.
Simboluri simple - Acestea sunt formate dintr-un singur caracter
-------- ----- ------ -------- ----- ------
+ operatorul de adunare
- operatorul de scadere/negare
* operatorul de multiplecare
/ operatorul de impartire
= operator relational
> operator relational
relational
!= -||-
^= -||-
= -||-
:= asignare
=> asociere
.. rang
|| concatenare
> -||-
-- comentariu
/* -||-
*/ -||-
Retineti ca spatiile nu sunt permise intre cele doua caractere ale simbolurilor combinate.
* Blocurile Incluse
Anumite blocuri pot fi incluse in altele, in orice fel in care instructiunea o permite( nu se recomanda sa se include blocuri in cadrul unor bucle). Partea executabila a unui bloc poate fi sparta in blocuri mai mici, si de asemenea sectiunea EXCEPTION poate include alte blocuri.
Exemplu:
BEGIN
-- actiuni in bucla principala
DECLARE
-- declaratii de obiecte
BEGIN
-- actiuni ale blocului
EXCEPTION
-- ce'i de facut daca apare o eroare in acest bloc.
END;
-- alte actiuni in blocul principal
EXCEPTION
WHEN errors_trickle_through THEN
BEGIN
-- minuire a erorilor in blocul propriu
EXCEPTION
-- ce'i de facut daca tratarea unei erori
-- merge gresit !!
END;
END;
Retineti ca sectiunea DECLARE este optionala ; exemplul prezentat contine citeva blocuri fare sectiunea DECLARE.
Domeniul OBIECTELOR
"Domeniul" unui obiect este zona unui program in care acest obiect poate fi folosit. Scurt zis este 'Timpul de viata'. Aceste reguli se aplica toturor obiectelor declarate, incluzind variabile, cursor, constante si exceptii definite de utilizator.
Domeniul unui obiect este blocul complet in care obiectul este declarat, inclusiv oricare sub-bloc inclus in acest bloc.
Obiectele declarate in sub-blocuri sunt valabile pina cind sub-blocul se termina.
Exemplu:
DECLARE
X NUMBER; ----- ----- ------|
BEGIN |
|
... | domeniul
| lui
DECLARE | x
Y NUMBER; -----| |
| domeniul lui y |
BEGIN | |
... | |
END; -----| |
|
... |
END; ----- ----- -----|
Retineti ca pe timpul 'Timpului de viata' al lui y,suntem tot in domeniul lui 'x', si putem defini expresii care sa foloseasca ambele variabile.
Sa presupunem ca variabila din sub-bloc nu se numeste 'y' ci 'x'.
Cu alte cuvinte, are acelasi nume ca si variabila din sub-bloc.
DECLARE
x NUMBER; -- variabila x din blocul principal
BEGIN
...
DECLARE
x NUMBER; -- variabila x din sub-bloc
BEGIN
...
END;
...
END;
Doua obiecte pot avea acelasi nume, dar definite in doua blocuri diferite. Acolo unde se intilneste aceasta situatie, ca in exemplul anterior, atunci numai obiectul declarat un sub-bloc (acesta este blocul curent) poate fi folosit.
Reguli de VIZIBILITATE
In exemplul anterior, variabila 'x' din sub-bloc dezactiveaza folosirea variabilei 'x' din blocul principal pina cind variabile intilnesc sfirsitul de valabilitate. In cadrul sub-blocului, variabila 'x' din blocul principal exista dar nu este vizibila.
* Declararea de Variabile si de constante
PL/SQL permite o varietate de tipuri de date ce pot fi folosite pentru declararea de variabile si de constante. Variabile in mod optional pot avea asignate valori la declarare, si pot sa-si schimbe valoarea prin asignari ulterioare in cadrul blocului.
Constatele sunt identificatori care pastreaza o valoare fixa care trebuie asignata cind constanta este declarata.
* Tipurile de DATE SCALARE
Aceste sunt tipurile de date care reprezinta o singura valoare. Principalele tipuri de date sunt acelea ce corespund coloanei de tipuri in tabela ORACLE. PL/SQL suporta de asemenea tipurile booleene.
TIPUL NUMERIC
NUMBER Valoare numerica cu precizia de 38 de cifre. Valorile sunt
rotunjite la numere intregi mai putin atunci cind este data
scala.( ex. NUMBER(7,2) inseamna 7 cifre, cu 2 zecimale.
Implicit precizia este de 38 cifre.)
TIPUL CARACTER
VARCHAR2 Folosit pentru a memora variabile de tip caracter.Lungime
implicita 1 caracter. Maxima permisa 32767.
ex. VARCHAR2(30)
CHAR PL/SQL Versiunea 1:la fel ca VARCHAR2, dar cu lungimea maxima
de 255
PL/SQL Versiunea 2:Lungimea poate fi mai mare de 32767
caractere.
TIPUL BOOLEAN
BOOLEAN Pentru minuirea valorilor booleene TRUE si FALSE. Rezultatul
expresiilor booleene poate fi asignat acestor variabile si
testat mai tirziu in instructiuni conditionale.
TIPUL DATA
DATE Datele valide sunt intre 4712 BC si 4712AD.
* Declararea VARIABILELOR
Variabilele PL/SQL pot fi declarate si optional le pot fi asignate o valoare initiala, in sectiunea DECLARE a unui bloc. Alte variabile referite in partea declarativa trebuie declarate separat in declaratiile anterioare.
Sintaxa:
indentificator tipdata [(precizia,scala)] [NOT NULL] [ := expresie ] ;
Unde 'expresie' poate fi o alta variabila, sau o expresie cu functii si operatori. Daca o valoare initiala nu este asignata, atunci variabila va contine o valoare nula pina cind ii va fi asignata ulterior o valoare. Constanta NOT NULL nu mai este permisa in aceasta situatie.
Exemple:
v_count NUMBER NOT NULL := 0;
v_salary NUMBER(7,2);
v_annsal NUMBER(9,2) := month_sal * 12;
-- mount_sal trebuie sa existe deja
postcode CHAR(7);
surname VARCHAR2(25) := 'Skywalker' ;
v_message VARCHAR2(80) := 'May the course be woth you!' ;
married BOOLEAN := FALSE;
today DATE := SYSDATE;
Este indicat sa nu alegeti acelasi nume (identificator) pentru o variabila ca nume pentru coloanele din tabela folosite in cadrul blocului. Daca variabilele PL/SQL apar in instructiuni SQL si au acelasi nume ca coloana, atunci ORACLE considera ce se refera coloana.
* Declararea CONSTANTELOR
Constantele permit asignarea unei valori unui identificator, pentru o viitoare referire in cadrul unui bloc. Orice incercare de a modicica valoarea unei constante va cauza producerea unei erori PL/SQL.
Sintaxa:
identificator CONSTANT tipdata [(precision,scala)] := expresie ;
Exemple:
pi CONSTANT NUMBER(9,5) := 3.14159 ;
vat CONSTANT NUMBER(4,2) := 17.5 ;
* ATRIBUIRILE si EXPRESIILE
Instructiunile de atribuire PL/SQL permit unei valori de a fi atribuite sau reatribuite unei variabile dupa sectiunea DECLARE din bloc. Variabila care va primi noua valoare trebuie explicit numita in partea stinga a operatorului de asignare. Asta este, nu se poate face referinta indirecta via o alta variabila.(Unele unitati PL/SQL cum ar fi SQL*Forms includ unele facilitati pentru adresare indirecta.)
Sintaxa:
identificator := expresie ;
Unde 'identificator' este numele variabilei tinta, sau cimp, pentru a primi valoarea 'expresie'.
'Expresie' poate fi literal, numele unei alte variabile existente, sau alta expresie necesara pentru a determina valoarea ce va fi asignata. De obicei, cele mai multe din expresiile disponibile in inderogarile SQL , clauza SELECT sunt disponibile aici. Functiile si expresiile permise in PL/SQL vor fi discutate mai tirziu in acest UNIT.
Exemple:
v_count := v_count +1;
ann_sal := mount_sal * 12 +NVL(comm,0);
lev := 6;
mess := 'The current level is ' || TO_CHAR(lev) ;
v_ename := 'KING';
Valorile pe care le pot lua variabilele booleene pot fi TRUE si FALSE, ca rezultat a unei expresii booleene.
Exemple:
overpaid := ann_sal > 35000 ;
male := UPPER(title) == 'MR' ;
salary_ok := NOT overpaid ;
female := NOT male ;
switch1 := TRUE;
switch2 := FALSE;
Accesarea variabilelor NON PL/SQL
Variabilele declarate in mediul de lucru 'masina' pot fi referite in instructiuni PL/SQL. Aceasta include variabilele de lucru ale masinii declarate in programele precompilate. Adresarea variabilelor non-PL/SQL trebuie facuta prin prefixarea cu ':' pentru a le distinge de declararea de variabile PL/SQL.
Exemple:
:screen_field1 := ann_sal ;
:warp_factor := 9 ;
:global.var1 := 'YES' ;
Detalii despre modul de minuire al variabilelor NON PL/SQL sunt descrise in cursuri ORACLE special destinate acestor variabile.
* Conversii de tipuri de date
PL/SQL incearca sa converteaza dipurile de date in mod dinamic daca sunt mixate in cadrul unei instructiuni. De exemplu daca un numar este asignat unei variabile CHAR, atunci in mod automat numarul este translatat astfel incit va putea fi stocat in cariabila de tip CHAR.
Exemple:
sal_message VARCHAR2(12) := 3500;
Situatia inversa se aplica de asemenea,
salary NUMBER(12,2) := sal_message;
Oricum, o eroare va fi generata daca valorile sunt incompatibile:
salary := 'King earns $5000'; -- eroare
Dovedind ca sunt compatibile, se pot asigna caractere in variabile DATE si vice-versa:
today VARCHAR2(9) := SYSDATE ;
hired DATE := '21-JAN-1992' ;
In cadrul expresiilor trebuie sa va asigurati ca tipurile de date sunt aceleasi. Daca apar tipuri diferite de date in aceiasi expresie, atunci se foloseste cea mai potrivita functie de conversie:
TO_CHAR
TO_DATE
TO_NUMBER
PL/SQL va incerca sa faca conversia, daca este posibil. De obicei, PL/SQL este mai pretentios decit SQL daca tipurile de date sunt mixate in cadrul unei expresiilor. Deci daca nu sunteti sigur, folositi cea mai potrivita functie de conversie.
Exemplu:
v_message VARCHAR2(80) := 'SCOTT earns' || TO_CHAR (month_sal*12) ;
Precedenta operatorilor
Operatorii logici, aritmetici si de concatenare folositi in PL/SQL sunt aceeasi ca in SQL. In plus mai exista un operator exponential(**) .
Ordinea in care operatiile sunt realizate poate fi controlata folosind paranteze, ca in SQL.
Reamintim ordinea naturala a operatiilor in cadrul unei expresii:
Ordinea de evaluare a operatorilor
-------- ----- ------ -------- ----- ------ -----------|
| | OPERATOR | OPERATIE |
|-------- ----- ------ -------- ----- ------ ----------|
|Primul | ** , NOT | exponential, negatie logica|
| | + , - | identitate, negatie |
| | * , / | inmultire, impartire |
| | + , - , || | adunare, scadere, |
| | | concatenare |
| | = , != , , = | comparatie |
| | IN NULL, LIKE, BETWEEN, IN | |
| | AND | conjunctie |
|Ultimul| OR | incluziune |
|-------- ----- ------ -------- ----- ------ ----------|
* Functii interne
Multe din functiile disponibile in SQL sunt de asemenea disponibile si in PL/SQL. Acestea includ:
+ Functii numerice pentru o singura linie
+ Functii caracter pentru o singura linie
+ Functii de conversie de tip de data
+ Functii de timp
+ Functii diverse
In plus PL/SQL ofera doua functii pentru minuirea raportatea erorilor denumite SQLCODE si SQLERRM care vor fi discutate in unitul urmator.
Functii nedisponibile in instructiuni structurale sunt:
GREATEST and LEAST
AGV, MIN, MAX, COUNT, SUM, STDDEV si VARIANCE
* Blocuri PL/SQL in buffere SQL
|-------- ----- ------ ----- ----- --------- ----- --------|
| SQL> DECLARE |
| 2 x NUMBER(7,2); |
| 3 BEGIN |
| 4 SELECT sal INTO x FROM emp WHERE empno= &&n; |
| 5 IF x |
|-------- ----- ------ ----- ----- --------- ----- --------|
+ Buffer-ul se inchide cu un '.'
+ Executia buffer-ului de face cu RUN sau '/'
Folosirea PL/SQL in SQL*Plus
Limbajul PL/SQL este permis si in SQL*Plus Versiunea 3.0 sau mai mare. Deoarece SQL*PLUS foloseste unitatea de executie PL/SQL in RDBMS, este necesar ca ORACLE RDBMS sa foloseasca aceasta facilitate. Aceasta optiune este:
Procedural Option (Oracle7 Server)
SQL*Plus este un bun instrument pentru testarea blocurilor PL/SQL,de aceea il vom folosi si noi pentru partea practica a cursului.
Sunt doua cai pentru a prelucra blocuri PL/SQL in SQL*Plus:
+ Se defineste un bloc in buffer-ul SQL, si apoi se ruleaza
+ Se defineste un bloc ca parte a unui script SQL*Plus, si apoi
se ruleaza scriptul
* Declararea de blocuri PL/SQL in buffere SQL
Acolo unde PL/SQL nu se foloseste, bufferul SQL poate contine numai o singura instructiune SQL. Daca se plaseaza o alta instructiune in buffer aceasta o suprascrie pe prima. De acea un bloc PL/SQL este tratat ca o instructiune continua iar ':' intr-un bloc un inchid si nici nu ruleaza bufferul.
SQL*Plus detecteaza inceputul unui bloc PL/SQL dupa introducerea cuvintelor cheie DECLARE si BEGIN la promptul 'SQL >'.Bufferul poate fi inchis fara a executa blocul sau programul prin introducerea unui '.' la prompt.
Exemplu:
SQL> DECLARE
2 x NUMBER(7,2);
3 BEGIN
4 SELECT sal INTO x FROM emp WHERE empno = 7788;
5 IF x
Continutul bufferului poate fi editat in maniera obisnuita sau salvat intr-un fisier folosind comanda SQL*Plus "SAVE".
Pentru a rula buffer-ul PL/SQL, tastati comanda RUN sau '/' la promptul SQL. Daca executia reuseste fara tratari de eroare atunci singurul mesaj care apare ar trebuie sa fie:
'PL/SQL procedure seccessfully completed'
* Folosirea de blocuri PL/SQL in cadrul fisierelor SQL*Plus
Fisiere SQL si comenzi SQL*Plus pot include blocuri PL/SQL care se comporta ca o singura instructiune in cadrul scriptului.Fisierul poate fi editat in maniera obisnuita, folosind comenzi ale sistemului de operare si editoare, sau cu ajutorul comenzii "SAVE" din buffer-ul SQL cum a fost mentionat mai inainte.
Retineti ca comenzile SQL*Plus nu pot apare in interiorul blocului PL/SQL dar pot fi incluse ori unde in alta parte in fisier. Variabilele de substitutie din SQL*Plus pot fi referite din cadrul blocului, dar retineti ca ele sunt inlocuite cu continutul variabilelor INAINTE ca codul sa fie complet interpretat sau executat.
Exemplu:
SET ECHO OFF
BEGIN
INSERT INTO dept ( deptno, dname ) VALUES ( &1, ' &2 ' );
COMMIT;
EXCEPTION
WHEN dup_val on index THEN
INSERT INTO tav VALUES (' Duplicate departament No.');
END;
/
SELECT * FROM error tab;
SET ECHO ON
SQL*Plus - descoperind ce se intimpla
+ dupa rularea unui bloc PL/SQL, veti vedea fiecare mesaj de
tipul
o 'PL/SQL procedure successfully completed'
( procedura PL/SQL terminata cu succes )
o Parse-time Error MEssages
( mesaje de eroare, aparute dinamic in timpul
procesarii.)
o An 'Unhandled Exception' Message
( Mesaj de eroare netratata.)
+ Instructiunile PL/SQL nu pot scrie pe ecran !
o Mesajele sunt scrise intr-o tabela si dupa acea se
parcurge tabela
o Scrie mesajele intr-o variabila de asociere si apoi
foloseste comanda PRINT dupa ce blocul a rulat.
o Se apeleaza o procedura care afiseaza mesajele pe ecran
Cum se pot afla rezultatele actiunilor PL/SQL
Daca apare vreo eroare netratata in timpul compilarii sau al executiei unui bloc, atunci sunt afisate mesaje pentru a informa utilizatorul ce eroare a aparut, o explicatie a erorii si o interpretare a liniei(lor) care au generat eroarea.
Oricum, continutul variabilelor si al tabelelor bazei de date nu pot fi afisate pe ecran in timpul executiei blocurilor PL/SQL in sesiuni SQL*PLus (cel putin pina cind nu sunt folosite procedurile pachetului DBMS_OUTPUT). Trebuie sa scrieti orice rezultat pe care doriti sa-l afisati intr-o tabela in baza de date si dupa executie sa parcurgeti tabela. Aceasta parcurgere poate fi facuta in acelasi fisier care cuprindea si blocul, ca in exemplul anterior.
Situatia este diferita in aplicatii cum ar fi SQL*Forms, unde blocurile PL/SQL pot afisa variabile direct pe ecran prin folosirea unor functii interne.
Apelul Functiilor si Procedurilor PL/SQL memorate
Atunci cind subprograme (functii si proceduri) PL/SQL sunt memorate in baza de date, ele pot fi apelate ca parte a unui alt bloc PL/SQL, daca cel care face apelul are drepturi suficiente. Sintaxa generala a apelului este:
nume-Procedura [ ( param1, param2, param3, ... ) ];
La apelare, parametrii trebuie pusi in ordinea de definire. Aceasta inseamna si tipurile de date trebuie sa fie aceleasi cu cele definite. Anumiti parametrii pot avea valori implicite, dar daca o valoare este data ca parametru atunci toti parametrii anteriori trebuie folositi.
In exemplul urmator, procedura CLEAR_DEPT are doi parametrii disponibili. Primul parametru DEPARTAMENT_NAME ( datatype VARCHAR2 ) si al doilea parametru DELETE_DEPARTAMENT ( datatype BOOLEAN cu valoarea implicita FALSE ).
CLEAR_DEPT( `ACCOUNTING' ); -- al doilea parametru este implicit
CLEAR_DEPT ( `ACCOUNTING', TRUE);
Parametrii pot fi folositi alternativ impreuna cu numele formal, prin folosirea operatorului de asociere. In acest caz pozitia lor nu mai este importanta:
CLEAR_DEPT ( DELETE_DEPARTAMENT => TRUE,
DEPARTAMENT_NAME => `ACCOUNTING' ) ;
In final, deoarece sub-programele trebuie apelate din blocuri PL/SQL, se poate apela din promptul SQL in SQL*Plus prin impachetarea apelului cu BEGIN si END.
Aceasta poate fi realizata prin comanda EXECUTE din SQL*Plus.
SQL> EXECUTE CLEAR_DEPT( `ACCOUNTING' );
care este identica cu :
BEGIN CLEAR_DEPT( `ACCOUNTING '); END;
Unitul 20, EXERCITII
In fiecare din urmatoarele exercitii puteti crea blocuri PL/SQL in buffere SQL si puteti salva mai tirziu in fisier, sau puteti alternativ edita fisierul folosind un editor de texte al masinii. In ambele cazuri, retineti numele pe care il dati fiecarui fisier.
In multe din exercitii, aveti nevoie de inregistrarea rezultatelor intr-o tabela. Vom folosi pentru aceasta tabela MESSAGES. Definirea acesteia este urmatoarea:
Table MESSAGES
Coloana Descrierea
------------ -------- ----- ------ ----
NUMCOL1 NUMBER (9,2)
NUMCOL2 NUMBER (9,2)
CHARCOL1 VARCHAR2(60)
CHARCOL2 VARCHAR2(60)
DATECOL1 DATE
DATECOL2 DATE
1. Creati un bloc, cu patru variabile:
o V_BOOL1 Boolean
o V_BOOL2 Boolean
o V_CHAR Character ( lungime variabila)
o V_NUM Number
si apoi atribuiti valori variabilelor dupa cum urmeaza:
Variabila Valoare
------------- -------- ----- ------ --------
V_CHAR Literar `42 este raspunsul'
V_NUM Primele doua caractere din V_CHAR
V_BOOL1 TRUE si FALSE functie de cum este V_NUM
fata de 100
V_BOOL2 Opus lui V_BOOL1
2. Scrieti si rulati un bloc PL/SQL care accepta soua numere din
variabile PL/SQL. Primul din numere trebuie "ridicat la
putere" cu cel de-al doilea numar, in cadrul blocului, si
rezultatul scris intr-o variabila PL/SQL. INregistrati
rezultatul in tabela MESSAGES, si alternativ in variabila de
asociere SQL*Plus. (Operatorul exponential in PL/SQL este
'**' ).
Unit 20 SOLUTII
1.
DECLARE
V_BOOL1 BOOLEAN;
V_BOOL2 BOOLEAN;
V_CHAR VARCHAR2(16); -- cel putin 16 caractere
V_NUM NUMBER(2);
BEGIN
V_CHAR := '42 este raspunsul';
V_NUM := SUNSTR(V_CHAR,1,2); -- conversie dinamica la mumar
V_BOOL1 := V_NUM
2.
DECLARE
V_RESULT NUMBER(9,2);
BEGIN
V_RESULT := &main_var ** &exponent ;
-- sau POWER(&main_var,&exponent);
INSERT INTO messages(numcol1) VALUES ( V_RESULT ) ;
END;
Sau o solutie alternativa folosind SQL*Plus :
BIND VARIABILES .............
VARIABILE V_RESULT NUMBER
BEGIN
:V_RESULT := &main_var ** &exponent;
END;
PRINT V_RESULT
|
