	Biologie	Chimie	Didactica	Fizica	Geografie	Informatica
	Istorie	Literatura	Matematica	Psihologie

Sql

Index » educatie » » informatica » Sql
» Programarea in Oracle utilizand PL/SQL

Programarea in Oracle utilizand PL/SQL

Programarea in Oracle utilizand PL/SQL

Procedural Language/Structured Query Language (PL/SQL) este o extensie procedurala a limbajului SQL; e un limbaj de acces a datelor pentru baze de date obiect relationale care permite gruparea unei multimi de comenzi intr-un bloc unic de tratare a datelor.

Limbajul SQL este limbajul standard in lumea bazelor de date, comun (sub diferite versiuni) sistemelor de gestiune a bazelor de date. SQL este un limbaj de cereri non-procedural care permite accesul la date, precizand ce trebuie si cum trebuie obtinut.

Limbajul PL/SQL este un limbaj de programare de tip structurat, apropiat de limbajul PASCAL, specific sistemului Oracle. Cererile sunt formulate intr-o maniera clasica, procedurala.

PL/SQL include instructiuni SQL pentru manipularea datelor (INSERT, UPDATE, DELETE, SELECT) si instructiuni pentru gestiunea tranzactiilor (COMMIT, ROLLBACK). De asemenea, include instructiuni proprii (BEGIN, END, DECLARE, atribuire . ).

Structura unui bloc PL/SQL

[nume_bloc]

[DECLARE

instructiuni de declarare]

BEGIN

instructiuni executabile (SQL sau PL/SQL)

[EXCEPTION

tratarea erorilor]

END [nume _bloc] ;

Tipuri de blocuri:

anonime, cu structura:

[DECLARE]

BEGIN

-- instructiuni

[EXCEPTION]

END;

proceduri, cu structura:

PROCEDURE nume

BEGIN

-- instructiuni

[EXCEPTION]

END;

- functii, cu structura:

FUNCTION nume

RETURN tip_data

BEGIN

--instructiuni

RETURN valoare;

[EXCEPTION]

END;

Lucrul cu variabilele in PL/SQL

Variabilele se declara in sectiunea declarativa a unui bloc PL/SQL. Se aloca spatiu in memorie pentru numele variabilei, tipul de data, valoare. Tot in aceasta sectiune se poate atribui o valoare initiala unei variabile sau poate fi impusa o constrangere NOT NULL.

In cadrul sectiunii executabile (de executie) pot fi asignate noi valori variabilelor, caz in care valorile existente ale variabilelor vor fi inlocuite cu altele noi.

Transmiterea valorilor in cadrul blocurilor PL/SQL se pot realiza prin intermediul parametrilor.

Vizualizarea rezultatelor se poate face prin intermediul "variabilelor de iesire".

Tipuri de date in PL/SQL

scalar, pastreaza o singura valoare

compus, permite definirea si manipularea grupurilor de campuri in cadrul blocurilor PL/SQL-de exemplu, record (inregistrare), table(tabel)

pointer

LOB(Large Object)- de exemplu, BLOB- pt. imagini, BFILE- pt. filme

Tipul scalar poate fi:

numeric: NUMBER[precizie, scala], BINARY_INTEGER, PLS_INTEGER

caracter/sir de caractere: CHAR[lungime_maxima], VARCHAR2(lungime_maxima), LONG

data calendaristica: DATE

logic (BOOLEAN).

Exemple:

TRUE reprezinta o valoare Booleana

15-MAR-03 reprezinta o data calendaristica

o fotografie se reprezinta printr-o data de tip BLOB

textul unui discurs se reprezinta printr-un LONG RAW

3277748.02 reprezinta un tip numeric cu precizie

un film se reprezinta printr-o data de tip BFILE

numele unui oras se reprezinta prin tipul VARCHAR2

Declararea variabilelor

Sintaxa:

identificator [CONSTANT] [NOTT NULL]

[ := | DEFAULT expr];

Constantele se declara similar cu variabilele, dar se foloseste cuvantul cheie CONSTANT si imediat i se atribuie o valoare.

