Academia de Studii Economice Bucuresti

Facultatea de Cibernetica, Statistica si Informatica Economica

Creare Tabele

PROMPT Creating Table 'CLIENTI'

DROP TABLE CLIENTI CASCADE CONSTRAINTS;

CREATE TABLE CLIENTI

(COD_CL NUMBER(6) NOT NULL,

COD_FISCAL NUMBER(6) NOT NULL,

DENUMIRE VARCHAR2(20) NOT NULL,

ADRESA VARCHAR2(30) NOT NULL,

JUD VARCHAR2(20) NOT NULL,

NR_TEL VARCHAR2(15) NOT NULL,

CONT_BANC VARCHAR2(20) NOT NULL,

PERS_CONT VARCHAR2(20) NOT NULL,

CONSTRAINT CL_PK PRIMARY KEY(COD_CL));

PROMPT Creating Table 'MATERIALE'

DROP TABLE MATERIALE CASCADE CONSTRAINTS;

CREATE TABLE MATERIALE

(COD_MAT NUMBER(6) NOT NULL,

DENUMIRE VARCHAR2(20) NOT NULL,

UM VARCHAR2(10) NOT NULL,

CONSTRAINT M_PK PRIMARY KEY(COD_MAT));

PROMPT Creating Table 'COMENZI'

DROP TABLE COMENZI CASCADE CONSTRAINTS;

CREATE TABLE COMENZI

(COD_COM NUMBER(6) NOT NULL,

COD_CL NUMBER(6) NOT NULL,

DESCRIERE VARCHAR2(1000) NOT NULL,

DATA_PRIM DATE NOT NULL,

DATA_LIVR DATE NOT NULL,

CONSTRAINT COM_PK PRIMARY KEY(COD_COM),

CONSTRAINT COM_FK FOREIGN KEY(COD_CL) REFERENCES CLIENTI(COD_CL));

PROMPT Creating Table 'RAND_COM'

DROP TABLE RAND_COM CASCADE CONSTRAINTS;

CREATE TABLE RAND_COM

(COD_COM NUMBER(6) NOT NULL,

COD_MAT NUMBER(6) NOT NULL,

CONSTRAINT RD_FK FOREIGN KEY(COD_COM) REFERENCES COMENZI(COD_COM),

CONSTRAINT RD_FK1 FOREIGN KEY(COD_MAT) REFERENCES MATERIALE(COD_MAT));

PROMPT Creating Table 'FURNIZORI'

DROP TABLE FURNIZORI CASCADE CONSTRAINTS;

CREATE TABLE FURNIZORI

(COD_FZ NUMBER(6) NOT NULL,

COD_FISC NUMBER(6) NOT NULL,

DENUMIRE VARCHAR2(20) NOT NULL,

ADRESA VARCHAR2(30) NOT NULL,

CONT_BANC VARCHAR2(25) NOT NULL,

CONSTRAINT FZ_PK PRIMARY KEY(COD_FZ));

PROMPT Creating Table 'FACTURI'

DROP TABLE FACTURI CASCADE CONSTRAINTS;

CREATE TABLE FACTURI

(NR_FACT NUMBER(6) NOT NULL,

COD_FZ NUMBER(6) NOT NULL,

DATA DATE NOT NULL,

VAL NUMBER(10,1) NOT NULL,

VAL_TVA NUMBER(10,1) NOT NULL,

CONSTRAINT F_PK PRIMARY KEY(NR_FACT),

CONSTRAINT F_FK FOREIGN KEY(COD_FZ) REFERENCES FURNIZORI(COD_FZ));

PROMPT Creating Table 'RAND_FACT'

DROP TABLE RAND_FACT CASCADE CONSTRAINTS;

CREATE TABLE RAND_FACT

(NR_FACT NUMBER(6) NOT NULL,

COD_COM NUMBER(6) NOT NULL,

COD_MAT NUMBER(6) NOT NULL,

PU NUMBER(6,1) NOT NULL,

CANT NUMBER(6) NOT NULL,

VAL NUMBER(10,1) NOT NULL,

VAL_TVA NUMBER(10,1) NOT NULL,

CONSTRAINT RF_FK1 FOREIGN KEY(NR_FACT) REFERENCES FACTURI(NR_FACT),

CONSTRAINT RF_FK2 FOREIGN KEY(COD_COM) REFERENCES COMENZI(COD_COM),

CONSTRAINT RF_FK3 FOREIGN KEY(COD_MAT) REFERENCES MATERIALE(COD_MAT));

