Tutoriale Oracle PL-SQL online gratuite


Aceste tutoriale de Oracle PL-SQL gratuite sunt adresate utilizatorilor incepatori. In total gasiti in aceasta pagina 100+ de lectii scrise Oracle PL-SQL impartite in 8 capitole.

De asemenea, oricand poti urma si unul din cele patru cursuri Oracle in clasa, cu traineri acreditati.

Ti se pare greu sa parcurgi singur lectiile Oracle online gratuite?

Inscrie-te la unul din cele 4 tipuri diferite de cursuri Oracle in clasa (cu instructori acreditati).

Lectia 1: Declararea Variabilelor PL-SQL

A) VARIABILE PL/SQL

  1. Sa se realizeze un block anonim PL/SQL in care sa declaram o variabila de tip varchar2(20) numita strNume si sa se asigneze acestei variabile valoarea ‘Ionescu’, apoi sa se afiseze aceasta valoare folosind pachetul Oracle dbms_output
  2. Sa se realizeze un block anonim PL/SQL in care sa declaram o variabila de tip varchar2(20) numita strProfesia si sa se asigneze acestei variabile valoarea DEFAULT ‘inginer’, apoi sa se afiseze aceasta valoare folosind pachetul Oracle dbms_output
  3. Sa se realizeze un block anonim PL/SQL in care sa declaram o variabila de tip Number(6,2) numita dblSalariul si sa se asigneze acestei variabile valoarea 1200.50, apoi sa se afiseze aceasta valoare folosind pachetul Oracle dbms_output. Output-ul sa fie: “Aveti salariul de 1200.50 RON”
  4. Sa se realizeze un block anonim PL/SQL in care sa declaram o variabila de tip Date numita datDataNasterii si sa se asigneze acestei variabile valoarea ’09-Mar-1980’, apoi sa se afiseze aceasta valoare folosind pachetul Oracle dbms_output. Output-ul sa fie: ‘V-ati nascut in data de 09-Mar-1980’
  5. Sa se realizeze un block anonim PL/SQL in care sa declaram o variabila de tip Boolean numita blnStareaCivila si sa se asigneze acestei variabile valoarea DEFAULT true, apoi sa se afiseze aceasta valoare folosind pachetul Oracle dbms_output. Output-ul sa fie: “Starea dvs civila este casatorit(a)” (folositi structura if, vezi sintaxa de la cursul urmator). Schimbati in cod valoarea variabilei blnStareaCivila in false si faceti astfel incat sa afiseze “Starea dvs civila este NEcasatorit(a)”
  6. Sa se realizeze un block anonim PL/SQL in care sa declaram o variabila de acelasi tip cu coloana Pret_Ref din tabela FILME si sa se asigneze acestei variabile valoarea 50000, apoi sa se afiseze aceasta valoare folosind pachetul Oracle dbms_output. Output-ul sa fie: “Filmul poate fi inchiriat cu pretul de referinta de 50000″

B) Blocuri Anonime

  1. Sa se realizeze un block anonim PL/SQL care sa preia de la tastatura data nasterii si care sa afiseze textul: “V-ati nascut in data X intr-o zi de Y”, unde X este data introdusa de utilizator, iar Y este ziua din saptamana (in litere in limba engleza) in care a picat data respectiva
  1. Sa se realizeze un block anonim PL/SQL care sa preia de la tastatura salariul si data angajarii si care sa afiseze “Salariul dvs. actual este X, dar va fi indexat cu Y%, deci rezulta un salariu de Z” (unde X este salariul introdus de la tastatura, Y este procentul de indexare, Z este salariul indexat). Y este un numar reprezentand numarul de ani scursi de la angajare.
  1. Sa se realizeze un block anonim PL/SQL care sa preia de la tastatura o zi din saptamana in limba engleza (De ex. FRIDAY) si care sa afiseze textul “Urmatoarea zi de X va pica in data de Y”, unde X reprezinta ziua din saptamana introdusa de utilizator, iar Y reprezinta data in care va pica urmatoarea zi de X.

REZOLVARI Lectia 1: Declararea Variabilelor PL-SQL

1.
DECLARE
strNume varchar2(20);
BEGIN
strNume:=’Ionescu’;
dbms_output.put_line(strNume);
END;
/

2.
DECLARE
strProfesia varchar2(20):=’inginer’;
BEGIN
dbms_output.put_line(strProfesia);
END;
/

3.
DECLARE
dblSalariul NUMBER(6,2);
BEGIN
dblSalariul:=1200.50;
dbms_output.put_line(‘Aveti salariul de ‘ || dblSalariul);
END;
/

4.
DECLARE
datDataNasterii Date;
BEGIN
datDataNasterii:=’09-MAR-1980′;
dbms_output.put_line(‘V-ati nascut in data de ‘ || datDataNasterii);
END;
/

5.
DECLARE
blnStareaCivila Boolean:=true;
BEGIN
IF blnStareaCivila=true Then
dbms_output.put_line(‘Starea dvs civila este casatorit(a)’);
ELSE
dbms_output.put_line(‘Starea dvs civila este NEcasatorit(a)’);
END IF;
END;
/

6.
DECLARE
intPretRef FILME.Pret_Ref%Type:=50000;
BEGIN
dbms_output.put_line(‘Filmul poate fi inchiriat cu pretul de referinta de ‘ || intPretRef);
END;
/

B) Functii PL/SQL

1.

DECLARE

          datDataNasterii Date:=’&BagaData’;

          strZiua          VARCHAR2(15);

BEGIN

          strZiua :=to_char(datDataNasterii,’Day’);

          dbms_output.put_line(‘V-ati nascut in data de ‘ || datDataNasterii || ‘ intr-o zi de ‘ || strZiua);

END;

