Oracle Sequences完全解释.docx
- 文档编号:24118535
- 上传时间:2023-05-24
- 格式:DOCX
- 页数:10
- 大小:16.39KB
Oracle Sequences完全解释.docx
《Oracle Sequences完全解释.docx》由会员分享,可在线阅读,更多相关《Oracle Sequences完全解释.docx(10页珍藏版)》请在冰豆网上搜索。
OracleSequences完全解释
OracleSequences完全解释
OracleSequences
General
DependentObjects
seq$
user_sequences
all_sequences
dba_sequences
seq
RelatedSystemPrivileges
createsequence
createanysequence
alteranysequence
dropanysequence
selectanysequence
NOTE:
ThealternativetosequencesusedinotherRDBMSproductsisautonumberingandkeepingthecurrentnumberinatable.Bothoftheseothermethodsdemandserializationastheycanonlydispenseonenumberatatime.
Tableexample:
CREATETABLEseqnum(
next_numberNUMBER
(1);
1.Locktheseqnumtableforyourtransaction
2.SELECTnext_numberFROMseqnum;
3.UPDATEseqnumSETnext_number=next_number+1;
4.Unlocktheseqnumtableforthenexttransation
TablesForSequenceDemos
CREATETABLEcampus_site(
site_idNUMBER(4),
organization_nameVARCHAR2(40),
campus_nameVARCHAR2(30),
address_idNUMBER(10))
TABLESPACEdata_sml;
CREATETABLEdivision(
division_idNUMBER(5),
site_idNUMBER(4),
division_nameVARCHAR2(40),
address_idNUMBER(10))
TABLESPACEdata_sml;
CREATETABLEdepartment(
department_idNUMBER(5),
division_idNUMBER(5),
department_nameVARCHAR2(40),
address_idNUMBER(10))
TABLESPACEdata_sml;
CREATETABLEseq_test(
testNUMBER(10))
TABLESPACEdata_sml;
CreateSequence
FullCreateSequenceSyntax
CREATESEQUENCE
INCREMENTBY
STARTWITH
MAXVALUE
MINVALUE
CYCLE/NOCYCLE
CACHE<#>/NOCACHE
ORDER/NOORDER;
CreateSequenceSimplestForm
CREATESEQUENCE
CREATESEQUENCEseq_campus_site_id;
SELECTseq_campus_site_id.NEXTVALFROMdual;
/
/
SimpleAutonumber
WithSequence
INSERTINTO
(
VALUES
(
INSERTINTOcampus_site
(site_id,organization_name,campus_name)
VALUES
(seq_campus_site_id.NEXTVAL,'Univ.ofWashington','MainSeattle');
SELECT*
FROMcampus_site;
INSERTINTOcampus_site
(site_id,organization_name,campus_name)
VALUES
(seq_campus_site_id.NEXTVAL,'Univ.ofWashington','Bothell');
SELECT*
FROMcampus_site;
SimpleAutonumberWith
SequenceIntoTwoTables
INSERTINTO
(
VALUES
(
CREATESEQUENCEseq_division_id;
INSERTINTOcampus_site
(site_id,organization_name,campus_name)
VALUES
(seq_division_id.NEXTVAL,'Univ.ofWashington','Tacoma');
INSERTINTOdivision
(division_id,site_id,division_name)
VALUES
(seq_division_id.NEXTVAL,seq_campus_site_id.CURRVAL,'Engineering');
SELECT*
FROMcampus_site;
SELECT*
FROMdivision;
SimpleTransactionNumberForAuditDemoingSTARTWITHandacautionwithCURRVAL
CREATESEQUENCE
CREATESEQUENCEseq_audit_txSTARTWITH297;
INSERTINTOcampus_site
(site_id,organization_name,campus_name)
VALUES
(seq_audit_tx.NEXTVAL,'Univ.ofWashington','Everett');
INSERTINTOdivision
(division_id,site_id,division_name)
VALUES
(seq_audit_tx.NEXTVAL,seq_audit_tx.CURRVAL,'Science');
INSERTINTOdepartment
(department_id,division_id,department_name)
VALUES
(seq_audit_tx.NEXTVAL,seq_audit_tx.CURRVAL,'Astronomy');
SELECT*FROMcampus_site;
SELECT*FROMdivision;
SELECT*FROMdepartment;
ROLLBACK;
INSERTINTOcampus_site
(site_id,organization_name,campus_name)
VALUES
(seq_audit_tx.NEXTVAL,'Univ.ofWashington','Everett');
INSERTINTOdivision
(site_id,division_id,division_name)
VALUES
(seq_audit_tx.CURRVAL,seq_audit_tx.NEXTVAL,'Science');
INSERTINTOdepartment
(division_id,department_id,department_name)
VALUES
(seq_audit_tx.CURRVAL,seq_audit_tx.NEXTVAL,'Astronomy');
SELECT*FROMcampus_site;
SELECT*FROMdivision;
SELECT*FROMdepartment;
INCREMENTBY
CREATESEQUENCE
CREATESEQUENCEseq_inc_by_twoINCREMENTBY2;
INSERTINTOseq_testVALUES(seq_inc_by_two.NEXTVAL);
/
/
SELECT*FROMseq_test;
CREATESEQUENCEseq_inc_by_tenINCREMENTBY10;
INSERTINTOseq_testVALUES(seq_inc_by_ten.NEXTVAL);
/
/
SELECT*FROMseq_test;
ALTERTABLEseq_testADDtest2NUMBER(10);
descseq_test
INSERTINTOseq_test
(test,test2)
VALUES
(seq_inc_by_ten.NEXTVAL,seq_inc_by_ten.NEXTVAL);
SELECT*FROMseq_test;
INSERTINTOseq_test
(test,test2)
VALUES
(seq_inc_by_ten.NEXTVAL,seq_inc_by_ten.CURRVAL);
SELECT*FROMseq_test;
ReverseDECREMENTBY
CREATESEQUENCE
MAXVALUE
INCREMENTBY
CREATESEQUENCEseq_reverseINCREMENTBY-5;
ALTERTABLEseq_testDROPCOLUMNtest2;
INSERTINTOseq_testVALUES(seq_reverse.NEXTVAL);
/
/
/
SELECT*FROMseq_test;
DROPSEQUENCEseq_reverse;
CREATESEQUENCEseq_reverseMAXVALUE150
STARTWITH150INCREMENTBY-5;
INSERTINTOseq_testVALUES(seq_reverse.NEXTVAL);
/
/
/
SELECT*FROMseq_test;
MAXVALUEDemo
CREATESEQUENCE
MAXVALUE
CREATESEQUENCEseq_maxvalSTARTWITH1MAXVALUE5;
INSERTINTOseq_testVALUES(seq_maxval.NEXTVAL);
/
/
/
SELECT*FROMseq_test;
INSERTINTOseq_testVALUES(seq_maxval.NEXTVAL);
SELECT*FROMseq_test;
INSERTINTOseq_testVALUES(seq_maxval.NEXTVAL);
CYCLEDemo
CREATESEQUENCE
MAXVALUE
CREATESEQUENCEseq_cycleSTARTWITH1MAXVALUE5CYCLE;
--defaultcacheis20
CREATESEQUENCEseq_cycleSTARTWITH1MAXVALUE5CYCLECACHE4;
TRUNCATETABLEseq_test;
INSERTINTOseq_testVALUES(seq_cycle.NEXTVAL);
/
/
/
/
/
/
SELECT*FROMseq_test;
CACHEDemo
CREATESEQUENCE
CREATESEQUENCEseq_cacheCACHE100;
SELECTsequence_name,last_number
FROMuser_sequences;
SELECTseq_cache.NEXTVALFROMdual;
SELECTsequence_name,last_number
FROMuser_sequences;
SELECTseq_cache.NEXTVALFROMdual;
/
SELECTsequence_name,last_number
FROMuser_sequences;
conn/assysdba
shutdownabort;
startup
connuwclass/uwclass
SELECTsequence_name,last_number
FROMuser_sequences;
SELECTseq_cache.NEXTVALFROMdual;
ORDERDemo
CREATESEQUENCE
CREATESEQUENCEseq_orderSTARTWITH1ORDER;
AlterSequence
ChangeIncrement
ALTERSEQUENCE
ALTERSEQUENCEseq_inc_by_tenINCREMENTBY20;
ChangeMaxValue
ALTERSEQUENCE
ALTERSEQUENCEseq_maxvalMAXVALUE10;
ChangeCycle
ALTERSEQUENCE
ALTERSEQUENCEseq_cycleNOCYCLE;
ChangeCache
ALTERSEQUENCE
ALTERSEQUENCEseq_cacheNOCACHE;
ChangeOrder
ALTERSEQUENCE
ALTERSEQUENCEseq_orderNOORDER;
DropSequence
DropSequence
DROPSEQUENCE
DROPSEQUENCEseq_cache;
SequenceResets
Byfindingoutthecurrentvalueofthesequenceandalteringtheincrementbytobenegativethatnumberandselectingthesequenceonce--thesequencecanberesetto0.
IfanysessionattemptstousethesequencewhilethisishappeninganORA-08004errorwillbegenerated.
CREATESEQUENCEseq;
SELECTseq.NEXTVALFROMdual;
SELECTseq.NEXTVALFROMdual;
SELECTseq.NEXTVALFROMdual;
COLUMNSnew_valinc;
SELECTseq.NEXTVALSFROMdual;
ALTERSEQUENCEseqINCREMENTBY-&incMINVALUE0;
SELECTseq.NEXTVALSFROMdual;
ALTERSEQUENCEseqincrementby1;
SELECTseq.NEXTVALFROMdual;
/
/
StoredProcedureMethod
CREATEORREPLACEPROCEDUREreset_sequence(
seq_nameINVARCHAR2,startvalueINPLS_INTEGER)AS
cvalINTEGER;
inc_byVARCHAR2(25);
BEGIN
EXECUTEIMMEDIATE'ALTERSEQUENCE'||seq_name||'MINVALUE0';
EXECUTEIMMEDIATE'SELECT'||seq_name||'.NEXTVALFROMdual'
INTOcval;
cval:
=cval-startvalue+1;
IFcval<0THEN
inc_by:
='INCREMENTBY';
cval:
=ABS(cval);
ELSE
inc_by:
='INCREMENTBY-';
ENDIF;
EXECUTEIMMEDIATE'ALTERSEQUENCE'||seq_name||inc_by||
cval;
EXECUTEIMMEDIATE'SELECT'||seq_name||'.NEXTVALFROMdual'
INTOcval;
EXECUTEIMMEDIATE'ALTERSEQUENCE'||seq_name||
'INCREMENTBY1';
ENDreset_sequence;
/
SequenceRelatedQueries
LastNumberSelectedFromSequence
SELECTsequence_name,last_number
FROMuser_sequences;
NextNumberFromSequence
SELECTsequence_name,(last_number+increment_by)NEXT_VALUE
FROMuser_sequences;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle Sequences完全解释 Sequences 完全 解释