--用来填充字符串的函数
function f_fill(sourceStr1 varchar2, --需要填充的字符串
columnType number, --字段类型,1:数字 2:字符
totalLength number --填充好了以后,该字符串的长度
) return varchar2 is
sourceStr varchar(52) := '';
result varchar2(52);
stuffing char(1) := ' '; -- 填充物,缺省是空格,如果该字段为数字,那么换作'0'
e_TooLong exception; --源字符串太长的异常
BEGIN
if columnType = 1 then
stuffing := '0';
end if;
if (length(sourceStr1) > totalLength) then
--sourceStr := substr(sourceStr1, 1, totalLength);
raise e_TooLong;
else
sourceStr := sourceStr1;
end if;
--如果sourceStr为null或者长度为0,那么,全部塞入0或者空格
if ((sourceStr is null) or (length(sourceStr) = 0)) then
result := f_get_fillString(stuffing, totalLength);
else
if columnType = 1 then
--如果是数字,那么左补0
result := f_get_fillString(stuffing,
totalLength - length(sourceStr)) ||
sourceStr;
else
result := sourceStr ||
f_get_fillString(stuffing,
totalLength - length(sourceStr));
end if;
end if;
return result;
END;--得到一个HOME保单的信息,然后把他们拼成一个合法的字符串
PROCEDURE P_GET_RECORDINFO_HOME(PolicyId in number) AS
result varchar(1000) := '';
policyNo varchar2(18);
BEGIN
beginresult:=f_fill('bbb',2,12);
exception
when others then
insert into table_log(...
end;end;
function f_fill(sourceStr1 varchar2, --需要填充的字符串
columnType number, --字段类型,1:数字 2:字符
totalLength number --填充好了以后,该字符串的长度
) return varchar2 is
sourceStr varchar(52) := '';
result varchar2(52);
stuffing char(1) := ' '; -- 填充物,缺省是空格,如果该字段为数字,那么换作'0'
e_TooLong exception; --源字符串太长的异常
BEGIN
if columnType = 1 then
stuffing := '0';
end if;
if (length(sourceStr1) > totalLength) then
--sourceStr := substr(sourceStr1, 1, totalLength);
raise e_TooLong;
else
sourceStr := sourceStr1;
end if;
--如果sourceStr为null或者长度为0,那么,全部塞入0或者空格
if ((sourceStr is null) or (length(sourceStr) = 0)) then
result := f_get_fillString(stuffing, totalLength);
else
if columnType = 1 then
--如果是数字,那么左补0
result := f_get_fillString(stuffing,
totalLength - length(sourceStr)) ||
sourceStr;
else
result := sourceStr ||
f_get_fillString(stuffing,
totalLength - length(sourceStr));
end if;
end if;
return result;
END;--得到一个HOME保单的信息,然后把他们拼成一个合法的字符串
PROCEDURE P_GET_RECORDINFO_HOME(PolicyId in number) AS
result varchar(1000) := '';
policyNo varchar2(18);
BEGIN
beginresult:=f_fill('bbb',2,12);
exception
when others then
insert into table_log(...
end;end;
然后在外层捕获
应该在package中声明e_TooLong异常,这样在整个包中可见:
create or replace package yourpackage as
...
e_TooLong exception; --子函数源字符串太长的异常
e_TooLong1 exception; --主函数源字符串太长的异常
v_sqlerrm varchar2(300) --捕获错误文本
v_sqlcode varchar2(100) --捕获错误代码
...
end yourpackage
--子函数
function f_fill(...)
...
begin
...
if (length(sourceStr1) > totalLength) then
--sourceStr := substr(sourceStr1, 1, totalLength);
raise e_TooLong;
else
sourceStr := sourceStr1;
end if;
exception
when e_Toolong then
v_sqlerrm:=substr(sqlerrm,1,300);
v_sqlcode:=sqlcode;
raise e_TooLong1; --抛出子函数异常给主函数
when others then
...
end;
--主函数
PROCEDURE P_GET_RECORDINFO_HOME(PolicyId in number) AS
...
BEGIN
beginresult:=f_fill('bbb',2,12);
exception
when e_TooLong1 then
insert into logtable values(v_sqlcode,v_sqlerrm);写错误日志
...
when others then
...
insert into table_log(...
end;
when e_Toolong then
raise;--相同异常再发生一次,并将该异常传递到外层