1.  SET SERVEROUTPUT ON;

DECLARE
v_dep NUMBER;
v_sr DECIMAL(6,2);
CURSOR cur_dep IS SELECT AVG(salary) AS srednia, department_name FROM employees, departments WHERE employees.department_id = departments.department_id ORDER BY srednia ASC;

BEGIN
OPEN cur_dep;
LOOP
    FETCH cur_dep INTO v_sr, v_dep;
    DBMS_OUTPUT.PUT_LINE(v_dep);
    EXIT WHEN cur_dep%ROWCOUNT = 3
END LOOP;
CLOSE cur_dep;
END;

2.  CREATE OR REPLACE PROCEDURE miasto(v_miasto VARCHAR2(20))
IS
CURSOR cur_miasto IS SELECT department_name, city FROM departments, locations WHERE departments.location_id = locations.location_id AND city = v_miasto;
v_dep VARCHAR2(20);
v_miast VARCHAR2(20);
miastox EXCEPTION;

BEGIN
LOOP
    OPEN cur_miasto;
    FETCH cur_miasto INTO v_dep, v_miast;
    EXIT WHEN cur_miasto%NOTFOUND;
    EXCEPTION
IF NO_DATA_FOUND THEN
    RAISE miastox;
    END IF;
END LOOP;
EXCEPTION
WHEN miastox THEN
DBMS_OUTPUT.PUT_LINE(„Brak wydziału w podanym mieście”);
END;
/-nowa karta

EXEC miasto;


4.  CREATE OR REPLACE PROCEDURE podwyzka(id_dep VARCHAR2(5), pens DECIMAL(2,1) DEFAULT 0.1)
IS
BEGIN
UPDATE employees SET salary = salary + (salary*pens) WHERE department_id = id_dep;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20001, ‘Złe ID wydziału’);
END;
/-nowa karta

EXEC podwyzka;


5.  CREATE OR REPLACE FUNCTION staz(p_id NUMBER) RETURN NUMBER IS

CURSOR cur_daty IS SELECT MONTHS_BETWEEN(TO_DATE(hire_date, ‘MM-DD-YYYY’), (TO_DATE(CURRENT_DATE, ‘MM-DD-YYYY’)) FROM employees WHERE employee_id = p_id;
CURSOR cur_daty_wszystkie IS SELECT MONTHS_BETWEEN(TO_DATE(hire_date, ‘MM-DD-YYYY’), (TO_DATE(CURRENT_DATE, ‘MM-DD-YYYY’)) FROM employees;
v_data NUMBER;
v_data_all NUMBER;
suma NUMBER := 0;
ile NUMBER := 0;
srednia NUMBER;

BEGIN
OPEN cur_daty;
FETCH cur_daty INTO v_data;
RETURN v_data;
CLOSE cur_daty;
OPEN cur_daty_wszystkie;
LOOP
FETCH cur_daty_wszystkie INTO v_data_all;
suma := suma + v_data_all;
ile := ile + 1;
EXIT WHEN cur_daty_wszystkie%NOTFOUND;
END LOOP;
srednia := suma/ile;
CLOSE cur_daty_wszystkie;
DBMS_OUTPUT.PUT_LINE(‘Średni staż wszystkich pracowników wynosi ‘ || srednia);
END;
/-nowa karta

DBMS_OUTPUT.PUT_LINE(staz(id_jakies));



6.  CREATE OR REPLACE FUNCTION dodatki(p_id NUMBER) RETURN NUMBER IS

CURSOR cur_dodatki IS SELECT commission_pct, MONTHS_BETWEEN(TO_DATE(hire_date, ‘MM-DD-YYYY’), (TO_DATE(CURRENT_DATE, ‘MM-DD-YYYY’)) FROM employees WHERE employee_id = p_id;
v_dodatek NUMBER;
v_data NUMBER;
v_suma NUMBER := 0;

BEGIN
OPEN cur_dodatki;
FETCH cur_dodatki INTO v_dodatek, v_data;
v_suma := v_data * 0.01;
RETURN v_suma;
CLOSE cur_dodatki;
END;
/-nowa karta

DBMS_OUTPUT.PUT_LINE(dodatki(id_jakies));