建立異常表：
-- Create table
create table ORACLEEXCEPTION
(
ID VARCHAR2(36),
CREATETIME DATE,
SQLCODE1 NVARCHAR2(300),
SQLERRM1 NVARCHAR2(300),
MYEXCEPTION NVARCHAR2(100)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
編寫oracle--function：
create or replace function getabin(abin in varchar2)
return varchar2
is
result varchar2(50);
sqlcode2 nvarchar2(500):='';
sqlerrm2 nvarchar2(500):='';
myexception nvarchar2(500):='';
exception1 exception;
begin
if(abin is not null) then
select count(1) into result from bing1 t where t.name1 like abin;
return(result);
elsif(abin is null) then
result:='there is a defined exception';
raise exception1;
end if;
exception
when exception1 then
sqlcode2:=sqlcode;
sqlerrm2:=sqlerrm;
begin
insert into oracleexception(id,createtime,sqlcode1,sqlerrm1,myexception) values (sys_guid(),sysdate,sqlcode2,sqlerrm2,'exception1');
commit;
end;
return(result);
when others then
result:='there is a other exception';
sqlcode2:=sqlcode;
sqlerrm2:=sqlerrm;
begin
insert into oracleexception(id,createtime,sqlcode1,sqlerrm1,myexception) values (sys_guid(),sysdate,sqlcode2,sqlerrm2,'others');
commit;
end;
return(result);
end;
測試oracle--function代碼：
declare
ename varchar2(50):='';
result varchar2(50);
begin
result:=getabin(ename);
dbms_output.put_line(result);
end;