-- zmienic check na jakas inna
--zad1

DROP TABLE log;
CREATE TABLE log (
date_of_action DATE,
user_id VARCHAR2(20),
package_name VARCHAR2(30)
);

CREATE OR REPLACE PACKAGE emp_admin AUTHID DEFINER AS
-- Deklaracja typu publicznego, kursora i wyjątku:
TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);
CURSOR desc_salary RETURN EmpRecTyp;
invalid_salary EXCEPTION;
-- Deklaracja publicznych podprogramów:
FUNCTION hire_employee (
last_name VARCHAR2,
first_name VARCHAR2,
email VARCHAR2,
phone_number VARCHAR2,
job_id VARCHAR2,
salary NUMBER,
commission_pct NUMBER,
manager_id NUMBER,
department_id NUMBER
) RETURN NUMBER;
-- Przeciążenie uprzednio zdefiniowanych
-- publicznych podprogramów:
PROCEDURE fire_employee (emp_id NUMBER);
PROCEDURE fire_employee (emp_email VARCHAR2);
PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);
FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp;
END emp_admin;


CREATE OR REPLACE PACKAGE BODY emp_admin AS
number_hired NUMBER; -- zmienna prywatna widoczna tylko w tym pakiecie
-- Zdefiniuj kursor zadeklarowany w specyfikacji pakietu:
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT employee_id, salary
FROM employees
ORDER BY salary DESC;
-- Zdefiniuj podprogramy zadeklarowane w specyfikacji pakietu:
FUNCTION hire_employee (
last_name VARCHAR2,
first_name VARCHAR2,
email VARCHAR2,
phone_number VARCHAR2,
job_id VARCHAR2,
salary NUMBER,
commission_pct NUMBER,
manager_id NUMBER,
department_id NUMBER
) RETURN NUMBER
IS
new_emp_id NUMBER;
BEGIN
new_emp_id := employees_seq.NEXTVAL;
INSERT INTO employees (
employee_id,
last_name,
first_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
)
VALUES (
new_emp_id,
hire_employee.last_name,
hire_employee.first_name,
hire_employee.email,
hire_employee.phone_number,
SYSDATE,
hire_employee.job_id,
hire_employee.salary,
hire_employee.commission_pct,
hire_employee.manager_id,
hire_employee.department_id
);
number_hired := number_hired + 1;
DBMS_OUTPUT.PUT_LINE('Liczba zatrudnionych pracowników: '
|| TO_CHAR(number_hired) );
RETURN new_emp_id;
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM employees WHERE employee_id = emp_id;
END fire_employee;
PROCEDURE fire_employee (emp_email VARCHAR2) IS
BEGIN
DELETE FROM employees WHERE email = emp_email;
END fire_employee;
-- Zdefiniuj prywatną funkcję dostępną tylko w pakiecie:
FUNCTION sal_ok (
jobid VARCHAR2,
sal NUMBER
) RETURN BOOLEAN
IS
min_sal NUMBER;
max_sal NUMBER;
BEGIN
SELECT MIN(salary), MAX(salary)
INTO min_sal, max_sal
FROM employees
WHERE job_id = jobid;
RETURN (sal >= min_sal) AND (sal <= max_sal);
END sal_ok;
PROCEDURE raise_salary (
emp_id NUMBER,
amount NUMBER
)
IS
sal NUMBER(8,2);
jobid VARCHAR2(10);
BEGIN
SELECT job_id, salary INTO jobid, sal
FROM employees
WHERE employee_id = emp_id;
IF sal_ok(jobid, sal + amount) THEN -- Wywołaj prywatną funkcję
UPDATE employees
SET salary = salary + amount
WHERE employee_id = emp_id;
ELSE
RAISE invalid_salary;
END IF;
EXCEPTION
WHEN invalid_salary THEN
DBMS_OUTPUT.PUT_LINE ('Wynagrodzenie jest poza określonym zakresem.');
END raise_salary;
FUNCTION nth_highest_salary (
n NUMBER
) RETURN EmpRecTyp
IS
emp_rec EmpRecTyp;
BEGIN
OPEN desc_salary;
FOR i IN 1..n LOOP
FETCH desc_salary INTO emp_rec;
END LOOP;
CLOSE desc_salary;
RETURN emp_rec;
END nth_highest_salary;
BEGIN -- część inicjalizacyjna ciała pakietu
INSERT INTO log (date_of_action, user_id, package_name)
VALUES (SYSDATE, USER, 'EMP_ADMIN');
number_hired := 0;
END emp_admin;


DECLARE
new_emp_id NUMBER(6);
BEGIN
new_emp_id := emp_admin.hire_employee (
'Belden',
'Enrique',
'EBELDEN',
'555.111.2222',
'ST_CLERK',
2500,
.1,
101,
110
);
DBMS_OUTPUT.PUT_LINE ('ID pracownika: ' || TO_CHAR(new_emp_id));
emp_admin.raise_salary (new_emp_id, 100);
DBMS_OUTPUT.PUT_LINE (
'10. Najwyższe wynagrodzenie to: '||
TO_CHAR (emp_admin.nth_highest_salary(10).sal) ||
', należy do pracownika: ' ||
TO_CHAR (emp_admin.nth_highest_salary(10).emp_id)
);
emp_admin.fire_employee(new_emp_id);
-- Możesz również usunąć nowo dodanego pracownika w następujący sposób:
-- emp_admin.fire_employee ('EBELDEN');
END;


--zad2

CREATE TABLE AUTO_KOMIS(
   nr_rej VARCHAR2
   PRIMARY KEY( one or more columns )
);