Atributul %TYPE declara o variabila avand acelasi tip cu o coloana a unui tabel sau cu o variabila care a fost declarata deja. In primul caz se prefixeaza %TYPE cu numele tabelului si al coloanei, iar in al doilea caz se prefixeaza cu numele coloanei.

PL/SQL suporta atributul %ROWTYPE prin care se declara un tip de date implicit, echivalent cu tipul unei linii dintr-un tabel al bazei de date. Elementele individuale ale acestei structuri de tip inregistrare sunt referite in maniera clasica, prefixand numele coloanei cu numele variabilei declarate.

Exemplu:

DECLARE

v_hiredate DATE;

v_deptno NUMBER(2) NOT NULL:=10;

v_location VARCHAR2(12):='ATLANTA';

c_comm CONSTANT NUMBER:=1500;

Asignarea (atribuirea) unor valori variabilelor

Sintaxa:

identificator:=expresie;

Exemple:

v_hiredate :='15-MAY_2003';

v_ename :='JOHAN';

O alta modalitate de asignare a unei valori unei variabile este de a folosi comanda SELECT astfel:

SELECT sal*0.10

INTO bonus

FROM emp

WHERE empno

Declararea variabilelor cu atributul %TYPE

Exemple:

v_ename emp.ename%TYPE;

v_sold NUMBER(7,2);

v_min_sold v_sold%TYPE :=10;

Functii SQL in PL/SQL

Pot fi folosite urmatoarele functii din SQL:

functii numerice care returneaza o singura linie;

functii pt.siruri de caractere care returneaza o singura linie;

functii de conversie;

functii pt.data si timp (date).

Nu pot fi folosite in SQL:

functia GREATEST

functia LEAST

functii grup (AVG, MIN, MAX, . ).

Operatori folositi:

logici

aritmetici ca si in SQL

de concatenare

paranteze pentru a controla ordinea operatorilor

in plus, operatorul exponential (**)

Precedenta operatorilor (de sus in jos):

Operatori

**, NOT

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN , IN

AND

Pentru a face distinctie intre diferiti identificatori sau intre identificatori si alte obiecte ale bazei de date, se utilizeaza conventii de nume:

Identificator	Conventie de nume	Exemple
Variabile (Variable)	v_nume	v_sal
Constante (Constant)	c_nume	c_company_name
Cursor (Cursor)	nume_cursere	emp_cursor
Exceptii (Exception)	e_nume	e_too_many
Tip tablou (Table Type)	nume_table_type	amount_table_type
Tabel (Table)	nume_tabel	order_total_table
Tipul inregistrare (Record Type)	nume_record_type	emp_record_type
Inregistrare (Record)	nume_record	customer_record
Parametru de substitutie SQL*PLUS	p_nume	p_sal
Variabila globala SQL*Plus	g_nume	g_year_sal

Referirea la o variabila de legatura se face in PL/SQL prin prefixarea acestei variabile cu caracterul " : ". In SQL*Plus, pentru declararea acestui tip de variabila se foloseste comanda VARIABLE, iar valoarea variabilei de legatura va fi tiparita utilizand comanda PRINT.

Exemple:

Afisarea mesajului "Primul meu bloc de instructiuni PL/SQL":

VARIABLE g_mesaj VARCHAR2(50)

BEGIN

:g_mesaj:='Primul meu bloc de instructiuni PL/SQL';

END;

PRINT g_mesaj

Afisarea salariului maxim al angajatilor din tabelul emp:

VARIABLE g_max_sal NUMBER

DECLARE

v_max_sal NUMBER;

BEGIN

SELECT MAX(sal)

INTO v_max_sal

FROM ang;

:g_max_sal :=v_max_sal;

END;

PRINT g_max_sal

Afisarea sumei a doua numere:

VARIABLE g_result NUMBER

ACCEPT p_num1 PROMPT 'Primul numar:'

ACCEPT p_num2 PROMPT 'Al 2-lea numar:'

DECLARE

v_num1 NUMBER:=&p_num1;

v_num2 NUMBER:=&p_num2;

