y2j23jc
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 ce PL/SQL este o extensie pentru SQL, regulele sintactice de baza aplicabile la SQL sunt aplicabile de asemenea si la PL/SQL.Iata un rezumat al acestora: 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

< operator relational

) delimitator de lista sau expresii

( -||-

; delimitator de instructiuni

, separator de obiect

. selector de componenta

@ delimitator de access la distanta

' delimitator de sir

: delimitator de masina

Simboluri Compuse - Sunt formate din doua caractere

---------------------------------------------------------------------

** exponential

<> relational

!= -||-

^= -||-

<= -||-

>= -||-

:= asignare

=> asociere

.. rang

|| concatenare

<< eticheta

>> -||-

-- 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 a(precizia,scala)i aNOT NULLi a := expresie i ;
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 a(precision,scala)i := 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<3000 THEN |

| 6 UPDATE emp SET sal=3000; |

| 7 WHERE empno = &&n; |

| 8 END IF; |

| 9 END; |

| 10. |

| SQL> |

|-----------------------------------------------------------------|

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 < 3000 THEN UPDATE emp SET sal = 3000

6 WHERE empno = 7788;

7 END IF;

8 END;

9 .

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
'PL/SQL procedure successfully completed'
( procedura PL/SQL terminata cu succes )
Parse-time Error MEssages
( mesaje de eroare, aparute dinamic in timpul procesarii.)
An 'Unhandled Exception' Message
( Mesaj de eroare netratata.)
Instructiunile PL/SQL nu pot scrie pe ecran !
Mesajele sunt scrise intr-o tabela si dupa acea se parcurge tabela
Scrie mesajele intr-o variabila de asociere si apoi foloseste comanda PRINT dupa ce blocul a rulat.
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 a ( param1, param2, param3, ... ) i;

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

Creati un bloc, cu patru variabile:
V_BOOL1 Boolean
V_BOOL2 Boolean
V_CHAR Character ( lungime variabila)
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

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

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 < 100;

V_BOOL2 := NOT V_BOOL1;

END;

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