--SELECT * FROM EMP;

--Ex1
CREATE TABLE top_dogs( name VARCHAR2(25), salary NUMBER(11,2));

SET SERVEROUT ON;
ACCEPT p_emp PROMPT 'Introduceti nume angajat: '
DECLARE
TYPE name_table_tip IS TABLE OF
emp.ename%TYPE INDEX BY BINARY_INTEGER;
var_name_table name_table_tip;

TYPE sal_table_tip IS TABLE OF
emp.sal%TYPE INDEX BY BINARY_INTEGER;
var_sal_table sal_table_tip;

BEGIN
SELECT ename, sal INTO var_name_table(1), var_sal_table(1) FROM emp WHERE LOWER(ename) = LOWER('&p_emp');
DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_name_table(1));
DBMS_OUTPUT.PUT_LINE('Salariu angajat: ' || var_sal_table(1));

INSERT INTO top_dogs VALUES(var_name_table(1), var_sal_table(1));
END;
/
SELECT * FROM top_dogs;


--Suplimentar
SET SERVEROUT ON;

DECLARE
TYPE emp_hire_record IS RECORD(
name emp.ename%TYPE,
hire_date emp.hiredate%TYPE
);

TYPE emp_hire_table IS TABLE OF
emp_hire_record INDEX BY BINARY_INTEGER;
var_emp_hire_table emp_hire_table;

BEGIN
SELECT ename, hiredate INTO var_emp_hire_table(1).name, var_emp_hire_table(1).hire_date FROM emp WHERE LOWER(ename) = LOWER('BLAKE');
SELECT ename, hiredate INTO var_emp_hire_table(2).name, var_emp_hire_table(2).hire_date FROM emp WHERE LOWER(ename) = LOWER('SCOTT');
SELECT ename, hiredate INTO var_emp_hire_table(3).name, var_emp_hire_table(3).hire_date FROM emp WHERE LOWER(ename) = LOWER('KING');
SELECT ename, hiredate INTO var_emp_hire_table(4).name, var_emp_hire_table(4).hire_date FROM emp WHERE LOWER(ename) = LOWER('FORD');

DBMS_OUTPUT.PUT_LINE('BEFORE:');
DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_emp_hire_table(1).name || ' Hire_date: ' || var_emp_hire_table(1).hire_date);
DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_emp_hire_table(2).name || ' Hire_date: ' || var_emp_hire_table(2).hire_date);
DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_emp_hire_table(3).name || ' Hire_date: ' || var_emp_hire_table(3).hire_date);
DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_emp_hire_table(4).name || ' Hire_date: ' || var_emp_hire_table(4).hire_date);
DBMS_OUTPUT.PUT_LINE('AFTER:');
FOR i IN 1 .. 4 LOOP
IF EXTRACT(day FROM var_emp_hire_table(i).hire_date)<>1 THEN

var_emp_hire_table(i).hire_date:=TRUNC(var_emp_hire_table(i).hire_date, 'MM');
DBMS_OUTPUT.PUT_LINE('Nu este angajat pe 1: ' || var_emp_hire_table(i).name);
ELSE
DBMS_OUTPUT.PUT_LINE('Angajat pe 1: ' || var_emp_hire_table(i).name);
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_emp_hire_table(1).name || ' Hire_date: ' || var_emp_hire_table(1).hire_date);
DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_emp_hire_table(2).name || ' Hire_date: ' || var_emp_hire_table(2).hire_date);
DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_emp_hire_table(3).name || ' Hire_date: ' || var_emp_hire_table(3).hire_date);
DBMS_OUTPUT.PUT_LINE('Nume angajat:' || var_emp_hire_table(4).name || ' Hire_date: ' || var_emp_hire_table(4).hire_date);

END;
/