BEGIN

:g_result:=v_num1+v_num2;

END;

PRINT g_result

Afisarea salariului total obtinut prin insumarea salariului de baza cu bonusul introdus in procente:

VARIABLE g_sal_total NUMBER

ACCEPT p_sal PROMPT 'Salariul este: '

ACCEPT p_bon PROMPT 'Bonusul in procente este: '

DECLARE

v_sal NUMBER:=&p_sal;

v_bon NUMBER:=&p_bon;

BEGIN

:g_sal_total:=NVL(v_sal , 0)*(1+NVL(v_bon , 0)/100);

END;

PRINT g_sal_total

Afisarea salariului total al angajatilor din departamentului 10 din tabelul emp;

VARIABLE g_sal NUMBER

DECLARE

v_sum_sal emp.sal%TYPE;

v_deptno NUMBER NOT NULL :=10;

BEGIN

SELECT SUM(sal)-- functie grup

INTO v_sum_sal

FROM emp

WHERE deptno=v_deptno;

:g_sal:=v_sum_sal;

END;

PRINT g_sal

Creati un tabel cu numele ang cu structura: marca, nume, meserie, sal, com, nrdept:

CREATE TABLE ang (marca NUMBER(4), nume VARCHAR2(15), meserie VARCHAR2(10), sal NUMBER(10),

com NUMBER(10), nrdept NUMBER(2));

Inserati in tabelul ang o inregistrare:

ACCEPT p_marca PROMPT 'Introduceti marca pt.un angajat: '

ACCEPT p_nume PROMPT 'Introduceti numele unui angajat : '

ACCEPT p_meserie PROMPT 'Introduceti meseria pt. un angajat : '

ACCEPT p_sal PROMPT 'Introduceti salariul unui angajat : '

ACCEPT p_nrdept PROMPT 'Introduceti un cod de departament: '

BEGIN

INSERT INTO ang (marca, nume, meserie, sal, com, nrdept)

VALUES (&p_marca, '&p_nume', '&p_meserie', &p_sal, NULL, &p_nrdept);

COMMIT;

END;

Sa se actualizeze salariul angajatilor care au meseria inginer din tabelul ang:

DECLARE

v_crestere_sal ang.sal%TYPE:=500000;

BEGIN

UPDATE ang

SET sal=sal+v_crestere_sal

WHERE meserie like 'ing%';

END;

Sa se stearga inregistrari din tabelul ang in functie de conditia introdusa:

ACCEPT p_conditie PROMPT 'Introduceti conditia de stergere: '

BEGIN

DELETE FROM ang

WHERE &p_conditie;

END;

Structuri de control in PL/SQL

Instructiunea IF (structura de control alternativa)

Sintaxa:

IF conditie THEN

instructiuni;

[ELSEIF conditie THEN

instructiuni;]

[ELSE

instructiuni;]

END IF;

Exista trei forme ale acestei instructiuni:

a) IF-THEN-END IF

IF conditie THEN

instructiuni;

END IF;

Exemplu:

- Sa se stabileasca pentru angajatul care are numele 'MILLER' ca: meseria (job) sa fie SALESMAN, codul departamentului in care lucreaza sa fie 35, iar comisionul sa fie 20% din salariu.

IF v_ename='MILLER' THEN

v_job :='SALESMAN' ;

v_deptno :=35;

v_comm :=sal*0.20;

END IF;

Observatii:

Daca conditia este adevarata se executa instructiunile ce urmeaza dupa THEN.

Daca conditia este falsa sau are ca rezultat o valoare NULL, PL/SQL ignora instructiunile care se realizeaza pe ramura adevarat (TRUE).

b) IF-THEN-ELSE-END IF

IF conditie1 THEN

instructiune1;

ELSE

instructiune2;

END IF;

Observatie: cand conditia este adevarata se executa instructiune1; altfel se executa instructiune2

In cadrul acestei forme poate sa apara si o subinstructiune IF imbricata:

IF conditie1 THEN

instructiune1;

ELSE

IF conditie2 THEN

instructiune2;

