The problem of "NULL" and "no" irritability in SQL

Very irritable, irritable very, summed up a bit.

The first simple:

NULL: Something that is not sure

None: nothing.

Complex see below ..... One, null value

In the simplest case, the usual work is definitely more complicated than this SQL, and in this just presenting this easy-to-be-wrong point, he could be a little bit of a complex SQL error that isn't easy to spot.

Above is a very simple table of all the data. Area_num Zone encoding area_name zone name Delflag has invalid ID 1 valid 0 is invalid (where Huaibei and Xuancheng delflag are null).

Now you want to find out which area information is valid, so use the following statement:

The above results do not have Huaibei and Xuancheng as expected in the first thought is Delflag not 0 of all should be queried out including Huaibei and Xuancheng.

In fact, the fields of Huaibei and Xuancheng delflag are null values. Inside Oracle NULL is worth the concept:

Null is a unique data type in the database, and when a column of one record is null, it indicates that the value of the columns is unknown and indeterminate. Since it is unknown, there are countless possibilities. Therefore, NULL is not a deterministic value.

So a null value (an indeterminate value) does not meet the condition of! = ' 0 '. The same is true for the following statements.

Second, no results

No result is actually a select query with no result set (NOT NULL, but no result)

The result is null:

No results:

The surface looks very clear, but in the practical application may be easy to mistake.

The following is an example of a stored procedure:

?

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 666768697071

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);--取操作日志表某个工单流程proceessid的操作人所属机构id，和操作名称  orgidCursor   pcursor%rowtype;--定义类型为pcursor行数据的 变量  orgid_var     varchar2(20);--存放操作人机构id变量  returnflag    varchar2(20);--返回的标志位  orgseqflag    varchar2(50);--操作人机构id及其所有父id 串起来字符  skforgflag    varchar2(20);--存放操作人机构id变量  count_var     number;--存放统计数字变量  oper_name_var varchar2(100);--操作名称begin  returnflag    := ‘1‘;--返回值初始化为1  orgseqflag    := null;--初始化  skforgflag    := null;--初始化  count_var     := 0;--初始化  oper_name_var := null;--初始化  open pcursor;--打开游标  loop    fetch pcursor      into orgidCursor;--把游标数据放进pcursor变量    exit when pcursor%notfound;      orgid_var     := orgidCursor.orgid;--从orgidCursor变量取值到orgid_var    oper_name_var := orgidCursor.oper_name;--从orgidCursor变量取值到oper_name_var      if (orgid_var is null) then--orgid_var是可能为空的 表里面t.orgid为空       null;    else      select count(1)        into count_var        from tssa_dsg.eosorg_t_organization b,             (select a.*                from tssa_dsg.bndict_t_dictionary a               where a.BUSINTYPEID = ‘WH_CH_ORAPROPERTY‘                 and a.status = ‘0‘) a       where b.orgproperty = a.businid(+)         and b.orgid = orgid_var;--统计操作日志表的机构id是否在机构静态表里      if (count_var > 0) then--在里面        select t.orgseq          into orgseqflag          from tssa_dsg.eosorg_t_organization t         where t.orgid = orgid_var        --取orgseqflag        ;        select a.businname          into skforgflag          from tssa_dsg.eosorg_t_organization b,               (select a.*                  from tssa_dsg.bndict_t_dictionary a                 where a.BUSINTYPEID = ‘WH_CH_ORAPROPERTY‘                   and a.status = ‘0‘) a         where b.orgproperty = a.businid(+)           and b.orgid = orgid_var;--取组织分类               if (orgseqflag like ‘99999.7676.%‘ or skforgflag = ‘省客服‘ or           (skforgflag != ‘省客支‘ and skforgflag != ‘省层面‘ and           oper_name_var = ‘话务员追加信息‘)) then          null;        else          returnflag := null;--不满足id条件 置返回值为null        end if;      end if;    end if;  end loop;  close pcursor;  return returnflag;end;

The effect of the above process is that the root work order process ID returns whether the ticket is handled only by someone in a particular organization.

The OrgID of the Operation log table has an empty condition.
if (Orgid_var is null) This condition determines if the record is null and void, does not participate in the judgment (business requirements).

If this condition is not added (and no

?

123456789

select count(1)        into count_var        from tssa_dsg.eosorg_t_organization b,             (select a.*                from tssa_dsg.bndict_t_dictionary a               where a.BUSINTYPEID = ‘WH_CH_ORAPROPERTY‘                 and a.status = ‘0‘) a       where b.orgproperty = a.businid(+)         and b.orgid = orgid_var;--统计操作日志表的机构id是否在机构静态表里

）

The following statement

?

1234	selectt.orgseq          into orgseqflag          from tssa_dsg.eosorg_t_organization t         wheret.orgid = orgid_var

There is no result, no value.

?

1	intoorgseqflag

will be error (debugging will error, direct operation without error) directly out of loop to return a null value is not in line with the business requirements.

?

12345678910

select count(1)        into count_var        from tssa_dsg.eosorg_t_organization b,             (select a.*                from tssa_dsg.bndict_t_dictionary a               where a.BUSINTYPEID = ‘WH_CH_ORAPROPERTY‘                 and a.status = ‘0‘) a       where b.orgproperty = a.businid(+)         and b.orgid = orgid_var;--统计操作日志表的机构id是否在机构静态表里if (count_var > 0) then--在里面

The above conditional action is the same as if (Orgid_var is null) and ignores the case where the mechanism ID of the Operation log table is not in the mechanism's static tables.

The problem of "NULL" and "no" irritability in SQL