/

2.

DECLARE

          intSal            NUMBER:=&BagaSalariul;

          intProcent      NUMBER:=&BagaProcentul;

          strRaspuns     VARCHAR2(200);

BEGIN

          strRaspuns:=’Sal. actual este de ‘ || intSal || ‘ dar va fi indexat cu ‘ || intProcent || ‘%, deci va rezulta un salariu de ‘ || intSal * (1+intProcent/100);

          dbms_output.put_line(strRaspuns);

END;

/

3.

DECLARE

          strZiua                    VARCHAr2(20):=’&BagaZiua’;

          datDataUrmatoare    DATE;

BEGIN

          datDataUrmatoare:=NEXT_DAY(SYSDATE,strZiua);

          dbms_output.put_line(‘Urmatoarea zi de ‘ || strZiua || ‘ o sa pice in data de ‘ || datDataUrmatoare);

END;

/

A) VARIABILE PL/SQ

Reprezinti o companie si doresti acest curs pentru angajatii/colegii tai?

Citeste cu atentie procedura noastra de instruire pentru companii: Ultra all inclusive (pentru cursurile Excel, valabila pentru toate cursurile noastre in clasa)

Lectia 2: Proceduri si Functii NESTOCATE PL-SQL

Scopul unei variabile (Block-uri IMBRICATE)

A) Scrieti codul de mai jos:

DECLARE

a NUMBER:=10;

BEGIN

DECLARE

b NUMBER:=20;

BEGIN

dbms_output.put_line(a);– se va vedea pt ca a este variabila globala                                                dbms_output.put_line(b);–se va vedea pt ca este variabila locala

END;

dbms_output.put_line(a);–se va vedea pt ca a este variabila globala

dbms_output.put_line(b);–NU se va vedea!!!

END;

/

Comentati ca mai jos si rulati din nou:

DECLARE

a NUMBER:=10;

BEGIN

DECLARE

b NUMBER:=20;

BEGIN

dbms_output.put_line(a);– se va vedea pt ca a este variabila globala                                                dbms_output.put_line(b);–se va vedea pt ca este variabila locala

END;

dbms_output.put_line(a);–se va vedea pt ca a este variabila globala

–dbms_output.put_line(b);–NU se va vedea!!!

END;

/

B) Proceduri CLIENT
1) Sa se realizeze o procedura CLIENT care sa primeasca ca argument un salariu si un procent de indexare si care sa afiseze textul “Salariul X a fost indexat cu procentul Y rezultand un salariu indexat de Z”, unde X este salariul primit de procedura, Y este procentul primit de procedura, iar Z este salariul indexat. Sa se invoce aceasta procedura CLIENT intr-un BLOCK anonim de trei ori cu urmatoarele valori:

salariul 1:                     400

procentul 1:                 40

salariul 2:                     700

procentul 2:                 55

salariul 3:                     900

procentul 3:                 15

2) Sa se modifice Block-ul de mai sus astfel incat sa primeasca de la tastatura salariul si procentul si de fiecare data cand se apeleaza sa trimita acest salariu si procent procedurii CLIENT.

3) Sa se realizeze o procedura numita procStergeInchirieriClient care sa primeasca ca argument id-ul unui client si care sa stearga din tabelul INCHIRIERI toate inchirierile clientului introdus (nu dati commit). Dupa ce o testati dati ROLLBACK (ca sa-si revina Baza la starea initiala)

4) Sa se realizeze o procedura numita procModificaClient care sa primeasca ca argumente id-ul unui client, noul_nume, noul_prenume, noua_adresa, noul_tel si care sa modifice pentru id-ul respectiv in tabelul CLIENTI coloanele NUME, Prenume, Adresa, Telefon. Sa se invoce aceasta procedura intr-un Block anonim ce va cere de la tastatura valorile pentru argumentele procedurii. Interogati tabelul clienti si verificati daca s-a facut modificarea. Dati rollback

5) Sa se realizeze o procedura numita crestePretReferintaFilme care sa primeasca ca si argument un procent de indexare pentru coloana Pret_Ref din tabelul Filme. Aceasta procedura va avea un argument de intrare (pProcIndex) si unul de iesire (pCatePreturiAmModificat). Sa se invoce procedura printr-un Block Anonim care sa ceara de la tastatura pProcIndex. Block-ul anonim sa afiseze numarul de linii modificate de catre procedura (pCatePreturiAmModificat). Interogati tabelul Filme pentru a vizualiza marirea preturilor. Dati rollback!

6) Creati tabela Angajati care sa contina toate liniile din schema scott tabela emp (aveti drepturi sa accesati tabele din alte scheme).

Create Table Angajati

As

Select * from scott.emp;

Creati tabela Departamente care sa contina toate liniile din schema scott tabela dept.

Create Table Departamente

As

Select * from scott.dept;

Creati o procedura cu urmatoarea definitie (header):

procIndexeazaSal(pProcIndex IN, pMeseria IN, pLaCatiAmModificat OUT)

aceasta procedura va primi de la tastatura (prin intermediul unui block anonim) parametri de intrare si va indexa salariul cu pProcIndex la toti cei ce au JOB-ul pMeseria, iar apoi returneaza numarul celor modificati pLaCatiAmModificat (acest numar va fi afisat de catre block-ul anonim “Au fost indexati cu pProcIndex un numar de pLaCatiAmModificat cu meseria pMeseria“). Interogati tabelul Angajati pentru a vizualiza marirea preturilor. Dati rollback!