PROMPT Creating Table 'PLATI'

DROP TABLE PLATI CASCADE CONSTRAINTS;

CREATE TABLE PLATI

(COD_PL NUMBER(6) NOT NULL,

COD_COM NUMBER(6) NOT NULL,

TERM_PLATA DATE NOT NULL,

DATA_PLATII DATE NOT NULL,

COMISION NUMBER(6) NOT NULL,

PENALIZARI NUMBER(6) NOT NULL,

CONSTRAINT PL_PK PRIMARY KEY(COD_PL),

CONSTRAINT PL_FK FOREIGN KEY(COD_COM) REFERENCES COMENZI(COD_COM));

Populare Tabele

DELETE FROM CLIENTI;

DELETE FROM COMENZI;

DELETE FROM MATERIALE;

DELETE FROM RAND_FACT;

DELETE FROM FACTURI;

DELETE FROM FURNIZORI;

DELETE FROM PLATI;

INSERT INTO CLIENTI VALUES

(1,11,'CONFARG SA','ELENA CUZA B3 C 46 PITESTI','ARGES','0723/535954','BRD-050502','Vito CORLEONE');

INSERT INTO CLIENTI VALUES

(2,21,'FINNE SA','VICTORIEI 5 PITESTI','ARGES','0720/535954','BCR-050502','Viorela POPESCU');

INSERT INTO CLIENTI VALUES

(3,31,'STYL SRL','BALCESCU 13 CLUJ-NAPOCA','CLUJ','0248/624826','BRD-123456','Ion MUSAT');

INSERT INTO CLIENTI VALUES

(4,41,'FARMEC SA','FLORILOR 2 IASI','IASI','0724/123456','RAIFFEISEN-0505','Marian POSTELNICU');

INSERT INTO CLIENTI VALUES

(5,51,'L-AMOUR SRL','PLOPILOR 27 ARAD','ARAD','0727/843460','BCR-130282','Florina COSTEA');

INSERT INTO MATERIALE VALUES

(1,'ATA','BUC');

INSERT INTO MATERIALE VALUES

(2,'NASTURI','BUC');

INSERT INTO MATERIALE VALUES

(3,'STOFA','M');

INSERT INTO MATERIALE VALUES

(4,'CAPTUSEALA','M');

INSERT INTO MATERIALE VALUES

(5,'MATASE','M');

INSERT INTO MATERIALE VALUES

(6,'ACCESORII','BUC');

INSERT INTO COMENZI VALUES

(1,1,'COSTUME DE BARBATI MARIMILE 40-50','27-jan-06','01-mar-06');

INSERT INTO COMENZI VALUES

(2,1,'COSTUME DE FEMEI MARIMILE 34-50','13-feb-06','23-apr-06');

INSERT INTO COMENZI VALUES

(3,2,'FUSTE PENTRU FEMEI SI FETE MARIMILE 12-50','19-mar-06','21-jun-06');

INSERT INTO COMENZI VALUES

(4,3,'CAMASI BARBATI','01-mar-06','16-may-06');

INSERT INTO COMENZI VALUES

(5,2,'SACOURI FEMEI','25-mar-06','27-may-06');

INSERT INTO RAND_COM VALUES (1,1);

INSERT INTO RAND_COM VALUES (1,2);   

INSERT INTO RAND_COM VALUES (1,3);

INSERT INTO RAND_COM VALUES (1,4);

INSERT INTO RAND_COM VALUES (2,1);

INSERT INTO RAND_COM VALUES (2,5);

INSERT INTO RAND_COM VALUES (2,4);

INSERT INTO RAND_COM VALUES (2,2);

INSERT INTO RAND_COM VALUES (2,6);

INSERT INTO RAND_COM VALUES (3,1);

INSERT INTO RAND_COM VALUES (3,3);

INSERT INTO RAND_COM VALUES (3,4);

INSERT INTO RAND_COM VALUES (3,2);

