execute immediate 'select check_result from zc.t_gx_incre_data where pid=:1'
into v_check_result
using v_pid;我查询结果放在v_check_result中
但是如果没有找到数据的话,就有问题了,偶刚刚开始学习ORACLE,不知道怎么来控制这中情况请大虾赐教
into v_check_result
using v_pid;我查询结果放在v_check_result中
但是如果没有找到数据的话,就有问题了,偶刚刚开始学习ORACLE,不知道怎么来控制这中情况请大虾赐教
select count(*) into i from zc.t_gx_incre_data where pid=:1
if i>0 then
execute immediate 'select check_result from zc.t_gx_incre_data where pid=:1'
into v_check_result
using v_pid;
enf if;
(source_name in varchar2,
target_name in varchar2,
times in out number)
is
query_str varchar2(4000);
source_name1 varchar2(64);
target_name1 varchar2(64);
cursor c1 is select segment_name from user_segments where segment_name=upper(source_name);
dummy c1%rowtype;
cursor c2 is select segment_name from user_segments where segment_name=upper(target_name);
dummy2 c2%rowtype;
begin
source_name1:=source_name;
target_name1:=target_name;
open c1;
fetch c1 into dummy;
-- if c1%found then
-- dbms_output.put_line(source_name1||'exist!');
-- end if;
open c2;
fetch c2 into dummy2;
-- if c2%notfound then
-- dbms_output.put_line(target_name1||'not exist!');
-- end if; if c2%notfound and c1%found then
query_str :='alter table '||source_name1||' rename to '||target_name1;
execute immediate query_str;
dbms_output.put_line('rename success!');
end if;
close c1;
close c2;
exception
WHEN OTHERS THEN
times:=times+1;
if times<100 then
-- dbms_output.put_line('times:'||times);
rename_table(source_name1,target_name1,times);
else
dbms_output.put_line(SQLERRM);
dbms_output.put_line('error over 100 times,exit');
end if;
end;一个重命名表名称的过程,里面用到了Exception.
is
begin
exception
WHEN OTHERS THEN
end;简化后的格式。