Laborator  5-6 PL/SQL
 Oracle
Introducere in PL/SQL
1. Structuri de bază în PL/SQL	1
2. Programe simple în PL/SQL	3
3. Instrucţiuni de control în PL/SQL	4
1.Instrucţiunea IF	4
2. Instructiunea CASE	6
3. Instructiunea  LOOP	7
4. Comanda  EXIT-WHEN	7
5. Etichetarea  unei blucle PL/SQL	7
6. Bucla WHILE-LOOP	8
7. Instructiunea FOR-LOOP	8


1. Structuri de bază în PL/SQL
PL înseamnă Procedural Language (deci limbaj procedural). Acesta este mai puternic decât SQL-ul. Unitatea de bază din PL/SQL se numeşte bloc. Toate programele sunt constituite din blocuri, care pot fi imbricate. Fiecare bloc are următoarea structură:

DECLARE
	/* Sectiunea de declarare: variabile, tipuri de date, subprograme locale */
BEGIN
/* Secţiunea executabilă: declaraţii SQL sau proceduri*/
/* Este singura secţiune din bloc care trebuie să apară */
EXCEPTION
/* Secţiunea de lucru cu excepţiile: declaraţii de lucru cu erorile */
END;

Observaţii:
Singura secţiune necesară este cea de execuţie; celelalte secţiuni sunt opţionale.
Singurele declaraţii SQL permise sunt SELECT, INSERT, UPDATE, DELETE şi alte manipulări de date sau controale legate de tranzacţie. Totuşi, fraza SELECT are o formă specială în care un singur tuplu este salvat în variabile. Restul instrucţiunilor de definire de date, cum ar fi CREATE, DROP, sau ALTER nu sunt permise.
Secţiunea executabilă poate conţine asignări, decizii, cicluri, apeluri de proceduri, şi triggere.
PL/SQL nu este Case Sensitive (nu face diferenţe între litere mici şi mari).
Pot fi utilizate comentariile /* ... */.
functia DBMS_OUTPUT.PUT_LINE este folosita pentru a afisa rezultatul din programul PL/SQL
Pentru a executa un program PL/SQL, textul în sine trebuie fie încheiat cu un punct “.”, urmată de o linie pe care se tastează run;
Informaţiile sunt transmise între programul PL/SQL şi baza de date prin intermediul unor variabile. Fiecare variabilă are un tip specific asociat acesteia Tipurile pot fi:
Unul din tipurile utilizate în SQL pentru câmpurile tabelei;
Un tip generic utilizat în PL/SQL cum ar fi NUMBER
Declarat ca să fie de acelaşi tip cu câmpul din baza de date.

Observaţii
Cel mai utilizat tip este NUMBER. Variabilele de tip NUMBER pot reţine fie un întreg, fie un număr real.
Pentru şiruri de caractere, cel mai utilizat este VARCHAR(n), unde n este lungimea maximă a şirului în bytes. Lungimea trebuie neapărat precizată, nu există o valoarea implicită. De exemplu, putem declara:
DECLARE
	pret NUMBER;
	denumire VARCHAR(20);

PL/SQL permite variabile BOOLEAN (logice), chiar dacă Oracle nu suportă acest tip pentru câmpurile tabelei.

De obicei, se foloseşte operatorul %TYPE. De exemplu:

DECLARE
	v_nume Stud.nume%TYPE;

defineşte pentru variabila v_nume acelaşi tip de dată ca şi câmpul nume din tabela Stud.
Dacă există o variabilă care este de tip articol, cu mai multe câmpuri, cea mai simplă posibilitate de declarare a acestei variabile este utilizarea operatorului %ROWTYPE. Rezultatul este un tip articol (înregistrare) în care câmpurile au acelaşi nume şi tip ca şi numele câmpurilor din tabelă. De exemplu:
DECLARE
	v_date_stud Stud%ROWTYPE;

defineşte variabila v_date_stud ca un articol cu exact aceleaşi câmpuri ca şi tabela Stud, fiind selectată pe rând câte o înregistrare.
Valoarea iniţială a oricărei variabile, indiferent de tipul ei, este Null.
Putem asocia valori unei variabile utilizând operatorul de atribuire ":=". Asignarea poate surveni imediat după declararea tipului variabilei, sau oriunde în cadrul porţiunii executabile a programului. De exemplu:
DECLARE
	a NUMBER := 3;
BEGIN
	a := a + 1;
END;
.
run;

Programul nu are nici un efect, pentru că asupra bazei de date nu se efectuează nici o modificare.

Alt exemplu:

	CREATE TABLE angajati (coda NUMBER (4) constraint pk_coda PRIMARY KEY,
nume VARCHAR2(15), pren VARCHAR2(15),orel NUMBER (3), bonus NUMBER(5,2),
salar NUMBER(6));

	set serveroutput on;

DECLARE
v_salar NUMBER;
v_ore_lucrate NUMBER := 40;
v_salar_orar NUMBER := 22.50;
v_bonus NUMBER := 150;
v_ang1 angajati%ROWTYPE;
BEGIN
v_salar := (v_ore_lucrate * v_salar_orar) + v_bonus;
v_ang1.pren := 'Ion';
v_ang1.nume := 'POP';
DBMS_OUTPUT.PUT_LINE ('Nume'||' '||'Prenume'||' '||'Salar');
DBMS_OUTPUT.PUT_LINE (v_ang1.nume||' '||v_ang1.pren||' '||v_salar);
END;
/
	run;

Alt exemplu:

INSERT INTO angajati VALUES (100, 'POP', 'Ion', 2.5, 500);
INSERT INTO angajati VALUES (101, 'LUSTREA', 'Anca', 3.5, 600);

set serveroutput on;

DECLARE
v_bonus NUMBER(8,2);
v_coda NUMBER(4) := 100;
BEGIN
SELECT salar * 0.10 INTO v_bonus FROM angajati
WHERE coda=v_coda;
DBMS_OUTPUT.PUT_LINE ('Bonus 10% pt. ang cu codul 100:'||' '||v_bonus);
END;
/
	run;



2. Programe simple în PL/SQL
Cel mai simplu mod de lucru este efectuarea unor declaraţii urmată de o secţiune executabilă care constă în una sau mai multe enunţuri SQL cu care suntem obişnuiţi. Diferenţa majoră este forma instrucţiunii SQL: după clauza SELECT, trebuie să avem o clauză INTO în care să enumerăm variabilele, pentru fiecare atribut din SELECT câte o variabilă.
Instrucţiunea SELECT în PL/SQL funcţionează doar dacă rezultatul interogării este un singur tuplu. Situaţia este identică cu SELECT -ul care furnizează o singură înregistrare despre care am vorbit la interogări. Dacă interogarea returnează mai mult de un tuplu, trebuie să folosim o tabelă cursor. De exemplu:
DROP TABLE T1;
CREATE TABLE T1(
	nume VARCHAR(20),
	salar INTEGER
);
DELETE FROM T1;
INSERT INTO T1 VALUES(’POPESCU Ion’, 600);
INSERT INTO T1 VALUES(’BALAJ Maria’, 500);
INSERT INTO T1 VALUES(’AVRAM Alina’, 700);
SELECT * FROM T1;
/* Deasupra e SQL simplu, mai jos este un program PL/SQL */
DECLARE
    v_nume VARCHAR(20);
    v_salar NUMBER;
BEGIN
	/* Pentru cei care au salarul=500, acesta se mareste cu 100*/
	SELECT nume, salar INTO v_nume,v_salar FROM T1 WHERE salar=500;
	Delete from t1 where salar=500;
	INSERT INTO T1 VALUES(v_nume,v_salar+100);

END;
.
run;
/* din nou SQL simplu */
SELECT * FROM T1;

Din fericire, un singur cuplu din tabela T1 verifică condiţia ca salarul să fie egal cu 500, şi anume (BALAJ Maria, 500). Comanda DELETE şterge înregistrarea respectivă, urmând ca INSERT sa o introducă din nou în tabelă, de data asta cu salarul mărit.

Alt exemplu:

CREATE TABLE T2 (nr1 INTEGER, nr2 INTEGER);

DELETE FROM T2;

INSERT INTO T2 VALUES(1, 3);
INSERT INTO T2 VALUES(2, 4);

/* Deasupra este SQL; mai jos program PL/SQL */

DECLARE
	v_nr1 NUMBER;
	v_nr2 NUMBER;
BEGIN
	/* daca primul numar este mai mare ca 1, se inverseaza insereaza o
	inregistrare noua in care numerele sunt inversate*/
	SELECT nr1,nr2 INTO v_nr1,v_nr2 FROM T2 WHERE nr1>1;
	INSERT INTO T2 VALUES (v_nr2,v_nr1);
END;
.
run;

3. Instrucţiuni de control în PL/SQL
PL/SQL permite crearea unor decizii sau cicluri.

1.Instrucţiunea IF
 are formatul general:
	IF <conditie> THEN <lista_declaratii> ELSE <lista_declaratii> END IF;

