如何在mysql数据里使用Oracle序列语法.NEXTVAL和.CURVAL.
假设在mysql中序列的语法是:
NEXTVAL('sequence');
CURRVAL('sequence');
SETVAL('sequence',value);
--1创建序列表
CREATE TABLE`lndl`.`LNDL_SEQUENCE` (
`NAME` varchar(50) NOT NULL,
`CURRENT_VALUE` int(11) NOT NULL,
`INCREMENT` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--2序列表里插入如下记录
insert into test.LNDL_SEQUENCE values('CONF_DEV',10000,1);
--3创建nextval function的代码如下:
DELIMITER $$
DROP FUNCTION IF EXISTS `nextval` $$
CREATE DEFINER=`lndl`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
UPDATE LNDL_SEQUENCE
SET CURRENT_VALUE = CURRENT_VALUE + INCREMENT
WHERE NAME = seq_name;
RETURN currval(seq_name);
END $$
DELIMITER ;
-- 4创建setval function的代码如下:
DELIMITER $$
DROP FUNCTION IF EXISTS `setval` $$
CREATE DEFINER=`lndl`@`%` FUNCTION `setval`(seq_name VARCHAR(50), value INTEGER) RETURNS int(11)
BEGIN
UPDATE LNDL_SEQUENCE
SET CURRENT_VALUE = value
WHERE NAME = seq_name;
RETURN currval(seq_name);
END $$
DELIMITER ;
-- 5创建currval function的代码如下:
DELIMITER $$
DROP FUNCTION IF EXISTS `currval` $$
CREATE DEFINER=`lndl`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT CURRENT_VALUE INTO value
FROM LNDL_SEQUENCE
WHERE NAME = seq_name;
RETURN value;
END $$
DELIMITER ;
--6测试
mysql> select * from lndl_sequence;
+----------+---------------+-----------+
| name| current_value | increment |
+----------+---------------+-----------+
| conf_dev |10010 |1 |
+----------+---------------+-----------+
1 row in set
mysql> select nextval('conf_dev');
+---------------------+
| nextval('conf_dev') |
+---------------------+
|10011 |
+---------------------+
1 row in set