INSERT INTO RAND_COM VALUES (5,1);

INSERT INTO RAND_COM VALUES (5,2);

INSERT INTO RAND_COM VALUES (4,1);

INSERT INTO RAND_COM VALUES (4,3);

INSERT INTO RAND_COM VALUES (4,4);

INSERT INTO FURNIZORI VALUES

(1,91,'ACCESORII SRL','UNIRII 5 BUCURESTI','BCR-123456');

INSERT INTO FURNIZORI VALUES

(2,92,'SIMPLU SRL','MORII 13 BUCURESTI','BCR-011234');

INSERT INTO FURNIZORI VALUES

(3,93,'MYLLIE SA','BASARABILOR 12 ARGES','BRD-130282');

INSERT INTO FURNIZORI VALUES

(4,94,'FASHION SRL','POSADA 5 ARGES','BRD-456321');

INSERT INTO FURNIZORI VALUES

(5,95,'BELLA SRL','ROMANA 27 BUCURESTI','RAIFFEISEN-456');

INSERT INTO FACTURI VALUES (1,1,'31-jan-06',0,0);

INSERT INTO FACTURI VALUES (2,2,'01-feb-06',0,0);

INSERT INTO FACTURI VALUES (3,3,'15-feb-06',0,0);

INSERT INTO FACTURI VALUES (4,4,'21-mar-06',0,0);

INSERT INTO FACTURI VALUES (5,5,'26-mar-06',0,0);

