Aplicatie informatica. utilizarea bazelor de date oracle in vanzari.

APLICATIE INFORMATICA. UTILIZAREA BAZELOR DE DATE ORACLE IN VANZARI.

1. CREAREA BAZEI DE DATE.

Se creaza tabelele CLIENT, FURNIZOR, TARA, APELURI_TEL, COMANDA, ARTICOLE_COMENZI, STOC_PRODUSE, CATALOG.

DROP TABLE Client;

CREATE TABLE Client

(

cod_client integer not null,

nume char(15) not null

constraint ck_client_nume check (nume=LTRIM(initcap(nume))),

prenume char(15)

constraint ck_client_prenume check (prenume=LTRIM(initcap(prenume))),

firma char(20) not null,

adresa1 char(50) not null,

oras char(15) default 'Dr.Tr. Severin',

cod_tarachar(2) default 'RO',

cod_postal char(5) default '1500',

telefon char(18),

primary key (cod_client),

foreign key(cod_tara) references Tara (cod_tara)

);

COMMIT

DROP TABLE Furnizor;

CREATE TABLE Furnizor

(

cod_furnizor integer not null,

nume_furnizor char(15)

constraint nn_furnizor_nume_furnizor not null,

timp_livrare integer default 14

constraint ck_furnizor_timp_livrare check (timp_livrare between 1 and 21),

cod_tara char(2),

primary key (cod_furnizor),

foreign key (cod_tara) references Tara(cod_tara)

);

COMMIT

DROP TABLE Tara;

CREATE TABLE Tara

(

cod_tara char(2) not null,

nume_tarachar(15) default 'Romania'

constraint ck_Tara_nume_tara check (nume_tara=LTRIM(initcap(nume_tara))),

primary key (cod_tara)

);

COMMIT

DROP TABLE Apeluri_Tel;

CREATE TABLE Apeluri_Tel

cod_clientinteger not null,

ora_apel varchar2(35) default systimestamp,

cod_apel number default 1

constraint ck_Apeluri_tel_cod_apel check (cod_apel in(1,2,3)),

descriere_apel varchar2(100),

primary key (cod_client,ora_apel),

foreign key (cod_client) references Client(cod_client)

COMMIT

DROP TABLE Comanda;

CREATE TABLE Comanda

(

nr_comanda INTEGER not null,

data_comanda DATE DEFAULT SYSDATE,

cod_client INTEGER NOT NULL,

data_livrare DATE DEFAULT SYSDATE+14,

data_platii DATE,

primary key (nr_comanda),

foreign key (cod_client) references Client(cod_client)

);

COMMIT

DROP TABLE Articole_Comenzi;

CREATE TABLE Articole_Comenzi

(

nr_comanda integer not null,

indice_comanda integer not null,

cod_produs integer not null,

cod_furnizorinteger not null,

cantitate number(6,2),

primary key (Nr_comanda, Indice_comanda),

foreign key (Nr_comanda) references Comanda(Nr_comanda) on delete cascade,

foreign key (Cod_produs,Cod_furnizor) references Stoc_produse(Cod_produs,Cod_furnizor)

);

COMMIT

DROP TABLE Stoc_produse;

CREATE TABLE Stoc_produse

(

cod_produsINTEGER NOT NULL,

cod_furnizor INTEGER NOT NULL,

denumire CHAR(50),

pret_unitar NUMBER(6,2)

CONSTRAINT ck_Stoc_produse_pret_unitar check (pret_unitar BETWEEN 1 and

500), cantitate_disponibila number(7,2)

CHECK (cantitate_disponibila BETWEEN 0 and 15000), u_m char(10)

DEFAULT 'BUC.'

CONSTRAINT ck_Stoc_produse_u_m CHECK (UPPER(u_m) IN ('LITRI',

'PACHET', 'BUC.')),

PRIMARY KEY (Cod_produs,Cod_furnizor),

FOREIGN KEY (Cod_furnizor) REFERENCES Furnizor(Cod_furnizor)

);

COMMIT

DROP TABLE Catalog;

CREATE TABLE Catalog

(

serie_catalog INTEGER NOT NULL,

cod_produs INTEGER NOT NULL,

cod_furnizorINTEGER NOT NULL,

descriere NVARCHAR2(100),

foto BLOB,

reclama BLOB,

PRIMARY KEY (Serie_catalog),

FOREIGN KEY (Cod_produs,Cod_furnizor) REFERENCES Stoc_produse

(Cod_produs, Cod_furnizor) ON DELETE CASCADE );

COMMIT

Schema bazei de date astfel create arata ca in figura urmatoare:

SCHEMA BAZEI DE DATE

2. DEFINIREA UNUI GENERATOR DE NUMERE DE SECVENTA SI A UNOR TRIGGERI SERIALI.

Inainte de a trece la inserarea efectiva a datelor in tabele vom crea pentru tabelele Client, Furnizor, Stoc_produse, Catalog cate un declansator (trigger) la nivel de linie pentru generarea valorilor implicite, prin care inaintea inserarii unei noi inregistrari valoarea atributului cod_client, cod_furnizor, cod_produs, serie_catalog respectiv, sa fie obtinuta in mod automat. Se va folosi functia NVL care ne permite inlocuirea unei valori NULL, atunci cand ea este intalnita, cu o valoare predefinita: NVL(parametru a carui valoare se testeaza, valoare care ii ia locul in caz de NULL).

Pentru tabelul comanda vom defini o secventa (sequence) care, la fel ca si triggerii de mai sus genereaza automat numere unice pentru campul nr_comanda. Daca asimilam comanda unei facturi, avem astfel definit intervalul (seriile) facturilor.

CREATE OR REPLACE TRIGGER serial_cl

BEFORE INSERT ON Client

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

BEGIN

SELECT NVL(MAX(cod_client),100)+1 INTO :NEW.cod_client

FROM Client

END;

CREATE OR REPLACE TRIGGER serial_fu

BEFORE INSERT ON Furnizor

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

BEGIN

SELECT NVL(MAX(cod_furnizor),1000)+1 INTO:NEW.cod_furnizor

FROM Furnizor

END;

CREATE OR REPLACE TRIGGER serial_st

BEFORE INSERT ON Stoc_produse

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

BEGIN

SELECT NVL(MAX(cod_produs),100000)+1 INTO:NEW.cod_produs

FROM Stoc_produse

END;

CREATE OR REPLACE TRIGGER serial_cat

BEFORE INSERT ON Catalog

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

BEGIN

SELECT NVL(MAX(serie_catalog),5000)+1 INTO:NEW.serie_catalog

FROM Catalog

END;

CREATE SEQUENCE secv_com // nume secventa

INCREMENT BY 1 // pasul de incrementare

START WITH 10501 // valoarea de pornire a secventei

MAXVALUE 10999 // valoarea maxima a secventei

NOCACHE NOCYCLE; // secventa finita

3. INSERAREA DATELOR IN TABELE.

Valorile pentru anumite atribute din tabela Comanda, Articole_comenzi, etc. nu vor direct introduse, deoarece aceste campuri sunt unele calculate, iar valorile lor se vor introduce ulterior.

Furnizor

Stoc produse

Client

Comanda

Tara

Articole_comenzi

4. MODIFICAREA STRUCTURII TABELELOR BAZEI DE DATE

1) Sa se modifice dimensiunea atributului desciere_apel in tabela Apeluri_tel, de la 100 la 50 de caractere.

ALTER TABLE Apeluri_tel MODIFY (descriere_apel varchar2(50));

DESCRIBE Apeluri_tel;

2) Adaugati atributul TVA si val fara TVA (number(6,2)), la tabela Articole_comenzi si Comanda. In plus in tabelul Comanda vom introduce si atributul val_totala.

ALTER TABLE Articole_comenzi ADD (TVA number(6,2));

ALTER TABLE Articole_comenzi ADD (val_fara_TVA number(6,2));

ALTER TABLE Comanda ADD (total_TVA number(6,2));

ALTER TABLE Comanda ADD (total_val_fara_TVA number(6,2));

ALTER TABLE Comanda ADD (val_totala number(6,2));

DESCRIBE Articole_comenzi;

DESCRIBE Comanda;

3) Adaugati atributul adresa2, char(20), la tabela Client, pentru cazul in care un client are mai mult de o adresa..

ALTER TABLE Client ADD (adresa2 char(20));

DESCRIBE Client;

5. ACTUALIZARI, INTEROGARI, FUNCTII MATEMATICE, FUNCTII PENTRU SIRURI DE CARACTERE ETC.

1) Sa se afiseze ultimul numar utilizat din secventa secv_com:

SELECT secv_com.CURRVAL

FROM DUAL;

2) Sa se mareasca pretul la motorina cu 0.15 RON iar la benzina cu 8%.

UPDATE stoc_produse

SET pret_unitar= pret_unitar+0.15

WHERE denumire LIKE 'Motorina%';

UPDATE stoc_produse

SET pret_unitar= pret_unitar+pret_unitar*0.08

WHERE denumire LIKE 'Benzina%';

SELECT * FROM stoc_produse

WHERE (denumire LIKE 'Motorina%') OR (denumire LIKE 'Benzina%')

ORDER BY pret_unitar;

3) Sa se calculeze si sa se introduca in tabelul Articole_comenzi valoarea atributului val_fara_TVA si TVA pentru fiecare tuplu.

