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/NOMAXVALUE
MINVALUE/NOMINVALUE
CYCLE/NOCYCLE
CACHE<#>/NOCACHE
ORDER/NOORDER;
CreateSequenceSimplestForm
CREATESEQUENCE;
CREATESEQUENCEseq_campus_site_id;
SELECTseq_campus_site_id.NEXTVALFROMdual;
/
/
SimpleAutonumber
WithSequence
INSERTINTO
（）
VALUES
（.NEXTVAL）;
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
（.CURRVAL）;
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
CREATESEQUENCESTARTWITH;
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
CREATESEQUENCEINCREMENTBY;
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
CREATESEQUENCESTARTWITH
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
CREATESEQUENCESTARTWITH
MAXVALUECYCLE;
CREATESEQUENCEseq_cycleSTARTWITH1MAXVALUE5CYCLE;
--defaultcacheis20
CREATESEQUENCEseq_cycleSTARTWITH1MAXVALUE5CYCLECACHE4;
TRUNCATETABLEseq_test;
INSERTINTOseq_testVALUES（seq_cycle.NEXTVAL）;
/
/
/
/
/
/
SELECT*FROMseq_test;
CACHEDemo
CREATESEQUENCECACHE;
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
CREATESEQUENCESTARTWITH1ORDER;
CREATESEQUENCEseq_orderSTARTWITH1ORDER;
AlterSequence
ChangeIncrement
ALTERSEQUENCEINCREMENTBY;
ALTERSEQUENCEseq_inc_by_tenINCREMENTBY20;
ChangeMaxValue
ALTERSEQUENCEMAXVALUE
ALTERSEQUENCEseq_maxvalMAXVALUE10;
ChangeCycle
ALTERSEQUENCE
ALTERSEQUENCEseq_cycleNOCYCLE;
ChangeCache
ALTERSEQUENCECACHE|NOCACHE
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;
升级会员 