Partea ELSE este opţională. Dacă se doreşte imbricarea:

IF <conditie_1> THEN ...
	ELSIF <conditie_2> THEN ...
	... ...
	ELSIF <conditie_n> THEN ...
	ELSE ...
END IF;
Exemplu IF fără ELSE:

SELECT * FROM T1;

DECLARE
v_nume VARCHAR2(20);
v_salar NUMBER;

BEGIN
/* Celor care au salarul=700, li se va mari salarul cu 100 lei */
SELECT nume,salar INTO v_nume,v_salar FROM T1 WHERE salar=700;
IF v_salar=700 THEN
	DELETE FROM T1 WHERE SALAR=700;
INSERT INTO T1 VALUES(v_nume,v_salar+100);
END IF;
END;
.
run;

SELECT * FROM T1;

Alt exemplu:

SELECT * FROM T1;

DECLARE
v_nume VARCHAR2(20);
v_salar NUMBER;

BEGIN
/* Celor care au salarul=700, li se va mari salarul cu 100 lei */
SELECT nume,salar INTO v_nume,v_salar FROM T1 WHERE salar=700;
IF v_salar=700 THEN
	DELETE FROM T1 WHERE SALAR=700;
INSERT INTO T1 VALUES(v_nume,v_salar+100);
END IF;
END;
.
run;

OPERATORII
Operator
Description
+ - / *
arithmetic
=
equality
!= or <>
inequality
||
string concatenation
:=
assignment

Function
Description
String Functions

upper(s), lower(s)
convert string s to upper/lower-case
initcap(s)
capitalise first letter of each word
ltrim(s), rtrim(s)
remove blank char. from left/right
substr(s,start,len)
sub-string of length len from position start
length(s)
length of s
Date Functions

sysdate
current date (on Oracle server)
to_date(date, format)
date formatting
Number Functions

round(x)
round real number x to integer
mod(n,p)
n modulus p
abs(x)
absolute value of x
dbms_random.random()
generate a random integer
Type Conversion Functions

to_char()
convert to string
to_date()
convert to date
to_number()
convert to number
Miscellaneous Functions

user
current Oracle user



2. Instructiunea CASE
Exemplu
DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  CASE grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;

Exemplu:

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  CASE
    WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
-- in loc de  folosi  ELSE in CASE se poate folosi
--  EXCEPTION
--    WHEN CASE_NOT_FOUND THEN
--      DBMS_OUTPUT.PUT_LINE('No such grade');

Tema:
dati 3 exemple de programe in PL/SQL care folosesc structura de control CASE, folosind obiectele din baza de date creata de voi.


3. Instructiunea  LOOP
Exista 3 forme pentru LOOP:
LOOP
 WHILE-LOOP
FOR-LOOP
Exemplu
LOOP
  sequence_of_statements
END LOOP;


Folosirea comenzii  EXIT
Comanda EXIT statement forteaza ca iesirea din blucla .
Exemplu
DECLARE
  credit_rating NUMBER := 0;
BEGIN
  LOOP
    credit_rating := credit_rating + 1;
    IF credit_rating > 3 THEN
       EXIT;  -- iesirea din blucla
    END IF;
  END LOOP;
  IF credit_rating > 3 THEN
       RETURN;  --se foloseste RETURN si nu  EXIT cand suntem in afara  LOOP
  END IF;
 DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating));
END;

Comanda EXIT trebuie plasata in interiorul buclei.
4. Comanda  EXIT-WHEN
Comanda EXIT-WHEN determina iesirea din bucla in urma unei conditii..
Exemplu
IF count > 100 THEN EXIT; ENDIF;
sau
EXIT WHEN count > 100;
Cele 2 constructii de mai sus sunt echivalente, dar  EXIT-WHEN este mai usor de inteles.
Tema: dati 2 exemple de programe in PL/SQL care folosesc structurile de control LOOP, folosind obiectele din baza de date creata de voi.

5. Etichetarea  unei blucle PL/SQL
Ca si blocurile PL/SQL, buclele pot fi etichetate.  Eticheta optionala inchisa intre << >> trebuie sa apara la inceputul buclei. Numele etichetei trebuie sa apara si la sfarsitul buclei.
Exemplu
DECLARE
  s      PLS_INTEGER := 0;
  i      PLS_INTEGER := 0;
  j      PLS_INTEGER;