UPDATE Articole_comenzi ac SET

val_fara_tva = cantitate*(SELECT pret_unitar FROM stoc_produse sp

WHERE ac.cod_produs=sp.cod_produs)/1.19;

UPDATE Articole_comenzi ac SET

tva = val_fara_tva*0.19;

SELECT * FROM Articole_comenzi (din motive de spatiu prezentam doar cateva linii).

4) Sa se calculeze valoarea, numarul de unitati si de SKU-uri de pe fiecare comanda

SELECT nr_comanda, SUM(cantitate*pret_unitar) AS valoare_comanda, SUM(cantitate) AS unitati, COUNT(*) AS SKU

FROM articole_comenzi a, stoc_produse sp

WHERE a.cod_produs=sp.cod_produs

GROUP BY nr_comanda

ORDER BY nr_comanda;

(din motive de spatiu prezentam doar cateva linii):

5) Sa se calculeze si sa se introduca in tabelul Comanda valoarea atributelor total_TVA, total_val_fara_TVA si val_totala pentru fiecare tuplu.

UPDATE Comanda cc SET

val_totala=(SELECT SUM(cantitate*pret_unitar)

FROM articole_comenzi a, stoc_produse sp

WHERE (a.cod_produs=sp.cod_produs) AND

nr_comanda=cc.nr_comanda);

UPDATE Comanda cc SET

total_TVA=0.19*val_totala/1.19;

UPDATE Comanda cc SET

total_val_fara_TVA=val_totala/1.19;

SELECT * FROM comanda

ORDER BY nr_comanda (din motive de spatiu prezentam doar cateva linii):

6) Sa se afiseze codul produsului, denumirea si pretul marit cu 5% pentru produsele livrate de furnizorul OMV. (dim motive de spatiu se vor afisa doar 12 tupluri):

SELECT cod_produs,denumire,cod_furnizor, pret_unitar AS PRET_INITIAL,

DECODE (cod_furnizor, 1003, pret_unitar+pret_unitar*0.05, pret_unitar) AS PRET_MARIT

FROM Stoc_produse

WHERE cod_produs<=100012;

Ca algoritm de analiza al functiei DECODE se poate observa ca, pentru coloana cod_furnizor (primul argument) are loc cautarea valorii "1003", iar in cazul in care aceasta este regasita, valoarea atributului pret din acel tuplu este actualizata cu 5%.

Pentru restul produselor care nu fac obiectul cautarii, se trece implicit, ultimul argument, in cazul de fata coloana pret_unitar.

7) Sa se afiseze Cantitatea Medie cumparata din fiecare produs (evident doar daca apare pe mai multe comenzi) si sa se ordoneze tuplurile dupa cantitate descrescator:

SELECT p.denumire, AVG (cantitate) cant_medie, COUNT(*) frecv_aparitie

FROM Stoc_produse P, Articole_comenzi a

WHERE p.cod_produs= a.cod_produs

GROUP BY p.denumire HAVING COUNT(*)>1

ORDER BY AVG (cantitate) DESC;

8) Sa se extraga o lista, ordonata alfabetic, ce va contine firmele (clientii) din Romania si adresa lor completa.

SELECT firma, CONCAT (adresa1, oras) as ADRESA_COMPLETA, nume_tara

FROM client c, tara t

WHERE (c.cod_tara=t.cod_tara) AND (nume_tara LIKE 'Ro%')

ORDER BY firma

9) Aceiasi cerinta ca la punctual 8) insa ordonarea se va face descrescator in functie de achizitiile fiecarei societati comerciale (topul firmelor din Romania).

DROP VIEW top_firme;

CREATE VIEW top_firme AS

SELECT cod_client, SUM(val_totala) AS total_achizitii, COUNT (*) AS nr_comenzi

FROM Comanda

GROUP BY cod_client

ORDER BY cod_client;

SELECT firma, total_achizitii, nr_comenzi

FROM top_firme t, client c

WHERE (t.cod_client=c.cod_client) AND (cod_tara IN('RO'))

ORDER BY total_achizitii DESC;

10) Sa se afiseze produsele care sunt oferite de furnizorul "OMV" - cantitate si pret. (Oferta de pret)

SELECT nume_furnizor, denumire, u_m, cantitate_disponibila, pret_unitar

FROM furnizor f ,stoc_produse p

WHERE (f.cod_furnizor=p.cod_furnizor) AND (nume_furnizor IN ('OMV'))

ORDER BY denumire;

11) Sa se afiseze comenzile cu valoare mai mica decat cea mai mare valoare a unei comenzi facute de firma 'Air France'. (1862.05 , 3488.20 , 1760)

SELECT nr_comanda, val_totala, cod_client

FROM comanda

WHERE val_totala < ANY( SELECT val_totala

FROM comanda c, client cl

WHERE c.cod_client=cl.cod_client

AND firma='Air France')

ORDER BY val_totala DESC;

Pentru interogarea de mai sus s-a folosit operatorul ANY cu semnificatia:

> ANYmai mare decat minim din lista

< ANYmai putin decat maxim din lista

= ANYin

> ALL     mai mare decat maxim din lista

< ALL     mai mic decat minim din lista

Cand se foloseste SOME/ANY, operatorul DISTINCT este frecvent utilizat pentru a impiedica sa se selecteze liniile de mai multe ori.

12) Afisati produsele care au fost comandate in cantitatea mai mare decat cea mai mica cantitate a produsului 100011 - 'Motorina Euro 4' (min(cantitate)100011=350).

SELECT a.cod_produs, s.denumire, a.cantitate

FROM Articole_comenzi a, Stoc_produse s

WHERE a.cod_produs=s.cod_produs AND

a.cod_produs!= 100011 AND cantitate > SOME

(SELECT DISTINCT cantitate

FROM Articole_comenzi

WHERE cod_produs=100011)

ORDER BY cantitate ASC;

13) Cate comenzi a facut fiecare client (clientii la care numele incepe cu ., clientii cu un anumit nr. de comenzi etc.)

SELECT nume, prenume, COUNT(*) AS nr_comenzi

FROM comanda co, client c

WHERE (co.cod_client=c.cod_client) AND (nume like '%')

GROUP BY nume , prenume

HAVING count(*)>0

ORDER BY nr_comenzi ASC;

14) Sa se afiseze, pentru fiecare comanda in parte, ce procent cantitativ si valoric reprezinta fiecare produs in parte din totalul produselor de pe comanda.

SELECT a.nr_comanda, a.cod_produs, a.procc/b.total*100 procent_cantitativ, a.procv/b.tval*100 procent_valoric

FROM

(SELECT nr_comanda, cod_produs, cantitate procc, val_fara_tva procv

FROM Articole_comenzi

GROUP BY nr_comanda, cod_produs,cantitate,val_fara_tva) a,

(SELECT nr_comanda, SUM (cantitate) total, SUM(val_fara_tva) tval

FROM Articole_comenzi

GROUP BY nr_comanda) b

WHERE a.nr_comanda=b.nr_comanda

ORDER BY nr_comanda

(din motive de spatiu afisam doar cateva comenzi)

15. Clientul care a facut cea mai mare comanda valoric/ unitati/ nr. SKU:

SELECT nume, prenume, firma ,nr_comanda, val_totala

FROM client c, comanda co

WHERE (c.cod_client=co.cod_client) AND (val_totala = (SELECT MAX(val_totala) FROM comanda));

CREATE OR REPLACE VIEW vc AS

SELECT nr_comanda, SUM(cantitate) AS unitati, COUNT(*) AS SKU

FROM articole_comenzi

GROUP BY nr_comanda;

SELECT nume, prenume, firma, co.nr_comanda, val_totala, unitati, SKU

FROM vc, Client c, comanda co

WHERE (vc.nr_comanda=co.nr_comanda) AND (co.cod_client=c.cod_client) AND (SKU/unitati=(SELECT MAX(SKU/unitati) FROM vc));

1 Sa se gasesca toti clientii care au comandat produse fabricate in Austria si pe cate comenzi:

SELECT nume, prenume, firma, COUNT(*) nr_comenzi

FROM Client c, Comanda co

WHERE c.cod_client=co.cod_client

AND nr_comanda IN

(SELECT c1.nr_comanda

FROM Comanda c1, Articole_comenzi a

WHERE c1.nr_comanda=a.nr_comanda

AND cod_produs IN

(SELECT a1.cod_produs

FROM Articole_comenzi a1, Stoc_produse p

WHERE a1.cod_produs = p.cod_produs

AND p.cod_furnizor IN

(SELECT p1.cod_furnizor

FROM Stoc_produse p1, Furnizor f

WHERE p1.cod_furnizor=f.cod_furnizor

AND cod_tara IN

(SELECT t1.cod_tara

FROM Furnizor t1, Tara t

WHERE t1.cod_tara=t.cod_tara

AND nume_tara = 'Austria')

) ) )

GROUP BY nume, prenume, firma;

17) Sa se determine toti clientii din orasul celui care a facut comanda 10505:

SELECT nume, prenume, firma, oras

FROM Client

