//LAB3
1. Afisati nume, prenumele si ID-ul angajatilor cu EMPLOYEE_ID mai mic de 110. Utilizati tipul Record si atributul %TYPE.
SET SERVEROUTPUT ON;
DECLARE
TYPE AngajatIM IS RECORD
 (ID employees.employee_ID%TYPE,
 prenume employees.first_name%TYPE,
 nume employees.last_name%TYPE
 );

CURSOR c_ang IS
SELECT last_name,  first_name , employee_id FROM employees WHERE employee_ID
< 110;
angajat AngajatIM;
BEGIN
OPEN c_ang;
 FETCH c_ang INTO angajat.nume, angajat.prenume, angajat.id ;
 WHILE c_ang%FOUND LOOP


DBMS_OUTPUT.PUT_LINE(angajat.nume||' '||angajat.prenume||' '||angajat.id);
FETCH c_ang INTO angajat.nume, angajat.prenume, angajat.id;
 END LOOP;
 CLOSE c_ang;
END;


 2. Afisati nume, prenumele si ID-ul angajatilor cu EMPLOYEE_ID mai mic de 110. Utilizati tipul Record si atributul %ROWTYPE
SET SERVEROUTPUT ON;
DECLARE
angIM employees%ROWTYPE;
CURSOR c_ang IS
SELECT * FROM employees WHERE employee_ID
< 110;

BEGIN
OPEN c_ang;
 FETCH c_ang INTO angIM;
 WHILE c_ang%FOUND LOOP


DBMS_OUTPUT.PUT_LINE(angIM.last_name||' '||angIM.first_name||' '||angIM.employee_id);
FETCH c_ang INTO angIM;
 END LOOP;
 CLOSE c_ang;
END;
Creati duplicate pentru tabelele employees si departments cu numele XX_emp si XX_dep, unde XX reprezinta initialele studentului. În problemele care urmeaza se vor utiliza colectii.
create table im_emp as select * from employees
create table im_dep as select * from departments


 3. Afisati angajatii cu un salariu mai mare decât 10000, în urmatoarea forma: are salariul Utilizati procedeul BULK COLLECT cu instructiunea SELECT.
DECLARE
 TYPE emp_nume IS TABLE OF employees.last_NAME%type;
 TYPE emp_prn IS TABLE OF employees.first_NAME%type;
 TYPE emp_slr IS TABLE OF employees.salary%type;
 t_nume emp_nume;
 t_prn emp_prn;
 t_sal emp_slr;
 CURSOR c IS
 SELECT first_name, last_name, salary from employees
 WHERE salary > 10000;
BEGIN
 OPEN c;
 FETCH c BULK COLLECT INTO t_nume, t_prn, t_sal;
 CLOSE c;
 FOR i IN t_nume.FIRST .. t_nume.LAST LOOP
 DBMS_OUTPUT.put_line(t_nume(i)||' '||t_prn(i)||' are salariul ' || t_sal(i));
 END LOOP;
END;


4. Afisati angajatii al caror ID este mai mare decât 200 în ordine alfabetica. Utilizati procedeul BULK COLLECT cu instructiunea FETCH.
DECLARE
 TYPE emp_nume IS TABLE OF employees.last_NAME%type;
 TYPE emp_prn IS TABLE OF employees.first_NAME%type;
 TYPE emp_id IS TABLE OF employees.employee_id%type;
 t_nume emp_nume;
 t_prn emp_prn;
 t_id emp_id;
 CURSOR c IS
 SELECT first_name, last_name, employee_id from employees
 WHERE employee_id > 200 order by last_name;
BEGIN
 OPEN c;
 FETCH c BULK COLLECT INTO t_nume, t_prn, t_id;
 CLOSE c;
 FOR i IN t_nume.FIRST .. t_nume.LAST LOOP
 DBMS_OUTPUT.put_line(t_nume(i)||' '||t_prn(i)||' are salariul ' || t_id(i));
 END LOOP;
END;


5. Afisati numarul de angajati pentru fiecare departament, în una din urmatoarele forme: In departamentul dep nu lucreaza niciun angajat. In departamentul dep lucreaza un angajat. In departamentul dep lucreaza nr angajati.



