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;