END IF;

Observatie: fiecare instructiune IF trebuie sa se incheie cu END IF.

Exemple:

- Realizati o crestere salariala pentru angajati, astfel: daca salariul este mai mare de 2000$ cu 5%, altfel cu 10%:

IF v_sal >2000 THEN

v_sal := v_sal * 0.05;

ELSE

v_sal := v_sal * 0.10;

END IF;

Observatie: a se revedea in SQL instructiunea DECODE.

- Realizati o crestere salariala pentru angajati, astfel: daca salariul este mai mare de 2000$ cu 5%, altfel, daca salariul este cuprins intre 1000$ si 2000 cu 10%, iar daca este mai mic decat 1000 cresterea va fi de 15%:

IF v_sal>2000 THEN

v_sal :=v_sal * 0.05;

ELSE

IF v_sal>=1000 AND v_sal<=2000 THEN

v_sal :=v_sal * 0.10;

END IF;

c) IF-THEN-ELSIF-END IF

IF conditie1 THEN

instructiune1;

ELSIF conditie2 THEN

instructiune2;

ELSIF conditie3 THEN

instructiune3;

END IF;

Exemplu:

- Realizati o modificare a comisionulul obtinut de angajati, astfel:

o daca salariul<1000 atunci comisionul va fi 10% din salariu

o daca salariul este intre 1000 si 1500, atunci comisionul va fi 15% din salariu

o daca salariul>1500 atunci comisionul va fi 20% din salariu

o altfel comisionul va fi 0 ;

IF v_sal<1000 THEN

v_com:=v_sal*.10;

ELSIF v_sal BETWEEN 1000 AND 1500 THEN

v_com :=v_sal*.15;

ELSIF v_sal>1500 THEN

v_com :=v_sal*.20;

ELSE

v_com :=0;

END IF;

Observatie: orice expresie aritmetica care contine valori null este evaluata la valoarea null.

Sa se scrie intr-un fisier de comenzi (script file) un bloc de instructiuni care realizeaza modificarea comisionului descrisa la aplicatia precedenta pentru un angajat. Codul angajatului (marca) va fi introdus de la tastatura de catre utilizator

ACCEPT p_marca PROMPT 'Introduceti codul unui angajat: '

DECLARE

v_marca ang.marca%TYPE := &p_marca;

v_sal ang.sal%TYPE;

v_com ang.com%TYPE;

BEGIN

SELECT sal

INTO v_sal

FROM ang

WHERE marca=v_marca;

IF v_sal<1000 THEN

v_com:=v_sal*.10;

ELSIF v_sal BETWEEN 1000 AND 1500 THEN

v_com :=v_sal*.15;

ELSIF v_sal>1500 THEN

v_com :=v_sal*.20;

ELSE

v_com :=0;

END IF;

UPDATE ang

SET com=v_com

WHERE marca=v_marca;

COMMIT;

END;

Instructiunea LOOP (structura de control repetitiva)

Aceasta structura repeta o instructiune sau o secventa de instructiuni de mai multe ori.

Tipuri:

a) BASIC Loop

Sintaxa:

LOOP

instructiune1;

EXIT [WHEN conditie];

END LOOP;

b) FOR Loop

Sintaxa:

FOR contor in [REVERSE]

val_initiala..val_finala LOOP

insructiune1;

instructiune2;

END LOOP;

c) WHILE Loop

Sintaxa:

WHILE conditie LOOP

instructiune1;

instructiune2;

END LOOP;

Creati un tabel cu numele tab1, avand structura rezultat VARCHAR2(15).

CREATE TABLE tab1 (rezultat VARCHAR2(15));

Sa se insereze in tabelul tab1 valorile lui i , stiind ca i=1..10, cu exceptia valorilor 6 si 8, pt.care se introduce valoarea null.

BEGIN

FOR i IN 1..10 LOOP

IF i=6 or i=8 THEN

NULL;

ELSE

INSERT INTO tab1 (rezultat)

VALUES (i);

END IF;

COMMIT;

END LOOP;

END;

Politica de confidentialitate