C) Functii CLIENT

  1. Sa se realizeze o functie CLIENT care sa primeasca ca argument o data calendaristica si returneaza ziua din saptamana in care a picat data respectiva. Sa se invoce aceasta functie intr-un block anonim ce va prelua de la tastaura data calendaristica respectiva. Block-ul va scrie pe ecran ‘Data X a picat in ziua de Y’, unde Y este ziua din saptamana in care a picat data

  2. Sa se creeze o functie care sa primeasca ca argument un string (numar de telefon) in formatul “CODENUMAR” si sa returneze un string in fomatul “(code)-XXX.XX.XXX” unde code este din 4 cifre si numarul este din 7

De ex:

0723222222>>>>(072)-322.22.22

F(‘0723222222’)—- (072)-322.22.22

REZOLVARI Lectia 2: Proceduri si Functii NESTOCATE PL-SQL

PROCEDURILE sau FUNCTIILE NESTOCATE pot fi refolosite doar de catre BLOCK-ul ce le contine.

Diferenta intre o procedura si o functie este ca functia RETURNEAZA o valoare scalara, iar PROCEDURA nu. Astfel rezultatul intors de o functie poate fi folosit intr-o expresie, pe cand procedura NU. Procedura accepta si valori de intrare si valori de IESIRE, pe cand functia numai valori de intrare.

B. Proceduri CLIENT

1.

DECLARE

–variabilele BLOCK-ului anonim

–aici poti prelua valorile de la utilizator

–Sal     Number;

–Proc   Number;

Procedure procIndexeaza

(

–aici se declara parametri

–de intrare sau de iesire ai procedurii

pSal                  IN NUMBER,

pProc               IN NUMBER

)

Is

–aici se declara variabilele locale

–procedurii

salIndex           NUMBER;

Begin

salIndex:=pSal* (1+pProc/100);

dbms_output.put_line(‘Salariul ‘ || pSal || ‘ a fost indexat cu procentul ‘ || pProc || ‘ rezultand un salariu indexat de ‘ || salIndex);

End;

BEGIN

–aici este corpul BLOCK-ului anonim

–liniile de cod scrise aici se proceseaza primele

–procedura de mai sus se va procesa numai daca este chemata aici

procIndexeaza(400,40);

procIndexeaza(700,55);

procIndexeaza(900,15);

END;

/

2.

DECLARE

–variabilele BLOCK-ului anonim

–aici poti prelua valorile de la utilizator

Sal       Number:=&BagaSalariul;

            Proc    Number:=&BagaProcentul;

Procedure procIndexeaza

(

–aici se declara parametri

–de intrare sau de iesire ai procedurii

pSal                  IN NUMBER,

pProc               IN NUMBER

)

Is

–aici se declara variabilele locale

–procedurii

salIndex           NUMBER;

Begin

salIndex:=pSal* (1+pProc/100);

dbms_output.put_line(‘Salariul ‘ || pSal || ‘ a fost indexat cu procentul ‘ || pProc || ‘ rezultand un salariu indexat de ‘ || salIndex);

End;

BEGIN

–aici este corpul BLOCK-ului anonim

–liniile de cod scrise aici se proceseaza primele

–procedura de mai sus se va procesa numai daca este chemata aici

procIndexeaza(Sal,Proc);

END;

/

3.

DECLARE

idClient             INCHIRIERI.CLIENT_ID%TYPE:=&BagaIdul;

————————-

PROCEDURE procStergeInchirieriClient

(

pIdClient          INCHIRIERI.CLIENT_ID%TYPE

)

IS

Begin

Delete from Inchirieri Where CLIENT_ID=pIdClient;

dbms_output.put_line(‘Au fost sterse toate inchirierile clientului cu id-ul ‘ || idClient);

End;

BEGIN

procStergeInchirieriClient(idClient);

END;

/

4.

DECLARE

idClient             CLIENTI.CLIENT_ID%TYPE:=&BagaIdul;

Nume              CLIENTI.NUME%TYPE:=’&BagaNoulNume’;

Prenume                      CLIENTI.PRENUME%TYPE:=’&BagaNoulPrenume’;

Adresa             CLIENTI.ADRESA%TYPE:=’&BagaNouaAdresa’;

Telefon             CLIENTI.TELEFON%TYPE:=’&BagaNoulTel’;

————————-

PROCEDURE procModificaClient

(

pIdClient          IN Clienti.CLIENT_ID%TYPE,

pNoulNume      IN CLIENTI.NUME%TYPE,

pNoulPrenume IN CLIENTI.PRENUME%TYPE,

pNouaAdresa   IN CLIENTI.ADRESA%TYPE,

pNoulTelefon    IN CLIENTI.TELEFON%TYPE

)

IS

Begin

UPDATE Clienti SET

Nume=pNoulNume,

Prenume=pNoulPrenume,

Adresa=pNouaAdresa,

Telefon=pNoulTelefon

Where

CLIENT_ID=pIdClient;

End;

BEGIN

procModificaClient (idClient, Nume, Prenume, Adresa, Telefon);

END;

/

5.

DECLARE

ProcIndex NUMBER:=&BagaProcentul;

CatePreturiAmModificat Number;

————————-

PROCEDURE crestePretReferintaFilme

(

pProcIndex IN NUMBER,

pCatePreturiAmModificat OUT NUMBER

)

IS

Begin

UPDATE Filme SET

Ai intrebari tehnice legate de Oracle PL-SQL si vrei sa te ajutam?

Detinem cea mai mare comunitate IT din Romania (peste 11.000 membri activi), unde primesti suport gratuit la problemele tale.

