写的两个函数放在一个包里,先声明包以及里面的函数
create or replace package Pkg_Generate_Num is
-- Author : ADMINISTRATOR
-- Created : 2012-12-28 7:18:36
-- Purpose :
-- 获取单据唯一编号
Function getVoucherNum(a_VoucherType varchar2) Return Varchar2;
Function getVoucherNum(a_VoucherType varchar2,a_len integer) Return Varchar2;
--技改工程保修中使用生成唯一编号
Function getVoucherNumBYJG(a_VoucherType varchar2) Return Varchar2;
end Pkg_Generate_Num;
下面就是包体了:
create or replace package body Pkg_Generate_Num is
-- 获取记录唯一ID 生成如这样qz_201406120000000001
function getVoucherNum(a_VoucherType varchar2) return Varchar2 Is
pragma autonomous_transaction;
mN_LastUID Varchar2(30);
mS_BillType Varchar2(30);
Begin
mS_BillType := a_VoucherType || to_char(Sysdate, 'yyyymmdd');
select Max_UID
into mN_LastUID
from sjt_MAXUID
Where bill_day = mS_BillType
for update;
mN_LastUID := mN_LastUID + 1;
UPDATE sjt_MAXUID
SET max_uid = mN_LastUID
Where bill_day = mS_BillType;
commit;
return mS_BillType || lpad(mN_LastUID, 10, '0');
exception
when NO_DATA_FOUND Then
Begin
Insert Into sjt_MAXUID
(bill_day, max_uid)
Values
(mS_BillType, 1);
commit;
return mS_BillType || lpad('1', 10, '0');
Exception
When Others Then
Rollback;
sys.dbms_lock.sleep(ceil(dbms_random.value*5));
Return getVoucherNum(a_VoucherType);
End;
end;
-- 获取记录唯一ID //输入的参数一个是前面添加的字符, 后面添加的是要生成几位数字
function getVoucherNum(a_VoucherType varchar2,a_len integer) return Varchar2 Is
pragma autonomous_transaction;
mN_LastUID Varchar2(30);
mS_BillType Varchar2(30);
mS_len integer;
Begin
mS_BillType := a_VoucherType ;
select Max_UID
into mN_LastUID
from sjt_MAXUID
Where bill_day = mS_BillType
for update;
mN_LastUID := mN_LastUID + 1;
if a_len - length(mN_LastUID) < 0 then
mS_len := length(mN_LastUID);
else
mS_len := a_len;
end if;
UPDATE sjt_MAXUID
SET max_uid = mN_LastUID
Where bill_day = mS_BillType;
commit;
return mS_BillType || lpad(mN_LastUID, mS_len, '0');
exception
when NO_DATA_FOUND Then
Begin
Insert Into sjt_MAXUID
(bill_day, max_uid)
Values
(mS_BillType, 1);
commit;
return mS_BillType || lpad('1', a_len, '0');
Exception
When Others Then
Rollback;
sys.dbms_lock.sleep(ceil(dbms_random.value*5));
Return getVoucherNum(a_VoucherType);
End;
end;
-- 获取记录唯一ID生成如这样 qz_0001
function getVoucherNumBYJG(a_VoucherType varchar2) return Varchar2 Is
pragma autonomous_transaction;
mN_LastUID Varchar2(30);
mS_BillType Varchar2(30);
Begin
mS_BillType := a_VoucherType;
select Max_UID
into mN_LastUID
from sjt_MAXUID
Where bill_day = mS_BillType
for update;
mN_LastUID := mN_LastUID + 1;
UPDATE sjt_MAXUID
SET max_uid = mN_LastUID
Where bill_day = mS_BillType;
commit;
return mS_BillType || '_' || lpad(mN_LastUID, 4, '0');
exception
when NO_DATA_FOUND Then
Begin
Insert Into sjt_MAXUID
(bill_day, max_uid)
Values
(mS_BillType, 1);
commit;
return mS_BillType|| '_' || lpad('1', 4, '0');
Exception
When Others Then
Rollback;
sys.dbms_lock.sleep(ceil(dbms_random.value*5));
Return getVoucherNum(a_VoucherType);
End;
end;
begin
-- Initialization
null;
end Pkg_Generate_Num;
这里用到了一张中间表,表结构如下:
-- Create table SJT_MAXUID
create table SJT_MAXUID
(
bill_day VARCHAR2(20) not null,
max_uid NUMBER(30)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SJT_MAXUID
add constraint PK_SJT_MAXUID_MAXUID primary key (BILL_DAY)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
这个就先整理到这里了,这个适合在写好一个字段后用触发器或者程序调用给另一个字段赋值。很方便,也很实用。