PLSQL lab 4

--SET SERVEROUTPUT ON
--DECLARE
--    v_nume employees.last_name%TYPE := Initcap('&p_nume');
--
--    FUNCTION f1 RETURN NUMBER IS
--        salariu employees.salary%TYPE;
--    BEGIN
--        SELECT salary INTO salariu
--        FROM employees
--        WHERE last_name = v_nume;
--        RETURN salariu;
--    EXCEPTION
--        WHEN NO_DATA_FOUND THEN
--            DBMS_OUTPUT.PUT_LINE('Nu exista angajati cu numele dat');
--        WHEN TOO_MANY_ROWS THEN
--            DBMS_OUTPUT.PUT_LINE('Exista mai multi angajati ' || 'cu numele dat');
--        WHEN OTHERS THEN
--            DBMS_OUTPUT.PUT_LINE('Alta eroare');
--    END f1;
--
--BEGIN
--    DBMS_OUTPUT.PUT_LINE('Salariul este ' || f1);
--END;
--/

--CREATE OR REPLACE FUNCTION f2_testing
--    (v_nume employees.last_name%TYPE DEFAULT 'Bell')
--RETURN NUMBER IS
--    salariu employees.salary%TYPE;
--    BEGIN
--        SELECT salary INTO salariu
--        FROM employees
--        WHERE last_name = v_nume;
--        RETURN salariu;
--    EXCEPTION
--        WHEN NO_DATA_FOUND THEN
--            RAISE_APPLICATION_ERROR(-20000,'Nu exista angajati cu numele dat');
--        WHEN TOO_MANY_ROWS THEN
--            RAISE_APPLICATION_ERROR(-20001,'Exista mai multi angajati cu numele dat');
--        WHEN OTHERS THEN
--            RAISE_APPLICATION_ERROR(-20002,'Alta eroare!');
--END f2_testing;
--/

--BEGIN
--    DBMS_OUTPUT.PUT_LINE('salariul este '|| f2_testing('Ozer'));
--END;
--/

--SELECT f2_testing FROM DUAL;
--SELECT f2_testing('King') FROM DUAL;

--DECLARE
--    v_nume employees.last_name%TYPE := Initcap('&p_nume');
--
--    PROCEDURE p3
--    IS
--        salariu employees.salary%TYPE;
--    BEGIN
--        SELECT salary INTO salariu
--        FROM employees
--        WHERE last_name = v_nume;
--        DBMS_OUTPUT.PUT_LINE('Salariul este '|| salariu);
--
--    EXCEPTION
--        WHEN NO_DATA_FOUND THEN
--            DBMS_OUTPUT.PUT_LINE('Nu exista angajati cu numele dat');
--        WHEN TOO_MANY_ROWS THEN
--            DBMS_OUTPUT.PUT_LINE('Exista mai multi angajati cu numele dat');
--        WHEN OTHERS THEN
--            DBMS_OUTPUT.PUT_LINE('Alta eroare');
--    END p3;
--
--BEGIN
--    p3;
--END;
--/
--CREATE OR REPLACE PROCEDURE p4_testing
--        (v_nume employees.last_name%TYPE)
--    IS
--        salariu employees.salary%TYPE;
--    BEGIN
--        SELECT salary INTO salariu
--        FROM employees
--        WHERE last_name = v_nume;
--        DBMS_OUTPUT.PUT_LINE('Salariul este '|| salariu);
--
--    EXCEPTION
--        WHEN NO_DATA_FOUND THEN
--            RAISE_APPLICATION_ERROR(-20000,'Nu exista angajati cu numele dat');
--        WHEN TOO_MANY_ROWS THEN
--            RAISE_APPLICATION_ERROR(-20001,'Exista mai multi angajati cu numele dat');
--        WHEN OTHERS THEN
--            RAISE_APPLICATION_ERROR(-20002,'Alta eroare!');
--END p4_testing;
--/

--BEGIN
--    p4_testing('Bell');
--END;
--/
--
--CREATE OR REPLACE PROCEDURE p5_testing (nr IN OUT NUMBER) IS
--BEGIN
--    SELECT manager_id INTO nr
--    FROM employees
--    WHERE employee_id = nr;
--END p5_testing;
--/
--
--VARIABLE ang_man NUMBER
--BEGIN
--    :ang_man:=200;
--END;
--/

EXECUTE p5_testing(:ang_man)
PRINT ang_man

DECLARE
    nume employees.last_name%TYPE;
PROCEDURE p6 (  rezultat    OUT employees.last_name%TYPE,
                comision    IN  employees.commission_pct%TYPE := NULL,
                cod         IN  employees.employee_id%TYPE := NULL)
    IS
    BEGIN
    IF (comision IS NOT NULL) THEN
        SELECT last_name
        INTO rezultat
        FROM employees
        WHERE commission_pct = comision;
        DBMS_OUTPUT.PUT_LINE('numele salariatului care are comisionul ' || comision || ' este ' || rezultat);

    ELSE
        SELECT last_name
        INTO rezultat
        FROM employees
        WHERE employee_id = cod;
        DBMS_OUTPUT.PUT_LINE('numele salariatului avand codul ' || cod || ' este ' || rezultat);
    END IF;
END p6;

BEGIN
    p6(nume,0.4);
    p6(nume,cod=>200);
END;
/