6. Afisati numele managerului fiecarui departament sub forma: Departamentul nume_dep este condus de nume_manager.
DECLARE
 TYPE tab_nume IS TABLE OF DEPARTMENTS.DEPARTMENT_NAME%type;
 TYPE tab_name IS TABLE OF employees.last_name%type;
 t_nume tab_nume;
 t_name tab_name;
 CURSOR c IS
 SELECT e.first_name, d.department_name FROM departments d, employees e
 WHERE d.manager_id=e.employee_id ORDER BY department_name;
BEGIN
 OPEN c;
 FETCH c BULK COLLECT INTO  t_nume, t_name;
 CLOSE c;
 FOR i IN t_nume.FIRST .. t_nume.LAST LOOP
 DBMS_OUTPUT.put_line('Departamentul ' || t_name(i) ||
 ' are managerul '|| t_nume(i));
 END LOOP;
END;


7. Afisati numele angajatilor din departamantul IT.
DECLARE
 TYPE tab_name IS TABLE OF employees.last_name%type;
TYPE tab_pren IS TABLE OF employees.first_name%type;
 t_name tab_name;
t_pren tab_pren;
 CURSOR c IS
 SELECT e.first_name,e.last_name FROM departments d, employees e
 WHERE d.department_id=e.department_id and d.department_name like 'IT' ORDER BY last_name;
BEGIN
 OPEN c;
 FETCH c BULK COLLECT INTO   t_name, t_pren;
 CLOSE c;
 FOR i IN t_name.FIRST .. t_name.LAST LOOP
 DBMS_OUTPUT.put_line( t_name(i) ||
 '  '|| t_pren(i));
 END LOOP;
END;

//SIM Laborator 4


1. Creati un bloc PL/SQL care împarte doua numere introduse de la tastatura. Se va trata exceptia aparuta la împartirea prin 0.
DECLARE
a number;
b number;
BEGIN
a:=&a;
b:=&b;
 DBMS_OUTPUT.PUT_LINE(a/b);
EXCEPTION
 WHEN ZERO_DIVIDE THEN
 DBMS_OUTPUT.PUT_LINE('S-a impartit la 0!');
END;


2. Afisati numele unui departament identificat prin ID. ID-ul va fi introdus de la tastatura. Tratati exceptiile care pot sa apara.
DECLARE
 d_name departments.department_name%TYPE;
 id departments.department_id%TYPE;
BEGIN
 id:='&id';
 SELECT department_name INTO d_name FROM departments
 WHERE department_id=id;
 DBMS_OUTPUT.PUT_LINE(d_name || ' has id '|| id);
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('Nu exista departament cu acest id!');
 WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('Prea multe departamente cu acest id!');
END;

Bun
DECLARE

 e_id employees.employee_id%TYPE;
 d_name departments.department_name%TYPE;
 name departments.department_name%TYPE;
 nr number;
BEGIN

d_name:='&d_name';

SELECT department_name INTO name from departments
where lower(department_name)=lower(d_name);

 SELECT count(e.employee_id) INTO nr FROM employees e, departments d
 WHERE lower(d.department_name)=lower(d_name) and e.department_id=d.department_id;
 DBMS_OUTPUT.PUT_LINE(d_name || ' has '|| nr || ' employees!');

EXCEPTION

 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('Nu exista departament cu acest nume!');
 WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('Prea multe departamente cu acest id!');
END;


3. Afisati numele unui angajat identificat prin ID. ID-ul va fi introdus de la tastatura. Definiti o exceptie care se declanseaza atunci când se introduce un numar mai mic decât 100. Tratati si celelalte situatii care pot sa apara.
DECLARE
 mai_mic EXCEPTION;
 e_name employees.last_name%TYPE;
 id employees.employee_id%TYPE;
BEGIN
 id:='&id';
 IF id<100 THEN
 RAISE mai_mic;
 END IF;
 SELECT last_name INTO e_name FROM employees
 WHERE employee_id=id;
 DBMS_OUTPUT.PUT_LINE(e_name || ' has id '|| id);

EXCEPTION
 WHEN mai_mic THEN
 DBMS_OUTPUT.PUT_LINE('Id-ul este prea mic! Sunt permise doar id-uri mai mari de 100');
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('Nu exista departament cu acest id!');
 WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('Prea multe departamente cu acest id!');
END;


4. Afisati numarul de angajati dintr-un departament identificat prin nume. Numele se introduce de la tastatura.
DECLARE
 e_id employees.employee_id%TYPE;
 d_name departments.department_name%TYPE;
 nr number;
