SET serveroutput ON;

--ZADANIE 1
DECLARE
  CURSOR cur IS
    SELECT last_name, salary
    FROM employees;
BEGIN
  FOR i IN cur LOOP
    DBMS_OUTPUT.PUT_LINE('PLACA PRACOWNIKA ' || i.last_name || ' WYNOSI '
      || i.salary || ' ZL');
  END LOOP;
END;
/

--ZADANIE 2
CREATE OR REPLACE PROCEDURE proc(l_name employees.last_name%TYPE) IS
  invalid_name EXCEPTION;
BEGIN
  UPDATE jobs
  SET min_salary = min_salary * 1.1
  WHERE job_id IN (SELECT job_id
                   FROM employees
                   WHERE LOWER(last_name) = LOWER(l_name));

  IF SQL%ROWCOUNT = 0 THEN
    RAISE invalid_name;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Zaktualizowano ' || SQL%ROWCOUNT || ' wierszy');
  END IF;
EXCEPTION
  WHEN invalid_name THEN
    DBMS_OUTPUT.PUT_LINE('Nie ma pracownika o takim nazwisku!');
END;
/

--wywolanie prawidlowe
BEGIN
  proc('King');
END;
/

--wywolanie nieprawidlowe
BEGIN
  proc('kowalski');
END;
/

--ZADANIE 3
CREATE OR REPLACE FUNCTION fun(dep departments.department_name%TYPE)
  RETURN NUMBER IS
  avg_salary NUMBER;
BEGIN
  SELECT AVG(e.salary)
  INTO avg_salary
  FROM employees e JOIN departments d ON e.department_id = d.department_id
  WHERE d.department_name = dep;

  IF avg_salary IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Nie ma dzialu o takiej nazwie!');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Srednia placa w dziale ' || dep ||
      ' to: ' || avg_salary);
  END IF;

RETURN avg_salary;
END;
/

--wywolanie prawidlowe
BEGIN
  DBMS_OUTPUT.PUT_LINE(fun('IT'));
END;
/

--wywolanie nieprawidlowe
BEGIN
  DBMS_OUTPUT.PUT_LINE(fun('AAAAAAAA'));
END;
/