SQL Server and Oracle sequence

1,sql Server sequence creation and use

Beginif EXISTS (SELECT * from sysobjects WHERE name = ' event_seq ') DROP SEQUENCE event_seqendcreate SEQUENCE Event_seqminva LUE 1MAXVALUE 999999999999999999START with 1INCREMENT by 1CACHE 20; --Goselect Next value for event_seq when used;

　　

2 Oracle sequence creation and use

BEGIN    EXECUTE IMMEDIATE ' DROP SEQUENCE staff_seq ';    EXCEPTION when OTHERS and then NULL; END; CREATE SEQUENCE staff_seqminvalue 1MAXVALUE 999999999999999999999999999START with 1INCREMENT by 1CACHE 20;-- Use a sequence in a trigger create OR REPLACE TRIGGER tr_inst_staff    before INSERT on M_maintenancestaff for each    rowbegin    Select Staff_seq. Nextval into:new. Staffindex from dual; End;--staff_seq. Nextval marks the next sequence value--staff_seq. Currval mark the next sequence value

Sequence key:

1) INCREMENT by is used to define the step of the sequence, and if omitted, the default is 1, and if a negative value is present, the values for the Oracle sequence are decremented by this step. 2) START with defines the initial value of the sequence (that is, the first value produced), which defaults to 1. 3) MAXVALUE defines the maximum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no maximum value defined, at which point the system can produce a maximum of 10 27 times for incrementing an Oracle sequence, and for a descending sequence, the maximum value is-1. 4) MinValue defines the minimum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means there is no minimum definition, and for a descending sequence, the minimum value that the system can produce is 10 of the 26 square, and for the increment sequence, the minimum value is 1. 5) cycle and nocycle indicate whether the sequence generator will loop if its value reaches the limit value. The cycle represents the loop, and the nocycle represents no loops. If the loop is, it loops to the minimum when the increment sequence reaches its maximum value, and to the maximum value when the descending sequence reaches the minimum value. If you do not loop, the error occurs when the limit value is reached and the new value continues to be generated. 6) the cache (buffer) defines the size of the memory block that holds the sequence, which defaults to 20. NoCache indicates that the sequence is not buffered in memory. Memory buffering of a sequence can improve the performance of the sequence.

SQL Server and Oracle sequence