WHERE oras IN (SELECT oras

FROM Client c, Comanda co

WHERE c.cod_client=co.cod_client

AND nr_comanda=10505

BLOCURI PL/SQL, CURSOARE, FUNCTII DE DATA, FUNCTII DE CONVERSIE, FUNCTII OLAP ETC.

1) Sa se seteze in tabelul Comanda data comenzii descrescator (de la data curenta - a fost setata ca fiind 05 mai) incepand cu ultima comanda, data de livrare ca fiind in prima vineri daca comanda este facuta in zilele luni si marti, sambata daca e facuta miercuri, sau peste o saptamana, tot vineri, daca comanda este facuta joi si vineri. Comenzile facute sambata si duminica vor avea ziua de livrare lunea, peste o saptamana

Data platii va fi ultima zi din luna (sau in prima zi lucratoare a urmatoarei luni daca ultima zi este sambata sau duminica) daca comanda este facuta pana pe 15 inclusiv, sau pana pe data de 15 a urmatoarei luni (sau in prima zi lucratoare dupa data de 15 daca aceasta este sambata sau duminica) daca comanda este facuta in intervalul 16 - sfarsitul lunii curente.

Se vor crea trei tabele in care se vor grupa comenzile in functie de ziua_livrarii (vineri, sambata si luni);

Pentru a putea realiza aceasta cerere multipla vom realiza un bloc PL/SQL anonim. Inainte vom introduce in schema de relatie a tabelului Comanda doua atribute suplimentare - ziua_comenzii si ziua livrarii:

ALTER TABLE Comanda

ADD ziua_comenzii CHAR(10);

ALTER TABLE Comanda

ADD ziua_livrarii CHAR(10);

SET SERVEROUTPUT ON

DECLARE

i NUMBER;

a NUMBER;

z NUMBER;

m NUMBER;

ziua CHAR(10);

data CHAR(10);

BEGIN

SELECT (MAX(nr_comanda)-MIN(nr_comanda)+1) INTO i

FROM Comanda

a:=i;

LOOP

ziua:=LOWER(TO_CHAR(SYSDATE-(a-i),'DAY'));

CASE

WHEN ziua IN ('monday','tuesday','friday') THEN

UPDATE Comanda

SET data_comanda=SYSDATE-(a-i), data_livrare=NEXT_DAY(SYSDATE- (a-i),6), ziua_comenzii = ziua , ziua_livrarii = LOWER(TO_CHAR(NEXT_DAY(SYSDATE- (a-i),6),'DAY'))

WHERE nr_comanda=(SELECT MAX(nr_comanda)-(a-i) FROM Comanda);

WHEN ziua='wednesday' THEN

UPDATE Comanda

SET data_comanda=SYSDATE-(a-i), data_livrare=NEXT_DAY(SYSDATE-(a-i),7), ziua_comenzii = ziua , ziua_livrarii = LOWER(TO_CHAR(NEXT_DAY(SYSDATE-(a-i),7),'DAY'))

WHERE nr_comanda=(SELECT MAX(nr_comanda)-(a-i) FROM Comanda);

WHEN ziua='thursday' THEN

UPDATE Comanda

SET data_comanda=SYSDATE-(a-i), data_livrare=NEXT_DAY(SYSDATE-(a-i)+7,6), ziua_comenzii = ziua, ziua_livrarii = LOWER(TO_CHAR( NEXT_DAY(SYSDATE-(a-i)+7,6) ,'DAY'))

WHERE nr_comanda=(SELECT MAX(nr_comanda)-(a-i) FROM Comanda);

WHEN ziua IN ('saturday','sunday') THEN

UPDATE Comanda

SET data_comanda=SYSDATE-(a-i), data_livrare=NEXT_DAY(SYSDATE-(a-i)+7,2),ziua_comenzii=ziua , ziua_livrarii = LOWER(TO_CHAR(NEXT_DAY(SYSDATE-(a-i)+7,2),'DAY'))

WHERE nr_comanda=(SELECT MAX(nr_comanda)-(a-i) FROM Comanda);

ELSE DBMS_OUTPUT.PUT_LINE(ziua);

END CASE

i:=i-1;

EXIT WHEN i=0;

END LOOP

SELECT (MAX(nr_comanda)-MIN(nr_comanda)+1) INTO i

FROM Comanda;

a:=i;

LOOP

z:=TO_NUMBER(TO_CHAR(SYSDATE-(a-i),'DD'));

m:=TO_NUMBER(TO_CHAR(SYSDATE-(a-i),'MM'));

CASE

WHEN z<=15 THEN

ziua:=LOWER(TO_CHAR(LAST_DAY(SYSDATE-(a-i)),'DAY'));

IF ziua IN ('saturday','sunday') THEN

UPDATE Comanda

SET data_platii=NEXT_DAY(LAST_DAY(data_comanda),2)

WHERE nr_comanda=(SELECT MAX(nr_comanda)-(a-i) FROM    Comanda);

ELSE

UPDATE Comanda

SET data_platii=LAST_DAY(data_comanda)

WHERE nr_comanda=(SELECT MAX(nr_comanda)-(a-i) FROM Comanda);

END IF

WHEN z>15 THEN

m:=m+1;

data:='15/'||TO_CHAR(m)||'/2008';

ziua:=LOWER(TO_CHAR(TO_DATE(data,'DD/MM/YYYY'),'DAY'));

IF ziua IN ('saturday','sunday') THEN

UPDATE Comanda

SET data_platii=NEXT_DAY(TO_DATE(data,'DD/MM/YYYY'),2)

WHERE nr_comanda=(SELECT MAX(nr_comanda)-(a-i) FROM Comanda);

ELSE

UPDATE Comanda

SET data_platii=TO_DATE(data,'DD/MM/YYYY')

WHERE nr_comanda=(SELECT MAX(nr_comanda)-(a-i) FROM Comanda);

END IF;

END CASE

i:=i-1;

EXIT WHEN i=0;

END LOOP;

UPDATE Comanda

SET ziua_comenzii='luni'

WHERE ziua_comenzii='monday';

UPDATE Comanda

SET ziua_comenzii='marti'

WHERE ziua_comenzii='tuesday';

UPDATE Comanda

SET ziua_comenzii='miercuri'

WHERE ziua_comenzii='wednesday';

UPDATE Comanda

SET ziua_comenzii='joi'

WHERE ziua_comenzii='thursday';

UPDATE Comanda

SET ziua_comenzii='vineri'

WHERE ziua_comenzii='friday';

UPDATE Comanda

SET ziua_comenzii='sambata'

WHERE ziua_comenzii='saturday';

UPDATE Comanda

SET ziua_comenzii='duminica'

WHERE ziua_comenzii='sunday';

UPDATE Comanda

SET ziua_livrarii='luni'

WHERE ziua_livrarii='monday';

UPDATE Comanda

SET ziua_livrarii='vineri'

WHERE ziua_livrarii='friday';

UPDATE Comanda

SET ziua_livrarii='sambata'

WHERE ziua_livrarii='saturday';

END;

PL/SQL procedure successfully completed.

DROP TABLE Livrare_vineri;

CREATE TABLE Livrare_vineri

AS

SELECT nr_comanda, data_comanda, ziua_comenzii, data_livrare, ziua_livrarii, data_platii

FROM comanda

WHERE ziua_livrarii='vineri';

DROP TABLE Livrare_luni;

CREATE TABLE Livrare_luni

AS

SELECT nr_comanda, data_comanda, ziua_comenzii,data_livrare, ziua_livrarii, data_platii

FROM comanda

WHERE ziua_livrarii='luni';

DROP TABLE Livrare_sambata;

CREATE TABLE Livrare_sambata

AS

SELECT nr_comanda, data_comanda, ziua_comenzii,data_livrare, ziua_livrarii, data_platii

FROM comanda

WHERE ziua_livrarii='sambata';

Livrare_luni

Livrare_vineri

Livrare_sambata

Se observa folosirea pentru operatiile cu date calendaristice a functiilor disponibile in Oracle: LAST-DAY(data) care intoarce ultima zi din luna in care se afla data-argument, NEXT-DAY(data, zi) care furnizeaza data primei zile (luni, marti, etc.) ce urmeaza datei argument si ADD-MONTHS(data, nr.) care aduna "nr." luni la data argument.

De asemenea o importanta deosebita in manipularea datelor o au functiile de conversie intre tipurile majore de date (numere, siruri de caractere si date calendaristice):

TO_CHAR, TO_DATE si TO_NUMBER.

2) La cati clienti, pe cate comenzi si in valoare de cati RON a comercializat marfa furnizorul X ? Pentru aceasta interogare complexa vom folosi un bloc anonim PL/SQL, dar mai intai, pentru a evita anumite neplaceri vom transforma in tabelul Furnizor, in coloana nume_furnizor, toate literele in litere mari cu ajutorul functiei UPPER():

UPDATE furnizor

SET nume_furnizor = UPPER(nume_furnizor);

SELECT nume_furnizor FROM furnizor;

SET SERVEROUTPUT ON

VARIABLE v CHAR(15);

ACCEPT nr_com PROMPT 'Introduceti numele furnizorului'

DECLARE

nr_com CHAR(15):=&nr_com;

BEGIN

:v:=UPPER(nr_com);

DECLARE

CURSOR dep_cursor1 IS

SELECT DISTINCT a.nr_comanda

FROM articole_comenzi a, stoc_produse p

WHERE a.cod_produs=p.cod_produs AND a.cod_furnizor IN

