MySQL Stored Procedure (5) -- sequence implementation

In
Oracle
Medium,
Sequence
Multiple tables and multiple fields can share one unique value.
MySQL
The auto-incrementing Column exists, which can basically meet
PK
. However, the auto-increment column has restrictions:

A.
It can only be used for one field in the Table. One column cannot have more than two auto-increment columns at the same time.
;

B.
The auto-increment column must be defined
Key
(
PK
Or
FK
)
;

C.
Auto-increment Columns cannot be shared by multiple tables.
;

D.
When
Insert
The statement does not include an auto-increment field or sets its value
Null
This value is automatically filled in.

If the field sequence is not required to increase, you can
MySQL
Implementation sequence:

SQL
Code 5-1:

Drop table if exists sequence; </p> <p> -- create a sequence TABLE and specify the seq column as an unsigned big integer. The unsigned value 0 (default) is supported) up to 18446744073709551615 (0 to 2 ^ 64-1 ). <Br/> create table sequence (<br/> name VARCHAR (50) not null, <br/> current_value bigint unsigned not null default 0, <br/> increment int not null default 1, <br/> primary key (name) -- duplicate seq is NOT allowed. <Br/>) ENGINE = InnoDB; </p> <p> DELIMITER/</p> <p> drop function if exists currval/</p> <p> create function currval (seq_name VARCHAR (50 )) <br/> returns bigint <br/> BEGIN <br/> DECLARE value BIGINT; <br/> SELECT current_value INTO value <br/> FROM sequence <br/> WHERE upper (name) = upper (seq_name); -- case insensitive. <br/> RETURN value; <br/> END; <br/>/</p> <p> DELIMITER; </p> <p> DELIMITER/</p> <p> drop function if exists nextval/</p> <p> create function nextval (seq_name VARCHAR (50 )) <br/> returns bigint <br/> BEGIN <br/> DECLARE value BIGINT; <br/> UPDATE sequence <br/> SET current_value = current_value + increment <br/> WHERE upper (name) = upper (seq_name ); <br/> RETURN currval (seq_name); <br/> END; <br/>/</p> <p> DELIMITER; </p> <p> DELIMITER/</p> <p> drop function if exists setval/</p> <p> create function setval (seq_name VARCHAR (50 ), value BIGINT) <br/> returns bigint <br/> BEGIN <br/> UPDATE sequence <br/> SET current_value = value <br/> WHERE upper (name) = upper (seq_name); <br/> RETURN currval (seq_name); <br/> END; <br/>/</p> <p> DELIMITER;

In
SQL
Sequence used in:

SQL
Code 5-2:

Create a sequence and insert a value to the sequence table: <br/> mysql> insert into sequence set name = 'myseq '; <br/> View the sequence that has been created: <br/> mysql> select * from sequence; <br/> + ------- + --------------- + --------- + <br/> | name | current_value | increment | <br/> + ------- + --------------- + ----------- + <br/> | myseq | 0 | 1 | <br/> + ------- + --------------- + ----------- + <br/> 1 row in set (0.00 sec) <br/> obtain the next value of the sequence, which is used for the first time. Therefore, the value is 1: <br/> mysql> select nextval ('myseq '); <br/> + ---------------- + <br/> | nextval ('myseq ') | <br/> + ---------------- + <br/> | 1 | <br/> + ------------------ + <br/> 1 row in set (0.00 sec)

Use the sequence in the Stored Procedure
SQL
Code 1-1
Created in
Testproc
Table), this stored procedure returns
ID
If insertion fails, return
-1
:

SQL code 5-3:

Create procedure test_sequence (IN I _name VARCHAR (100), <br/> OUT o_ret BIGINT) <br/> BEGIN <br/> DECLARE EXIT HANDLER FOR SQLWARNING, NOT FOUND, SQLEXCEPTION set o_ret =-1; <br/> set o_ret = nextval ('myseq '); <br/> insert into testproc VALUES (o_ret, I _name ); <br/> insert into testproc3 VALUES (o_ret, I _name); <br/> END;