Skip to content

Instantly share code, notes, and snippets.

@AndreiDuma

AndreiDuma/laboratoare.sql

Last active February 8, 2016 17:44
Show Gist options
  • Save AndreiDuma/ce8d23a3de88df19b437 to your computer and use it in GitHub Desktop.
Save AndreiDuma/ce8d23a3de88df19b437 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
laboratoare.sql
-- Laborator 1
-- blocuri anonime imbricate secventiale
declare
idemp number(4) := 7839;
nume varchar(10);
begin
select ename into nume from emp where empno = idemp;
dbms_output.put_line('Numele ang este: ' || nume);
exception
when no_data_found then
dbms_output.put_line('Nu am gasit ang cu id '||idemp);
end;
/
begin
insert into emp(empno, ename, job, mgr, sal)
values(1234,'Georgescu','clerck',7566,1250);
end;
/
declare
v_nume varchar2(20);
v_job varchar2(20);
begin
select ename, job into v_nume, v_job, from emp
where hiredate between '01-01-1992' and '31-12-1992';
exception
when no_data_found then
insert into mesaje values(1, 'Nu exista angajari in 1992');
when too_many_rows then
insert into mesaje values(2, 'Sunt mai multe angajari in 1992');
end;
/
declare
idDepartament number(4);
idAngajat number(4);
numeAngajat varchar(30);
comision number(10);
dataAngajare date;
begin
idDepartament := &idDept;
idAngajat := &idAng;
begin
select ename, hiredate, comm into numeAngajat, dataAngajare, comision
from emp where empno = idAngajat and deptno = idDepartament;
dbms_output.put_line(rpad('Nume', 30, ' ')||rpad('Data angajare', 15, ' ')||lpad('Comision', 10, ' '));
dbms_output.put_line(rpad(numeAngajat, 30, ' ')||rpad(dataAngajare, 15, ' ')||lpad(comision, 10, ' '));
exception
when no_data_found then null;
end;
end;
/
declare
numeDepartament varchar(20);
salariu number;
begin
select sum(sal+nvl(comm, 0)) into salariu from emp
where deptno = &&idDept and job = '&job' group by deptno;
select dname into numeDepartament from dept
where deptno = &idDept;
insert into mesaje values('Suma veniturilor in dept ' || &idDept, ' este ' || to_char(salariu));
exception
when no_data_found then
insert into mesaje(mesaj1) values('In departamentul ' || &idDept || ' nu exista angajati');
end;
/
Enunt: SA se scriu un bloc PLSQL care primeste de la tastatura un salariu (un numar) si afiseaza gradul salarial, care se gaseste in tabela salgrade. Tratati erorile care pot sa apara.
declare
grad number(4);
begin
select grade into grad from salgrade
where losal <= &&input_sal and hisal >= &input_sal;
dbms_output.put_line('Gradul este ' || grad);
exception
when no_data_found then
dbms_output.put_line('Nu poate fi incadrat');
end;
/
-- Laborator 2
-- tipuri de date scalare corelate inregistrare (record) update row varray index-by table nested table bulk cllect
declare
numeAngajat varchar2(20);
idDepartament integer;
numeDepartament varchar(20);
dataAngajare date;
idAngajat number(6);
stare boolean := true;
venit float;
zileLucrate number(2);
venitOrar real;
zileLuna constant smallint := 21;
begin
idAngajat := &idAngajat;
select deptno, ename, sal + nvl(comm, 0), hiredate
into idDepartament, numeAngajat, venit, dataAngajare
from emp where empno = idAngajat;
select dname
into numeDepartament
from dept where deptno = idDepartament;
venitOrar := round(venit / (zileLuna * 8), 2);
zileLucrate := sysdate - dataAngajare;
dbms_output.put_line(numeAngajat || ' are un venit orar de ' || venitOrar || ' si face parte din departamentul '
|| numeDepartament || '.' || chr(13) || chr(10) || 'A lucrat in firma un numar total de ' || zileLucrate || ' zile.');
exception
when no_data_found then
stare := false;
dbms_output.put_line('Angajatul nu se afla in baza de date!');
end;
/
*****************************************************
declare
numeAngajat varchar2(20);
functie string(30);
dataMax date;
dataInceput date := '1-JAN-1982';
dataSfarsit date := '31-DEC-1982';
begin
select max(hiredate) into dataMax from emp
where hiredate between dataInceput and dataSfarsit;
select ename, job into numeAngajat, functie
from emp where hiredate = dataMax;
dbms_output.put_line('Ultimul angajat venit in firma in anul 1982 este ' || numeAngajat || ' si are functia ' || functie);
exception
when no_data_found then
dbms_output.put_line('Nu a fost angajat nimeni in 1982.');
when too_many_rows then
dbms_output.put_line('Sunt mai multe angajari in ultima zi.');
end;
/
*****************************************************
declare
numeAngajat varchar(20);
functie string(20);
dataMax date;
vechime interval year(2) to month;
begin
vechime := interval '29-6' year to month;
dbms_output.put_line('Vechimea solicitata = ' || vechime);
select hiredate into dataMax from emp
where hiredate < sysdate - vechime;
dbms_output.put_line('Data maxima ' || dataMax);
select ename, job into numeAngajat, functie
from emp where hiredate = dataMax;
dbms_output.put_line('Angajatul cu vechimea cautata este ' || numeAngajat || ' si are functia ' || functie);
exception
when no_data_found then
dbms_output.put_line('Nu exista angatati cu aceasta vechime');
when too_many_rows then
dbms_output.put_line('Sunt mai multi angatati cu aceasta vechime');
end;
/
*****************************************************
declare
numeAngajat emp.ename%type;
venit emp.sal%type;
idDepartament emp.deptno%type;
departament dept%rowtype;
begin
select deptno, ename, sal + nvl(comm, 0)
into idDepartament, numeAngajat, venit
from emp where empno = &idAngajat;
select * into departament from dept
where deptno = idDepartament;
dbms_output.put_line(numeAngajat || ' face parte din departamentul ' || departament.dname);
exception
when no_data_found then
dbms_output.put_line('Angajatul nu se gaseste in baza de date');
end;
/
*****************************************************
declare
type deptRecord is record
(
idDept number(2),
numeDept varchar2(14),
locatie varchar2(13)
);
recDept deptRecord;
deptInfo deot%rowtype;
begin
select * into deptInfo from dept where deptno=10;
recDept := deptInfo;
dbms_output.put_line('Denumirea departamentului ' || recDept.idDept || ' este ' || recDept.numeDept);
end;
/
*****************************************************
declare
type myRecord is record
(
idDepartament dept.deptno%type,
numeDepartament dept.dname%type,
locatie dept.loc%type
);
deptRecord myRecord;
deptRecordUpdate myRecord;
deptRecordInsert myRecord;
begin
deptRecordInsert.idDepartament := 50;
deptRecordInsert.numeDepartament := 'IT Department';
deptRecordInsert.locatie := 'Bucuresti';
insert into dept
values
(
deptRecordInsert.idDepartament,
deptRecordInsert.numeDepartament,
deptRecordInsert.locatie
);
select * into deptRecord form dept
where deptno = deptRecordInsert.idDepartament;
dbms_output.put_line('Informatii dupa insert ' || deptRecord.idDepartament || ' ' || deptRecord.numeDepartament || ' ' || deptRecord.locatie);
deptRecordUpdate.idDepartament := deptRecordInsert.idDepartament;
deptRecordUpdate.numeDepartament := 'Operatii';
deptRecordUpdate.locatie := 'Sibiu';
update dept set row = deptRecordUpdate.idDepartament
returning deptno, dname, loc into deptRecord;
dbms_output.put_line('Informatii dupa delete '|| deptRecord.idDepartament || ' ' || deptRecord.numeDepartament || ' ' || deptRecord.locatie);
end;
/
*****************************************************
-- Laborator 3
-- if then else elsif case loop while for searched case goto <<label>>
set serveroutput on;
set verify off;
declare
numeAngajat varchar2(20);
comAngajat number;
functie varchar2(15);
ecuson emp.empno%type;
begin
ecuson := &idAngajat;
select ename, job, comm into numeAngajat, functie, comAngajat
from emp
where empno = ecuson;
if lower(functie) = 'manager' and (comAngajat = 0 or comAngajat is null) then
update emp set comm = 0.1 * sal where empno = ecuson;
DBMS_OUTPUT.PUT_LINE('Comisionul lui '||numeAngajat||' a fost modificat!');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Nu exista angajat cu empno = '||ecuson);
end;
****************************************
declare
numeAngajat varchar2(30);
ecuson emp.empno%type;
dataAngajare date;
begin
ecuson := &idAng;
select ename, hiredate into numeAngajat, dataAngajare
from emp where empno = ecuson;
if dataAngajare < add_months(sysdate, -384) then
update emp set comm = 0.1 * sal where empno = ecuson;
DBMS_OUTPUT.PUT_LINE('Angajatul '||numeAngajat||' a primit comision!');
else
dbms_output.put_line('Angajatul nu indeplineste conditia.');
end if;
exception
when no_data_found then
dbms_output.put_line('Nu exista angajat cu ecusonul = '||ecuson);
end;
****************************************
set serveroutput on;
set verify off;
declare
numeAngajat varchar2(30);
comAngajat number;
functie varchar2(15);
ecuson emp.empno%type;
begin
ecuson := &idAngajat;
select ename, job, comm into numeAngajat, functie, comAngajat
from emp
where empno = ecuson;
if lower(functie) = 'president' then
dbms_output.put_line('Presedintele nu primeste comision.');
elsif lower(functie) = 'manager' and (comAngajat = 0 or comAngajat is null) then
update emp set comm = 0.1 * sal where empno = ecuson;
dbms_output.put_line('Comisionul lui '||numeAngajat||' a fost modificat');
else
dbms_output.put_line('Angajatul nu face parte din conducere');
end if;
exception
when no_data_found then
dbms_output.put_line('Nu exista angajat cu empno = '||ecuson);
end;
****************************************
set serveroutput on;
set verify off;
declare
numeAngajat varchar2(20);
venit number;
gradSalariu integer;
impozit number;
ecuson emp.empno%type;
begin
ecuson := &idAngajat;
select ename, sal+nvl(comm, 0) into numeAngajat, venit
from emp where empno = ecuson;
select grade into gradSalariu from salgrade
where losal < venit and venit <= hisal;
<<mycase>>
case gradSalariu
when 1 then impozit := venit * 0.10;
when 2 then impozit := venit * 0.15;
when 3 then impozit := venit * 0.20;
when 4 then impozit := venit * 0.25;
else
impozit := venit * 0.30;
end case mycase;
dbms_output.put_line('Angajatul '||numeAngajat||' are venitul = '||venit||' si un impozit = '||impozit);
exception
when no_data_found then
dbms_output.put_line('Nu exista anagajat cu ecusonul = '||ecuson);
end;
****************************************
set serveroutput on;
set verify off;
declare
grade float := 7.2;
begin
case
when grade >= 9 then dbms_output.put_line('excellent');
when grade >= 8 and grade < 9 then dbms_output.put_line('very good');
when grade >= 7 and grade < 8 then dbms_output.put_line('good');
when grade >= 6 and grade < 7 then dbms_output.put_line('fair');
when grade >= 5 and grade < 6 then dbms_output.put_line('bad');
when grade < 5 then dbms_output.put_line('poor');
else dbms_output.put_line('no such grade');
end case;
end;
****************************************
set serveroutput on;
set verify off;
declare
nume emp.ename%type;
functie varchar(30);
begin
select e.ename, (case e.job
when 'ANALYST' then 'Analist'
when 'SALESMAN' then 'Salesman'
else 'Nu avem functia'
end)
into nume, functie
from emp e where empno = &idEmp;
dbms_output.put_line(nume||' are functie '||functie);
end;
****************************************
Pentru fiecare angajat care nu are comision sa se calculeze un comision astfel: daca angajatul a venit in firma inainte de 1981 comisionul este de 10% din salariu, daca a venit in 1981 15%, 1982 20%, dupa 1982 25%.
set serveroutput on;
set verify off;
declare
type angajat_t is record (
empno emp.empno%type,
ename emp.ename%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.sal%type,
deptno emp.deptno%type
);
deptno dept.deptno%type;
ename emp.ename%type;
dname dept.dname%type;
comm emp.comm%type;
depts int;
type deptids_t is table of dept.deptno%type;
type angajati_t is table of angajat_t;
angajati angajati_t;
deptids deptids_t;
cnt int;
cnt2 int;
begin
select deptno bulk collect into deptids from dept;
for cnt in 1 .. deptids.count
loop
begin
select dname into dname from dept where deptno=deptids(cnt);
dbms_output.put_line(dname);
select empno, ename, hiredate, sal, comm, deptno bulk collect into angajati from emp where deptno=deptids(cnt);
for cnt2 in 1 .. angajati.count
loop
begin
select comm into comm from emp where empno = angajati(cnt2).empno;
dbms_output.put_line(lpad(' ', 4)||angajati(cnt2).ename);
end;
end loop;
end;
end loop;
exception
when no_data_found then
dbms_output.put_line('Departamentul nu are angajati.');
end;
-- Laborator 4
-- exceptii predefinite nedefinite definite
declare
begin
exception
when others then ceva, ceva;
end;
set serveroutput on;
declare
insert_angajat exception;
pragma exception_init(insert_angajat, -01400);
begin
insert into emp(ename, hiredate, sal)
values('Niculescu', '14-AUG-2010', 1450);
exception
when insert_angajat then
dbms_output.put_line('Nu se accepta inregistrari noi in tabela fara empno');
dbms_output.put_line('Cod eroare SQL '||SQLCODE);
dbms_output.put_line('Mesaj '||SQLERRM);
end;
/
-- Laborator 5
-- cursoare implicite explicite cu parametri ref cursor sys_refcursor
set serveroutput on;
declare
cursor c_angajati is
select d.dname, e.empno, e.ename, e.sal, e.comm
from emp e inner join dept d on d.deptno = e.deptno
where lower(e.job) like lower('Manager');
angajat c_angajati%rowtype;
venit number := 0;
begin
-- output diverse
for angajat in c_angajati
loop
venit := round(angajat.sal + nvl(angajat.comm, 0));
-- output
venit := 0;
end loop;
end;
******************************************************************************************
set serveroutput on;
declare
cursor c_angajati is
select d.dname, e.empno, e.ename, e.sal, e.comm
from emp e inner join dept d on d.deptno = e.deptno
for update of comm;
angajat c_angajati%rowtype;
comisionNou number default 0;
begin
open c_angajati;
loop
fetch c_angajati into angajat;
exit when c_angajati%notfound;
if add_months(anagajat.hiredate, 240) < sysdate then
comisionNou := nvl(angajat.comm, 0) + round(0.1 * anagajat.sal, 0);
update emp set comm = comisionNou where current of c_angajati;
end if;
end loop;
close c_angajati;
end;
******************************************************************************************
set serveroutput on;
declare
cursor c_angajati(idDept number, functie char, dataAng date) is
select deptno, ename, job, hiredate, from emp
where deptno = idDept and lower(job) = lower(functie) and hiredate > dataAng;
angajat c_angajati%rowtype;
begin
open c_angajati(10, 'clerk', '1-JUL-81');
loop
fetch c_angajati into anagajat;
exit when c_angajati%notfound;
-- diverse
end loop;
close c_angajati;
end;
-- Laborator 6
-- proceduri
set serveroutput on;
declare
procedure procFaraParam
as
begin
dbms_output.put_line('Am apelat o procedura fara parametrii');
end;
begin
procFaraParam();
end;
*************************************************************
set serveroutput on;
declare
procedure salariu(deptId in number,
functie in out varchar2, salariuMaxim out number)
is
salmax number;
begin
select max(sal) into salmax from emp
where deptno = deptId and lower(job) = lower(functie) group by deptno;
salariuMaxim := salmax;
functie := case upper(functie)
when 'ANALYST' then 'Analist'
when 'SALESMAN' then 'Salesman'
else 'Nu exista functia'
end;
exception
when no_data_found then
dbms_output.put_line('Nu a fost gasita nicio inregistrare');
end;
begin
declare
numeDepartament emp.ename%type;
idDept emp.deptno%type;
functie varchar(40);
salMax number;
begin
idDept := &idDepartament;
functie := '&numeFunctie';
select dname into numeDepartament from dept where deptno = idDept;
salariu(idDept, functie, salMax);
dbms_output.put_line('In departamentul '||numeDepartament||
' salariu maxim pentru functia '||functie||' este '||salMax);
exception
when no_data_found then
dbms_output.put_line('Departament inexistent');
end;
end;
*************************************************************
create or replace procedure sp_venit(idDept in number,
venit in out number, dataRef in date default sysdate)
is
numeDept dept.dname%type;
begin
select dname into numeDept from dept where deptno = idDept;
select nvl(sum(sal+nvl(comm, 0)), 0) into venit from emp
where deptno = idDept and add_months(hiredate, 240) < dataRef;
dbms_output.put_line(rpad(numeDept, 20)||rpad(venit, 20));
exception
when no_data_found then
dbms_output.put_line('Nu a fost gasita nicio inregistrare!');
end sp_venit;
set serveroutput on;
declare
dataRef date default sysdate;
venit number := 0;
total number := 0;
begin
for rand in (select distinct deptno from dept order by deptno)
loop
sp_venit(rand.deptno, venit, dataRef);
total := total + nvl(venit, 0);
end loop;
dbms_output.put_line('Total '||total);
end;
*************************************************************
set serveroutput on;
declare
procedure concediu
as
cursor c_concediu is select * from emp order by deptno;
randConcediu c_concediu%rowtype;
type r_concediu is record(
numeDept dept.dname%type,
numeAng emp.ename%type,
dataAng emp.hiredate%type,
sef varchar2(2),
aniVechime number,
zileConcediu number
);
concediu r_concediu;
manager emp.mgr%type;
begin
open c_concediu;
loop
fetch c_concediu into randConcediu;
exit when c_concediu%notfound;
select dname into concediu.numeDept from dept
where deptno = randConcediu.deptno;
concediu.dataAng := randConcediu.hiredate;
concediu.numeAng := randConcediu.ename;
concediu.aniVechime := trunc(months_between(sysdate, randConcediu.hiredate)/12);
begin
select mgr into manager from emp
where mgr = randConcediu.empno;
exception
when no_data_found then concediu.sef := 'NU';
when too_many_rows then concediu.sef := 'DA';
end;
if concediu.aniVechime<32 and concediu.sef = 'DA' then
concediu.zileConcediu:=20;
elsif concediu.aniVechime>=32 and concediu.sef = 'DA' then
concediu.zileConcediu:=22;
elsif concediu.aniVechime<32 and concediu.sef = 'NU' then
concediu.zileConcediu:=15;
else
concediu.zileConcediu:=17;
end if;
dbms_output.put_line(rpad(concediu.numeDept, 20)
||rpad(concediu.numeAng, 20)||rpad(concediu.dataAng, 20)
||rpad(concediu.sef, 20)||rpad(concediu.aniVechime, 20)
||rpad(concediu.zileConcediu, 20));
end loop;
end;
begin
concediu();
end;
*************************************************************
set serveroutput on;
declare
procedure distributie(sal in number, vechime in number, nrang1 in number,
nrang2 in number, prima out number, grupa out number)
is
begin
if vechime <= 31 then
prima := round(0.3*sal/nrang1);
grupa := 1;
else
prima := round(0.7*sal/nrang2);
grupa := 2;
end if;
end;
begin
declare
nrang1 number := 0;
nrang2 number := 2;
salSef emp.sal%type;
prima number;
grupa number;
numeSef varchar2(20);
vechime number;
begin
dbms_output.put_line(rpad('Nume sef', 20)||rpad('Salariu sef', 20)
||rpad('Nume subaltern', 20)||rpad('Vechime', 20)
||rpad('Prima', 20)||rpad('Grupa', 20));
for sef in (select distinct mgr from emp where mgr is not null)
loop
nrAng1 := 0;
nrAng2 := 0;
select ename, sal into numeSef, salSef from emp where empno=sef.mgr;
for angajat in (select distinct empno from emp where mgr=sef.mgr)
loop
select trunc(months_between(sysdate, hiredate)/12) into vechime
from emp where empno = angajat.empno;
if vechime<31 then
nrAng1:=nrAng1+1;
else
nrAng2:=nrAng2+1;
end if;
end loop;
for angajat in (select distinct ename,
trunc(months_between(sysdate, hiredate)/12) vechime
from emp where mgr=sef.mgr)
loop
distributie(salSef,angajat.vechime, nrAng1, nrAng2,prima,grupa);
dbms_output.put_line(rpad(numeSef, 20)||rpad(salSef, 20)
||rpad(angajat.ename,20)||rpad(angajat.vechime,20)
||rpad(prima,20)||rpad(grupa,20));
end loop;
end loop;
end;
end;
*************************************************************
Sa se creeze o procedura care primeste ca parametru un id de angajat. Daca salariatul cu id-ul respectiv nu are comision sa i se calculeze comisionul astfel (pe table). Salariile minim, mediu, maxim sunt per departament.
Daca salariatul are comision, procedura va intoarce in blocul apelant comisionul actual si mesajul "Are comision.", altfel va intoarce in blocul apelant comisionul calculat si mesajul "Am calculat un comision pentru gradul &grad pentru salariul &salariu.". Tratati exceptiile pentru procedura.
In blocul apelant sa se apeleze procedura pentru toti si sa afisati, numele, comisionul si mesajul.
Pentru salgrade foloseste distinct.
set serveroutput on;
declare
cursor c_angajat is select * from emp;
angajat c_angajat%rowtype;
msg varchar2(500);
comms number;
procedure Comision(idAng in number, comision out number, mesaj out varchar2)
is
comm number;
ename emp.ename%type;
sal emp.sal%type;
dept emp.deptno%type;
salmin number;
salmax number;
salmed number;
salgrd number;
begin
select comm, ename, sal, deptno into comm, ename, sal, dept from emp where empno = idAng;
comm := nvl(comm, 0);
if comm != 0 then
comision := comm;
mesaj := 'Angajatul '||ename||' are comision';
else
select distinct grade into salgrd from salgrade
where sal <= hisal and sal > losal;
select min(sal) into salmin from emp
where deptno=dept order by deptno;
select max(sal) into salmax from emp
where deptno=dept order by deptno;
select avg(sal) into salmed from emp
where deptno=dept order by deptno;
if salgrd = 1 and sal < salmed then
comision := 0.4 * salmax;
elsif salgrd = 1 and sal >= salmed then
comision := 0.3 * salmin;
elsif salgrd = 2 and sal < salmed then
comision := 0.3 * salmax;
elsif salgrd = 2 and sal >= salmed then
comision := 0.2 * salmin;
elsif salgrd = 3 and sal < salmed then
comision := 0.2 * salmax;
elsif salgrd = 3 and sal >= salmed then
comision := 0.1 * salmin;
elsif salgrd > 3 and sal < salmed then
comision := 0.1 * salmax;
elsif salgrd > 3 and sal >= salmed then
comision := 0.05 * salmin;
end if;
mesaj := 'Am calculat comisionul pentru gradul '
||salgrd||' pentru salariul '||sal;
end if;
end;
begin
open c_angajat;
loop
fetch c_angajat into angajat;
exit when c_angajat%notfound;
Comision(angajat.empno, comms, msg);
dbms_output.put_line(msg);
end loop;
close c_angajat;
end;
-- Laborator 7
-- functii
set servereoutput on;
declare
function f_salariati(p_idDept in number) return number
as
nrAng number;
begin
select count(distinct empno) into nrAng
from emp where deptno = p_idDept;
return nrAng;
end;
begin
declare
numeDept dept.dname%type;
idDept dept.deptno%type;
nrAng number;
begin
idDept := &idDept;
select dname into numeDept from dept where deptno := idDept;
nrAng := f_salariati(idDept);
dbms_output.put_line('Departamentul '||numeDept||' are '||nrAng||' salariati');
exception
when no_data_found then
dbms_output.put_line('Departament inexistent');
end;
end;
*****************************************************************************
create table temp_puncte
(
idAng number(4),
nrPuncte number(3),
idDept number(2)
);
create or replace function f_puncte(p_idAng in number)
return number
is
dataAng emp.hiredate%type;
idDept dept.deptno%type;
salariu emp.sal%type;
comision emp.comm%type;
salMax number;
puncte number := 0;
begin
select hiredate, deptno, sal, nvl(comm, 0)
into dataAng, idDept, salariu, comision
from emp where empno = p_idAng;
if months_between(sysdate, dataAng) > 32*12 then
puncte := puncte + 30;
else
puncte := puncte + 15;
end if;
select max(sal) into salMax from emp where deptno = idDept;
if salMax = salariu then
puncte := puncte + 20;
else
puncte := puncte + 10;
end if;
if comision > 0 then
puncte := puncte + 10;
else
puncte := puncte + 5;
end if;
return puncte;
end f_puncte;
*****************************************************************************
set serveroutput on;
declare
puncte number;
cursor c_angajati is select empno, ename, deptno from emp;
numeDept dept.dname%type;
numeSef emp.ename%type;
dataAng emp.hiredate%type;
aniVechime number;
salMaxDept number;
comision emp.comm%type;
begin
delete from temp_puncte;
for angajat in c_angajati
loop
puncte := f_puncte(angajat.empno);
insert into temp_puncte values(angajat.empno, puncte, angajat.deptno);
end loop;
dbms_output.put_line(rpad('Departament', 20)||rpad('Salariu maxim', 20)
||rpad('Nume angajat', 20)||rpad('Ani vechime', 20)||
rpad('Comision', 20)||rpad('Puncte angajat'), 20));
dbms_output.put_line(rpad('=', 120, '='));
for contor1 in (select max(nrPuncte) maxPuncte, idDept
from temp_puncte group by idDept order by idDept)
loop
for contor2 in (select idAng from temp_puncte
where nrPuncte = contor1.maxPuncte and idDept = contor1.idDept)
loop
select dname into numeDept from dept where deptno = contor1.idDept;
select max(sal) into salMaxDept from emp where deptno = contor1.idDept;
select hiredate, nvl(comm, 0), ename into dataAng, comision, numaAng
from emp where empno = contor2.idAng;
aniVechime := trunc(month_between(sysdate, dataAng)/12);
dbms_output.put_line(rpad(numeDept, 20)||rpad(salMaxDept, 20)
||rpad(numeAng, 20)||rpad(aniVechime, 20)
||rpad(comision, 20)||rpad(contor1.maxPuncte, 20));
end loop;
end loop;
end;
*****************************************************************************
Sa se scrie o functie care verifica daca un angajat este sef.
Sa se scrie o functie care calculeaza venitul anual al unui angajat.
Sa se scrie o functie care ofera un procent de premiere astfel: daca venitul mediu anual este mai mare decat venitul anual din firma, 10%, altfel 20%
Sa se scrie o procedura care pentru toti angajatii din firma va calcula un bonus astfel: daca nu e sef bonusul este salariul anual * procenutl de premiere: nume angajat nu este sef asa ca primeste un bonus de; daca este sef: nume angajat nu primeste bonus pentru ca este sef
Scrieti un bloc apelant care cheama procedura.
set serveroutput on;
create or replace function f_isBoss(p_idAng in number) return boolean
as
nrAng number;
begin
select count(distinct empno) into nrAng
from emp where mgr = p_idAng;
if nrAng > 0 then
return true;
end if;
return false;
end;
set serveroutput on;
create or replace function f_annualIncome(p_idAng in number)
return number
as
annualIncome number;
income number;
sal emp.sal%type;
comm emp.sal%type;
begin
select sal, comm into sal, comm from emp where empno = p_idAng;
income := sal + nvl(comm, 0);
annualIncome := 12 * income;
return annualIncome;
end;
set serveroutput on;
create or replace function f_givePro(p_idAng in number) return number
is
cursor c_angajati is select * from emp;
sal emp.sal%type;
comm emp.comm%type;
angajat c_angajati%rowtype;
income number := 0;
nrAng number := 0;
annualIncome number;
begin
for angajat in c_angajati
loop
income := income + (12 * (angajat.sal + nvl(angajat.comm, 0)));
nrAng := nrAng + 1;
end loop;
annualIncome := income / nrAng;
income := f_annualIncome(p_idAng);
if income > annualIncome then
return 0.1;
end if;
return 0.2;
end;
set serveroutput on;
create or replace procedure bonus4All
is
cursor c_angajati is select * from emp;
angajat c_angajati%rowtype;
bonus number;
begin
for angajat in c_angajati
loop
if f_isBoss(angajat.empno) then
dbms_output.put_line(angajat.ename||' nu primeste bonus pentru ca e sef.');
else
bonus := f_annualIncome(angajat.empno) * f_givePro(angajat.empno);
dbms_output.put_line(angajat.ename||' nu este sef asa ca primeste un bonus'||
bonus);
end if;
end loop;
end;
set serveroutput on;
begin
bonus4All();
end;
-- Laborator 8
-- pachete
create table lista
(
den_dep varchar2(20),
nume_sef VARCHAR2(20),
data_sef date,
nume_sub varchar2(20),
data_sub date,
com_sub number
)
create or replace package angajare as
cursor depart is select deptno, dname from dept order by deptno;
v_dep depart%rowtype;
function vechime(data_ang date, data_ang_sef date) return boolean;
procedure prelucrare;
end angajare;
/
create or replace package body angajare as
function vechime(data_ang date, data_ang_sef date)
return boolean
is
verif boolean;
begin
if data_ang < data_ang_sef then verif := true;
else verif := false;
end if;
return verif;
end vechime;
procedure prelucrare is
cursor c_ang is select * from emp;
w_c c_ang%rowtype;
sef number(4);
nume_sef varchar2(20);
data_ang_sef date;
nume varchar2(20);
conditie boolean;
begin
delete from lista;
for i in angajare.depart
loop
begin
select empno, ename, hiredate into sef, nume_sef, data_ang_sef
from emp where deptno = i.deptno and lower(job) = 'manager';
exception
when no_data_found then sef := 0;
end;
open c_ang;
loop
fetch c_ang into w_c;
exit when c_ang%notfound;
if w_c.mgr = sef then
conditie := vechime(w_c.hiredate, data_ang_sef);
if conditie in (true) and nvl(w_c.comm, 0) <> 0 then
insert into lista values(i.dname, nume_sef, data_ang_sef,
w_c.ename, w_c.hiredate, w_c.comm);
end if;
end if;
end loop;
close c_ang;
end loop;
end prelucrare;
end angajare;
/
begin
angajare.prelucrare;
end;
select * from lista;
**************************************************************************
create or replace package apel as
var number := 10;
procedure f(x in number := var);
end apel;
/
create or replace package body apel as
function f_test(nr number := var) return number is
begin
if nr = var then return 1;
end if;
return 2;
end f_test;
procedure f(x in number := var)
is
begin
if x = var then
dbms_output.put_line('Sunt pe ramura if '||f_test());
else
dbms_output.put_line('Sunt pe ramura else '||f_test(x));
end if;
end f;
end apel;
/
set serveroutput on;
begin
apel.f();
apel.f(4);
end;
**************************************************************************
create or replace package prima as
function venit_maxim(nr_dep number) return number;
function vechime_maxima(nr_dep number) return number;
procedure calcul;
end prima;
/
create or replace package body prima as
function venit_maxim(nr_dep number) return number is
venit_max number;
begin
select max(sal+nvl(comm, 0)) into venit_max
from emp
where deptno = nr_dep;
return venit_max;
end venit_maxim;
function vechime_maxima(nr_dep number) return number is
vec_max number;
begin
select max(months_between(sysdate, hiredate)) into vec_max from emp
where deptno = nr_dep;
return vec_max;
end vechime_maxima;
procedure calcul is
cursor c_dep is select distinct deptno from dept;
w_dep c_dep%rowtype;
den_dep dept.dname%type;
nume emp.ename%type;
data_ang emp.hiredate%type;
venit_m number;
vec_m number;
venit_a number;
vec_a number;
venit_prim number;
vec_prim number;
ok integer := 0;
begin
dbms_output.put_line(rpad('Departament', 15)||rpad('Nume', 10)
||rpad('Venit', 10)||rpad('Vechime', 10));
dbms_output.put_line(rpad('=', 45, '='));
open c_dep;
loop
fetch c_dep into w_dep;
exit when c_dep%notfound;
begin
select dname into den_dep from dept where deptno = w_dep.deptno;
venit_m := trunc(prima.venit_maxim(w_dep.deptno));
vec_m := trunc(prima.vechime_maxima(w_dep.deptno));
venit_prim := 0;
vec_prim := 0;
for i in (select distinct empno, ename from emp
where deptno = w_dep.deptno)
loop
ok := 0;
select trunc(sal+nvl(comm, 0)),
trunc(months_between(sysdate, hiredate))
into venit_a, vec_a from emp where empno = i.empno;
if venit_a = venit_m and vec_a = vec_m then
venit_prim := venit_a;
vec_prim := vec_a;
ok := 1;
elsif venit_a = venit_m and vec_a <> vec_m then
venit_prim := venit_a;
vec_prim := 0;
ok := 1;
elsif venit_a <> venit_m and vec_a = vec_m then
venit_prim := 0;
vec_prim := vec_a;
ok := 1;
end if;
end loop;
exception
when too_many_rows then
dbms_output.put_line('Exista mai multe inregistrari');
when no_data_found then
dbms_output.put_line('Nu exista inregistrari');
end;
end loop;
close c_dep;
end calcul;
end prima;
/
**************************************************************************
Sa se faca o lista ordonata dupa numele angajatului care sa contina: (ce e pe tabla).
create or replace package premiere as
procedure print_all;
end premiere;
/
create or replace package body premiere as
function get_dname(dept number)
return varchar2
is
dname varchar2(20);
begin
select dname into dname from dept where deptno = dept;
return dname;
end get_dname;
function get_medsal_for_func(func varchar2)
return number
is
medsal number;
func_low varchar2(20) := lower(func);
begin
select avg(sal + nvl(comm, 0)) into medsal
from emp
where lower(job) = func_low;
return medsal;
end get_medsal_for_func;
procedure print_all
is
cursor c_ang is select * from emp order by ename;
ang c_ang%rowtype;
dept_sef number;
prema number;
nume_sef emp.ename%type;
functie_sef emp.job%type;
begin
dbms_output.put_line(rpad('Nume ang', 15)||rpad('Nume dept ang', 15)||
rpad('Func ang', 15)||rpad('Nume sef', 15)||rpad('Nume dept sef', 15)||
rpad('Functie sef', 15)||rpad('BONUS', 15));
open c_ang;
loop
fetch c_ang into ang;
exit when c_ang%notfound;
if ang.mgr > 0 then
select deptno into dept_sef from emp where empno = ang.mgr;
if ang.deptno = dept_sef then
prema := 0.2 * get_medsal_for_func(ang.job);
else
prema := 0.1 * get_medsal_for_func(ang.job);
end if;
else
prema := -1;
end if;
if prema > 0 then
select ename, job into nume_sef, functie_sef from emp where empno = ang.mgr;
dbms_output.put_line(rpad(ang.ename, 15)||rpad(get_dname(ang.deptno), 15)||rpad(ang.job, 15)||rpad(nume_sef, 15)||
rpad(get_dname(dept_sef), 15)||rpad(functie_sef, 15)||rpad(prema, 15));
else
dbms_output.put_line(ang.ename||get_dname(ang.deptno)||ang.job||'No sef'
||'No sef'||'No sef'||'No bonus');
end if;
end loop;
close c_ang;
end print_all;
end premiere;
/
begin
premiere.print_all;
end;
/
-- Laborator 9
-- triggers
create or replace trigger insertemp
before insert on emp
begin
dbms_output.put_line('S-a adaugat un nou angajat');
end;
************************************************
insert into emp(empno, ename, sal) values(999, 'Preda', 1500);
************************************************
create or replace trigger updateemp
after update on emp
begin
dbms_output.put_line('S-a facut o modificare in tabela emp');
end;
************************************************
update emp set comm = 100 where empno = 7902;
************************************************
create or replace trigger updateemp2
after update on emp
for each row
when(old.job <> 'MANAGER')
begin
dbms_output.put_line('S-a facut o modificare noua in tabela emp');
end;
************************************************
create or replace trigger insertemp2
before insert on emp
for each row
when (new.sal > 500 and new.sal < 5000)
begin
dbms_output.put_line('S-a facut o noua inserare in tabela emp');
end;
************************************************
create or replace trigger majorareSal
after update on emp
for each row
when (new.sal > old.sal)
begin
dbms_output.put_line('Salariul lui '||:old.ename
||' a fost majorat de la '||:old.sal||' la '||:new.sal);
end;
************************************************
create table mesaje
(
mesaj1 varchar2(40),
mesaj2 varchar2(40)
);
************************************************
create or replace trigger monitor
after insert or delete or update of sal, comm
on emp for each row
declare
salariu emp.sal%type;
comision emp.comm%type;
begin
if inserting then
insert into mesaje values('Inserare in tabela emp',
to_char(sysdate, 'dd-mm-yyyy hh:mi:ss'));
elsif deleting then
insert into mesaje values('Stergere din tabela emp',
to_char(sysdate, 'dd-mm-yyyy hh:mi:ss'));
elsif updating('sal') then
insert into mesaje values('Salariu modificat, old='||:old.sal
||' new='||:new.sal, to_char(sysdate, 'dd-mm-yyyy hh:mi:ss'));
elsif updating('comm') then
insert into mesaje values('Comision modificat, old='||:old.comm||
' new='||:new.comm, to_char(sysdate, 'dd-mm-yyyy hh:mi:ss'));
end if;
end;
************************************************
create or replace view sefi as
select d.deptno, d.dname, e.empno, e.ename, e.hiredate, e.sal, e.comm
from dept d inner join emp e on e.deptno = d.deptno
where e.empno in (select distinct mgr from emp);
************************************************
create or replace trigger manager
instead of insert on sefi
referencing new as n
for each row
declare
nr number;
begin
select count(*) into nr from emp where empno = :n.empno;
if nr = 0 then
insert into emp(empno, ename, deptno)
values(:n.empno, :n.ename, :n.deptno);
update emp set sal = :n.sal, comm = :n.comm,
hiredate = :n.hiredate, mgr = :n.empno
where empno = :n.empno;
end if;
select count(*) into nr from dept where deptno = :n.deptno;
if nr = 0 then
insert into dept(deptno, dname) values(:n.deptno, :n.dname);
end if;
end;
************************************************
create table log
(
nume varchar2(20),
salariu_vechi number,
salariu_nou number,
data_operare varchar2(20),
operator varchar2(20),
operatie varchar2(20)
)
************************************************
Sa se scrie un trigger care afiseaza un mesaj pentru fiecare operatie de tip insert update sau delete pe tablea dept. Mesajul va afisa: numele operatorului, data operatiei, valorile vechi, valorile noi, tipul operatiei si campurile pe care a fost facuta operatia. Sa se trateze cazurile in care se modifica locatie si dname.
create or replace trigger deptcomb
after delete or insert or update of loc, dname on dept
for each row
declare
message_head varchar(500) := user||' la data '||
to_char(sysdate, 'dd-mm-yyyy hh:mi:ss')||' a facut ';
begin
if inserting then
message_head := message_head||
' insert si noile valori sunt ';
-- inserting('loc') then
message_head := message_head||'location ='||:new.loc;
--end if;
--if inserting('dname') then
message_head := message_head||' dname='||:new.dname;
--end if;
elsif updating then
message_head := message_head||' update pe campul/campurile ';
if updating('loc') then
message_head := message_head||' location: new_location='||:new.loc||' si old_location='||:old.loc;
end if;
if updating('dname') then
message_head := message_head||' dname new_dname='||:new.dname||' si old_dname='||:old.dname;
end if;
elsif deleting then
message_head := message_head||' delete si vechea valoare/vechile valori ';
--if deleting('loc') then
message_head := message_head||' location='||:old.loc;
-- end if;
--if deleting('dname') then
message_head := message_head||' dname='||:old.dname;
--end if;
end if;
dbms_output.put_line(message_head);
end;
/
set serveroutput on;
insert into dept values(11, 'saormar', 'bucuresti');
insert into dept values(12, 'pizzer', null);
insert into dept values(13, null, 'londra');
update dept set loc = 'varsovia' where deptno = 12;
update dept set dname = 'varsovia' where deptno = 12;
delete from dept where deptno = 13;
/
rollback;
/
@AndreiDuma
Copy link
Author

Salut :d

@grigoroiualex
Copy link

Salut. Bine că ai lăsat link-ul că habar n-aveam pe unde am pus documentul și mâine s-ar putea dovedi util.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment