create or replace function GETLASTLESSENDATE(CRIMINALNOWINFOID in NUMBER) return date is
Result date; startdate date;
begin
select c.xfbd23 into startdate from (
select sc.xfbd23
from sentence_change sc
where sc.state = '已通过'
and sc.criminal_now_info_id = CRIMINALNOWINFOID
and (sc.xfbd01=7 or sc.xfbd01=8 or sc.xfbd01 =9 or sc.xfbd01 =10)
order by sc.xfbd23 desc
) c
where rownum = 1;
Result := startdate;
return(Result);
end GETLASTLESSENDATE;请问各位大侠,如果c.xfbd23为空时执行c.xfbd23 into startdate是不是要出错,怎么可以解决这个问题呢,谢谢
Result date; startdate date;
begin
select c.xfbd23 into startdate from (
select sc.xfbd23
from sentence_change sc
where sc.state = '已通过'
and sc.criminal_now_info_id = CRIMINALNOWINFOID
and (sc.xfbd01=7 or sc.xfbd01=8 or sc.xfbd01 =9 or sc.xfbd01 =10)
order by sc.xfbd23 desc
) c
where rownum = 1;
Result := startdate;
return(Result);
end GETLASTLESSENDATE;请问各位大侠,如果c.xfbd23为空时执行c.xfbd23 into startdate是不是要出错,怎么可以解决这个问题呢,谢谢
--比如说如果c.xfbd23为空的话返回当前的系统时间
select nvl(c.xfbd23,sysdate) into startdate from --或者用exception
exception when no_data_found then
result:=sysdate;
--例如:
create or replace function GETLASTLESSENDATE(CRIMINALNOWINFOID in NUMBER)
return date
is
Result date;
startdate date;
begin
select c.xfbd23 into startdate from (
select sc.xfbd23 from sentence_change sc
where sc.state = '已通过'
and sc.criminal_now_info_id = CRIMINALNOWINFOID
and (sc.xfbd01=7 or sc.xfbd01=8 or sc.xfbd01 =9 or sc.xfbd01 =10)
order by sc.xfbd23 desc
) c
where rownum = 1;
Result := startdate;
return Result;
exception when no_data_found then --捕获异常,返回空的日期
return to_date(null);
end GETLASTLESSENDATE;
例如 to_date('20101204','yyyymmdd')
create or replace function GETLASTLESSENDATE(CRIMINALNOWINFOID in NUMBER) return date is
Result date;
startdate date;
enddate date;
tempVar varchar2(90);
begin
select c.xfbd23 into startdate from (
select sc.xfbd23
from sentence_change sc
where sc.state = '已通过'
and sc.criminal_now_info_id = CRIMINALNOWINFOID
and (sc.xfbd01=7 or sc.xfbd01=8 or sc.xfbd01 =9 or sc.xfbd01 =10)
order by sc.xfbd23 desc
) c
where rownum = 1;
-- exception when no_data_found then --捕获异常,返回空的日期
-- startdate:=null;
select to_char(c.invalid_date) into tempVar from (
select t.invalid_date from criminal__invalid t where t.state='已通过'
and t.criminal_now_info_id = CRIMINALNOWINFOID
order by t.invalid_date desc
) c
where rownum = 1;
exception when no_data_found then --捕获异常,返回空的日期
enddate:=null;
--dbms_output.put_line(time_after - time_before);
dbms_output.put_line(startdate); dbms_output.put_line(enddate);
if(enddate is null) then
Result := startdate;
else if(to_number(startdate)-to_number(enddate)>0) then
Result := startdate;
else
Result := enddate;
end if;
end if;
return(Result);
end GETLASTLESSENDATE;
exception when no_data_found then --捕获异常,返回空的日期
startdate:=null;
enddate:=null;
select sc.xfbd23
from sentence_change sc
where sc.state = '已通过'
and sc.criminal_now_info_id = CRIMINALNOWINFOID
and (sc.xfbd01=7 or sc.xfbd01=8 or sc.xfbd01 =9 or sc.xfbd01 =10)
order by sc.xfbd23 desc
) c
where rownum = 1;
执行出来为空,也要出错哪嘛,我是想这个语为空时,怎么不让引响后面的SQL执行
Result date; startdate date;
begin
select c.xfbd23 into startdate from (
select sc.xfbd23
from sentence_change sc
where sc.state = '已通过'
and sc.criminal_now_info_id = CRIMINALNOWINFOID
and (sc.xfbd01=7 or sc.xfbd01=8 or sc.xfbd01 =9 or sc.xfbd01 =10)
order by sc.xfbd23 desc
) c
where rownum = 1;
Result := startdate;
return(Result);
exception
when others when
return null;
end GETLASTLESSENDATE;
Result date; startdate date;
begin
begin
select c.xfbd23 into startdate from (
select sc.xfbd23
from sentence_change sc
where sc.state = '已通过'
and sc.criminal_now_info_id = CRIMINALNOWINFOID
and (sc.xfbd01=7 or sc.xfbd01=8 or sc.xfbd01 =9 or sc.xfbd01 =10)
order by sc.xfbd23 desc
) c
where rownum = 1;
Result := startdate;
return(Result);
exception
when others when
return 指定日期;
end;
........;
end GETLASTLESSENDATE;
Result date;
startdate date;
enddate date;
begin
begin
--得到最后减刑时间
select c.xfbd23 into startdate from (
select sc.xfbd23
from sentence_change sc
where sc.state = '已通过'
and sc.criminal_now_info_id = CRIMINALNOWINFOID
and (sc.xfbd01=7 or sc.xfbd01=8 or sc.xfbd01 =9 or sc.xfbd01 =10)
order by sc.xfbd23 desc
) c
where rownum = 1;
--exception when no_data_found then --捕获异常,返回空的日期
--startdate:=null;
Result := startdate;
exception
when others then
startdate:= null;
end; ---得到设置为最后的无效时间
select c.invalid_date into enddate from (
select t.invalid_date from criminal__invalid t where t.state='已通过'
and t.criminal_now_info_id = CRIMINALNOWINFOID
order by t.invalid_date desc
) c
where rownum = 1;
Result := enddate;
exception when no_data_found then --捕获异常,返回空的日期
enddate:=null;
--dbms_output.put_line(time_after - time_before);
dbms_output.put_line(startdate);
dbms_output.put_line(enddate);
if(startdate is null and enddate is null) then
Result := null;
else if(enddate is null and startdate is not null) then
Result := startdate;
else if(enddate is not null and startdate is null)then
Result := enddate;
else if(to_number(startdate)-to_number(enddate)>0) then
Result := startdate;
else if(to_number(startdate)-to_number(enddate)<0) then
Result := enddate;
end if;
end if;
end if;
end if;
end if;
return(Result);
end GETLASTLESSENDATE;我这样写,当两个时间不为空的时候,怎么说没有返回值呢,请问错在哪里,谢谢