(SELECT f.cod_furnizor

FROM furnizor f, stoc_produse p1

WHERE f.cod_furnizor=p1.cod_furnizor AND nume_furnizor=:v)

ORDER BY nr_comanda;

CURSOR dep_cursor2 IS

SELECT DISTINCT cod_client

FROM articole_comenzi a, comanda c

WHERE a.nr_comanda=c.nr_comanda

AND cod_furnizor =(SELECT cod_furnizor

FROM furnizor

WHERE nume_furnizor=:v)

ORDER BY cod_client;

dep_rec1 dep_cursor1%rowtype;

dep_rec2 dep_cursor2%rowtype;

numec CHAR(20);

val NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE( :v ||' a livrat marfa pe COMENZILE');

IF NOT dep_cursor1%isopen THEN

OPEN dep_cursor1;

END IF

LOOP

FETCH dep_cursor1 INTO dep_rec1;

EXIT WHEN dep_cursor1%notfound OR dep_cursor1%rowcount>10;

DBMS_OUTPUT.PUT_LINE (dep_rec1.nr_comanda);

END LOOP

DBMS_OUTPUT.PUT_LINE('Adica pe un numar de '||dep_cursor1%rowcount||' comenzi');

CLOSE dep_cursor1;

DBMS_OUTPUT.PUT_LINE( ' LA CLIENTII ');

IF NOT dep_cursor2%isopen THEN

OPEN dep_cursor2;

END IF

LOOP

FETCH dep_cursor2 INTO dep_rec2;

EXIT WHEN dep_cursor2%notfound OR dep_cursor2%rowcount>10;

SELECT firma INTO numec

FROM client WHERE cod_client=dep_rec2.cod_client;

DBMS_OUTPUT.PUT_LINE (numec);

END LOOP

DBMS_OUTPUT.PUT_LINE('Adica la un numar de '||dep_cursor2%rowcount|| ' clienti');

CLOSE dep_cursor2;

SELECT SUM(tva+val_fara_tva) INTO val

FROM articole_comenzi

WHERE cod_furnizor = (SELECT cod_furnizor

FROM furnizor

WHERE nume_furnizor=:v);

DBMS_OUTPUT.PUT_LINE('Valoarea marfurilor comercializate catre acestia fiind de '||val||' RON');

END

END

Dupa lansarea in executie a blocului PL/SQL de mai sus si introducerea de la tastatura a furnizorului dorit (indiferent de formatul upper sau lower al caracterului), vom obtine:

old 2: nr_com CHAR(15):=&nr_com;
new 2: nr_com CHAR(15):='shell oil';
SHELL OIL a livrat marfa pe COMENZILE
10504
10505
10511
10513
10516
10517
Adica pe un numar de 6 comenzi
LA CLIENTII
Henkel Romania
Faur S.A.
Air France
Dacia S.A.
Bush investment inc.
Adica la un numar de 5 clienti
Valoarea marfurilor comercializate catre acestia fiind de 1115962.75 RON
PL/SQL procedure successfully completed.

Se observa folosirea variabilei v, prefixata cu ":". Aceasta este o variabila de tip host (a aplicatiilor gazda) - BIND VARIABLES cu urmatoarele proprietati:

- Sunt variabile de legatura cu aplicatia in care ruleaza motorul PL/SQL. Trebuie declarate in aplicatie si pot fi accesate si modificate in blocuri.

-Se declara in afara blocului PL/SQL cu ajutorul cuvantului VARIABLE

-Se afiseaza in afara blocului cu comanda PRINT

-Pentru utilizarea lor in cadrul blocului sau intr-o fraza SQL din afara blocului se prefixeaza cu ":".

3) Sa se determine cei mai vanduti trei combustibili dintre produsele comercializate si, evident, cei mai slab tranzactionati combustibili.

Fiind, de asemenea, o solicitare complexa vom folosi un bloc PL/SQL anonim:

SET SERVEROUTPUT ON

DECLARE

CURSOR dep_cursor3 IS

SELECT denumire, SUM(cantitate) AS volum_litri, COUNT(*) AS nr_comenzi, ROUND(SUM(cantitate*pret_unitar)) AS valoare_RON

FROM Articole_comenzi a, Stoc_produse p

WHERE (a.cod_produs=p.cod_produs) AND ((denumire LIKE '%otorina%') OR (denumire LIKE '%enzina%')) AND u_m='Litri'

GROUP BY denumire

ORDER BY volum_litri DESC;

CURSOR dep_cursor4 IS

SELECT denumire, SUM(cantitate) AS volum_litri, COUNT(*) AS nr_comenzi, ROUND(SUM(cantitate*pret_unitar)) AS valoare_RON

FROM articole_comenzi a, stoc_produse p

WHERE (a.cod_produs=p.cod_produs) AND ((denumire LIKE '%otorina%') OR (denumire LIKE '%enzina%')) AND u_m='Litri'

GROUP BY denumire

ORDER BY volum_litri ASC;

dep_rec3 dep_cursor3%rowtype;

dep_rec4 dep_cursor4%rowtype;

BEGIN

DBMS_OUTPUT.PUT_LINE( 'Cei mai vanduti 3 combustibili sunt');

IF NOT dep_cursor3%isopen THEN

OPEN dep_cursor3;

END IF;

LOOP

FETCH dep_cursor3 INTO dep_rec3;

EXIT WHEN dep_cursor3%notfound OR dep_cursor3%rowcount>3;

DBMS_OUTPUT.PUT_LINE('Locul' ||dep_cursor3%rowcount||' '||dep_rec3.denumire ||LPAD(dep_rec3.volum_litri,20,'.') ||' litri vanduti pe '|| dep_rec3.nr_comenzi || ' comenzi in valoare de '||dep_rec3.valoare_RON||' RON');

END LOOP;

CLOSE dep_cursor3;

DBMS_OUTPUT.PUT_LINE(LPAD('=',100,'='));

DBMS_OUTPUT.PUT_LINE( 'Iar cei mai putin vanduti 3 combustibili sunt:');

IF NOT dep_cursor4%isopen THEN

OPEN dep_cursor4;

END IF;

LOOP

FETCH dep_cursor4 INTO dep_rec4;

EXIT WHEN dep_cursor4%notfound OR dep_cursor4%rowcount>3;

IF dep_cursor4%rowcount=1 THEN

DBMS_OUTPUT.PUT_LINE('Locul' ||dep_cursor4%rowcount||' (din coada!!!) '||dep_rec4.denumire ||LPAD(dep_rec4.volum_litri,20,'.') ||' litri vanduti pe '|| dep_rec4.nr_comenzi || ' comenzi in valoare de '||dep_rec4.valoare_RON||' RON');

ELSE

DBMS_OUTPUT.PUT_LINE('Locul' ||dep_cursor4%rowcount||' '||dep_rec4.denumire ||lpad(dep_rec4.volum_litri,20,'.') ||' litri vanduti pe '|| dep_rec4.nr_comenzi || ' comenzi in valoare de '||dep_rec4.valoare_RON||' RON');

END IF

END LOOP

CLOSE dep_cursor4;

END

Dupa executia acestui bloc PL/SQL anonym vom obtine:

Cei mai vanduti 3 combustibili sunt
Locul1 Benzina fara plumb Mol Dynamic Euro4 .1570 litri vanduti pe 3 comenzi in valoare de 4193700 RON
Locul2 Benzina fara plumb CO95 .1465 litri vanduti pe 4 comenzi in valoare de 3128197 RON
Locul3 Motorina Euro4 .1410 litri vanduti pe 3 comenzi in valoare de 2528437 RON
=========================================================================
Iar cei mai putin vanduti 3 combustibili sunt:
Locul1 (din coada!!!) Benzina V-Power Euro4 ..340 litri vanduti pe 1 comenzi in valoare de 468180 RON
Locul2 Benzina fara plumb OMV Carerra 100 ..400 litri vanduti pe 1 comenzi in valoare de 664000 RON
Locul3 Benzina fara plumb CO98 ..580 litri vanduti pe 2 comenzi in valoare de 677030 RON
PL/SQL procedure successfully completed.

4) Ultima intrebare din acest subcapitol se refera la utilizarea asa-numitelor functii analitice (OLAP- On Line Analytical Processing). Astfel obtinerea unor subtotaluri devine mai facila decat in varianta clasica in care frazele SELECT erau conectate prin operatorul UNION.

Functia GROUPING poate fi inclusa in fraza SELECT, argumentul acestei functii fiind coloana da grupare. Ea returneaza 1 atunci cand coloana respectiva este inclusa intr-un bloc de agregare superior sau 0 pentru linii "normale" (din afara subtotalurilor).

Functia CUBE permite ca datele sa fie agregate simultan dupa doi, trei, etc. parametri.

Atunci sa se afiseze clientii si valoare comenzilor acestora pe fiecare luna, subtotal pentru fiecare luna, subtotal pentru fiecare client si toate lunile si subtotal pentru toti clientii si toate lunile (total general):

SELECT

EXTRACT (MONTH FROM data_comanda) AS luna,

CASE GROUPING(nume)

WHEN 1

THEN

CASE GROUPING (EXTRACT (MONTH FROM data_comanda))

WHEN 1

THEN ' TOTAL GENERAL = '

ELSE chr(123)||'----- ----- ---------------Total achizitii pentru luna----- ----- ---------'||chr(125)||' '|| EXTRACT (MONTH FROM data_comanda)

