Stored Procedure & Function

Create Stored Procedure

CREATE OR REPLACE PROCEDURE FINDMIN (
    V_NUM1 IN NUMBER,
    V_NUM2 IN NUMBER,
    V_RES  OUT NUMBER
) IS
BEGIN
    IF V_NUM1 > V_NUM2 THEN
        V_RES := V_NUM2;
    ELSE
        V_RES := V_NUM1;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Min value is:' || V_RES);
END;

Call Stored Procedure

BEGIN
    FINDMIN(15, 55);
END;

Create Function

CREATE OR REPLACE FUNCTION CHECK_SAL (
    EMP_NO EMPLOYEES.EMPLOYEE_ID%TYPE
) RETURN BOOLEAN IS

    V_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE;
    V_SAL     EMPLOYEES.SALARY%TYPE;
    V_AVG_SAL EMPLOYEES.SALARY%TYPE;
BEGIN
    SELECT SALARY, DEPARTMENT_ID
    INTO V_SAL, V_DEPT_ID
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = EMP_NO;

    SELECT AVG(SALARY)
    INTO V_AVG_SAL
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = V_DEPT_ID;

    IF V_SAL > V_AVG_SAL THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;

Call Function

DECLARE
    V_EMP_ID NUMBER := &EMP_ID;
BEGIN
    IF ( CHECK_SAL(V_EMP_ID) IS NULL ) THEN
        DBMS_OUTPUT.PUT_LINE('NULL');
    ELSIF ( CHECK_SAL(V_EMP_ID) ) THEN
        DBMS_OUTPUT.PUT_LINE('>');
    ELSE
        DBMS_OUTPUT.PUT_LINE('<');
    END IF;
END;