Variable in Function

[jaevencooler]

Moin, Moin,

ich habe auf meiner mySQL Instance die fehlenden Sequencen aus der Oracle wie folgt "ersetzt" :

Code:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `nextval`(`seq_name` varchar(100)) RETURNS bigint(20)
BEGIN
  declare cur_val bigint(20);

  SELECT sequence_cur_value 
  INTO cur_val
  FROM gemiii.sequence_data
  WHERE sequence_name = seq_name;

  IF cur_val IS NOT NULL THEN
    UPDATE gemiii.sequence_data
    SET sequence_cur_value = IF ( (sequence_cur_value + sequence_increment) > sequence_max_value,
                                  IF ( sequence_cycle = TRUE,
                                       sequence_min_value,
                                       NULL
                                     ),
                                  sequence_cur_value + sequence_increment
                                )
    WHERE sequence_name = seq_name;
  END IF;

  RETURN cur_val;
END

die Tabelle dazu sieht folgt aus :

CREATE TABLE `sequence_data` (
  `sequence_name` varchar(100) NOT NULL,
  `sequence_increment` int(11) unsigned NOT NULL DEFAULT '1',
  `sequence_min_value` int(11) unsigned NOT NULL DEFAULT '1',
  `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT '18446744073709551615',
  `sequence_cur_value` bigint(20) unsigned DEFAULT '1',
  `sequence_cycle` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sequence_name`),
  UNIQUE KEY `sequence_name_UNIQUE` (`sequence_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

funktioniert recht gut !

Beste Grüße Michael