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: