NULL and NULL strings in Oracle

In Oracle, a keyword is NULL, indicating that a value is unknown and uncertain. Since it is unknown, there are several possibilities. Therefore, NULL is not a definite value. Let's take a look at two examples:

Example 1:

Declare

V_b1 boolean: = null; -- the boolean value can be null. Of course, the other two values can only be true or false.

V_a int: = null;
V_ B int: = null; -- an integer can be assigned null.
V_s varchar2 (20): = null; -- the character can be assigned null.
Begin
If (v_a = v_ B) then -- v_a and v_ B are both null, but the result is that they are not equal. Changing to v_a = null still produces the same result.
Dbms_output.put_line ('v _ a equal v_ B ');
Else
Dbms_output.put_line ('v _ a not equal v_ B '); -- output this result.
End if;

If (v_b1) then -- change v_b1 to not v_b1 and the result will be the same. That is, v_b1 and the reverse result will not be true.
Dbms_output.put_line ('v _ b1 equal true ');
Else
Dbms_output.put_line ('v _ b1 is not true'); -- output this result.
End if;

If (true or v_b1) then -- output v_b1 equal true
Dbms_output.put_line ('v _ b1 equal true ');
Else
Dbms_output.put_line ('v _ b1 is not true'); -- if you change the previous judgment condition to true and v_b1, enter this statement.
End if;
End;

Example 2:

Create table test (ename char (1), constraint ck_test check (ename in ('A', null )));

-- The preceding statement creates a table with only one column named ename, and adds constraints to this column. Its values can only be in the list ('A', null ).In fact, we can insert any characterIn this case, it seems that null can represent all characters. Remove it and only insert 'A'

However, if we create a table like this

Create table test1 (ename char (1), constraint ck_test check (ename not in ('A', null )));

-- Assume that if null represents all characters, it cannot be entered. but it is not. it does not seem to have any effect here. removing it is the same as removing it. bothAny character except 'A' can be inserted.

1. How to understand the usage of null

Check whether the above two examples are a bit dizzy. In fact, we can understand it in this way. since null is an uncertain type, it can be converted to any value of any type. it is like the object type in C. it is a parent class of any type. can represent any type. we can dynamically determine the specific type of the program when running it. if null is in a condition judgment condition, let it take all values of a specific type for judgment. true if all results are true.

Example 1The two integer v_a and v_ B are both null. to make them equal, let null Take All integers for determination. it cannot be equal. this is false. true or v_b1 is true because if the preceding or is true, the v_b1 is not judged. if both v_b1 and not v_b1 are false, the values of v_b1 can be set to true and false. If both values are set to true, the final result is false.

In this way, it is not difficult to understand why we insert a null value in a table. however, we cannot query by condition = null. however, oracle provides the keyword "is" to determine whether a value is null.

Example 2The second case is a bit abnormal. We think that if you don't want to insert anything, but creating a table doesn't make sense.

It is unclear how Oracle stores the null value and operates the null value in the background.

2. null and empty strings

In fact, the operation of a null string is the same as that of other characters, but the eyes should be better when using it. Do not make a mistake. For example

Create table arwen (ename varchar2 (1); -- this column can only insert one character

Insert into arwen values ('');--Note that there is a space in the quotation mark. If there are two spaces, an error will occur because they are two characters.

Select * from arwen where ename = ''; -- returns a row. A space must be enclosed in quotation marks.

If it is '', there is no space in the quotation marks..Oracle changes it to null by default when it is inserted. so if there is no space in the quotation marks, we cannot call it an empty string. I don't know what it is. it seems a bit redundant when null exists.

For example, create table arwen (eno int );

Insert into arwen values (44 );

Insert into arwen alues (''); -- there is no space in the quotation marks

Insert into arwen alues (null );

All the preceding three statements are successfully inserted.

Although ''is converted to null by default, it cannot be used with'' or = ''.

For example, you select count (*) from arwen or select count (*) from arwen where ename is null -- the result is 3

However, if select count (*) from arwen where ename is ''or select count (*) from arwen where ename ='', the result is 0.

3. The nvl function is often used when null is used.

If it is null, it is converted to other stuff.

For example, after inserting three rows above, we can look for select nvl (ename, 11) from arwen; -- the result is 44 11 11.

If the ename is null, it is converted to 11. If it is not null, It is output as is.

4. note when using null

When avg, max, and min functions are used, null is ignored.

For example, create table arwen (eno int );

Insert into arwen values (1 );

Insert into arwen values (2 );

Insert into arwen values (null );

Select avg (eno) from arwen; -- the result is. This is because null is ignored, otherwise it should be 1.

Select max (eno) from arwen; -- the result is 2

If null is treated as the maximum value in sorting, it is like an infinity.

When inserting data into a table, it is very convenient for users to set null if a column is not inserted. however, it seems unnecessary to use it elsewhere. so try not to use it. Only when performing some operations on the table, consider null judgment. Use the nvl function and is null judgment.