Lectia 3: Structuri de control in PL-SQL

  1. Sa se realizeze un block anonim PL/SQL care sa preia de la tastatura data angajarii. In cazul in care data respectiva pica intr-o duminica bock-ul sa afiseze “Data invalida, este imposibil sa te fi angajat intr-o zi de DUMINICA”, altfel “Te-ai angajat intr-o zi de X”, unde X reprezinta ziua in care a picat data respectiva.

  2. Sa se realizeze un block anonim PL/SQL care sa preia de la tastatura meseria (de ex. “inginer”) si un salariu si care sa indexeze salariul in functie de meserie. Si anume: daca meseria este profesor salariul sa se indexeze cu 30 %, daca este inginer sa se indexeze cu 40%, pentru restul sa nu indexeze. Output-ul va fi:”Meseria dvs. Este X deci procentul de indexare va fi de Y rezultand un salariu indexat de Z”.

  3. Sa se realizeze un block anonim PL/SQL care sa afiseze pentru un an introdus de la tastatura calendarul de mai jos:

01-JAN-2005 a picat intr-o zi de Duminica

02-JAN-2005 a picat intr-o zi de Luni

03-JAN-2005 a picat intr-o zi de Marti

……

  1. Sa se realizeze un block anonim PL/SQL care sa afiseze datele calendaristice si ziua din saptamana in care au picat, de la ziua curenta pana la ziua in care se termina luna curenta (folositi o bucla WHILE).

23-MAR-2007 a picat intr-o zi de Vineri

24-MAR-2007 a picat intr-o zi de Sambata

……..

31-MAR-2007 a picat intr-o zi de Duminica

REZOLVARI Lectia 3: Structuri de control in PL-SQL

 1.

DECLARE

datDataAngajarii        DATE:=’&BagaDataAngajarii’;

strZiua                         VARCHAR2(20);

BEGIN

strZiua:=to_char(datDataAngajarii,’day’);

IF trim(strZiua)=’sunday’ Then

dbms_output.put_line(‘Data invalida, este imposibil sa te fi angajat intr-o zi de DUMINICA’);

Else

dbms_output.put_line(‘Te-ai angajat intr-o zi de ‘ || strZiua);

END IF;

END;

/

Lectia 4: Interactiunea codului PL-SQL cu datele dintr-o baza de date

  1. Sa se realizeze o procedura stocata numita spDaInfoAngajat care sa sprimeasca un id de angajat si care sa returneze in doi parametri de output numele si job-ul angajatului respectiv din tabela Angajati. Sa se invoce procedura respectiva intr-un block anonim care sa primeasca de la tastatura id-ul angajatului si care sa afiseze textul “Angajatul cu id-ul X se numeste Y si are functia Z”, unde X reprezinta id-ul intrdus de la tastatura, iar Y si Z sunt numele respectiv Job-ul returnate de procedura stocata.

  2. Sa se realizeze o functie stocata numita spDaSalMediuAnual care sa primeasca ca si argument de input un id de departament si sa returneze salariul mediu anual pentru toti angajatii din departamentul respectiv. Sa se invoce functia respectiva (intr-un select anonim) pentru fiecare departament din tabele Departamente.

OBS. Tabela Departamente se va crea identica cu tabela DEPT din schema scott

Create Table Departamente

As

Select * from scott.dept;

  1. Sa se realizeze o procedura stocata spAgregariDupaDepartament care sa primeasca ca si argument un id de departament si sa returneze sub forma unor parametri de output: numele departamentului, salariul mediu, total salarii

Sa se invoce procedura de mai sus intr-un block anonim, care sa preia de la tastatura id-ul de departament si care sa afiseze pe ecran mesajul:”Departamentul cu numele NUME are salariul mediu de SAL_MEDIU, respectiv total salarii SUMA_SALARII”, unde NUME, SAL_MEDIU si SUMA_SALARII sunt valorile parametrilor de output.

  1. Sa se creeze un tabel numit Produse care sa contina urmatoarele coloane:

ID_Produs               NUMBER sa fie Primary KEY

Nume_Produs          VARCHAR2(20)

Descriere                VARCHAR2(200)

Pret                       NUMBER

De asemenea sa se creeze o secventa (sqIdP) care sa fie folosita pe post de generator de numere pentru coloana Primary Key ID_Produs (secventa sa porneasca de la 10 si sa mearga din 10 in 10).

Sa se realizeze o procedura stocata numita spInsertProduse care sa insereze in tabela clienti un produs. Aceasta procedura sa aiba 3 argumente de intrare (pNume_Produs, pDescriere, pPret) si unul de iesire (pId_Nou). Procedura pe baza argumentelor de intrare sa insereze in tabela Produse un produs si sa returneze in parametrul de output (pId_Nou) id-ul noului produs.

Sa se invoce procedura de mai sus intr-un block anonim care sa preia de la tastatura argumentele de intrare, iar apoi sa returneze textul “Produsul a fost adaugat cu succes in baza de date si are id-ul X”, unde X reprezinta valoarea parametrului de output returnat de procedura.

REZOLVARI Lectia 4: Interactiunea codului PL-SQL cu datele dintr-o baza de date

1) Procedura stocata

Create or Replace Procedure spDaInfoAngajat

(

p_id              IN       Angajati.EMPNO%TYPE,

p_nume         OUT    Angajati.ENAME%TYPE,

p_job           OUT    Angajati.JOB%TYPE

)

AS

Begin

Select Ename, JOB Into p_nume,p_job From Angajati

Where EMPNO=p_id;

End;

/

Block-ul anonim ce invoca procedura

Declare

id       Angajati.EMPNO%TYPE:=&BagaIdul;

nume Angajati.ENAME%TYPE;

job     Angajati.JOB%TYPE;

Begin

spDaInfoAngajat(id, nume, job);

dbms_output.put_line(‘Angajatul cu id-ul ‘ || id || ‘ se numeste ‘ || nume || ‘ si are functia ‘ || job);

End;

/

2) Functia stocata

Create or Replace Function spDaSalMediuAnual

(

p_id_dept      IN       Departamente.DEPTNO%TYPE

)

Return Number

AS