BEGIN
d_name:='&d_name';
 SELECT count(e.employee_id) INTO nr FROM employees e, departments d
 WHERE lower(d.department_name)=lower(d_name) and e.department_id=d.department_id;
 DBMS_OUTPUT.PUT_LINE(d_name || ' has '|| nr || ' employees!');

EXCEPTION

 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('Nu exista departament cu acest nume!');
 WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('Prea multe departamente cu acest id!');
END;


5. Creati un exemplu pentru tratarea exceptiei CURSOR_ALREADY_OPEN.
SET SERVEROUTPUT ON;
DECLARE
 d_id departments.department_id%TYPE;
 d_name departments.department_name%TYPE;
 CURSOR d_info is SELECT  department_id, department_name from     departments
 WHERE department_id>100;
BEGIN
 OPEN d_info;
 FETCH d_info into d_id, d_name;
 WHILE d_info%FOUND LOOP
 FETCH d_info into d_id, d_name;

 DBMS_OUTPUT.PUT_LINE('Department '||d_name || ' has the id '|| d_id);
 END LOOP;
 OPEN d_info;
EXCEPTION
 WHEN CURSOR_ALREADY_OPEN THEN
 DBMS_OUTPUT.PUT_LINE('The cursor is already opened!');
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('Nu exista departament cu acest id!');
 WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('Prea multe departamente cu acest id!');
END;

///Laborator 5 SIM


1. Scrieti o functie care returneaza ID-ul unei persoane identificata prin nume si prenume.
SET SERVEROUTPUT ON;
DECLARE
 v_nume employees.last_name%TYPE;
 --v_nume employees.first_name%TYPE;
 v_id employees.employee_id%type;
 FUNCTION f2 (p_nume IN employees.last_name%TYPE)
 RETURN NUMBER
 IS
 id employees.employee_id%type;
 BEGIN
 SELECT employee_id INTO id FROM employees
 WHERE upper(last_name) = upper(p_nume);
 RETURN id;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
 RETURN -1;
 WHEN TOO_MANY_ROWS THEN
 RETURN -2;
 WHEN OTHERS THEN
 RETURN -3;
 END f2;
BEGIN
 v_id:= f2('&v_nume');
 IF v_id>0 THEN
 DBMS_OUTPUT.PUT_LINE('Id-ul este ' || v_id);
 ELSE
 CASE v_id
 WHEN -1 THEN
 DBMS_OUTPUT.PUT_LINE('Nu exista niciun angajat cu numele dat');
 WHEN -2 THEN
 DBMS_OUTPUT.PUT_LINE('Exista mai multi angajati cu numele dat');
 ELSE
 DBMS_OUTPUT.PUT_LINE('A aparut o eroare!');
 END CASE;
 END IF;
END;


2. Scrieti o procedura care afi?eaza numele unei persoane identificata prin ID, precum si departamentul în care lucreaza.
DECLARE
 PROCEDURE p2
 IS
 v_nume employees.last_name%type;
 v_id employees.employee_id%type;
 v_dep departments.department_name%type;
 BEGIN
 SELECT last_name, department_name INTO v_nume, v_dep FROM departments d,  employees e
 WHERE e.employee_id = &v_id and e.department_id = d.department_id;
 DBMS_OUTPUT.PUT_LINE('Numele  este ' || v_nume || '  si departamentul este '|| v_dep);
 END p2;
BEGIN
 --apelul procedurii
  p2;
 END;


3. Scrieti o procedura stocata care mareste salariul unei persoane identificata prin ID astfel: daca persoana are 0.1-0.2 puncte comision, salariul creste cu 10%, iar daca persoana are peste 0.2 de puncte, salariul creste cu 25%. Procedura primeste ID-ul ca parametru de intrare si returneaza salariul actual si salariul modificat ca parametri de iesire. Aten?ie: modificarea se va face în tabelul XX_Angajati, iar numele procedurii stocate va fi de forma XX_nume.
/* CREATE PROCEDURE mpcc_nume(a_sal OUT NUMBER, m_sal OUT NUMBER)
IS
CP EMPLOYEES.COMMISSION_PCT%TYPE;
BEGIN
SELECT NVL(COMMISSION_PCT,0) , SALARY into cp, a_sal FROM MI_ANG
WHERE EMPLOYEE_ID= &ID;
IF(CP>=0.1 AND CP <0.2) THEN
DBMS_OUTPUT.PUT_LINE('Salariul vechi este ' || a_sal || '  si salariul nou  este '|| a_sal*1.1);
ELSIF(CP >=0.2) THEN
DBMS_OUTPUT.PUT_LINE('Salariul vechi este ' || a_sal || '  si salariul nou  este '|| a_sal*1.25);
END IF;

END mpcc_nume; */

