oracle中的''和null

 declare
 v_new varchar2(20) := '';
 cursor c_test is select distinct no from test where dno is not null;
 v_test c_test%rowtype;
begin
 open c_test;
 loop
  fetch c_test into v_test;
  --根据dno查新的dno
  begin
   select new_dno into v_new from test2 where old_dno = v_test.dno;
  exception
   when no_data_found then
    v_new := '';
  end;
  
  if v_new <> '' then 
   update test set dno = v_new where dno = v_test.dno;
  end if;
 end loop;
 close c_test;
end;

上面的代码进不了update语句。

最后发现是''在作怪。在oracle中''和null是一样的。

null不能和任何值比较。这样自然就进不了update语句了。

所以最后改成下面的就OK了。

 

declare
 v_new varchar2(20) := null;
 cursor c_test is select distinct no from test where dno is not null;
 v_test c_test%rowtype;
begin
 open c_test;
 loop
  fetch c_test into v_test;
  --根据dno查新的dno
  begin
   select new_dno into v_new from test2 where old_dno = v_test.dno;
  exception
   when no_data_found then
    v_new := null;
  end;
  
  if v_new is not null then 
   update test set dno = v_new where dno = v_test.dno;
  end if;
 end loop;
 close c_test;
end;