Purpose: Replace the automatic increasing function of Table ID in MSSQL through this function
Theme 1: How to get a sequence value through the Sequence name
method:
1. Create a table sequence, save the value of sequence
2. Create a stored procedure getNextSequence to get the next sequence
Topic 2: How to get a sequence value through a table name
1. Create a table tablesequence, where is the column in the table, which sequence
2. Create a stored procedure CreateTableseq, put the contents of the table TableSequence into the global temporary table @@ Tmptablesequence to improve performance
3. Create a stored procedure getNextSequenceByTableName to get the next sequence
Theme 1: How to get a sequence value through the Sequence name
method:
1. Create a table sequence, save the value of sequence
Table sequence:
Table Name
type of data
Is it empty?
index
Description
Seqname
Varchar2 (50)
N
PK
SEQUENCE name
Startwith
Number
N
Start value
Increment
Number
N
Increment
LastValue
Number
N
The last value of this sequence
Minvalue
Number
N
Minimum
MaxValue
Number
N
Maximum value, 0 means no
Cycle
Bit
N
Whether it can be loop
2. Create a stored procedure getNextSequence to get the next sequence
parameter:
SEQNAME SEQUENCE name
return:
The value of the next sequence, such as an error, returns 0
Process:
1. This stored procedure acquires LastValue from the table Sequence from the parameter seqname
2. LastValue plus incrementing or decreasing, get the next sequence value (referred to as @newseq)
3. Update this record with @newseq
4. Return to @newseq
Topic 2: How to get a sequence value through a table name
1. Create a table tablesequence, where is the column in the table, which sequence
Table tablequern
Table Name
type of data
Is it empty?
index
Description
TableName
Varchar2 (50)
N
PK
Table Name
Seqname
Varchar2 (50)
N
SEQUENCE name
2. Create a stored procedure CreateTableseq, put the contents of the table TableSequence into the global temporary table @@ Tmptablesequence to improve performance
parameter:
no
return:
no
Process:
1. Judging whether there is a temporary table @@TMPTableSequence exists
2. If there is no existence, create this temporary table (Table format with tablesequence), and get data from TableSequence, put it in a temporary table @@Tmptablesequence
3. Create a stored procedure getNextSequenceByTableName to get the next sequence
parameter:
@TableName table name
return:
The value of the next sequence, such as an error, returns 0
Process:
1. This stored procedure gets the sequence name (@seqname) from the table @@TMPTABLENAME from the table @@TMPTABLENAME (@seqname)
2. Get LastValue from Table Sequence from @seqname
3. LastValue plus the increment or decreasing value, get the next sequence value (@newseq)
4. Update the corresponding record of table sequnce with @newseq
return