END

ELSE

CASE GROUPING (EXTRACT (MONTH FROM data_comanda))

WHEN 1

THEN ' Total achizitii pentru clientul ----->'|| nume

ELSE nume

END

END AS nume_client,

SUM(val_totala) AS valoare

FROM Comanda co, Client c

WHERE co.cod_client=c.cod_client

GROUP BY CUBE(EXTRACT (MONTH FROM data_comanda), nume)

ORDER BY EXTRACT (MONTH FROM data_comanda), nume

Functii analiticeOLAP (On Line Analytical Processing)

7. BLOCURI PL/SQL DENUMITE -PROCEDURI, FUNCTII, TRIGGERI

Spre deosebire de blocurile anonime blocurile numite (sau denumite) prezinta o sectiune suplimentara - antetul (in care se specifica numele, parametrii si alte clauze specifice) si care au urmatoarele proprietati:

-pot fi proceduri /functii;

-se pot stoca la nivel de Oracle Server (proceduri/functii stocate) sau de aplicatie (Developer Suite);

-se pot grupa in pachete de programe (PACKAGE)

-variabilele declarate in zona declarativa a procedurii se numesc FORMAL PARAMETERS. Pentru acestia se pot specifica valori implicite (DEFAULT)

-variabilele utilizate in apelul procedurii/functiei se numesc ACTUAL PARAMETERS

-cand procedura/functia e apelata, variabilele din procedura sunt iincarcate cu valorile variabilelor definite in zona declarativa a blocului anonym;

-in corpul procedurilor/functiilor nu se pot utiliza variabile globale sau de substitutie, acestea vor fi transmise in subprograme cu ajutorul parametrilor;

-pentru afisarea erorilor aparute la compilare se utilizeaza SHOW ERRORS

Parametrii utilizati intr-o procedura (formal parameters-parametri formali) pot fi de tip:

- in (valoarea lui 'actual parameter' este transferata in variabila definita in procedura. Aceasta variabila este considerata read-only). Cand procedura se incheie, controlul revine mediului apelant, actual parameter nu se modifica. Este modul implicit. Un astfel de parametru poate apare doar in partea dreapta a (:=)!

- out (valoarea lui "formal parameter" este transferata in "actual parameter" cand procedura se incheie). Un astfel de parametru poate apare doar in partea stanga a (:=)!

- in out (valorile sunt transferate de la un tip de variabila la celalalt (la lansarea in executie/terminarea procedurii)). Un astfel de parametru poate apare in ambele parti ale (:=)!

1) Sa se creeze o procedura, numita "calcul furnizor", prin care, cu ajutorul a doi parametri de intrare - numele unui furnizor X si numarul unei comenzi Y, sa se determine valoarea produselor furnizate de X pe comanda Y, evident daca acest lucru exista.

Pentru a testa daca un furnizor X a livrat marfa pe o anumita comanda Y se va crea functia check_furnizor care returneaza TRUE in cazul in care se regasesc produse ale lui X pe comanda Y si FALSE in caz contrar:

CREATE OR REPLACE FUNCTION check_furnizor(nf IN furnizor.nume_furnizor%type, c IN comanda.nr_comanda%type)

RETURN Boolean

IS

v NUMBER;

BEGIN

v:=0;

SELECT SUM(cantitate*pret_unitar) INTO v

FROM stoc_produse s, articole_comenzi a

WHERE s.cod_produs=a.cod_produs AND nr_comanda=c AND s.cod_furnizor = (SELECT cod_furnizor FROM furnizor f WHERE nume_furnizor=nf);

IF v<>0 THEN

RETURN TRUE;

ELSE

RETURN FALSE;

END IF;

END;

CREATE OR REPLACE PROCEDURE calcul_furnizor(ff IN Furnizor.nume_furnizor%type,nc IN Comanda.nr_comanda%type, val OUT number)

IS

BEGIN

IF (check_furnizor(ff,nc)) THEN

SELECT SUM(pret_unitar*cantitate) INTO val FROM Articole_comenzi a, Stoc_produse p WHERE a.cod_produs=p.cod_produs AND nr_comanda=nc

AND p.cod_furnizor IN (SELECT p1.cod_furnizor FROM Stoc_produse p1, furnizor f WHERE p1.cod_furnizor=f.cod_furnizor AND nume_furnizor= ff);

DBMS_OUTPUT.PUT_LINE('Furnizorul '||ff||' a livrat marfa pe comanda '||nc||' in valoare de');

ELSE

DBMS_OUTPUT.PUT_LINE('Furnizorul '||ff||' nu a livrat marfa pe comanda '||nc);

END IF;

END;

Pentru lansarea in executie a procedurii folosim urmatorul bloc PL/SQL:

SET SERVEROUTPUT ON

DECLARE

n NUMBER;

f Furnizor.nume_furnizor%type;

c Comanda.nr_comanda%type;

BEGIN

calcul_furnizor(UPPER(&f),&c, n);

DBMS_OUTPUT.PUT_LINE(n);

END;

old 6: calcul_furnizor(UPPER(&f),&c, n);
new 6: calcul_furnizor(UPPER('mol hungary'),10515, n);
Furnizorul MOL HUNGARY a livrat marfa pe comanda 10515 in valoare de
1135
PL/SQL procedure successfully completed.

old 6: calcul_furnizor(UPPER(&f),&c, n);
new 6: calcul_furnizor(UPPER('mol hungary'),10501, n);
Furnizorul MOL HUNGARY nu a livrat marfa pe comanda 10501
PL/SQL procedure successfully completed.

2) Sa se creeze o procedura calcul_furnizor_total, care cu ajutorul numelui unui furnizor ca si parametru de intrare sa calculeze numarul de unitati vandute de acesta si valoarea totala .

CREATE OR REPLACE PROCEDURE calcul_furnizor_total(ff IN Furnizor.nume_furnizor%type, val1 OUT NUMBER, val2 OUT NUMBER)

IS

BEGIN

SELECT SUM(cantitate) ,SUM(pret_unitar*cantitate) into val1,val2

FROM Articole_comenzi a, Stoc_produse p

WHERE a.cod_produs=p.cod_produs AND p.cod_furnizor IN

(SELECT p1.cod_furnizor

FROM Stoc_produse p1, Furnizor f

WHERE p1.cod_furnizor=f.cod_furnizor AND nume_furnizor=ff);

END;

Procedura va fi apelata in doua blocuri PL/SQL:

a)

SET SERVEROUTPUT ON

DECLARE

v1 NUMBER;

v2 NUMBER;

CURSOR dep_cursor5 IS

SELECT DISTINCT nume_furnizor

FROM furnizor;

dep_rec5 dep_cursor5%rowtype;

BEGIN

IF NOT dep_cursor5%isopen THEN

OPEN dep_cursor5;

END IF;

LOOP

FETCH dep_cursor5 INTO dep_rec5;

EXIT WHEN dep_cursor5%notfound OR dep_cursor5%rowcount>10;

calcul_furnizor_total(dep_rec5.nume_furnizor, v1,v2);

DBMS_OUTPUT.PUT_LINE ('Furnizorul '||dep_rec5.nume_furnizor||' ---> cantitate totala '||v1||' unitati--->valoare_totala '||v2|| ' RON');

END LOOP;

CLOSE dep_cursor5;

END;

Furnizorul PETROM S.A. ---> cantitate totala 848 unitati--->valoare_totala 5903.25 RON
Furnizorul OMV ---> cantitate totala 1908 unitati--->valoare_totala 9380.4 RON
Furnizorul GENERAL MOTORS ---> cantitate totala 41 unitati--->valoare_totala 7835 RON
Furnizorul LUK OIL ---> cantitate totala 1643 unitati--->valoare_totala 12675.75 RON
Furnizorul SHELL OIL ---> cantitate totala 717 unitati--->valoare_totala 6948.3 RON
Furnizorul MOL HUNGARY ---> cantitate totala 2230 unitati--->valoare_totala 10532.4 RON
PL/SQL procedure successfully completed.

b)

SET SERVEROUTPUT ON

DECLARE

cant NUMBER:=0;

v NUMBER:=0;

v1 NUMBER;

v2 NUMBER;

CURSOR dep_cursor5 IS

SELECT DISTINCT nume_furnizor

FROM furnizor;

dep_rec5 dep_cursor5%rowtype;

BEGIN

IF NOT dep_cursor5%isopen THEN

OPEN dep_cursor5;

END IF;

LOOP

FETCH dep_cursor5 INTO dep_rec5;

EXIT WHEN dep_cursor5%notfound OR dep_cursor5%rowcount>10;

calcul_furnizor_total(dep_rec5.nume_furnizor, v1,v2);

cant:=cant+v1;

v:=v+v2;

END LOOP;

CLOSE dep_cursor5;

DBMS_OUTPUT.PUT_LINE ('Furnizorii au livrat '||cant ||' unitati in valoare de '||v||' RON');

END;

Furnizorii au livrat 7387 unitati in valoare de 53275.1 RON
PL/SQL procedure successfully completed.

3) Sa se creeze o procedura prin care sa se actualizeze cantitatea disponibila in tabela Stoc_produse, in urma efectuarii comenzilor. In caz ca socul de marfa este insuficient onorarii comenzilor sa se afiseze un mesaj de eroare.