create or replace PROCEDURE ioana (p_id IN number, p_sal_act out number, p_sal_mod out number)
 IS
 p_comm mi_ang.commission_pct%type;
 BEGIN
 SELECT nvl(commission_pct, 0), salary INTO p_comm, p_sal_act FROM mi_ang
where employee_id = p_id;

if p_comm <= 0.2 then
 update mi_ang set salary = salary + salary * 0.1 where employee_id = p_id;
else
if p_comm > 0.2 then
 update mi_ang set salary = salary + salary * 0.25 where employee_id = p_id;
end if;
end if;
select salary INTO p_sal_mod from mi_ang where employee_id = p_id;

 END ioana;

DECLARE
 v_id mi_ang.employee_id%type;
 v_sal_act mi_ang.salary%type;
 v_sal_mod mi_ang.salary%type;

BEGIN
 --apelul procedurii
 ioana(&v_id, v_sal_act, v_sal_mod);
 DBMS_OUTPUT.PUT_LINE('Salariul inainte de marire:  ' || v_sal_act || '.  Salariul dupa marire :  ' || v_sal_mod);
END;

4. Scrieti o func?ie stocata care pentru un anumit cod de departament, dat ca parametru, returneaza numarul salariatilor care lucreaza în el.

5. ?terge?i toate obiectele create.

//Laborator 6 SIM


1.	Scrie?i o procedura stocata care afi?eaza to?i angaja?ii al caror nume începe cu o anumita litera. Se va afi?a: numele (cu majuscule), prenumele ?i anul angajarii. Indica?ie: se vor utiliza func?iile SUBSTR, UPPER, EXTRACT, SQLERRM.

SET SERVEROUTPUT ON;
declare type tab_nume is table of varchar2(50);
tn tab_nume;
type tab_an is table of number;
ta tab_an;caracter char(1);
i integer;
procedure pStocata(l_nume out tab_nume,l_an out tab_an,c in char)
is
begin
 select concat(upper(last_name),concat(' ',first_name)) nume, extract(year from hire_date) bulk collect into l_nume,l_an from employees where substr(upper(last_name),0,1)=c;
end pStocata;
begin
caracter:='&caracter';
pStocata(tn,ta,upper(caracter));
for i in tn.first .. tn.last
 loop
dbms_output.put_line(tn(i)||' - '||ta(i));
end loop;
--dbms_output.put_line('Caracterul introdus: '||caracter);
end;

//	SIM Laborator 7


 1. Modificati exemplul 1 astfel încât trigger-ul sa nu permita modificari în zilele de joi/vineri, apoi efectuati o stergere din tabelul XX_angajati.
CREATE OR REPLACE TRIGGER mi_ex1
BEFORE INSERT OR UPDATE OR DELETE ON mi_angajati
BEGIN
IF TO_CHAR(SYSDATE, 'D')= 5 THEN
 RAISE_APPLICATION_ERROR(-20001,'Tabelul nu poate fi actualizat');
END IF;
END;
DELETE FROM mi_angajati WHERE employee_id<110


2. Eliminati trigger-ul creat anterior si testati exemplul 2.
drop trigger mi_ex1;
CREATE OR REPLACE TRIGGER mi_ex2
AFTER DELETE ON mi_angajati
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('A fost sters un angajat');
END;
DELETE FROM mi_angajati WHERE employee_id<110


 3. Creati un tabel de audit numit XX_Operatii_Angajati care sa contina urmatoarele coloane: DataModif DATE Operatie VARCHAR(20) ValInitiala NUMBER(8,2) ValFinala NUMBER(8,2) În acest tabel de audit vor fi stocate toate operatiile facute de utilizatori în tabela XX_angajati pe coloana salary. Realizati un trigger numit XX_monitorizare “sensibil” la INSERT, UPDATE, DELETE care sa se declanseze pentru fiecare linie modificata în parte si sa înregistreze fiecare operatie efectuata în tabelului de audit XX_Operatii_Angajati. Dupa realizarea trigger-ului modificati, inserati, respectiv stergeti linii din tabela XX_angajati.
