oracle中自動(dòng)編號(hào)的字段怎么讓他重新從1開(kāi)始編號(hào)

可以寫(xiě)個(gè)dbms_job定時(shí)刪除序列并重建。存儲(chǔ)過(guò)程如下：

目前創(chuàng)新互聯(lián)已為上千多家的企業(yè)提供了網(wǎng)站建設(shè)、域名、雅安服務(wù)器托管、網(wǎng)站托管、企業(yè)網(wǎng)站設(shè)計(jì)、環(huán)江網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。

create?or?replace?procedure?prc_mq_resetsequence(seqname?in?varchar2)?authid?current_user?is

num?number;

begin

select?count(*)

into?num

from?user_sequences

where?sequence_name?=?upper(seqname);

if?num??0?then

execute?immediate?'drop?sequence?'?||?seqname;

end?if;

execute?immediate?'create?sequence?'?||?seqname?||

'?minvalue?1?maxvalue?9999999999999?start?with?1?increment?by?1?cache?20';

end;

oracle 怎樣用UPDATE 更新序列？？

update A

set id = id +100

where id = (一共有多少條記錄)

這樣好像就可以了

oracle怎么新增存單號(hào)

oracle存儲(chǔ)過(guò)程生成單號(hào)

oracle存儲(chǔ)過(guò)程生成單號(hào),Oracle生成單據(jù)編號(hào)存儲(chǔ)過(guò)程的實(shí)例代碼

?

旅程1229

轉(zhuǎn)載

關(guān)注

0點(diǎn)贊·137人閱讀

Oracle生成單據(jù)編號(hào)存儲(chǔ)過(guò)程，在做訂單類(lèi)似的系統(tǒng)都可能會(huì)存在訂單編號(hào)不重復(fù)，或是流水號(hào)按日，按年，按月進(jìn)行重新編號(hào)。

可以參考以下存儲(chǔ)過(guò)程

CREATE OR REPLACE

procedure Pro_GetBillNO(TypeTable in varchar2,cur_mycursor out sys_refcursor)

as

DReceiptCode varchar2(40);

DReceiptName varchar2(50);

DPrefix1 varchar2(50);

DISO varchar2(50);

DIsAutoCreate varchar2(20);

DPrefix2 varchar2(20);

DPrefix3 varchar2(20);

DDateValue date;

DNO number;

DLength number;

DResetType number;

DSeparator varchar2(20);

DReturnValue varchar2(50);

strSql varchar2(1000);

begin

DReturnValue:='';

select "ReceiptCode","ReceiptName","Prefix1","ISO","IsAutoCreate","Prefix2","Prefix3","DateValue","NO","Length","ResetType","Separator" into

DReceiptCode,DReceiptName,DPrefix1,DISO,DIsAutoCreate,DPrefix2,DPrefix3,DDateValue,DNO,DLength,DResetType,DSeparator from

"SysReceiptConfig" where "ReceiptCode"=TypeTable;

if to_number(DResetType)0

then

if DIsAutoCreate=1 THEN

if DResetType=1 then --

oracle 怎樣使UPDATE 更新序列？？

可以使用update select 語(yǔ)法進(jìn)行更新

喜歡研究的可以自行學(xué)習(xí)下，以便積累

不想浪費(fèi)時(shí)間可以看下面：

UPDATE A SET ID = (SELECT ROWNUM FROM A ORDER BY 時(shí)間 DESC)

參考語(yǔ)法：

UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)

WHERE ID IN (SELECT B.ID FROM B WHERE A.ID = B.ID)