存储过程执行sql语句无数据的问题

[ecjtuhr]

我写了个存储过程，如下：
CREATE OR REPLACE Procedure PRO_Update_dzk Is
v_count NUMBER;
v_hid Varchar2(30);
v_mlph VARCHAR2(50);
v_mlxz VARCHAR2(100);

Cursor cur_hid Is Select hid From T_DZK_TEMP t Where t.bz='0' Or t.flag='0'Order By t.pcsbm;


Begin
v_count:=0;
For cursor_hid In cur_hid Loop
v_count:=v_count+1;
v_h:='';
v_z:='';
v_hid:=cursor_hid.hid;
Select t.h,t.z into v_h,v_z From aa t Where t.hid=v_hid ; --此处有可能查出数据为空，属正常现象
If (v_h Is Null) And (v_z Is Null) Then
Delete From T_DZK_TEMP a Where a.hid=v_hid;
Else
Update T_DZK_TEMP a Set a.h=v_h ,a.z= v_z Where a.hid=v_hid;
End If;
if (v_count mod 1000 )=0 then
Commit;
end if;
End Loop;
Commit;
End ;

在上述红色标记的地方，有可能为空，我原本想，如果为空，那么就走下面的流程，可是存储过程却退出了报错，说无数据，请教怎么处理，如果为空那么继续下面的代码？

[孤独剑_LPZ]

用nvl转换字段,防止为空

[心中的彩虹]

CREATE OR REPLACE Procedure PRO_Update_dzk is
  v_count NUMBER;
  v_hid Varchar2(30);
  v_mlph VARCHAR2(50);
  v_mlxz VARCHAR2(100);
   
  Cursor cur_hid Is Select hid From T_DZK_TEMP t Where t.bz='0' Or t.flag='0'Order By t.pcsbm;   
   
   
Begin
  v_count:=0;
  For cur_hid_rec In cur_hid Loop
  v_count:=v_count+1;
  v_h:='';
  v_z:='';
  v_hid:=cur_hid_rec.hid;   
  Select t.h,t.z into v_h,v_z From aa t Where t.hid=v_hid ; 
--此处有可能查出数据为空，属正常现象   
  If (v_h Is Null) And (v_z Is Null) Then
  Delete From T_DZK_TEMP a Where a.hid=v_hid;  
  Else   
  Update T_DZK_TEMP a Set h=v_h ,z= v_z Where a.hid=v_hid;   
  End If;
  if mod(v_count,1000)=0 then
  Commit;
  end if;
  End Loop;
  Commit;
End ;

[心中的彩虹]

For cur_hid_rec In cur_hid Loop

v_hid:=cur_hid_rec.hid;

if mod(v_count,1000)=0 then


改了这三个 试下看看

[ecjtuhr]

[Quote=引用 4 楼 wkc168 的回复:]
For cur_hid_rec In cur_hid Loop

v_hid:=cur_hid_rec.hid;

if mod(v_count,1000)=0 then


改了这三个 试下看看
[/Quote]

没什么区别嘛！

[ecjtuhr]

[Quote=引用 2 楼 asftrhgjhkjlkttttttt 的回复:]
用nvl转换字段,防止为空
[/Quote]
倒是可取的办法.

[zhangwonderful]

先执行:select count(*) into v_count from aa t Where t.hid=v_hid;
再执行以下语句：
if v_count>0 then
Select t.h,t.z into v_h,v_z From aa t Where t.hid=v_hid;
else
--
end if;

[心中的彩虹]

--修剪了下 在试试看
CREATE OR REPLACE Procedure PRO_Update_dzk is
  v_count NUMBER;
  v_hid Varchar2(30);
  v_mlph VARCHAR2(50);
  v_mlxz VARCHAR2(100);
  n number;
   
  Cursor cur_hid Is Select hid From T_DZK_TEMP t Where t.bz='0' Or t.flag='0' Order By t.pcsbm;   
 
Begin
  v_count:=0;
  For cur_hid_rec In cur_hid Loop
  v_count:=v_count+1;
  v_hid:=cur_hid_rec.hid;
  Select count(*) into n From aa t Where t.hid=v_hid ; 
if n>0 then 
  Select t.h,t.z into v_h,v_z From aa t Where t.hid=v_hid ; 

  Update T_DZK_TEMP a Set h=v_h ,z= v_z Where a.hid=v_hid;   
  else 
  Delete From T_DZK_TEMP a Where a.hid=v_hid; 
  end if;
  End Loop;
  Commit;
End ;

[longfeih]

[Quote=引用 7 楼 zhangwonderful 的回复:]
先执行:select count(*) into v_count from aa t Where t.hid=v_hid;
再执行以下语句：
if v_count>0 then
Select t.h,t.z into v_h,v_z From aa t Where t.hid=v_hid;
else
--
end if;
[/Quote]


楼上的正解 也是最常用的办法

[iqlife]

Select t.h,t.z From aa t Where t.hid=v_hid ;
查询结果是什么？