Sequences Emulation - Oracle to MySQL Migration

MySQL does not provide a sequence object, but you can use stored procedures and user-defined functions to emulate DROP SEQUENCE, CREATE SEQUENCE statements and NEXTVAL function.

Note that often it is enough to use AUTO_INCREMENT columns in MySQL instead of sequences, especially when they are just used for generate column IDs and not shared by multiple tables.

CREATE SEQUENCE in MySQL

You can use a table to store sequence names, their current values, increments and other properties.

Stores procedure implementing CREATE SEQUENCE:

MySQL

  DROP PROCEDURE IF EXISTS CreateSequence;
 
  DELIMITER //
 
  CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT)
  BEGIN
     -- Create a table to store sequences
     CREATE TABLE IF NOT EXISTS _sequences
     (
         name VARCHAR(70) NOT NULL UNIQUE,
         next INT NOT NULL,
         inc INT NOT NULL
     );
 
     -- Add the new sequence
     INSERT INTO _sequences VALUES (name, start, inc);  
  END
  //
  DELIMITER ;

Now you can execute the following statement in MySQL instead of CREATE SEQUENCE:

  -- Create a sequence
  CALL CreateSequence('cities_id_seq', 1, 1);

DROP SEQUENCE in MySQL

To drop a 'sequence' you have to call a procedure that deletes the corresponding row from the sequence table.

Stores procedure implementing DROP SEQUENCE:

MySQL

  DROP PROCEDURE IF EXISTS DropSequence;
 
  DELIMITER //
 
  CREATE PROCEDURE DropSequence (vname VARCHAR(30))
  BEGIN
     -- Drop the sequence
     DELETE FROM _sequences WHERE name = vname;  
  END
  //
  DELIMITER ;

Now you can execute the following statement in MySQL instead of DROP SEQUENCE:

  -- Drop a sequence
  CALL DropSequence('cities_id_seq');

NEXTVAL in MySQL

Now you can use the following function to get the next sequence value in MySQL:

  DROP FUNCTION IF EXISTS NextVal;
 
  DELIMITER //
 
  CREATE FUNCTION NextVal (vname VARCHAR(30))
    RETURNS INT
  BEGIN
     -- Retrieve and update in single statement
     UPDATE _sequences
       SET next = (@next := next) + 1
       WHERE name = vname;
 
     RETURN @next;
  END
  //
  DELIMITER ;

Note that NextVal function assigns the current value to a session variable @next and updates the sequence table in single UPDATE statement i.e protected from race condition and can be safely executed by multiple concurrent sessions.

Now you can call this function to get next sequence value, for example in a SELECT statement:

  -- Get next values
  SELECT NextVal('cities_id_seq');

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.

Discussion

Raj Singh, June 18, 2014 4:31 am

Dear Team,

       I want to create a procedure which will create dynamic procedures in mysql below is the my procedure, but when i am trying to execute it, it's not executing successfully, please guide me

DROP PROCEDURE IF EXISTS emp_prc; DELIMITER $$ CREATE PROCEDURE emp_prc (INOUT p_err_message VARCHAR(4000)) BEGIN

   SET @l_query =  'DROP PROCEDURE if EXISTS emp_prc_dyn;
		delimiter $$ 
		create procedure emp_prc_dyn(inout p_err_message varchar(4000))
		begin
			declare branch_code varchar(20);
			select xcode 
			into branch_code
			from xbranch limit 1; 
			insert into test values(branch_code);
			commit;
	     end $$ ';
   PREPARE stmt FROM @l_query;
   SELECT  stmt;
   EXECUTE stmt;     
   DEALLOCATE PREPARE stmt;

END $$

Thanks & Regards Raj Singh.

You could leave a comment if you were logged in.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference