Home - Rasfoiesc.com
Educatie Sanatate Inginerie Business Familie Hobby Legal
Doar rabdarea si perseverenta in invatare aduce rezultate bune.stiinta, numere naturale, teoreme, multimi, calcule, ecuatii, sisteme



Biologie Chimie Didactica Fizica Geografie Informatica
Istorie Literatura Matematica Psihologie

Sql


Index » educatie » » informatica » Sql
» Sintaxa instructiunilor PL/SQL


Sintaxa instructiunilor PL/SQL




Sintaxa instructiunilor PL/SQL

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:=’Henderson’;

Numerele pot fi simple valori sau notatii

stiintifice

Constantele

O constanta este un numar, caracter, sir de caractere, sau valoare de tip boolean nereprezentata de un identificator.

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

Constanta

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):=’Europe’;

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




loading...




Politica de confidentialitate


Copyright © 2020 - Toate drepturile rezervate