v_sal_mediu   NUMBER;

Begin

Select avg(sal) Into v_sal_mediu From Angajati

Where DEPTNO=p_id_dept;

Return v_sal_mediu;

End;

/

Block-ul anonim ce invoca functia

select deptno,

dname,

spDaSalMediuAnual(deptno) “Sal Mediu”

From Departamente;

3) Procedura stocata

Create or Replace Procedure spAgregariDupaDepartament

(

p_id_dept      IN       Departamente.DEPTNO%TYPE,

p_nume_dept OUT    Departamente.DNAME%TYPE,

p_sal_mediu   OUT    Number,

p_sal_total     OUT    Number

)

AS

Begin

Select avg(sal), sum(sal) Into p_sal_mediu, p_sal_total From Angajati

Where DEPTNO=p_id_dept;

Select DNAME Into p_nume_dept from Departamente

Where DEPTNO=p_id_dept;

End;

/

 Block-ul anonim ce invoca procedura

Declare

id                 Angajati.EMPNO%TYPE:=&BagaIdul;

nume_dept    Departamente.DNAME%TYPE;

sal_mediu      Number;

sal_total        Number;

Begin

spAgregariDupaDepartament(id, nume_dept, sal_mediu, sal_total);

dbms_output.put_line(‘Departamentul cu numele ‘ || nume_dept || ‘ are salariul mediu de ‘ || sal_mediu || ‘, respectiv total salarii ‘ || sal_total);

End;

/

4) Creare tabela Produse

Create Table Produse

(

ID_Produs               NUMBER,

Nume_Produs          VARCHAR2(20),

Descriere                VARCHAR2(200),

Pret                       NUMBER,

CONSTRAINT pkIDP Primary Key (ID_Produs)

);

            Creare secventa sqIdP

Create Sequence sqIdP

Increment By 10

Start With 10;

Creare Procedura

Create or Replace Procedure spInsertProduse

(

pNume             IN Produse.Nume_Produs%TYPE,

pDescriere IN Produse.Descriere%TYPE,

pPret      IN Produse.Pret%TYPE,

pId_Nou           OUT Produse.ID_Produs%TYPE

)

As

Begin

Select sqIdP.NEXTVAL INTO pId_Nou from dual;

Insert Into Produse

Values

(

pId_Nou,

pNume,

pDescriere,

pPret

);

End;

/

            Creare block anonim

Declare

Nume    Produse.Nume_Produs%TYPE:=’&BagaNumeleProdusului’;

Descriere Produse.Descriere%TYPE:=’&BagaDescrierea’;

Pret       Produse.Pret%TYPE:=&BagaPretul;

ID_Nou            Produse.ID_Produs%TYPE;

Begin

spInsertProduse(Nume, Descriere, Pret, ID_NOU);

dbms_output.put_line(‘Produsul a fost adaugat cu succes in baza de date si are id-ul ‘ || ID_NOU);

End;

/

Lectia 5: Tipuri de date compuse in PL-SQL

  1. Sa se scrie un block anonim care sa preia de la tastatura un id de angajat si care sa afiseze toate coloanele din tabela Angajati folosind o variabila de tip %ROWTYPE.

  2. Sa se scrie un block anonim care sa preia de la tastatura un id de angajat si care sa afiseze numai coloanele Ename, Job, Hiredate (SA SE FOLOSEASCA O VARIABILA DE TIP RECORD)

  3. Sa se rescrie exercitiul de la punctul 3 din “5 WORKSHOP Interactiunea cu Oracle din PLSQL” (cu o mica modificare si anume nu mai afisati Numele departamentului), astfel incat sa foloseasca variabila de tip RECORD.

  4. Sa se scrie un block anonim care sa contina un RECORD_TABLE cu 3 coloane:

Numele, Functia, DataAngajarii

Sa se populeze acest RECORD_TABLE cu 2 Record-uri:

Numele          Functia           DataAngajarii

Ion               Functionar     09-Mar-1970

Maria           Inginer 07-AUG-1960

Sa se parcurga acest RECORD_TABLE si sa se insereze in tabela Angajati toate liniile continute de acesta.

REZOLVARI Lectia 5: Tipuri de date compuse in PL-SQL

1) Block-ul anonim

Declare

ID_P             Angajati.EMPNO%TYPE:=&BagaIdul;

linie_gasita     Angajati%ROWTYPE;

Begin

select * Into linie_gasita from Angajati

Where EMPNO=ID_P;

dbms_output.put_line(‘Nume angajat:’ || linie_gasita.ename);

dbms_output.put_line(‘Nume angajat:’ || linie_gasita.job);

dbms_output.put_line(‘Nume angajat:’ || linie_gasita.sal);

dbms_output.put_line(‘Nume angajat:’ || linie_gasita.hiredate);

End;

/

2) Block-ul anonim

Declare

ID_P             Angajati.EMPNO%TYPE:=&BagaIdul;

Type type_linie_gasita IS RECORD

(

Numele                   Angajati.ENAME%TYPE,

Profesia        Angajati.JOB%TYPE,

DataAng                  Angajati.HIREDATE%TYPE

);

linie_gasita     type_linie_gasita;

Begin

select ename, job, hiredate Into linie_gasita from Angajati

Where EMPNO=ID_P;

dbms_output.put_line(‘Nume angajat:’ || linie_gasita.Numele);

dbms_output.put_line(‘Nume angajat:’ || linie_gasita.Profesia);

dbms_output.put_line(‘Nume angajat:’ || linie_gasita.DataAng);

End;

/

3) Procedura stocata

Create or Replace Procedure spAgregariDupaDepartament

(

p_id_dept      IN       Departamente.DEPTNO%TYPE,

p_sal_mediu   OUT    Number,

p_sal_total     OUT    Number

)

