DBMS task 3

CREATE OR REPLACE PACKAGE dept_pack AS
    FUNCTION count_dept RETURN INT;
    PROCEDURE total_salary(
        department IN VARCHAR,
        res OUT NUMBER);
    PROCEDURE total_comm(
        department IN VARCHAR,
        res OUT NUMBER);
END dept_pack;​

CREATE OR REPLACE PACKAGE BODY dept_pack AS
    FUNCTION count_dept RETURN INT
    IS
    BEGIN
        DECLARE res INT;
        BEGIN
            SELECT COUNT(*) INTO res FROM dept;
            RETURN res;
        END;
    EXCEPTION
        WHEN OTHERS THEN
            log_info(SUBSTR(SQLERRM, 1, 100), 'dept_pack.count_dept');
    END count_dept;
    PROCEDURE total_salary(
        department IN VARCHAR,
        res OUT NUMBER)
    IS
    BEGIN
        WITH joined AS (SELECT emp.sal AS sal, dept.dname AS name FROM emp INNER JOIN dept ON emp.deptno = dept.deptno)
            SELECT SUM(sal) INTO res FROM joined WHERE name = department;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            log_info(SUBSTR(SQLERRM, 1, 100), 'dept_pack.total_salary');
    END total_salary;
    PROCEDURE total_comm(
        department IN VARCHAR,
        res OUT NUMBER)
    IS
    BEGIN
        WITH joined AS (SELECT emp.comm AS comm, dept.dname AS name FROM emp INNER JOIN dept ON emp.deptno = dept.deptno)
            SELECT SUM(comm) INTO res FROM joined WHERE name = department;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            log_info(SUBSTR(SQLERRM, 1, 100), 'dept_pack.total_comm');
    END total_comm;
END dept_pack;​

CREATE OR REPLACE PACKAGE emp_pack AS
    PROCEDURE count_emp(
        res OUT NUMBER);
    PROCEDURE raise_all(
        percent IN NUMBER);
    PROCEDURE raise_position(
        position IN VARCHAR2,
        percent IN NUMBER);
END emp_pack;​

CREATE OR REPLACE PACKAGE BODY emp_pack AS
    PROCEDURE count_emp(
        res OUT NUMBER)
    IS
    BEGIN
        SELECT COUNT(*) INTO res FROM emp;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            log_info(SUBSTR(SQLERRM, 1, 100), 'emp_pack.count_emp');
    END count_emp;
    PROCEDURE raise_all(
        percent IN NUMBER)
    IS
    BEGIN
        UPDATE emp SET sal = sal + sal * percent / 100;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            log_info(SUBSTR(SQLERRM, 1, 100), 'emp_pack.raise_all');
    END raise_all;
    PROCEDURE raise_position(
        position IN VARCHAR2,
        percent IN NUMBER)
    IS
    BEGIN
        UPDATE emp SET sal = sal + sal * percent / 100 WHERE job = position;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            log_info(SUBSTR(SQLERRM, 1, 100), 'emp_pack.raise_position');
    END raise_position;
END emp_pack;​