Inainte de descrierea procedurii propriu-zise vom crea functia find_indice(nr_comanda), care returneaza numarul de linii al fiecarei comenzi:

CREATE OR REPLACE FUNCTION find_indice(n_com articole_comenzi.nr_comanda%type)

RETURN NUMBER

IS

ind NUMBER;

BEGIN

SELECT MAX(indice_comanda) INTO ind FROM Articole_comenzi WHERE nr_comanda = n_com;

RETURN ind;

END

CREATE OR REPLACE PROCEDURE ACTUALIZARE(com IN Articole_comenzi.nr_comanda%type)

IS

ind Articole_comenzi.indice_comanda%type;

n NUMBER;

n1 NUMBER;

BEGIN

ind:=find_indice(com);

WHILE(ind>0)

LOOP

SELECT cantitate_disponibila INTO n

FROM Stoc_produse s,Articole_comenzi a

WHERE s.cod_produs=a.cod_produs AND a.nr_comanda= com AND a.indice_comanda=ind;

SELECT cantitate INTO n1 FROM Articole_comenzi

WHERE (nr_comanda=com) AND (indice_comanda=ind);

IF n-n1<0 THEN

RAISE_APPLICATION_ERROR (-20500, 'Eroare la actualizarea stocurilor: Stoc insuficient !!! Refaceti comanda!!!');

ELSE

UPDATE stoc_produse

SET cantitate_disponibila = cantitate_disponibila - (SELECT cantitate FROM Articole_comenzi

WHERE (nr_comanda=com) AND (indice_comanda=ind))

WHERE cod_produs=(SELECT cod_produs FROM Articole_comenzi

WHERE(nr_comanda=com)AND(indice_comanda=ind));

ind:=ind-1;

END IF;

END LOOP;

END

Aceasta procedura poate fi folosita pentru fiecare comanda in parte ca in blocul PL/SQL de mai jos:

SET SERVEROUTPUT ON

DECLARE

nr_com Articole_comenzi.nr_comanda%type:=&nr_com;

BEGIN

actualizare(nr_com);

DBMS_OUTPUT.PUT_LINE('S-au actualizat stocurile in tabela stoc_produse in urma comenzii '|| nr_com);

END;

old 2: nr_com Articole_comenzi.nr_comanda%type:=&nr_com;
new 2: nr_com Articole_comenzi.nr_comanda%type:=10512;
S-au actualizat stocurile in tabela stoc_produse in urma comenzii 10512
PL/SQL procedure successfully completed.

SELECT s.cod_produs, denumire, cantitate_disponibila

FROM Stoc_produse s, Articole_comenzi a

WHERE s.cod_produs=a.cod_produs AND nr_comanda= 10512

Initial stocurile fiind urmatoarele:

sau putem aplica procedura de actualizare la toata tabela stoc_produse ca in blocul de mai jos:

SET SERVEROUTPUT ON

DECLARE

CURSOR dep_cursor6 IS

SELECT DISTINCT nr_comanda

FROM Comanda;

dep_rec6 dep_cursor6%rowtype;

BEGIN

IF NOT dep_cursor6%isopen THEN

OPEN dep_cursor6;

END IF;

DBMS_OUTPUT.PUT_LINE ('S-au actualizat comenzile:');

LOOP

FETCH dep_cursor6 INTO dep_rec6;

EXIT WHEN dep_cursor6%notfound;

if dep_recnr_comanda<>10512 THEN

actualizare(dep_recnr_comanda);

DBMS_OUTPUT.PUT_LINE (dep_recnr_comanda);

END IF;

END LOOP;

CLOSE dep_cursor6;

END;

S-au actualizat comenzile:
10501
10502
10503
10504
10505
10506
10507
10508
10509
10510
10511
10513
10514
10515
10516
10517
10518
PL/SQL procedure successfully completed.

S-a introdus optiunea if dep_recnr_comanda<>10512 pentru a nu dubla actualizarea comenzii 10512.

4) Sa se introduca in baza de date o noua comanda.

Desi pare o cerinta simpla trebuie avut in vedere ca o comanda noua produce efecte directe si indirecte in trei tabele: Comanda, Articole_comenzi, Stoc_produse si Apeluri_tel.

Pe langa facila inserare a unor valori pentru anumite atribute pentru altele trebuie facute calcule destul de complicate. Astfel in tabelul Stoc_produse va trebui sa diminuam stocul_disponibil in functie de cantitatile de produse comandate, in tabelul Articole_comenzi trebuie calculate valoarea atributelor valoare_fara_tva, valoare_tva, iar in tabelul Comanda valoarea atributelor tva, valoare fara tva, valoare totala. Mai mult in acest ultim tabel trebuie evaluate, conform cerintelor enuntate la subcapitolul , data livrarii si data_platii, ziua comenzii si ziua_livrarii. La fel, in tabelul Apeluri_tel, fiecare comanda va avea un corespondent, cod_apel fiind '1' pentru comenzi in valoare mai mica de 1000 RON, '2' pentru comenzi cu valoare cuprinsa intre 1000 si 3000 RON si '2' pentru valoare mai mare ca 3000 RON.

Pentru a putea trata toate modificarile prin care trece baza de date in urma efectuarii unei noi comenzi vom folosi mai multe proceduri si triggeri si un bloc PL/SQL anonim. Toata secventa de operatii care se desfasoara asupra datelor din B.D. reprezinta de fapt o tranzactie. Pe scurt pasii sunt:

Pasul I. Se insereaza o noua linie in tabelul Comanda. Inainte de a se permite aceasta operatiune trigger-ul secure-insert (BEFORE INSERT) verifica ora si ziua. In caz ca suntem in afara programului de lucru se semnaleaza eroare. Dupa inregistrarea efectiva a tuplului in tabelul Comanda trigger-ul update_comanda (AFTER INSERT) calculeaza valoarea atributelor data_livrarii, data_platii, ziua_comenzii si ziua_livrarii plecand de la sysdate.

Pasul II. Se insereaza o noua linie in tabelul Articole_comenzi. Dupa aceasta operatie trigger-ul actualizez_stoc (AFTER INSERT) calculeaza atributele tva si val_fara_tva in Articole_comenzi si total_tva, total_val_fara_tva si val_totala in tabelul Comanda. Mai mult, se apeleaza procedura de actualizare a stocurilor (creata la intrebarea de mai sus -3). In caz ca se depaseste stocul disponibil pentru produsul comandat se semnaleaza eroare.

Pasul III. Se insereaza o noua linie in tabelul Apeluri_tel.

Toata aceasta secventa de operatii are sens numai daca se deruleaza in totalitate. Se observa ca exista motive sa apara erori. De exemplu se poate inregistra o comanda in tabelul Comanda, dar in tabelul Articole_comenzi inregistrarea esueaza datorita depasirii stocului_disponibil.

Pentru a evita astfel de neplaceri avem la dispozitie comenzile ROLLBACK care anuleaza modificarile aduse bazei de date de la ultima tranzactie incheiata cu success (sau de la conectarea la serverul bazei de date) si COMMIT prin care modificarile vor deveni permanente in baza de date si, de asemenea, vizibile de catre ceilalti utilizatori.

Daca tranzactia este prea "stufoasa" avem posibilitatea folosirii unor puncte intermediare de salvare a datelor numite SAVEPOINT-uri.

CREATE OR REPLACE PROCEDURE insert_articole1(nr_com IN NUMBER, ind IN NUMBER, cod_p IN NUMBER, cant IN NUMBER)

IS

f NUMBER;

BEGIN

SELECT cod_furnizor INTO f

FROM stoc_produse

WHERE cod_produs=cod_p;

INSERT INTO Articole_comenzi(nr_comanda, indice_comanda, cod_produs, cod_furnizor, cantitate) VALUES(nr_com,ind,cod_p,f,cant);

END;

(la apelarea acestei proceduri se insereaza in tabelul Articole_comenzi un nou tuplu)

CREATE OR REPLACE PROCEDURE insert_comanda(cod_cl IN client.cod_client%type)

IS

BEGIN

INSERT INTO Comanda(nr_comanda, data_comanda, cod_client) VALUES(secv_com.nextval, sysdate, cod_cl);

END;

(la apelarea acestei proceduri se insereaza in tabelul Comanda un nou tuplu)

CREATE OR REPLACE FUNCTION cod n_com NUMBER)

RETURN NUMBER

IS

v NUMBER;

BEGIN

SELECT val_totala INTO v FROM Comanda

WHERE nr_comanda = n_com;

CASE

WHEN v<1000 THEN RETURN 1;

WHEN v>=1000 AND v<3000 THEN RETURN 2;

WHEN v>=3000 THEN RETURN 3;

END CASE;

END;

(aceasta functie returneaza valoarea atributului cod_apel din tabelul Apeluri_tel)

CREATE OR REPLACE PROCEDURE insert_apeluri cod_cl IN client.cod_client%type)

IS

c NUMBER;

nc NUMBER;

BEGIN

SELECT MAX(nr_comanda) INTO nc FROM Comanda;

c:=cod(nc);

EXECUTE IMMEDIATE 'INSERT INTO Apeluri_tel (cod_client,cod_apel) VALUES ( ' ||cod_cl || ',' ||c|| ')';

END;

