oracle 自定义函数SQL问题

[yangwei19820]

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是不是要出错，怎么可以解决这个问题呢，谢谢

[gelyon]

用捕获异常形式处理就可以了

[真的不知道取什么名]

如果为空你想返回什么呢？

--比如说如果c.xfbd23为空的话返回当前的系统时间
select nvl(c.xfbd23,sysdate) into startdate from 

--或者用exception
exception when no_data_found then
result:=sysdate;

[gelyon]

--例如：
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;

[yangwei19820]

如果c.xfbd23为空的话返回一个指定的时间，怎么在上面的SQL中实现啊，谢谢

[真的不知道取什么名]

[Quote=引用 4 楼 yangwei19820 的回复:]
如果c.xfbd23为空的话返回一个指定的时间，怎么在上面的SQL中实现啊，谢谢
[/Quote]把sysdate 改成你指定的时间
例如 to_date('20101204','yyyymmdd')

[yangwei19820]

再请教一个问题，像下面这个函数中有两个SQL，两个SQL都有可能为空，怎么用两次异常处理不能呢，那又该怎么处理啊，请高手指点，谢谢
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_mark_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;

[真的不知道取什么名]

[Quote=引用 6 楼 yangwei19820 的回复:]
再请教一个问题，像下面这个函数中有两个SQL，两个SQL都有可能为空，怎么用两次异常处理不能呢，那又该怎么处理啊，请高手指点，谢谢
create or replace function GETLASTLESSENDATE(CRIMINALNOWINFOID in NUMBER) return date is
Result date;

startdate date;
en……
[/Quote]一个exception 发到最后 你的触发条件都是no_data_found
exception when no_data_found then --捕获异常，返回空的日期
startdate:=null;
enddate:=null;

[yangwei19820]

如果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;
执行出来为空，也要出错哪嘛，我是想这个语为空时，怎么不让引响后面的SQL执行

[心中的彩虹]

[Quote=引用楼主 yangwei19820 的回复:]
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
fro……
[/Quote]

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 others when
return null;
end GETLASTLESSENDATE;

[心中的彩虹]

[Quote=引用 9 楼 wkc168 的回复:]
引用楼主 yangwei19820 的回复:
create or replace function GETLASTLESSENDATE(CRIMINALNOWINFOID in NUMBER) return date is
Result date;

startdate date;
begin
select c.xfbd23 into startdate from (
select ……
[/Quote]

create or replace function GETLASTLESSENDATE(CRIMINALNOWINFOID in NUMBER) return date is
  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;

[yangwei19820]

create or replace function GETLASTLESSENDATE(CRIMINALNOWINFOID in NUMBER) return date is
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_mark_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;我这样写，当两个时间不为空的时候，怎么说没有返回值呢，请问错在哪里，谢谢