create table mi_Operatii_Angajati (
DataModif DATE,
 Operatie VARCHAR(20),
 ValInitiala NUMBER(8,2),
 ValFinala NUMBER(8,2) );

set serveroutput on;
create or replace trigger mi_pb3
after insert or update or delete on mi_angajati
for each row
begin
if inserting then
insert into mi_Operatii_Angajati values(sysdate,'insert',:old.salary,:new.salary);
elsif deleting then
insert into mi_Operatii_Angajati values(sysdate,'delete',:old.salary,:new.salary);
elsif updating('salary') then
insert into mi_Operatii_Angajati values(sysdate,'update',:old.salary,:new.salary);
end if;
end;

insert into mi_angajati(first_name, last_name,email, phone_number,hire_date, job_id,salary, manager_id, department_id) values ('Ciprian', 'Stavovei', 'satv@yahoo.com','515.124.4269','22-12-2015'	, 2200 , 110, 108,100);

select * from mi_Operatii_Angajati
delete from mi_angajati
where employee_id = 110

update mi_angajati
set salary = 2000
where salary < 2000

//SIM LABORATOR 8



1. Creati un pachet care sa contina functii pentru calculul valorii medii a salariului pentru un anumit departament, pentru toti angajatii, respectiv pentru salariatii angajati într-un anumit an (dat ca parametru).
CREATE OR REPLACE PACKAGE mi_pachet1 IS
FUNCTION f_mediadep(dep departments.department_id%TYPE)
RETURN NUMBER;
FUNCTION f_media(hire number)
RETURN NUMBER;
END mi_pachet1;
CREATE OR REPLACE PACKAGE BODY mi_pachet1 IS
 FUNCTION f_mediadep(dep departments.department_id%TYPE)
 RETURN NUMBER
 IS
 numar NUMBER;
 BEGIN
 SELECT avg(salary) INTO numar FROM employees
 WHERE department_id=dep;
 RETURN numar;
 END f_mediadep;
Function f_total
return number is
total number;
BEGIN
SELECT avg(salary) INTO total
 FROM employees;
return total;
end f_total;
FUNCTION f_media(hire number)
 RETURN NUMBER
 IS
 sal NUMBER;
 BEGIN
 SELECT avg(salary) INTO sal
 FROM employees WHERE extract(year from hire_date) = hire;
 RETURN sal;
 END f_media;
END mi_pachet1;
SELECT mi_pachet1.f_mediadep(80) FROM DUAL;
SELECT mi_pachet1.f_media(1999) FROM DUAL;
SELECT mi_pachet1.f_total FROM DUAL;

SET SERVEROUTPUT On
BEGIN
DBMS_OUTPUT.PUT_LINE('Media salariilor  din departament: '|| mi_pachet1.f_mediadep(80));
DBMS_OUTPUT.PUT_LINE('Media salariilor celor angajati intr-un anumit an: '|| mi_pachet1.f_media(&a));
DBMS_OUTPUT.PUT_LINE('Media salariilor este : '|| mi_pachet1.f_total);
END;




2. Creati un pachet care contine:
create table mi_dep as select * from departments
a. procedurile ADD_DEP, MODIF_DEP si DEL_DEP corespunzatoare operatiilor de adaugare, actualizare (a numelui) si stergere a unui departament din tabelul XX_dep
b. functia GET_DEP care determina denumirea unui departament, pe baza codului acestuia
CREATE OR REPLACE PACKAGE mi_pachet2 IS
	FUNCTION get_dep(dep mi_dep.department_id%TYPE)
                    RETURN mi_dep.department_name%TYPE;
	PROCEDURE add_dep(dep mi_dep%ROWTYPE);
	PROCEDURE mod_dep(dep mi_dep.department_id%TYPE, nume mi_dep.department_name%TYPE);
	PROCEDURE del_dep(dep mi_dep.department_id%TYPE);
END mi_pachet2;

CREATE OR REPLACE PACKAGE BODY mi_pachet2 IS
FUNCTION get_dep(dep mi_dep.department_id%TYPE)
 RETURN mi_dep.department_name%TYPE
 IS
 nume  mi_dep.department_name%TYPE;
 BEGIN
 SELECT department_name into nume FROM mi_dep
 WHERE department_id=dep;
 RETURN nume;
EXCEPTION
		WHEN NO_DATA_FOUND THEN return 'departamentul nu exista!';
 END get_dep;