(la apelarea acestei proceduri se insereaza in tabelul Apeluri_tel un nou tuplu)

Se oserva folosirea in corpul acestei proceduri a comenzii EXECUTE IMMEDIATE, o facilitate introdusa de Oracle 8i. In mod normal, PL/SQL nu permite crearea si stergerea de tabele, definirea de restrictii, in general comenzi DDL(Data Definition Language). Prin folosirea comenzii de mai sus acest lucru devine posibil (SQL Dinamic).

CREATE OR REPLACE

TRIGGER actualizez_stoc AFTER INSERT ON Articole_comenzi

DECLARE

nc NUMBER;

BEGIN

SELECT MAX(nr_comanda) INTO nc

FROM Comanda;

actualizare(nc);

UPDATE Articole_comenzi ac

SET val_fara_tva=cantitate*(SELECT pret_unitar FROM Stoc_produse sp WHERE ac.cod_produs=sp.cod_produs)/1.19

WHERE nr_comanda=nc;

UPDATE Articole_comenzi ac SET

tva = val_fara_tva*0.19

WHERE nr_comanda=nc;

UPDATE Comanda cc SET

val_totala=(SELECT SUM(cantitate*pret_unitar)

FROM Articole_comenzi a, Stoc_produse sp

WHERE (a.cod_produs=sp.cod_produs) AND

nr_comanda=nc)

WHERE nr_comanda=nc;

UPDATE Comanda cc SET

total_TVA=0.19*val_totala/1.19

WHERE nr_comanda=nc;

UPDATE Comanda cc SET

total_val_fara_TVA=val_totala/1.19

WHERE nr_comanda=nc;

END;

(Dupa inserarea unui tuplu in tabelul Articole_comenzi acest declansator calculeaza valoarea atributelor tva si val_fara_tva in tabelul Articole_comenzi si total_tva, total_val_fara_tva si val_totala in tabelul Comanda)

CREATE OR REPLACE

TRIGGER update_comanda AFTER INSERT ON Comanda

DECLARE

nc NUMBER;

z NUMBER;

m NUMBER;

data CHAR(10);

ziua CHAR(10);

BEGIN

ziua:=LOWER(TO_CHAR(SYSDATE,'DAY'));

SELECT MAX(nr_comanda) INTO nc

FROM Comanda;

CASE

WHEN ziua IN ('monday','tuesday','friday') THEN

UPDATE Comanda

SET data_livrare=NEXT_DAY(SYSDATE,6)

WHERE nr_comanda=nc;

WHEN ziua ='wednesday' THEN

UPDATE Comanda

SET data_livrare=NEXT_DAY(SYSDATE,7)

WHERE nr_comanda=nc;

WHEN ziua ='thursday' THEN

UPDATE Comanda

SET data_livrare=NEXT_DAY(SYSDATE+7,6)

WHERE nr_comanda=nc;

WHEN ziua IN ('saturday','sunday') THEN

UPDATE Comanda

SET data_livrare=NEXT_DAY(SYSDATE+7,2)

WHERE nr_comanda=nc;

END CASE;

UPDATE Comanda

SET ziua_comenzii ziua, ziua_livrarii=LOWER(TO_CHAR(data_livrare,'DAY'))

WHERE nr_comanda=nc;

z:=TO_NUMBER(TO_CHAR(SYSDATE,'DD'));

m:=TO_NUMBER(TO_CHAR(SYSDATE,'MM'));

SELECT LOWER(TO_CHAR(LAST_DAY(data_comanda),'DAY'))

INTO ziua

FROM Comanda

WHERE Nr_comanda=nc;

CASE

WHEN z<=15 THEN

IF ziua IN ('saturday','sunday') THEN

UPDATE Comanda

SET data_platii=NEXT_DAY(LAST_DAY(data_comanda),2)

WHERE nr_comanda=nc;

ELSE

UPDATE Comanda

SET data_platii=LAST_DAY(data_comanda)

WHERE nr_comanda=nc;

END IF;

WHEN z>15 THEN

m:=m+1;

data:='15/'||TO_CHAR(m)||'/2008';

IF ziua IN ('saturday','sunday') THEN

UPDATE Comanda

SET data_platii=NEXT_DAY(TO_DATE(data,'DD/MM/YYYY'),2)

WHERE nr_comanda=nc;

ELSE

UPDATE Comanda

SET data_platii=TO_DATE(data,'DD/MM/YYYY')

WHERE nr_comanda=nc;

END IF;

END CASE;

END;

(Dupa inserarea unui tuplu in tabelul Comanda acest declansator completeaza valoarea atributelor data_livrare, data_platii, ziua_comenzii si ziua_livrarii in tabelul in tabelul Comanda)

CREATE OR REPLACE

TRIGGER SECURE_INSERT BEFORE INSERT ON Comanda

BEGIN

