資料庫如何增加賦值

Ⅰ 鏁版嵁搴撲腑鐨勪竴寮犺〃閲岄潰鏈塈D瀛楁 浣嗘湭璧嬪 鎬庝箞鎻掑叆id鑷澧炲 灝卞儚id瀛楁佃嚜澧炴晥鏋滀竴鏍

insert into PRODUCT (浣犲緩鐨勫簭鍒.NEXTVAL,PRD_DESCRIPTION) values (id,description錛
鍦ㄦ瘡嬈℃彃鍏ユ暟鎹鐨勬椂鍊欙紝id閮戒細鎸変綘寤虹殑搴忓垪鐨勫為噺鑷鍔ㄥ炲姞銆

sequence灝辨槸鎵璋撶殑搴忓垪鍙鳳紝姣忔″彇鐨勬椂鍊欏畠浼氳嚜鍔ㄥ炲姞錛屼竴鑸鐢ㄥ湪闇瑕佹寜

搴忓垪鍙鋒帓搴忕殑鍦版柟銆

1銆 create sequence

浣犻栧厛瑕佹湁create sequence鎴栬卌reate any sequence鏉冮檺錛

create sequence emp_sequence

INCREMENT BY 1 -- 姣忔″姞鍑犱釜

START WITH 1 -- 浠1寮濮嬭℃暟

NOMAXVALUE -- 涓嶈劇疆鏈澶у

NOCYCLE -- 涓鐩寸瘡鍔狅紝涓嶅驚鐜

CACHE 10;

涓鏃﹀畾涔変簡emp_sequence錛屼綘灝卞彲浠ョ敤CURRVAL錛孨EXTVAL

CURRVAL=榪斿洖sequence鐨勫綋鍓嶅

NEXTVAL=澧炲姞sequence鐨勫礆紝鐒跺悗榪斿洖sequence鍊

姣斿傦細

emp_sequence.CURRVAL

emp_sequence.NEXTVAL

鍙浠ヤ嬌鐢╯equence鐨勫湴鏂癸細

- 涓嶅寘鍚瀛愭煡璇銆乻napshot銆乂IEW鐨 SELECT 璇鍙

- INSERT璇鍙ョ殑瀛愭煡璇涓

- NSERT璇鍙ョ殑VALUES涓

- UPDATE 鐨 SET涓

鍙浠ョ湅濡備笅渚嬪瓙錛

INSERT INTO emp VALUES

(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);

SELECT empseq.currval FROM DUAL;

浣嗘槸瑕佹敞鎰忕殑鏄錛

- 絎涓嬈NEXTVAL榪斿洖鐨勬槸鍒濆嬪礆紱闅忓悗鐨凬EXTVAL浼氳嚜鍔ㄥ炲姞浣犲畾涔夌殑INCREMENT BY鍊礆紝

鐒跺悗榪斿洖澧炲姞鍚庣殑鍊箋侰URRVAL 鎬繪槸榪斿洖褰撳墠sequence鐨勫礆紝浣嗘槸鍦ㄧ涓嬈NEXTVAL

鍒濆嬪寲涔嬪悗鎵嶈兘浣跨敤CURRVAL錛屽惁鍒欎細鍑洪敊銆備竴嬈NEXTVAL浼氬炲姞涓嬈sequence鐨勫礆紝

鎵浠ュ傛灉浣犲湪鍚屼竴涓璇鍙ラ噷闈浣跨敤澶氫釜NEXTVAL錛屽叾鍊煎氨鏄涓嶄竴鏍風殑銆傛槑鐧斤紵

- 濡傛灉鎸囧畾CACHE鍊礆紝oracle灝卞彲浠ラ勫厛鍦ㄥ唴瀛橀噷闈㈡斁緗涓浜泂equence錛岃繖鏍峰瓨鍙栫殑蹇

浜涖

cache閲岄潰鐨勫彇瀹屽悗錛宱racle鑷鍔ㄥ啀鍙栦竴緇勫埌cache銆 浣跨敤cache鎴栬鎬細璺沖彿錛 姣斿

鏁版嵁搴撶獊鐒朵笉姝ｅ父down鎺夛紙shutdown abort),cache涓鐨剆equence灝變細涓㈠け. 鎵浠ュ彲

浠ュ湪create sequence鐨勬椂鍊欑敤nocache闃叉㈣繖縐嶆儏鍐點

2銆 Alter sequence

浣犳垨鑰呮槸璇sequence鐨刼wner錛屾垨鑰呮湁ALTER ANY sequence鏉冮檺鎵嶈兘鏀瑰姩sequence銆 鍙

浠alter闄start鍊間箣澶栫殑鎵鏈塻equence鍙傛暟銆傚傛灉鎯寵佹敼鍙榮tart鍊礆紝蹇呴』drop sequence

鍐峳e-create銆備緥瀛愶細

ALTER sequence emp_sequence

INCREMENT BY 10

MAXVALUE 10000

CYCLE -- 鍒10000鍚庝粠澶村紑濮

NOCACHE;

褰卞搷sequence鐨勫垵濮嬪寲鍙傛暟錛

sequence_CACHE_ENTRIES =

璁劇疆鑳藉悓鏃惰玞ache鐨剆equence鏁扮洰銆

鍙浠ュ緢綆鍗曠殑Drop sequence

DROP sequence order_seq;

緇撴潫

Ⅱ oracle鏁版嵁搴撴庝箞澧炲姞涓涓瀛楁電殑鍊

浣犳槸瑕乻ql璇鍙ヨ繕鏄浠涔堬紵

鐒跺悗鍚庨潰鏈変釜"+"鍜"-"鍙 鍔犲彿灝辨槸澧炲姞涓鍒椾簡銆傚傛灉鏄痵ql璇鍙 altertable 浣犵殑琛ㄥ悕 add 澧炲姞鐨勫垪鍚 Integer(澧炲姞鍒楃殑瀛楁電被鍨) 甯屾湜鑳藉府鍒頒綘錛