PROCEDURE add_dep(dep mi_dep%ROWTYPE) AS
	BEGIN
	INSERT INTO mi_dep VALUES(dep.DEPARTMENT_ID, dep.DEPARTMENT_NAME, dep.MANAGER_ID, dep.LOCATION_ID);
	END add_dep;
PROCEDURE mod_dep(dep mi_dep.department_id%TYPE, nume mi_dep.department_name%TYPE) AS
	BEGIN
	UPDATE mi_dep
	SET department_name = nume
	WHERE department_id = dep;
	END mod_dep;
PROCEDURE del_dep(dep mi_dep.department_id%TYPE) AS
	BEGIN
	DELETE FROM mi_dep
	WHERE department_id = dep;
	END del_dep;

END mi_pachet2;

SELECT mi_pachet2.get_dep(80) FROM DUAL;


//Sim Laborator 9


1. Creati un script PL/SQL care apeleaza functia XX_numar pentru a afisa numarul salariatilor din tabelul employees al caror salariu depaseste valoarea 10000, respectiv numarul salariatilor din tabelul XX_emp al caror salariu depaseste valoarea 8000.
CREATE OR REPLACE FUNCTION mi_numar(val NUMBER, tabel VARCHAR2)
RETURN NUMBER IS
 sir VARCHAR2(500);
 rez NUMBER;
 BEGIN
 sir := 'SELECT COUNT(*) FROM ' || tabel ||
 ' WHERE salary >= :x';
 EXECUTE IMMEDIATE sir INTO rez USING val;
 RETURN rez;
END mi_numar;

SET SERVEROUTPUT ON;
 BEGIN
   dbms_output.put_line('Numar salariati ' || mi_numar(10000, 'employees'));
END;

SET SERVEROUTPUT ON
BEGIN
   dbms_output.put_line('Numar salariati ' || mi_numar(8000, 'mi_emp'));
END;



 2. Utilizând SQL dinamic nativ, sa se creeze tabelul XX_tab(col VARCHAR2(15)), apoi sa se insereze în acesta numele departamentelor din tabelul XX_dep, sa se tipareasca continutul tabelului utilizând un bloc anonim, iar în final sa se stearga tabelul creat.
create table mi_tab(col VARCHAR2(15))

DECLARE
bloc VARCHAR2(200);
BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO mi_tab SELECT department_name FROM mi_dep';
        bloc := 'BEGIN
FOR i IN (SELECT * FROM mi_tab) LOOP
DBMS_OUTPUT.PUT_LINE (i.col);
END LOOP;
END;';
	EXECUTE IMMEDIATE bloc;
	EXECUTE IMMEDIATE 'DROP TABLE mi_tab';
END;


3. Creati un pachet care contine: a. o functie prin care se vor obtine toti angajatii care au un anumit job, dat ca parametru b. o procedura prin care se mareste cu o anumita valoare (data ca parametru) salariile angajatilor care au un anumit job (dat ca parametru)
SET SERVEROUTPUT ON;
CREATE OR REPLACE PACKAGE mi_pachet AS
TYPE refcursor IS REF CURSOR;
FUNCTION f1 (id employees.job_id%TYPE) RETURN refcursor;
PROCEDURE p1(valoare NUMBER,id employees.job_id%TYPE);
END mi_pachet;


CREATE OR REPLACE PACKAGE BODY mi_pachet AS
FUNCTION f1 (id employees.job_id%TYPE) RETURN refcursor IS
rez refcursor;
comanda VARCHAR2(500);
BEGIN
comanda := 'SELECT first_name FROM employees  WHERE  upper(job_id)=' || upper (id);
OPEN rez FOR comanda;
RETURN rez;
END;
PROCEDURE p1(valoare NUMBER,id employees.job_id%TYPE) is
begin
	execute immediate 'UPDATE mi_emp set salary=salary +' || valoare || ' where upper( job_id)='|| upper( id);
end p1;
END mi_pachet;

set serveroutput on;
DECLARE
bloc VARCHAR2(500);
BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO cioata_tab SELECT department_name FROM
cioata_dep';
        bloc := 'BEGIN
FOR i IN (SELECT * FROM cioata_tab) LOOP
DBMS_OUTPUT.PUT_LINE (i.col);
END LOOP;
END;';
EXECUTE IMMEDIATE bloc;
EXECUTE IMMEDIATE 'DROP TABLE cioata_tab';
END;