I wrote code to get a batting average and on-base percentage. Problem is, whenever I skipped to insert a value, since it's a null value I only get an error message and the code returns nothing.
What I want to do is when I skip to insert a value, program automatically turns that empty value into 0 and make this code works.
For example:
PA : 595
Hit : 153
NIBB : 79
SC :
SF : 3
and makes code return
BA : .298
Onbase: .388
This is my code:
set serveroutput ON
accept p_num1 prompt 'PA : '
accept p_num2 prompt 'Hit : '
accept p_num3 prompt 'NIBB : '
accept p_num4 prompt 'SC : '
accept p_num5 prompt 'SF : '
DECLARE
v_pa number:=&p_num1;
v_hit number:=&p_num2;
v_nibb number:=&p_num3;
v_sc number:=&p_num4;
v_sf number:=&p_num5;
v_ba number(4,3);
v_ob number(4,3);
BEGIN
v_ba := v_hit/(v_pa-v_nibb-v_sc-v_sf);
v_ob := (v_hit+v_nibb)/(v_pa-v_sc);
dbms_output.put_line('BA : ' || v_ba);
dbms_output.put_line('OnBase : ' || v_ob);
end;
/
I tried to use both exception and default, but neither of them worked.
One option is to include NVL function:
SQL> set serveroutput on
SQL> DECLARE
2 v_pa NUMBER := 595;
3 v_hit NUMBER := 153;
4 v_nibb NUMBER := 79;
5 v_sc NUMBER := NULL;
6 v_sf NUMBER := 3;
7 v_ba NUMBER (4, 3);
8 v_ob NUMBER (4, 3);
9 BEGIN
10 v_ba :=
11 NVL (v_hit, 0) / (NVL (v_pa, 0) - NVL (v_nibb, 0) - NVL (v_sc, 0) - NVL (v_sf, 0));
12 v_ob := (NVL (v_hit, 0) + NVL (v_nibb, 0)) / (NVL (v_pa, 0) - NVL (v_sc, 0));
13 DBMS_OUTPUT.put_line ('BA : ' || v_ba);
14 DBMS_OUTPUT.put_line ('OnBase : ' || v_ob);
15 END;
16 /
BA : ,298
OnBase : ,39
PL/SQL procedure successfully completed.
SQL>
That works, kind of. However, it wouldn't work if divisor evaluates to zero as you'd get ORA-01476 error. Handle it, somehow. For example, you could detect whether divisor is equal to zero and then divide by a very large number (and get 0 as the final result):
SQL> DECLARE
2 v_pa NUMBER := 0;
3 v_hit NUMBER := 153;
4 v_nibb NUMBER := 0;
5 v_sc NUMBER := NULL;
6 v_sf NUMBER := 0;
7 v_ba NUMBER (4, 3);
8 v_ob NUMBER (4, 3);
9 --
10 v_div_ba NUMBER;
11 v_div_ob NUMBER;
12 BEGIN
13 v_div_ba := (NVL (v_pa, 0) - NVL (v_nibb, 0) - NVL (v_sc, 0) - NVL (v_sf, 0));
14 v_div_ob := (NVL (v_pa, 0) - NVL (v_sc, 0));
15 --
16 v_ba := NVL (v_hit, 0) / CASE WHEN v_div_ba = 0 THEN 1E99 ELSE v_div_ba END;
17 v_ob :=
18 (NVL (v_hit, 0) + NVL (v_nibb, 0)) / CASE WHEN v_div_ob = 0 THEN 1E99 ELSE 0 END;
19
20 DBMS_OUTPUT.put_line ('BA : ' || v_ba);
21 DBMS_OUTPUT.put_line ('OnBase : ' || v_ob);
22 END;
23 /
BA : 0
OnBase : 0
PL/SQL procedure successfully completed.
SQL>
Or, you could include EXCEPTION handlers - two of them, enclosing v_ba and v_obcalculations into its ownbegin-exception-end` block:
SQL> DECLARE
2 v_pa NUMBER := 0;
3 v_hit NUMBER := 153;
4 v_nibb NUMBER := 0;
5 v_sc NUMBER := NULL;
6 v_sf NUMBER := 0;
7 v_ba NUMBER (4, 3);
8 v_ob NUMBER (4, 3);
9 BEGIN
10 BEGIN
11 v_ba :=
12 NVL (v_hit, 0)
13 / (NVL (v_pa, 0) - NVL (v_nibb, 0) - NVL (v_sc, 0) - NVL (v_sf, 0));
14 EXCEPTION
15 WHEN ZERO_DIVIDE
16 THEN
17 v_ba := 0;
18 END;
19
20 BEGIN
21 v_ob := (NVL (v_hit, 0) + NVL (v_nibb, 0)) / (NVL (v_pa, 0) - NVL (v_sc, 0));
22 EXCEPTION
23 WHEN ZERO_DIVIDE
24 THEN
25 v_ob := 0;
26 END;
27
28 DBMS_OUTPUT.put_line ('BA : ' || v_ba);
29 DBMS_OUTPUT.put_line ('OnBase : ' || v_ob);
30 END;
31 /
BA : 0
OnBase : 0
PL/SQL procedure successfully completed.
SQL>
So, quite a few options ... some more are also possible. Pick the one you find the most appropriate.