Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to assign a value of zero if null.

953957Aug 27 2012 — edited Aug 27 2012
I have a package, procedure and many functions. I am trying to get the DBMS output to list a value for each, if it is null, assign it a zero and move on.
I cannot find any examples of where I can do this. Any ideas on how I can assign a zero when there is no value in the table?
CREATE OR REPLACE PACKAGE emp_net_pay IS
  PROCEDURE emp_tot_sal  (v_empno IN NUMBER);
  FUNCTION find_emp      (v_empno IN NUMBER) RETURN BOOLEAN;
  FUNCTION tot_sal_com   (v_empno IN NUMBER) RETURN NUMBER;
  FUNCTION bef_tax_ded   (emp_no IN NUMBER)  RETURN NUMBER;
  FUNCTION temp_sal_btd  (emp_no IN NUMBER)  RETURN NUMBER;
  FUNCTION tax_emp_sal   (emp_no IN NUMBER)  RETURN NUMBER;
  FUNCTION sal_aft_tax   (emp_no IN NUMBER)  RETURN NUMBER;
  FUNCTION aft_tax_ded   (emp_no IN NUMBER)  RETURN NUMBER;
  FUNCTION tot_net_sal   (emp_no IN NUMBER)  RETURN NUMBER;
END emp_net_pay;
/
CREATE OR REPLACE PACKAGE BODY emp_net_pay IS
--Procedure calculates totals: Salary plus commission
PROCEDURE emp_tot_sal (v_empno IN NUMBER)
IS
  v_tot_sal  NUMBER;
  v_bef_ded  NUMBER;
  v_temp_sal NUMBER;
  v_tot_tax  NUMBER;
  v_sal_tax  NUMBER;
  v_aft_ded  NUMBER;
  v_net_sal  NUMBER;
 BEGIN
  v_tot_sal  := tot_sal_com (v_empno);
  v_bef_ded  := bef_tax_ded (v_empno);
  v_temp_sal := temp_sal_btd(v_empno);
  v_tot_tax  := tax_emp_sal (v_empno);
  v_sal_tax  := sal_aft_tax (v_empno);
  v_aft_ded  := aft_tax_ded (v_empno);
  v_net_sal  := tot_net_sal (v_empno);
     DBMS_OUTPUT. PUT_LINE ('Salary before deductions for employee # ' || v_empno || ' equal: ' || to_char(v_tot_sal,'$999,999.99'));
     DBMS_OUTPUT. PUT_LINE ('Before tax deductions for    employee # ' || v_empno || ' equal: ' || to_char(v_bef_ded,'$999,999.99'));
     DBMS_OUTPUT. PUT_LINE ('Salary minus deductions for  employee # ' || v_empno || ' equal: ' || to_char(v_temp_sal,'$999,999.99'));
     DBMS_OUTPUT. PUT_LINE ('Total taxes for              employee # ' || v_empno || ' equal: ' || to_char
(v_tot_tax,'$999,999.99'));
     DBMS_OUTPUT. PUT_LINE ('Salary minus taxes for       employee # ' || v_empno || ' equal: ' || to_char
(v_sal_tax,'$999,999.99'));
     DBMS_OUTPUT. PUT_LINE ('After tax deductions for     employee # ' || v_empno || ' equal: ' || to_char(v_aft_ded,'$999,999.99'));
     DBMS_OUTPUT. PUT_LINE ('Net salary for               employee # ' || v_empno || ' equal: ' || to_char
(v_net_sal,'$999,999.99'));
 END emp_tot_sal; 
..................................................
Salary before deductions for employee # 7369 equal:      $900.00
Before tax deductions for    employee # 7369 equal:       $95.34
Salary minus deductions for  employee # 7369 equal:      $804.66
Total taxes for                   employee # 7369 equal:       $56.33
Salary minus taxes for          employee # 7369 equal:      $748.33
After tax deductions for       employee # 7369 equal:        $8.00
Net salary for                      employee # 7369 equal:      $740.33
*******************************************************

alary before deductions for employee # 7654 equal:    $2,650.00
efore tax deductions for    employee # 7654 equal:
alary minus deductions for  employee # 7654 equal:
otal taxes for                    employee # 7654 equal:
alary minus taxes for          employee # 7654 equal:
fter tax deductions for       employee # 7654 equal:
et salary for                      employee # 7654 equal:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
This post has been answered by Vivek L on Aug 27 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 24 2012
Added on Aug 27 2012
3 comments
4,097 views