Last active
February 8, 2016 17:44
-
-
Save AndreiDuma/ce8d23a3de88df19b437 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; | |
/ |
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
Salut :d