--但个函数进行管理
CREATE OR REPLACE function testuser.proone(shortName in varchar2) return varchar2 is
tMaxNo2 varchar(64);
pragma autonomous_transaction;
begin
declare
tMaxNo integer := 0;
tMaxNo1 varchar(32) ;
tMaxNo2 varchar(32) ;
yearY varchar(2) ;
begin
select max(maxno) into tMaxNo from testuser.t_maxno_conf where type='proone';
if tMaxNo is null or tMaxNo='' then
insert into testuser.t_maxno_conf(type,maxno)values('proone',1);
commit;
tMaxNo:=1;
select lpad('1','2',0) into tMaxNo1 from dual;
tMaxNo2:='HK-'||yearY||'-'||shortName||'-'||tMaxNo1;
else
update testuser.t_maxno_conf a set a.maxno=a.maxno+1 where a.type='proone';
commit;
select lpad(tMaxNo,'2',0) into tMaxNo1 from dual;
select SUBSTR(extract(year from sysdate),3,4) into yearY from dual;
tMaxNo2:='HK-'||yearY||'-'||shortName||'-'||tMaxNo1;
END IF;
return tMaxNo2;
end;
end proone; --不需要加属主,否则报错
/
包的方式进行函数管理
包头
create or replace package testuser.testuser_project_package is
function proone(shortName in varchar2) return varchar2 ;
end testuser_project_package; --结尾不需要加属主
/
包体
create or replace package body testuser.testuser_project_package --包体开头,可以加属主
is
function proone(shortName in varchar2)
return varchar2 is
tMaxNo2 varchar(64);
pragma autonomous_transaction;
begin
declare
tMaxNo integer := 0;
tMaxNo1 varchar(32) ;
tMaxNo2 varchar(32) ;
yearY varchar(2) ;
begin
select max(maxno) into tMaxNo from testuser.t_maxno_conf where type='proone';
if tMaxNo is null or tMaxNo='' then
insert into testuser.t_maxno_conf(type,maxno)values('proone',1);
commit;
tMaxNo:=1;
select lpad('1','2',0) into tMaxNo1 from dual;
tMaxNo2:='HK-'||yearY||'-'||shortName||'-'||tMaxNo1;
else
update testuser.t_maxno_conf a set a.maxno=a.maxno+1 where a.type='proone';
commit;
select lpad(tMaxNo,'2',0) into tMaxNo1 from dual;
select SUBSTR(extract(year from sysdate),3,4) into yearY from dual;
tMaxNo2:='HK-'||yearY||'-'||shortName||'-'||tMaxNo1;
END IF;
return tMaxNo2; --每个函数体的结尾标示
end ;
end proone; --包体的结尾标示
end testuser_project_package; --结尾不需要加属主
/