BEGIN
  <<outer_loop>>
  LOOP
    i := i + 1;
    j := 0;
    <<inner_loop>>
    LOOP
      j := j + 1;
      s := s + i * j;
      EXIT inner_loop WHEN (j > 5);
      EXIT outer_loop WHEN ((i * j) > 15);
    END LOOP inner_loop;
  END LOOP outer_loop;
  DBMS_OUTPUT.PUT_LINE ('The sum of products equals: ' || TO_CHAR(s));
END;

6. Bucla WHILE-LOOP
Bucla  WHILE-LOOP se executa pana atata timp cat conditia este adevarata.
WHILE condition LOOP
  sequence_of_statements
END LOOP;
Aceasta este echivalenta cu:
LOOP
  sequence_of_statements
  EXIT WHEN boolean_expression;
END LOOP;

Tema: dati 2 exemple de programe in PL/SQL care folosesc structurile de control WHILE-LOOP, folosind obiectele din baza de date creata de voi.

7. Instructiunea FOR-LOOP
Bucla  FOR itereaza peste un anumit interval de intregi. Numarul de iteratii este cunoscut inainte de intrarea in blucla. Operatorul (..) serveste  ca operator de domeniu. Domeniul este calculat cand se intra prima data in bucla si nu mai este niciodata reevaluat.
Exemplu:
DECLARE
  p     NUMBER := 0;
BEGIN
  FOR k IN 1..500 LOOP – calcularea lui pi cu 500 termeni
    p := p +  (  ( (-1) ** (k + 1) ) / ((2 * k) - 1) );
  END LOOP;
  p := 4 * p;
  DBMS_OUTPUT.PUT_LINE( 'pi is approximately : ' || p ); -- print result
END;
Exemplu:
BEGIN
  FOR i IN REVERSE 1..3 LOOP
       DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
  END LOOP;
END;

In interiorul unei blucle FOR, variabila cu care se itereaza i nu poate schimbata,ci numai citita.
BEGIN
  FOR i IN 1..3 LOOP  -- assign the values 1,2,3 to i
    IF i < 3 THEN
       DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
    ELSE
       i := 2; -- nu este permisa, produce o  eroare
    END IF;
  END LOOP;
END;

Tema: dati 2 exemple de programe in PL/SQL care folosesc structurile de control FOR-LOOP, folosind obiectele din baza de date creata de voi.




1.
DROP TABLE angajati;
CREATE TABLE angajati (
                         coda NUMBER (4) constraint pk_coda PRIMARY KEY,
nume VARCHAR2(15),
pren VARCHAR2(15),
orel NUMBER (3),
bonus NUMBER(5,2),
salar NUMBER(6));

set serveroutput on;

DECLARE
v_salar NUMBER;
v_ore_lucrate NUMBER := 40;
v_salar_orar NUMBER := 22.50;
v_bonus NUMBER := 150;
v_ang1 angajati%ROWTYPE;
BEGIN
v_salar := (v_ore_lucrate * v_salar_orar) + v_bonus;
v_ang1.pren := 'Ion';
v_ang1.nume := 'POP';
DBMS_OUTPUT.PUT_LINE ('Nume'||' '||'Prenume'||' '||'Salar');
DBMS_OUTPUT.PUT_LINE (v_ang1.nume||' '||v_ang1.pren||' '||v_salar);
END;


2.

DECLARE
                x NUMBER(7,2);
BEGIN
          SELECT sal INTO x FROM emp WHERE empno = 7788;
          IF x < 3000 THEN UPDATE emp SET sal = 3000
                                  WHERE  empno  = 7788;
          END IF;
END;

3.  Exemplu de FOR

SET SERVEROUTPUT ON;
DECLARE someone emp%ROWTYPE;
BEGIN
  FOR someone IN (SELECT * FROM emp WHERE empno < 7900 )
  LOOP
    DBMS_OUTPUT.PUT_LINE('name = ' || someone.ename ||
                         ', job = ' || someone.job);
  END LOOP;
END;

4 Exemplu de doua LOOP etichetate.

SET SERVEROUTPUT ON;
DECLARE
  s      PLS_INTEGER := 0;
  i      PLS_INTEGER := 0;
  j      PLS_INTEGER;
BEGIN
  <<outer_loop>>
  LOOP
    i := i + 1;
    j := 0;
    <<inner_loop>>
    LOOP
      j := j + 1;
      s := s + i * j;
      EXIT inner_loop WHEN (j > 5);
      EXIT outer_loop WHEN ((i * j) > 15);
    END LOOP inner_loop;
  END LOOP outer_loop;
  DBMS_OUTPUT.PUT_LINE ('The sum of products equals: ' || TO_CHAR(s));
END;