Oracle Development Series (1): Crazy errors: null value and no value (no result), too lenull

Oracle Development Series (1): Crazy errors: null value and no value (no result), too lenull

Recently, I encountered some problems when I was doing development and writing operations. I found the cause for a long time and did not know it before. In this case, let's take a note and give it a reference.

I. null Value

The following is the simplest example. It must be much more complicated than this SQL statement in normal work. In this case, we only present this error-prone point, it may be a small problem that cannot be easily discovered due to complicated SQL errors.

The preceding figure shows all the data in a simple table. Area_num region code area_name region name delflag has invalid Id 1 valid 0 invalid (among which the delflag of Huaibei and xuancheng is null ).

To find out the valid region information, use the following statement:

In the above results, there was no difference between Huaibei and xuancheng. At the beginning, all those that thought that delflag was not 0 should be queried, including Huaibei and xuancheng.

In fact, the fields of Huaibei and xuancheng delflag are null values. The concept of null in oracle is as follows:

NULL is a unique data type in the database. If a column in a record is NULL, the value of this column is unknown and uncertain. Since it is unknown, there are several possibilities. Therefore, NULL is not a definite value.

So the null value (uncertain value) does not match! = '0. The following statement is also true.

2. No results

No result is actually a select query with no result set (not null, but no result)

The result is null:

No results:

On the surface, it is clear, but it may be easy to make a mistake in practical applications.

The following is an example of the stored procedure:

Create or replace function getProceessidAllDealBySkf_l (proceessid in number) return varchar2 as cursor pcursor is (select distinct t. orgid, t. oper_name from tssa_his_dsg.wh_common_busilog_td t where t. processinstid = proceessid); -- obtains the Organization id of the operator of a ticket flow proceessid in the operation log table, and the operation name orgidCursor pcursor % rowtype; -- defines the variable orgid_var varchar2 (20) of the pcursor row data; -- stores the operator's organization id variable returnflag varchar2 (20); -- returns the flag orgseqflag Varchar2 (50); -- operator organization id and all parent IDs string up with the character skforgflag varchar2 (20); -- stores the operator organization id variable count_var number; -- stores the statistical variable oper_name_var varchar2 (100); -- operation name begin returnflag: = '1'; -- the return value is initialized to 1 orgseqflag: = null; -- initialize skforgflag: = null; -- initialize count_var: = 0; -- initialize oper_name_var: = null; -- initialize open pcursor; -- open the cursor loop fetch pcursor into orgidCursor; -- Put cursor data into the pcursor variable exit when pcursor % notfound; orgid_var: = orgidCursor. or Gid; -- from orgidCursor variable value to orgid_var oper_name_var: = orgidCursor. oper_name; -- from orgidCursor variable value to oper_name_var if (orgid_var is null) then -- orgid_var is a table that may be empty t. orgid is null; else select count (1) into count_var from tssa_dsg.eosorg_t_organization B, (select. * from tssa_dsg.bndict_t_dictionary a where. BUSINTYPEID = 'wh _ CH_ORAPROPERTY 'and. status = '0') a where B. orgproperty =. businid (+) and B .org Id = orgid_var; -- count whether the Organization id of the operation log table is in the Mechanism static table if (count_var> 0) then -- select t. orgseq into orgseqflag from tssa_dsg.eosorg_t_organization t where t. orgid = orgid_var -- Take orgseqflag; select. businname into skforgflag from tssa_dsg.eosorg_t_organization B, (select. * from tssa_dsg.bndict_t_dictionary a where. BUSINTYPEID = 'wh _ CH_ORAPROPERTY 'and. status = '0') a where B. orgproperty =. businid (+) And B. orgid = orgid_var; -- Take the Organization Category if (orgseqflag like '192. 99999.% 'or skforgflag = 'provincial Customer Service' or (skforgflag! = 'Provincial Customer Support 'and skforgflag! = 'Province level' and oper_name_var = 'operator append information') then null; else returnflag: = null; -- if the return value is null if the id condition is not met, end if; end if; end loop; close pcursor; return returnflag; end;

The above process is used to indicate whether the ticket is handled only by a person in a specific organization by the root ticket process id.

The orgid of the operation log table is empty.
If (orgid_var is null) is null. if it is null, the record is invalid and does not participate in the judgment (business requirements ).

If this condition is not added (and no

Select count (1) into count_var from tssa_dsg.eosorg_t_organization B, (select. * from tssa_dsg.bndict_t_dictionary a where. BUSINTYPEID = 'wh _ CH_ORAPROPERTY 'and. status = '0') a where B. orgproperty =. businid (+) and B. orgid = orgid_var; -- determines whether the Organization id of the operation log table is in the organization static table.

)

The following statement

select t.orgseq          into orgseqflag          from tssa_dsg.eosorg_t_organization t         where t.orgid = orgid_var

No result is found, that is, no value.

 into orgseqflag

An error will be reported (debugging will report an error and direct running will not report an error). directly jump out of the loop and return a null value, which does not meet the business requirements.

Select count (1) into count_var from tssa_dsg.eosorg_t_organization B, (select. * from tssa_dsg.bndict_t_dictionary a where. BUSINTYPEID = 'wh _ CH_ORAPROPERTY 'and. status = '0') a where B. orgproperty =. businid (+) and B. orgid = orgid_var; -- count whether the Organization id of the operation log table is in the Mechanism static table if (count_var> 0) then -- inside

The above condition is similar to if (orgid_var is null) in that the Organization id of the operation log table is not in the Mechanism static table.

In this post "How to Write SQL statements to remove NULL values in oracle returned results", I can see your answer. I don't quite understand how to use it?

Four records in two data tables a (id, name, age) and three records in B (id (foreign key), address)
Select * from a right join B on. id = B. id: displays data with the same id in Table a and table B based on the table on the right. Three data records are returned. The fourth id in Table a is not displayed because the address in Table B has no value.
Select * from a left join B a. id = B. id; displays all IDs in Table a, but one of the addresses is null. 4 records are returned.
 
How to write an SQL statement to remove NULL values in oracle return results)

There is no simple method, but you can query the system table to reduce your syntax writing. For example:
Select 'and' | COLUMN_NAME | 'is NOT null' from all_tab_columns where table_name = 'table _ name'

In this way, all fields in the table automatically constitute a Condition Statement.