Home > Documentation > Oracle to MySQL

Before digging into sequence conversion from Oracle to MySQL it is important to understand if it is really necessary. If Oracle sequence is just used to generate unique column IDs, it can be replaced by MySQL AUTO_INCREMENT column.

If scenario of using sequence is more complicated, for example single sequence is shared by multiple tables, the task is to emulate Oracle DROP SEQUENCE, CREATE SEQUENCE and NEXTVAL statements via MySQL user-defined functions and stored procedures.

CREATE SEQUENCE

Sequence can be emulate via a service table that stores sequence name, start value and increment step:

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

Now sequence can be created as: CALL CreateSequence('sequence1', 1, 1);

NEXTVAL

Function emulating Oracle NEXTVAL retrieves current 'next_value' and then increments it by 'inc_step':

  DROP FUNCTION IF EXISTS NextVal;
  DELIMITER //
  CREATE FUNCTION NextVal (IN seq_name VARCHAR(30))
    RETURNS INT
  BEGIN
     DECLARE next_val INT;
     -- Retrieve current value
     SELECT next_value INTO next_val FROM __sequences_tbl__
       WHERE name = seq_name;
     -- Update sequence table with new value
     UPDATE __sequences_tbl__
       SET next_value = next_value + inc_step 
       WHERE name = seq_name;
     RETURN next_val;
  END
  //
  DELIMITER ;

Now the next value of sequence can be obtained as: SELECT NextVal('sequence1');

DROP SEQUENCE

According to service table approach to emulating sequences, dropping a sequence is equal to deleting the corresponding row from the sequence table:

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

Now the sequence can be dropped as: CALL DropSequence('sequence1');