在ORACLE中产生流水号一般是序列的方式来生成。但是有时候也不那么方便,所以就写了一个存储过程来产生这个序列号。支持两种流水号,即00001,201108310001。
首先需要建立一个表:
create table T_ID
(
IDNAME VARCHAR2(40) not null,
IDCATE VARCHAR2(1),
IDLENGTH INTEGER,
IDVALUE VARCHAR2(40)
)
;
alter table T_ID
add constraint IDNAME_KEY primary key (IDNAME);
存储过程如下:
create or replace procedure USP_GETID(mTable in varchar2, mCate in integer, mlength in integer,mFormat in varchar2,mValue out varchar2)
as
mZero varchar(40);
tmpValue varchar(40);
tmpCount number;
begin
mZero:= '000000000000000000000000000000';
mValue:= '1';
tmpValue:='';
select count(IDNAME) into tmpCount from t_id where IDNAME = mTable;
if (tmpCount=1) then
select idValue into tmpValue from t_id where IDNAME = mTable;
end if;
if (tmpValue is null or tmpValue='') then
begin
if (mCate=1) then
mValue := to_char(substr(mZero,1,mlength -1)) || '' ||'1';
else
mValue := to_char(sysdate,mFormat) || substr(mZero,1,mlength-1-length(mFormat)) || '1';
end if;
insert into t_id values (mTable,to_char(mCate),mlength,mValue);
end;
else
begin
if (mCate=1) then
begin
tmpValue := to_number(tmpValue) + 1;
mValue := to_char(substr(mZero,1,mlength - length(tmpValue))) || '' || to_char(tmpValue);
end;
else
begin
if (to_char(sysdate,mFormat)=substr(tmpValue,1,length(mFormat))) then
begin
tmpValue := to_char(to_number(substr(tmpValue,length(mFormat)+1,mlength-length(mFormat))) + 1);
mValue := to_char(sysdate,mFormat) || substr(mZero,1,mlength -length(tmpValue)-length(mFormat)) || '' || to_char(tmpValue);
end;
else
begin
mValue := to_char(sysdate,mFormat)+ substr(mZero,1,mlength-length(mFormat)) ||'' || '1';
end;
end if;
end;
end if;
update t_id set IDVALUE=mValue,IDCATE=mCate,IDLENGTH=mlength where IDNAME=mTable;
end;
end if ;
end USP_GETID;
C#调用方式
#region 通过存储过程产生编号
public string GetIDByProc(string Table, int len)
{
return GetIDByProc(Table, "1", len, "");
}
public string GetIDByProc(string Table, int len, string DateFormat)
{
return GetIDByProc(Table, "0", len, DateFormat);
}
private string GetIDByProc(string Table, string idCate, int len, string DateFormat)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetStoredProcCommand("USP_GETID");
db.AddInParameter(command, "mTable", DbType.String, Table);
db.AddInParameter(command, "mCate", DbType.Int32, idCate);
db.AddInParameter(command, "mlength", DbType.Int32,len);
db.AddInParameter(command, "mFormat", DbType.String,DateFormat);
db.AddOutParameter(command, "mValue", DbType.String, 40);
db.ExecuteNonQuery(command);
object o = db.GetParameterValue(command, "mValue");
return Convert.ToString(o);
}
#endregion