oracle存过id,最近用oracle写了个产生ID号得存储过程，有类似需求的可以参考

在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