AS

Type type_linie_gasita IS RECORD

(

Sal_Mediu      Number,

Sal_Total       Number

);

linie_gasita     type_linie_gasita;

Begin

Select avg(sal), sum(sal) Into linie_gasita From Angajati

Where DEPTNO=p_id_dept;

p_sal_mediu:=linie_gasita.Sal_Mediu;

p_sal_total:=linie_gasita.Sal_Total;

End;

/

Block-ul anonim ce invoca procedura

Declare

id                 Angajati.EMPNO%TYPE:=&BagaIdul;

sal_mediu      Number;

sal_total        Number;

Begin

spAgregariDupaDepartament(id, sal_mediu, sal_total);

dbms_output.put_line(‘Departamentul cu id-ul ‘ || id || ‘ are salariul mediu de ‘ || sal_mediu || ‘, respectiv total salarii ‘ || sal_total);

End;

/

4) Block-ul anonim

Declare

Type type_linie IS Record

(

Numele                   VARCHAR2(20),

Functia                   VARCHAR2(20),

DataAngajarii DATE

);

Type  type_tabel_virtual IS TABLE OF type_linie

Index By Binary_Integer;

tabel_virtual   type_tabel_virtual;

linia1             type_linie;

linia2             type_linie;

linia3             type_linie;

linia4             type_linie;

Begin

linia1.Numele:=’Ion’;

linia1.Functia:=’Functionar’;

linia1.DataAngajarii:=’09-Mar-1970′;

–adaug linia1 la tabelul virtual

tabel_virtual(0):=linia1;

linia2.Numele:=’Maria’;

linia2.Functia:=’Inginer’;

linia2.DataAngajarii:=’07-AUG-1960′;

–adaug linia2 la tabelul virtual

tabel_virtual(1):=linia2;

linia3.Numele:=’Radu’;

linia3.Functia:=’Manager’;

linia3.DataAngajarii:=’03-Mar-1975′;

–adaug linia3 la tabelul virtual

tabel_virtual(2):=linia3;

linia4.Numele:=’Vasile’;

linia4.Functia:=’Actor’;

linia4.DataAngajarii:=’02-SEP-1950′;

–adaug linia4 la tabelul virtual

tabel_virtual(3):=linia4;

–Parcurg tabelul virtual pentru a afisa liniile din el

for i in 0..3 LOOP

dbms_output.put_line(‘Nume:’ || tabel_virtual(i).Numele);

dbms_output.put_line(‘Functia:’ || tabel_virtual(i).Functia);

dbms_output.put_line(‘Data angajarii:’ || tabel_virtual(i).DataAngajarii);

dbms_output.put_line(‘___________________’);

End LOOP;

End;

Lectia 6: Tratarea exceptiilor in PL-SQL

A) Erori Predefinite

  1. Sa se completeze procedura de la punctul 1. “5 WORKSHOP Interactiunea cu Oracle din PLSQL”:

Sa se realizeze o procedura stocata numita spDaInfoAngajat care sa sprimeasca un id de angajat si care sa returneze in doi parametri de output numele si job-ul angajatului respectiv din tabela Angajati. Sa se invoce procedura respectiva intr-un block anonim care sa primeasca de la tastatura id-ul angajatului si care sa afiseze textul “Angajatul cu id-ul X se numeste Y si are functia Z”, unde X reprezinta id-ul intrdus de la tastatura, iar Y si Z sunt numele respectiv Job-ul returnate de procedura stocata.

astfel incat sa trateze si urmatoarea eroare posibila:

In cazul in care se introduce un id inexistent, sa se afiseze mesajul “Id angajat inexistent”

  1. Sa se realizeze un block anonim care sa primeasca de la tastatura un nume de angajat (de ex. SMITH) si apoi sa afiseze Job-ul, Sal si Hiredate-ul pentru respectivul angajat. Sa se trateze eroarea ce apare in cazul in care se gasesc mai multi cu numele respectiv (too_many_rows)

B) Erori ne-predefinite
Sa se adauge o constrangere de tip CHECK la tabela Angajati care sa nu permita adaugarea de salarii mai mici de 100. Sa se realizeze un block anonim care sa preia de la tastatura EmpNo, Ename, JOB, SAL si care sa adauge in tabela Angajati o linie cu datele respective. Sa se prinda exceptia generata in cazul in care se introduce de la tastatura un sal mai mic de 100, afisand un mesaj de genul “Salariul nu poate fi mai mic decat 100″.

C) Erori Utilizator
Sa se stearga constrangerea de mai sus. Sa se testeze block-ul de mai sus pentru un salariu mai mic de 100. Ar trebui sa mearga fara afisarea exceptiei. Dorim sa punem constrangerea logic. Adica luam noi decizia sa iesim cu o exceptie din block atunci cand se introduce un salariu mai mic de 100.

Pentru aceasta sa se realizeze o procedura stocata care sa contina block-ul de mai sus. Aceasta procedura stocata sa dea o exceptie Utilizator (cu codul ) atunci cand se incearca adaugarea unui salariu mai mic de 100. Sa se invoce procedura intr-un block anonim prinzand exceptia in acest block.

REZOLVARI Lectia 6: Tratarea exceptiilor in PL-SQL

Procedura stocata modificata

Create or Replace Procedure spDaInfoAngajat

(

p_id    IN       Angajati.EMPNO%TYPE,

p_nume         OUT    Angajati.ENAME%TYPE,

p_job OUT    Angajati.JOB%TYPE

)

AS

Begin

Select Ename, JOB Into p_nume,p_job From Angajati

Where EMPNO=p_id;

EXCEPTION

WHEN NO_DATA_FOUND Then

dbms_output.put_line(‘Id angajat inexistent’);

End;

/

            Block-ul anonim ramane nemodificat

Declare

id       Angajati.EMPNO%TYPE:=&BagaIdul;

nume Angajati.ENAME%TYPE;

job     Angajati.JOB%TYPE;

Begin

spDaInfoAngajat(id, nume, job);

dbms_output.put_line(‘Angajatul cu id-ul ‘ || id || ‘ se numeste ‘ || nume || ‘ si are functia ‘ || job);

End;

/

Lectia 7: Cursoare PL-SQL

1) Sa se scrie un block anonim care sa afiseze din tabela EMP coloanele Nume, Salariu, Functia dar numai pe cei ce au salariul mai mare de 2000, folosind cursoare.

Nr. Crt.           Nume              Salariul           Functia

_______________________________________________

1                      JONES            2200                MANAGER

2                      JONES            3272.5             MANAGER

3                      BLAKE          3135                MANAGER

4                      CLARK          2695                MANAGER

5                      SCOTT            3000                ANALYST

6                      KING              5000                PRESIDENT

7                      FORD             3000                ANALYST

2) Avem un buget pentu bonusuri salariale in valoare de 1500. Vrem sa distribuim acesti bani astfel:

-prioritate vor avea angajatii mai vechi din firma.

-fiecare salariat va primi 10% din salariul pe care il are in prezent.

-daca un salariat nu poate sa-si primeasca toata suma (pentru ca nu mai sunt bani), nu va primi nimic.

Afisati salariatii ce vor primi bani, salariul vechi,ce bani vor primi precum si suma eventual ramasa.

Acest lucru se va realiza implementand o procedura stocata numita spdaBonus care sa primeasca ca si parametru de input bonusul total disponibil si un parametru de output care sa stocheze eventuala suma ramasa.

 spdaBonus(2500,suma_ramasa)

Nr. Crt.           Nume              Salariul           Bonus

___________________________________________

1                      SMITH            1064.8             106.48

2                      ALLEN           1600                160

3                      WARD            1250                125

4                      JONES            3272.5             327.25

5                      BLAKE          3135                313.5

6                      CLARK          2695                269.5

7                      TURNER        1500                150

A mai ramas de distribuit suma de 48.27

Rollback;

Exercitiul de mai jos sa fie implementat intr-o procedura stocata numita daBugetSalarizare care sa aiba un parametru de input pCati si parametru de output pSumaNecesara

Declare

V_Cati                        Number:=&PrimiiCati;

V_suma_necesara       Number;

Begin

daBugetSalarizare(V_Cati, V_suma_necesara)

dbms …(’Bugetul total pt primii ‘ || v_cati || ‘este de ‘ || v_suma_necesara);

End;

/

Introducem 10 la v_cati. Cat va fi V_suma_necesara?

3) Daca ar fi sa dau un bonus la salariu la cei mai noi 5 angajati din firma, cate 10 centi pentru fiecare zi lucarata in firma, ce suma mi-ar trebui? Afisati numele, Data Angajarii, bonusul si suma totala pentru bonus.

Nr. Crt.           Nume              Data Ang       Bonus

________________________________________

1                      ADAMS         23-MAY-87    725.1

2                      SCOTT            19-APR-87      728.5

3                      MILLER         23-JAN-82      919.7

4                      FORD             03-DEC-81     924.8

5                      JAMES           03-DEC-81     924.8

Bugetul total este 5149.3

REZOLVARI Lectia 7: Cursoare PL-SQL

1.

Declare

cursor cBogati IS

Select Ename, Sal, Job

From Angajati

Where Sal>2000;

v_nume                  Angajati.Ename%Type;

v_sal                      Angajati.Sal%Type;

v_job                      Angajati.Job%Type;

v_linie_curenta         NUMBER:=0;

Begin

open cBogati;

dbms_output.put_line(rpad(‘Nr. Crt.’,10,’ ‘) || rpad(‘Nume’,10,’ ‘) || rpad(‘Salariul’,10,’ ‘) || rpad(‘Functia’,10,’ ‘));

dbms_output.put_line(‘________________________________________’);

Loop

Fetch cBogati Into v_nume,v_sal,v_job;

v_linie_curenta:=v_linie_curenta+1;

Exit When cBogati%NOTFOUND;

dbms_output.put_line(rpad(v_linie_curenta,10,’ ‘) || rpad(v_nume,10,’ ‘) || rpad(v_sal,10,’ ‘) || rpad(v_job,10,’ ‘));

End Loop;

close cBogati;

End;

/

2.

Declare

cursor cBonus IS

Select Ename, Sal

From Angajati

Order By Hiredate;

v_nume                  Angajati.Ename%Type;

v_sal                      Angajati.Sal%Type;

v_buget                  NUMBER:=1500;

v_bonus                  NUMBER;

v_suma_ramasa       NUMBER;

v_linie_curenta NUMBER:=0;

Begin

open cBonus;

dbms_output.put_line(rpad(‘Nr. Crt.’,10,’ ‘) || rpad(‘Nume’,10,’ ‘) || rpad(‘Salariul’,10,’ ‘) || rpad(‘Bonus’,10,’ ‘));

dbms_output.put_line(‘________________________________________’);

Loop

Fetch cBonus Into v_nume,v_sal;

v_linie_curenta:=v_linie_curenta+1;

v_bonus:=v_sal/10;

v_buget:=v_buget-v_bonus;

Exit When cBonus%NOTFOUND OR v_buget<0;

dbms_output.put_line(rpad(v_linie_curenta,10,’ ‘) || rpad(v_nume,10,’ ‘) || rpad(v_sal,10,’ ‘) || rpad(v_bonus,10,’ ‘));

End Loop;

v_suma_ramasa:=v_buget+v_bonus;

