我写了个存储过程,如下:
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 ;在上述红色标记的地方,有可能为空,我原本想,如果为空,那么就走下面的流程,可是存储过程却退出了报错,说无数据,请教怎么处理,如果为空那么继续下面的代码?
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 ;在上述红色标记的地方,有可能为空,我原本想,如果为空,那么就走下面的流程,可是存储过程却退出了报错,说无数据,请教怎么处理,如果为空那么继续下面的代码?
查询结果是什么?
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 ;
改了这三个 试下看看
再执行以下语句:
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 ;