INSERT INTO RAND_FACT VALUES (1,1,1,1,2000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (1,1,2,1.2,2000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (1,2,5,17.5,340,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (1,2,6,21,200,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (1,5,2,1.5,2300,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (2,1,3,10,2000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (2,2,4,13,130,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (2,4,1,1.3,13000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (2,4,3,1.5,3000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (2,4,4,1,2000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (3,1,4,10,200,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;   

INSERT INTO RAND_FACT VALUES (3,2,1,15,1000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;   

INSERT INTO RAND_FACT VALUES (3,2,2,1.8,2000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (4,3,1,1.3,13000,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (4,3,2,13,130,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (4,5,1,1.3,1230,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (5,3,3,13,130,0,0);

--UPDATE RAND_FACT SET VAL=PU*CANT;

--UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

INSERT INTO RAND_FACT VALUES (5,3,4,23,2530,0,0);

UPDATE RAND_FACT SET VAL=PU*CANT;

UPDATE RAND_FACT SET VAL_TVA=1.19*VAL;

UPDATE FACTURI SET VAL=(SELECT SUM(RAND_FACT.VAL) FROM RAND_FACT WHERE NR_FACT=1)WHERE NR_FACT=1;

UPDATE FACTURI SET VAL_TVA=(SELECT SUM(RAND_FACT.VAL_TVA) FROM RAND_FACT WHERE NR_FACT=1)WHERE NR_FACT=1;   

UPDATE FACTURI SET VAL=(SELECT SUM(RAND_FACT.VAL) FROM RAND_FACT WHERE NR_FACT=2)WHERE NR_FACT=2;

UPDATE FACTURI SET VAL_TVA=(SELECT SUM(RAND_FACT.VAL_TVA) FROM RAND_FACT WHERE NR_FACT=2)WHERE NR_FACT=2;

UPDATE FACTURI SET VAL=(SELECT SUM(VAL) FROM RAND_FACT WHERE NR_FACT=3)WHERE NR_FACT=3;

UPDATE FACTURI SET VAL_TVA=(SELECT SUM(VAL_TVA) FROM RAND_FACT WHERE NR_FACT=3)WHERE NR_FACT=3;

UPDATE FACTURI SET VAL=(SELECT SUM(VAL) FROM RAND_FACT WHERE NR_FACT=4)WHERE NR_FACT=4;

UPDATE FACTURI SET VAL_TVA=(SELECT SUM(VAL_TVA) FROM RAND_FACT WHERE NR_FACT=4)WHERE NR_FACT=4;

UPDATE FACTURI SET VAL=(SELECT SUM(VAL) FROM RAND_FACT WHERE NR_FACT=5)WHERE NR_FACT=5;

UPDATE FACTURI SET VAL_TVA=(SELECT SUM(VAL_TVA) FROM RAND_FACT WHERE NR_FACT=5)WHERE NR_FACT=5;

INSERT INTO PLATI VALUES

(1,1,'04-mar-06','05-mar-06',0,5);

INSERT INTO PLATI VALUES

(2,2,'30-apr-06','27-apr-06',5,0);

INSERT INTO PLATI VALUES

(3,4,'27-apr-06','29-apr-06',0,5);

INSERT INTO PLATI VALUES

(4,5,'30-may-06','30-may-06',0,0);

--bloc anonim

SET SERVEROUTPUT ON

DECLARE

f_name VARCHAR2(20);

BEGIN

SELECT DENUMIRE

INTO f_name

FROM CLIENTI

WHERE COD_CL = 1;

DBMS_OUTPUT.PUT_LINE('Primul client adaugat in baza de date este: ' || f_name);

END;

--variabile de mediu

variable val_factura number

begin

select VAL_TVA into :val_factura

from facturi where NR_FACT=1;

end;

print val_factura

select DATA , VAL from facturi

where VAL_TVA=:val_factura;

--variabile de tipul coloanelor

set serveroutput on

DECLARE

v_denmat materiale.DENUMIRE%type;

v_nr number(2);

BEGIN

select m.DENUMIRE, count(r.COD_COM) into v_denmat, v_nr

from materiale m, rand_com r

where m.COD_MAT=r.COD_MAT and m.COD_MAT=1

group by m.DENUMIRE;

dbms_output.put_line('Materialul: '||upper(v_denmat)||' apare in '||v_nr||' randuri de comenzi');

END;

--blocuri imbricate

set serveroutput on

<<blocA>>

DECLARE

v_codcl clienti.COD_CL%type;

BEGIN

select COD_CL into v_codcl

from clienti where DENUMIRE='CONFARG SA';

<<blocB>>

DECLARE

v_nr number(2):=0;

BEGIN

select count(COD_CL) into v_nr from comenzi where COD_CL=blocA.v_codcl;

dbms_output.put_line('Clientul: '||blocA.v_codcl||' are: '||v_nr||' comenzi');

END;

end;

--variabile de substitutie

SET VERIFY OFF

SET AUTOPRINT ON

VARIABLE cl_cod number

ACCEPT p_nrc prompt 'Introduceti numarul comenzii: '

DECLARE

v_nrc NUMBER(6):=&p_nrc;

BEGIN

select COD_CL into :cl_cod

from comenzi where COD_COM=v_nrc;

select DENUMIRE

from clienti where COD_CL=cl_cod;

END;

--structuri de control

DECLARE

v_valf facturi.VAL_TVA%type;

v_valMed v_valf%type;

v_nrfact number(4):=100;

i number(4):=1;

BEGIN

SELECT count(NR_FACT) into v_nrfact from facturi;

SELECT avg(VAL_TVA) into v_valMed from facturi;

dbms_output.put_line('Valoare medie a celor '||v_nrfact||' facturi este: '||v_valMed);

dbms_output.put_line('Facturile cu valori peste: '||v_valMed||' sunt:');

loop

select VAL_TVA into v_valf from facturi where NR_FACT=i;

IF v_valf>v_valMed THEN

dbms_output.put_line('Factura cu numarul '||i||' are valoarea: '||v_valf);

END IF;

i:=i+1;

exit when i>v_nrfact;

end loop;

end;

--variabile record

DECLARE

TYPE fz_type IS RECORD

(codfz furnizori.COD_FISC%type NOT NULL:=100,

numefz furnizori.DENUMIRE%type,

adrfz furnizori.ADRESA%type);

furnizor fz_type;

BEGIN

SELECT COD_FISC, DENUMIRE, ADRESA into furnizor from furnizori where COD_FZ=1;

dbms_output.put_line('Furnizorul: '||furnizor.numefz|| ' cu adresa: '||furnizor.adrfz||' a fost adaugat primul.');

END;

--variabile cu structura tabelelor

DECLARE

furnizor furnizori%rowtype;

BEGIN

SELECT * into furnizor from furnizori where COD_FZ=1;

dbms_output.put_line('Furnizorul: '||furnizor.DENUMIRE|| ' cu adresa: '||furnizor.ADRESA||' a fost adaugat primul.');

END;

--variabile table of

DECLARE

--declararea tipului si a variabilei

type cl_tab is table of clienti%rowtype index by pls_integer;

v_tab cl_tab;

BEGIN

--incarcarea in tabela:

for i in 1..5 loop

SELECT * into v_tab(i) from clienti where COD_CL=i;

end loop;

--extragerea din tabela

for i in v_tab.first..v_tab.last loop

dbms_output.put_line('Clientul: '|| v_tab(i).DENUMIRE|| ' are adresa: '||v_tab(i).ADRESA);

end loop;

dbms_output.put_line('Total clienti in tabela: '|| v_tab.count);

end;

--interactiunea cu serverul

SET SERVEROUTPUT ON

declare

v_codpl plati.COD_PL%type;

v_codcom plati.COD_COM%type;

v_comis plati.COMISION%type;

v_penaliz plati.PENALIZARI%type;

begin

execute immediate 'DROP table platief';

execute immediate 'CREATE table platief AS SELECT COD_PL, COD_COM, COMISION, PENALIZARI FROM plati where DATA_PLATII<SYSDATE';

SELECT COD_PL, COD_COM, COMISION, PENALIZARI INTO v_codpl, v_codcom, v_comis, v_penaliz

FROM plati where COD_COM=1;

INSERT INTO platief (COD_PL, COD_COM, COMISION, PENALIZARI) VALUES (v_codpl, v_codcom, v_comis, 0.5*v_penaliz);

DBMS_OUTPUT.PUT_LINE ('In tabela platilor efectuate a fost adaugata plata avand codul: '||v_codpl||' pentru comanda '||v_codcom||' reducand penalizarile la '||0.5*v_penaliz);

end;

select * from platief;

--cursor implicit

set serveroutput on

declare

cursor mat_cursor is select COD_MAT, DENUMIRE, UM from materiale;

begin

dbms_output.put_line('Lista materialelor:');

for mat_rec in mat_cursor loop

dbms_output.put_line(mat_rec.DENUMIRE||' are unitatea de masura: '||mat_rec.UM);

end loop;

end;

--cursor explicit

set serveroutput on

declare

cursor mat_cursor is select COD_MAT, DENUMIRE, UM from materiale;

mat_rec mat_cursor%rowtype;

begin

dbms_output.put_line('Lista materialelor:');

open mat_cursor;

loop

fetch mat_cursor into mat_rec;

exit when mat_cursor%notfound;

dbms_output.put_line(mat_rec.DENUMIRE||' are unitatea de masura: '||mat_rec.UM);

end loop;

close mat_cursor;

end;

--cursor ca parametru

set serveroutput on

declare

v_val number(10);

cursor fact_cursor (p_val number) is

select f.NR_FACT fact, SUM(r.VAL) val

from facturi f, rand_fact r where f.NR_FACT=r.NR_FACT

group by f.NR_FACT

having sum(r.VAL)>p_val

order by sum(r.VAL);

fact_rec fact_cursor%rowtype;

begin

v_val:=15000;

dbms_output.put_line('Total inscris pe fiecare factura cu suma totala de plata mai mare de:'||v_val);

if not fact_cursor%isopen then

open fact_cursor(v_val);

end if;

loop

fetch fact_cursor into fact_rec;

exit when fact_cursor%notfound;

dbms_output.put_line('Factura '||fact_rec.fact||' are valoarea: '||fact_rec.val);

end loop;

close fact_cursor;

end;

--clauze

declare

cursor com_cursor is

select COD_COM, DESCRIERE, DATA_LIVR

from comenzi

for update of DATA_LIVR nowait;

begin

for com_rec in com_cursor loop

update comenzi

set DATA_LIVR=SYSDATE

where current of com_cursor;

dbms_output.put_line('Comanda '||com_rec.COD_COM||' - '||com_rec.DESCRIERE||' are data livrarii: '||com_rec.DATA_LIVR);

end loop;

end;

--exceptii predefinite

SET SERVEROUTPUT ON

DECLARE

descr comenzi.DESCRIERE%type;

BEGIN

select DESCRIERE into descr from comenzi where DATA_LIVR=SYSDATE;

DBMS_OUTPUT.PUT_LINE('Comanda care se livreaza azi are descrierea: '||descr);

EXCEPTION

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('Astazi se livraza mai multe comenzi! Utilizati un cursor pentru selectie!');

END;

--exceptii non-predefinite

SET SERVEROUTPUT ON

DECLARE

INSERT_EXCEPT EXCEPTION;

PRAGMA EXCEPTION_INIT(INSERT_EXCEPT, -01400);

BEGIN

insert into rand_fact (NR_FACT, COD_COM) values (2, 2);

EXCEPTION

WHEN insert_except THEN

DBMS_OUTPUT.PUT_LINE('Nu ati precizat informatii suficiente pentru randul facturii 2.');

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

--exceptii utilizator

DECLARE

INVALID_COM EXCEPTION;

BEGIN

UPDATE comenzi

SET DATA_LIVR=SYSDATE

WHERE COD_COM=1000;

IF SQL%NOTFOUND THEN

RAISE INVALID_COM;

END IF;

COMMIT;

EXCEPTION

WHEN INVALID_COM THEN

DBMS_OUTPUT.PUT_LINE('Nu exista comanda cu acest numar');

END;

--proceduri

CREATE or REPLACE PROCEDURE raise_valf_med

(nrf IN facturi.NR_FACT%type, valf_med IN OUT facturi.VAL_TVA%type)

IS

dataf facturi.DATA%type;

valf facturi.VAL_TVA%type;

BEGIN

Select DATA, VAL_TVA into dataf, valf from facturi where NR_FACT=nrf;

IF valf<valf_med then

DBMS_OUTPUT.PUT_LINE('Factura '||nrf||' din '||dataf||' are valoarea mai mica decat '||valf_med);

END IF;

select avg(VAL_TVA) into valf_med from facturi;

End;

SET SERVEROUTPUT ON

DECLARE

fact_nr facturi.NR_FACT%type;

fact_valmed facturi.VAL_TVA%type;

BEGIN

fact_nr:=1;

fact_valmed:=100000;

raise_valf_med(fact_nr, fact_valmed);

Exception

When NO_DATA_FOUND then

DBMS_OUTPUT.PUT_LINE('Factura '||fact_nr||' nu exista.');

END;

--exceptii in proceduri:

CREATE or REPLACE PROCEDURE raise_plati

(cod IN plati.COD_PL%type, procent IN number)

IS

Invalid_plata EXCEPTION;

BEGIN

Update plati

Set COMISION=COMISION*(1+procent/100)

Where COD_PL=cod;

IF SQL%NOTFOUND THEN

Raise Invalid_plata;

END IF;

EXCEPTION

WHEN Invalid_plata THEN

DBMS_OUTPUT.PUT_LINE('Plata cu numarul '||cod||' nu exista!');

END;

EXECUTE raise_plati(1, 10)

Select object_name

From user_objects

Where object_type='PROCEDURE';

Select text

From user_source

Where name='RAISE_PLATI' and type='PROCEDURE';

--functii

CREATE OR REPLACE FUNCTION check_val(factnr facturi.NR_FACT%type)

RETURN Boolean

IS

valf facturi.VAL_TVA%type;

avg_val facturi.VAL_TVA%type;

BEGIN

SELECT VAL_TVA into valf from facturi where NR_FACT=factnr;

SELECT avg(VAL_TVA) into avg_val from facturi;

IF valf>avg_val then

return true;

ELSE

return false;

end if;

EXCEPTION

WHEN NO_DATA_FOUND THEN

return NULL;

end;

show errors

describe check_val;

SET SERVEROUTPUT ON

BEGIN

dbms_output.put_line('Verific factura 1');

IF (check_val(1) IS NULL) then

dbms_output.put_line('Nu exista aceasta factura.');

elsif (check_val(1)) then

dbms_output.put_line('Factura are valoarea mai mare decat media.');

else

dbms_output.put_line('Factura are valoarea mai mica decat media.');

end if;

END;

--pachete

CREATE OR REPLACE PACKAGE comm_pkg

IS

Std_comm NUMBER;

comm_invalid exception;

pragma exception_init(comm_invalid, -20210);

PROCEDURE reset_comm(new_comm NUMBER);

END comm_pkg;

CREATE OR REPLACE PACKAGE BODY comm_pkg

IS

FUNCTION validate(p_comm NUMBER)

RETURN BOOLEAN

IS

v_max_comm plati.COMISION%type;

BEGIN

SELECT MAX(COMISION) INTO v_max_comm

FROM plati;

RETURN (p_comm BETWEEN 0.0 AND v_max_comm);

END validate;

PROCEDURE reset_comm(new_comm NUMBER)

IS

BEGIN

IF validate(new_comm) THEN

std_comm:=new_comm;

ELSE

RAISE_APPLICATION_ERROR(-20210, 'Comision Invalid!');

END IF;

END reset_comm;

BEGIN

IF std_comm IS NULL THEN

std_comm:=0.10;

END IF;

EXCEPTION

WHEN comm_invalid THEN

DBMS_OUTPUT.PUT_LINE('Comisionul este foarte mare!');

END comm_pkg;

show errors

set serveroutput on

EXECUTE comm_pkg.reset_comm(0.95);

EXECUTE comm_pkg.reset_comm(0.15);

BEGIN

DBMS_OUTPUT.PUT_LINE('Comisionul este: '||comm_pkg.std_comm);

END;

Select text

From user_source

Where name='COMM_PKG' and type='PACKAGE';

Select text

From user_source

Where name='COMM_PKG' and type='PACKAGE BODY';

--supraincarcari

CREATE OR REPLACE PACKAGE mat_pkg

IS

PROCEDURE add_material(matcod number, den varchar2:= 'unknown', unitmas varchar2:='M');

PROCEDURE add_material(den varchar2:= 'unknown', unitmas varchar2:='M');

END mat_pkg;

CREATE OR REPLACE PACKAGE BODY mat_pkg

IS

PROCEDURE add_material(matcod number, den varchar2:= 'unknown', unitmas varchar2:='M')

IS

BEGIN

INSERT INTO materiale(COD_MAT, DENUMIRE, UM) values (matcod, den, unitmas);

END add_material;

PROCEDURE add_material(den varchar2:= 'unknown', unitmas varchar2:='M')

IS

v_max_id materiale.COD_MAT%type;

BEGIN

Select max(COD_MAT) into v_max_id from materiale;

v_max_id:=v_max_id+1;

INSERT INTO materiale(COD_MAT, DENUMIRE, UM) values (v_max_id, den, unitmas);

END add_material;

END mat_pkg;

EXECUTE mat_pkg.add_material(6, 'MATASE', 'M')

EXECUTE mat_pkg.add_material('VATELINA', 'M')

SELECT * FROM materiale where UM like 'M%';

Select text

From user_source

Where name='MAT_PKG' and type='PACKAGE BODY';

--triggeri la nivel de rand

CREATE OR REPLACE TRIGGER SECURE_RAND BEFORE INSERT OR UPDATE ON RAND_FACT

FOR EACH ROW

begin

:new.VAL:=(:new.PU)*(:new.CANT);

:new.VAL_TVA:=(:new.VAL)*(1.19);

end;

CREATE or REPLACE PROCEDURE update_fact(nrfact IN facturi.NR_FACT%type)

IS

begin

UPDATE FACTURI SET VAL=(SELECT SUM(RAND_FACT.VAL) FROM RAND_FACT WHERE NR_FACT=nrfact) WHERE NR_FACT=nrfact;

UPDATE FACTURI SET VAL_TVA=(SELECT SUM(RAND_FACT.VAL_TVA) FROM RAND_FACT WHERE NR_FACT=nrfact) WHERE NR_FACT=nrfact;

END;

insert into rand_fact values (1,3,4,23,2530,0,0);

execute update_fact(1);

--triggeri pentru restrictii

CREATE OR REPLACE

TRIGGER MATERIALE_UK_TRG BEFORE INSERT ON MATERIALE

FOR EACH ROW

BEGIN

SELECT COD_MAT INTO :new.COD_MAT FROM materiale where DENUMIRE=:new.DENUMIRE;

end;

show errors trigger MATERIALE_UK_TRG

insert into materiale values (7,'ACCESORII','BUC');

select * from materiale;