本人因需要建立一个函数,通过输入值返回一组一条记录,结构为TONBR(calling,clled,status),在调用此函数时经常会使数据库连接失败,在SQL*Plus和TODA中均会出现这种问题,我基本上知道问题的原因,是因为在Select语句中没有查找到数据而,抛出no_data异常,然后导致的,当我只执行一条Select语句时,可以通过异常处理来完成,但是当我需要执行多条Select语句以及其他所需语句时就不能很好的处理了。原码如下,希望大家帮助解决一下。 /*CREATE OR REPLACE FUNCTION "FUN_GETCN"
(CALLING in varchar2, CALLED in varchar2)
return ONBR as
vData TONBR := TONBR();
vvData ONBR;
vUP varchar(2);
vCalling varchar(30);
vCalled varchar(30);
vtempnbr varchar(30);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
--初始化返回值
vCalling:=CALLING;
vCalled:=CALLED;
vUP:='0'; --查找主叫信息
Select SERV_NBR into vtempnbr From SERV_USER where SECOND_PHONE = CALLING;
if (vtempnbr=null)
then
Select SECOND_PHONE into vtempnbr From SERV_USER where SERV_NBR = CALLING;
if (vtempnbr<>null)
then
vCalling:=vtempnbr;
end if;
else
vCalling:=vtempnbr;
end if; --查找被叫信息
Select SERV_NBR, USER_PRI into vtempnbr, vUP From SERV_USER where SECOND_PHONE = CALLED;
if (vtempnbr=null)
then
Select SECOND_PHONE, USER_PRI into vtempnbr , vUP From SERV_USER where SERV_NBR = CALLED;
if (vtempnbr<>null)
then
vCalled:=vtempnbr;
end if;
else
vCalled:=vtempnbr;
end if;
vData.Extend;
vvData := ONBR(vCalling,vCalled, vUP);
vData(1) := vvData;
return vData;
end;
(CALLING in varchar2, CALLED in varchar2)
return ONBR as
vData TONBR := TONBR();
vvData ONBR;
vUP varchar(2);
vCalling varchar(30);
vCalled varchar(30);
vtempnbr varchar(30);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
--初始化返回值
vCalling:=CALLING;
vCalled:=CALLED;
vUP:='0'; --查找主叫信息
Select SERV_NBR into vtempnbr From SERV_USER where SECOND_PHONE = CALLING;
if (vtempnbr=null)
then
Select SECOND_PHONE into vtempnbr From SERV_USER where SERV_NBR = CALLING;
if (vtempnbr<>null)
then
vCalling:=vtempnbr;
end if;
else
vCalling:=vtempnbr;
end if; --查找被叫信息
Select SERV_NBR, USER_PRI into vtempnbr, vUP From SERV_USER where SECOND_PHONE = CALLED;
if (vtempnbr=null)
then
Select SECOND_PHONE, USER_PRI into vtempnbr , vUP From SERV_USER where SERV_NBR = CALLED;
if (vtempnbr<>null)
then
vCalled:=vtempnbr;
end if;
else
vCalled:=vtempnbr;
end if;
vData.Extend;
vvData := ONBR(vCalling,vCalled, vUP);
vData(1) := vvData;
return vData;
end;
(CALLING in varchar2, CALLED in varchar2)
return ONBR as
vData TONBR := TONBR();
vvData ONBR;
vUP varchar(2);
vCalling varchar(30);
vCalled varchar(30);
vtempnbr varchar(30);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
--初始化返回值
vCalling:=CALLING;
vCalled:=CALLED;
vUP:='0'; --查找主叫信息
Select SERV_NBR into vCalling From SERV_USER where SECOND_PHONE = CALLING;
exception when no_data_found then
begin
Select SECOND_PHONE into vCalling From SERV_USER where SERV_NBR = CALLING;
exception when no_data_found then
null;
end; --查找被叫信息
Select SERV_NBR, USER_PRI into vCalled, vUP From SERV_USER where SECOND_PHONE = CALLED;
exception when no_data_found then
begin
Select SECOND_PHONE, USER_PRI into vCalled, vUP From SERV_USER where SERV_NBR = CALLED;
exception when no_data_found then
null;
end;
vData.Extend;
vvData := ONBR(vCalling,vCalled, vUP);
vData(1) := vvData;
return vData;
end;
但是每一条select语句都用异常判断,是不是太麻烦了,可不可以直接使用一个参数来屏蔽调这个功能。
至于count正是我不想用的方法,本身count一次就比较费成本,我处理又是实时系统。