PL/SQL Procedure/Function to display total salary without using sum function

SQL> CREATE OR REPLACE PROCEDURE P_TOTAL_SALARY (I_DEPTNO NUMBER)
  2  AS
  3     V_SAL_SUM   NUMBER := 0;
  4  BEGIN
  5     FOR K IN (SELECT SAL, COMM
  6                 FROM EMP
  7                WHERE DEPTNO = I_DEPTNO)
  8     LOOP
  9        V_SAL_SUM := V_SAL_SUM + NVL (K.SAL, 0) + NVL (K.COMM, 0);
 10     END LOOP;
 11
 12     DBMS_OUTPUT.PUT_LINE ('Total Salary Of Employee=' || V_SAL_SUM);
 13  EXCEPTION
 14     WHEN OTHERS
 15     THEN
 16        DBMS_OUTPUT.PUT_LINE (
 17           'Oracel Error Code-' || SQLCODE || '~Error Message-' || SQLERRM);
 18  END;
 19  /

Procedure created.

SQL> -- Execution
SQL> SET SERVEROUTPUT ON
SQL> EXEC P_TOTAL_SALARY(10);
Total Salary Of Employee=8750

PL/SQL procedure successfully completed.


SQL> BEGIN P_TOTAL_SALARY(10); END;
  2  /
Total Salary Of Employee=8750

PL/SQL procedure successfully completed.

SQL>
SQL> -- Using SUM function
SQL> SELECT SUM(NVL(SAL,0)+NVL(COMM,0)) AS TOTAL_SALARY FROM EMP WHERE DEPTNO=10;

TOTAL_SALARY
------------
        8750

SQL>

SQL> CREATE OR REPLACE FUNCTION F_TOTAL_SALRAY (I_DEPTNO NUMBER)
  2     RETURN NUMBER
  3  AS
  4     V_SAL_SUM   NUMBER := 0;
  5  BEGIN
  6     FOR K IN (SELECT SAL, COMM
  7                 FROM EMP
  8                WHERE DEPTNO = I_DEPTNO)
  9     LOOP
 10        V_SAL_SUM := V_SAL_SUM + NVL (K.SAL, 0) + NVL (K.COMM, 0);
 11     END LOOP;
 12
 13     DBMS_OUTPUT.PUT_LINE ('Total Salary Of Employee=' || V_SAL_SUM);
 14     RETURN V_SAL_SUM;
 15  EXCEPTION
 16     WHEN OTHERS
 17     THEN
 18        RETURN 0;
 19        DBMS_OUTPUT.PUT_LINE (
 20           'Oracel Error Code-' || SQLCODE || '~Error Message-' || SQLERRM);
 21  END;
 22  /

Function created.

SQL> SELECT F_TOTAL_SALRAY(10) FROM DUAL;

F_TOTAL_SALRAY(10)
------------------
              8750

Total Salary Of Employee=8750
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     V_DEPTNO      NUMBER := 10;
  3     V_TOTAL_SAL   NUMBER := NULL;
  4  BEGIN
  5     V_TOTAL_SAL := F_TOTAL_SALRAY (V_DEPTNO);
  6     DBMS_OUTPUT.PUT_LINE ('Total Salary Of Employee=' || V_TOTAL_SAL);
  7  END;
  8  /
Total Salary Of Employee=8750
Total Salary Of Employee=8750

PL/SQL procedure successfully completed.

SQL>