Consider the following table with its two columns:
CREATE TABLE TEMP (id NUMBER, value NUMBER);
The first two INSERT statements are identical. This third inserts a NULL for the column VALUE.
INSERT INTO temp VALUES (1, 100); INSERT INTO temp (id, value) VALUES (1, 100); INSERT INTO temp (id) VALUES (2);
Is this what you want? Do you want NULL? Maybe a zero would be better. The following replaces the DEFAULT NULL with a zero.
CREATE TABLE TEMP (id NUMBER, value VARCHAR2(10) DEFAULT 0);
The default for VALUE is no longer NULL. This inserts a (2, 0)
INSERT INTO temp (id) VALUES (2);
If a zero can be interpreted as no data, then use a DEFAULT as was done earlier. There are situations where a zero equates to no data. One example is a checking account. If there is a zero balance, there is no money. Other situations require a NULL. Consider an environmental biologist measuring microbes in a polluted river. On some days, the microbe count may be zero. On other days the river may be frozen. On these days an insert should be a NULL. A zero value, on days when the river is frozen, could skew any analysis of the data. For the environmentalist, there is a difference between a zero and a NULL. A NULL means no sample was taken. For the checking account, a zero is equivalent to the absence of money.
PL/SQL expressions that include NULL values can be tricky. Refer to Chapter 11, Section 11.4 for additional discussion on this topic.
3.149.229.19