Oracle's easy-to-make set of errors and tricks

Introduction

This article records the pitfalls of Oracle programming that are easily encountered in daily development and some usage tips that are not updated regularly.

1.sum (), Max (), Min (), AVG () and other functions get null values

DeclareN_num Number(5):="';beginSelect sum(1) intoN_num fromDualwhere 1=2; N_num:=N_num+2;d Bms_output.put_line (n_num);End;

The code above describes a scenario that we develop on a daily basis, summing up the data of a table, and then computing it in various ways. The normal situation is no problem, but the sum table has no data, we will get a null value, and then cause a series of subsequent calculations are null values,

Cause business errors.

Workaround: Add the NVL () function to handle this exception, as follows:

DeclareN_num Number(5):="';beginSelectNvlsum(1),0) intoN_num fromDualwhere 1=2; N_num:=N_num+2;d Bms_output.put_line (n_num);End;

Oracle's easy-to-make set of errors and tricks