IF (TO_CHAR(SYSDATE,'DY') IN ('SAT', 'SUN')) OR (TO_CHAR (SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '16:00') THEN

RAISE_APPLICATION_ERROR (-20500, 'Este permisa inserarea in tabela numai in timpul programului de lucru!');

END IF;

END;

(Acest trigger permite accesul controlat pentru inregistrarea de noi comenzi in baza de date).

SET SERVEROUTPUT ON;

DECLARE

cod_client NUMBER:=&cod_client;

nc NUMBER;

cod_produs NUMBER;

cantitate NUMBER;

BEGIN

insert_comanda(cod_client);

SELECT MAX(nr_comanda) INTO nc

FROM Comanda

cod_produs:=&cod_produs;

cantitate:=&cantitate;

insert_articole1(nc,1,cod_produs,cantitate);

Insert_apeluri(cod_client);

END;

ROLLBACK/COMMIT;

Acest bloc PL/SQL apeleaza procedurile, trigger-ii si functiile de mai sus. In caz de esec (aparitia unor eventuale erori generate pe parcursul desfasurarii tranzactiei) vom incheia blocul PL/SQL cu ROLLBACK, in caz contrar cu COMMIT.

A. eroare generata de incercarea de lansare a unei noi comenzi in afara programului de lucru!(se foloseste ROLLBACK);

old 11: cantitate:=&cantitate;
new 11: cantitate:=20;

DECLARE

ERROR at line 1:
ORA-20500: Este permisa inserarea in tabela numai in timpul programului de lucru!
ORA-06512: at 'SYSMAN.SECURE_INSERT', line 3
ORA-04088: error during execution of trigger 'SYSMAN.SECURE_INSERT'
ORA-06512: at 'SYSMAN.INSERT_COMANDA', line 4
ORA-06512: at line 7

B. eroare generata de incercarea de lansare a unei noi comenzi care depaseste stocul de marfa disponibil! (se foloseste ROLLBACK);

old 11: cantitate:=&cantitate;
new 11: cantitate:=300;

DECLARE

ERROR at line 1:
ORA-20500: Eroare la actualizarea stocurilor: Stoc insuficient !!! Refaceti comanda!!!
ORA-06512: at 'SYSMAN.ACTUALIZARE', line 16
ORA-06512: at 'SYSMAN.ACTUALIZEZ_STOC', line 6
ORA-04088: error during execution of trigger 'SYSMAN.ACTUALIZEZ_STOC'
ORA-06512: at 'SYSMAN.INSERT_ARTICOLE1', line 8
ORA-06512: at line 12

C. Comanda reusita (se foloseste COMMIT);

old 11: cantitate:=&cantitate;
new 11: cantitate:=35;
PL/SQL procedure successfully completed.

8. TIPUL DE DATE LOB

Oracle ofera facilitati pentru manipularea fisierelor in baza de date cu ajutorul atributelor de tip LOB (Large Objects). Dupa cum s-a subliniat si in cap. 5 in functie de zona de pastrare a informatiilor se disting doua categorii de atribute LOB:

- cu stocare interna: Fisierul este stocat chiar in interiorul bazei de date fiind independent de modificarile efectuate in fisierul sursa pe disc. Astfel avem date BLOB (BINARY LOB- date nestructurate) si CLOB (CHARACTER LOB - date sub forma de siruri de caractere).

- cu stocare externa : Fisierul este stocat pe disc, in baza de date fiind inregistrata doar adresa fisierului respectiv. In aceasta categorie intra tipul BFILE.

Plecand de la aceste considerente sa se introduca in tabelul Catalog pentru produsul "Odorizant auto Car fresh" (cod 100014, furnizor 1002) o poza (*. Jpeg) si un film continand reclama (*.avi).

Valoarea unui atribut BLOB este formata din doua parti:

- valoarea LOB propriu-zisa, ce contine sirul de biti din care este formata imaginea

- pointerul LOB (adresa) care indica localizarea fizica in baza de date a valorii LOB de mai sus.

Astfel pentru inserarea unei valori BLOB intr-un tabel vom crea mai intai pointerul LOB (folosind functia Oracle empty_blob()), dupa care vom incarca valoarea LOB. Trebuie precizat ca serverul Oracle nu acceseaza direct fisierele de pe disc, ci trebuie creat obiectul DIRECTORY, corespunzator directorului in care se gasesc fisierele.

CREATE OR REPLACE DIRECTORY MEDIA

AS 'G:IMG';

Directory created.

Dupa care urmam cei doi pasi descrisi mai sus:

6       se inesereaza un nou tuplu in tabelul Catalog, initializand adresele pentru atributele BLOB cu functia empty_blob();

7      

INSERT INTO Catalog(Cod_produs, Cod_furnizor, foto, reclama)

VALUES(100014,1002,empty_blob(), empty_blob());

1 row created.

construim un bloc PL/SQL in care:

-se extrag adresele atributelor foto si reclama si se blocheaza inregistrarea cu nu Select For Update;

-se initializeaza doua variabile de tip BFILE cu adresa fizica de pe disc a fisierelor pe care vrem sa le incarcam.

-se incarca continutul fisierelor in baza de date la adresele specificate la primul pas:

Pentru a ne asigura de existenta fisierelor in locatia respectiva, pe disc, utilizam functia DBMS_LOB.FILEEXISTS(adresa BFILE) care returneaza 1 daca exista fisier la adresa respectiva si 0 in caz contrar.

DECLARE

f1 BFILE;

f2 BFILE;

b1 BLOB;

b2 BLOB;

BEGIN

SELECT foto, reclama INTO b1,b2 FROM Catalog WHERE serie_catalog=5001 FOR UPDATE;

f1:=BFILENAME('MEDIA','IM000642.JPG');

f2:=BFILENAME('MEDIA','IM00013MPG');

IF DBMS_LOB.FILEEXISTS(f1)=0 OR DBMS_LOB.FILEEXISTS(f2)=0

THEN

RAISE_APPLICATION_ERROR (-20500, 'Eroare la deschiderea fisierelor');

ELSE

DBMS_LOB.FILEOPEN(f1, DBMS_LOB.FILE_READONLY);

DBMS_LOB.LOADFROMFILE(b1,f1,DBMS_LOB.GETLENGTH(f1));

DBMS_LOB.FILEOPEN(f2, DBMS_LOB.FILE_READONLY);

DBMS_LOB.LOADFROMFILE(b2,f2,DBMS_LOB.GETLENGTH(f2));

DBMS_LOB.FILECLOSE(f1);

DBMS_LOB.FILECLOSE(f2);

COMMIT

END IF;

END;

PL/SQL procedure successfully completed.

Mai repetam pasii de mai sus pentru produsul "Solutie parbriz Winter Glass-30", cod 100021, furnizor 1003.

SELECT Serie_catalog, DBMS_LOB.GETLENGTH(foto) SIZE_FOTO, DBMS_LOB.GETLENGTH(reclama) SIZE_RECLAMA

FROM Catalog

9. MANIPULAREA DATELOR ORACLE IN APLICATIILE JAVA

Pentru a realiza conectarea si manipularea datelor unei baze de date o aplicatie Java utilizeaza urmatoarele clase JDBC impreuna cu derivatele lor: Connection, Statement si ResultSet. Se disting urmatoarele etape:

-inregistrarea driverului JDBC;

-crearea unei conexiuni cu BD cu ajutorul unui obiect de tip Connection;

-crearea unui obiect Statement pe baza obiectului Connection, creat mai sus;

-executia unei fraze SQL prin intremediul obiectului Statement, daca e cazul preluarea rezultatului returnat de Server sub forma unui obiect ResultSet;

-validarea actualizarilor (COMMIT) sau invalidarea (ROLLBACK);

-inchiderea conexiunii;

O aplicatie poate initializa si utiliza mai multe obiecte de tip Connection, fiecare comunicand cu serverul ORACLE. Obiectele de tip Connection sunt unitati indivizibile de tranzactie, commiterea realizandu-se la nivelul acestuia.

Pe baza unui obiect Connection se pot construi mai multe obiecte de tip Statement si implicit mai multe seturi de date ResultSet.

Aceste etape sunt urmate in pachetul de clase JAVA din Anexa I. Ele constituie o interfata grafica prin care putem accesa baza de date orcl si tabelele create de noi pe parcursul acestei lucari. Aplicatia deschide o singura conexiune cu baza de date Oracle in clasa principala Start.java.

Manipularea datelor (fraze SELECT, etc.) este asigurata de primul buton din cele patru (Listati .), care utilizeaza metoda executeQuery() a clasei Statement, direct in clasa principala Start.java.

Al doilea buton (Insert produs nou) permite introducerea in tabelul Stoc_Produse a unui produs nou folosind metoda executeUpdate() a clasei Statement intr-o instanta a clasei InsertProdus.java.

Al treilea buton (Insert comanda noua) asigura partea cea mai complexa si anume realizarea unei tranzactii (lnsarea unei comenzi noi, cu efect in tabelele Comanda, Articole_comenzi, Stoc_produse si Apeluri_tel.). Tranzactia se executa prin apelarea de catre aplicatia Java a procedurilor stocate pe serverul ORACLE, folosind clasa CallableStatement (permite transmiterea unor parametri si prelucrarea rezultatelor returnate de procedura sau functia executata) din pachetul java.sql intr-o instanta a clasei InsertComanda.java.

Mai trebuie precizat ca in mod implicit orice actualizare a bazei de date prin intermediul unei intrefete JAVA este definitiva!(COMMIT). Pentru a putea controla acest aspect dezactivam aceasta proprietate cu ajutorul metodei setAutoCommit(false) a clasei Connection la inceputul tranzactiei, iar la sfarsit folosim metodele Rollback() sau Commit() ale aceleiasi clase pentru anularea, respective salvarea tranzactiei. Reactivarea modului de comitere automata poate fi facuta in orice moment prin setAutoCommit(true).

De mentionat ca pentru manipularea datelor BLOB din tabelul Catalog folosirea puntii jdbc:odbc pentru conectarea la baza de date nu mai functioneaza. Continutul atributelor de tip BLOB implica clase si metode ce gestioneaza fluxuri de octeti (InputStream si OutputStream). Atunci este necesara folosirea driverului JDBC Oracle ojdbc14.jar (descarcat de pe site-ul Oracle) si copierea intr-o locatie disponibila mediului run-time Java. Inregistrarea driverului se face cu ajutorul metodei statice registerDriver() definite in clasa DriverManager. Crearea unui obiect Connection se face cu ajutorul metodei statice getConection() a aceleiasi clase DriverManager, informatiile pentru conectare fiind trei parametri de tip sir de caractere:

-jdbc:oracle:thin:@<adresa IP a calculatorului pe care ruleaza serverul Oracle>:<portul de comunicare cu serverul, default 1521>:<numele instantei active a serverului Oracle>;

-user - numele de utilizator Oracle;

-pass - parola.

Al patrulea buton - "Media visualization" (clasa Media.java)- acceseaza datele de tip large object (BLOB) aflate in tabelul Catalog prin deschiderea unui flux.

Un flux este un canal de comunicatie unidirectional intre doua procese. Un flux ce citeste date se numeste flux de intrare. Un flux ce scrie date se numeste flux de iesire. Fluxurile sunt canale de comunicatie seriale pe 8 sau 16 biti. Fluxurile sunt unidirectionale, de la producator la consumator. Fiecare flux are un singur proces producator si un singur proces consumator.

Clasele radacina pentru ierarhiile ce reprezinta fluxuri de caractere sunt:

- Reader - pentru fluxuri de intrare

- Writer - pentru fluxuri de iesire.

Acestea sunt superclase abstracte pentru toate clasele ce implementeaza fluxuri specializate pentru citirea/scrierea datelor pe 16 biti si vor contine metodele comune tuturor. Ca o regula generala, toate clasele din aceste ierarhii vor avea terminatia Reader sau Writer in functie de tipul lor, cum ar fi in exemplele: FileReader, BufferedReader, FileWriter, BufferedWriter, etc. De asemenea, se observa ca o alta regula generala, faptul ca unui flux de intrare XReader ii corespunde uzual un flux de iesire XWriter, insa acest lucru nu este obligatoriu. Cu ajutorul acestor superclase se vor manipula atribute de tip CLOB (Character Large Object).

Clasele radacina pentru ierarhia fluxurilor de octeti sunt:

. InputStream - pentru fluxuri de intrare

. OutputStream - pentru fluxuri de iesire.

Acestea sunt superclase abstracte pentru clase ce implementeaza fluxuri specializate pentru citirea/scrierea datelor la nivel de octet (byte), cazul atributelor BLOB (Binary LargeObject). Ca si in cazul fluxurilor pe caractere denumirile claselor vor avea terminatia superclasei lor: FileInputStream, BufferedInputStream, FileOutputStream, BufferedOutputStream, etc., fiecarui flux de intrare XInputStream corespunzandu-i uzual un flux de iesire XOutputStream, fara ca acest lucru sa fie obligatoriu.

Astfel vom folosi clasa InputStream in programul nostru pentru a descaraca din tabelul Catalog al bazei de date Oracle continutul atributului BLOB si scrierea sa intr-un fisier temporar (proiect.tmp) pe disc. Se foloseste extensia tmp pentru ca este posibil sa nu cunoastem tipul fisierului extras. Deschiderea fisierului se face cu browser-ul Internet Explorer care va determina automat aplicatia necesara vizualizarii (Word, Excel, Windows Media Player, etc.).

Mai trebuie mentionat in incheierea acestei succinte prezentari ca metodele definite in clasele din pachetul java.sql pot genera erori la conectare, interogare, etc. care apar doar la momentul executiei si nu la compilare. Ca urmare orice apel al acestor metode impune gestionarea eventualelor erori prin intermediul blocurilor try catch.