dbms_output.put_line(‘A mai ramas de distribuit suma de ‘ || v_suma_ramasa);

close cBonus;

End;

/

3.

Declare

cursor cBonus IS

Select Ename, HireDate

From Angajati

Where Hiredate is not null

Order By Hiredate Desc;

v_nume                  Angajati.Ename%Type;

v_data_ang              Angajati.Hiredate%Type;

v_buget                  NUMBER:=0;

v_bonus                  NUMBER:=0;

v_linie_curenta NUMBER:=0;

Begin

open cBonus;

dbms_output.put_line(rpad(‘Nr. Crt.’,10,’ ‘) || rpad(‘Nume’,10,’ ‘) || rpad(‘Data Ang’,10,’ ‘) || rpad(‘Bonus’,10,’ ‘));

dbms_output.put_line(‘________________________________________’);

Loop

Fetch cBonus Into v_nume,v_data_ang;

v_linie_curenta:=v_linie_curenta+1;

v_bonus:=trunc(SYSDATE-v_data_ang)/10;

v_buget:=v_buget+v_bonus;

Exit When cBonus%NOTFOUND OR v_linie_curenta=6;

dbms_output.put_line(rpad(v_linie_curenta,10,’ ‘) || rpad(v_nume,10,’ ‘) || rpad(v_data_ang,10,’ ‘) || rpad(v_bonus,10,’ ‘));

End Loop;

dbms_output.put_line(‘Bugetul total este ‘ || v_buget);

close cBonus;

End;

/

Lectia 8: Triggers (declansatoare)

1) Sa se implementeze un trigger ce nu lasa sa se modifice in niciun fel datele din tabela Angajati in zilele de Duminica. In functie de operatiile ce se incearca sa apara un mesaj de eroare de genul:

“nu puteti modifica datele in zilele de Duminica” sau

“nu puteti sa inserati date Duminica” sau

“nu puteti sa stergeti date Duminica”

Obs. Trebuie sa facem trigger-ul “sensibil” la operatiile INSERT, UPDATE, DELETE la timing-ul BEFORE (inainte de modificarea propriu-zisa).

Sa se testeze apoi trigger-ul pentru cele 3 operatii de mai sus (rugati trainer-ul sa dea data server-ului intr-o zi de Duminica).

2) Sa se creeze un tabel de audit numit Operatii_Angajati care sa contina urmatoarele coloane:

CineAModificat          VARCHAR2(20)

InCeData                    DATE

CeOperatieAFacut      VARCHAR2(20)

CeAModificat             VARCHAR2(20)

CuCeAModificat        VARCHAR2(20)

Acest tabel de audit isi propune sa stocheze toate operatiile facut de utilizatori in tabela Angajati PE COLOANA SAL.

Sa se realizeze un trigger numit monitorizare “sensibil” la INSERT, UPDATE, DELETE la timing-ul AFTER si care sa se declanseze pentru fiecare linie modificata in parte. Acest trigger sa se ocupe cu popularea tabelului de audit Operatii_Angajati.

Dupa realizarea trigger-ului incercati sa modificati, inserati, respectiv stergeti linii din tabela Angajati. Apoi interogati tabela Operatii_Angajati, pentru a putea vedea un istoric cu toate operatiile facute. La sfarsit dati rollback!

3) Faceti in asa fel incat managerii din tabela Angajati sa nu poata fi schimbati vreodata din functie. Incercati sa modificati job-ul unui manager, apoi job-ul altuia.

4) Faceti in asa fel incat KING sa ramana singurul PRESIDENT din tabela angajati. Incercati sa modificati job-ul unui angajat, in PRESIDENT

REZOLVARI Lectia 8: Triggers (declansatoare)

1.

Create Or Replace Trigger sec_duminica

Before Delete Or Insert Or Update On Angajati

Begin

IF rtrim(to_char(SYSDATE,’day’))<>’sunday’ Then

return; — iese din trigger daca nu este duminica

end if;

IF Deleting Then

raise_application_error(-20501,’Nu puteti sterge nimic din tabela Angajati intr-o zi de Duminica’);

ElsIf Inserting Then

raise_application_error(-20502,’Nu puteti adauga nimic in tabela Angajati intr-o zi de Duminica’);

ElsIf Updating Then

raise_application_error(-20503,’Nu puteti modifica nimic din tabela Angajati intr-o zi de Duminica’);

End If;

End;

/

2.

Create Table Operatii_Angajati

(

CineAModificat                      VARCHAR2(20),

InCeData                    DATE,

CeOperatieAFacut      VARCHAR2(20),

CeAModificat             VARCHAR2(20),

CuCeAModificat                    VARCHAR2(20)

);

/

Create Or Replace Trigger monitorizare

After Delete Or Insert Or Update On Angajati

For Each Row

Begin

IF Deleting Then

Insert  Into Operatii_Angajati

Values(User,Sysdate,’Stergere’,:old.SAL,”);

ElsIf Inserting Then

Insert  Into Operatii_Angajati

Values(User,Sysdate,’Adaugare’,”,:new.SAL);

ElsIf Updating Then

Insert  Into Operatii_Angajati

Values(User,Sysdate,’Stergere’,:old.SAL,:new.SAL);

End If;

End;

3.

Create Or Replace Trigger managers_for_life

Before Update On Angajati

For Each Row

Begin

IF :old.job=’MANAGER’ AND UPDATING(‘JOB’) Then

:new.job:=’MANAGER’;

End If;

End;

/

update angajati set job=’jobless’;

4.

Create Or Replace Trigger king_president__for_life

Before Update On Angajati

For Each Row

When (new.job=’PRESIDENT’ and old.ename<>’KING’)

Begin

raise_application_error(-20501,’Numai King poate sa fie President’);

End;

/