序号发生器：sp_get_uni_id

说明

序号发生器存储过程sp_get_uni_id用以修改系统表 id_codeid 的值。

目前作用于两个功能点：
1）lczCreater的文件上传；
2）lczCreater的主从表模型的主键自动生成。

源码

MySql的SQL语句

CREATE PROCEDURE sp_get_uni_id(
 IN in_name VARCHAR(50),
 IN in_step INT,
 OUT out_value VARCHAR(50)
)
BEGIN
  START TRANSACTION;

  UPDATE ID_CODEID SET ID_VALUE = ID_VALUE+in_step WHERE ID_NAME = in_name;
  SELECT ID_VALUE - in_step + 1 INTO out_value FROM ID_CODEID WHERE ID_NAME = in_name;
  IF out_value IS NULL
  THEN
    INSERT INTO ID_CODEID (ID_NAME, ID_VALUE) VALUES (in_name, in_step);
    SET out_value = 1;
  END IF;

  COMMIT;
END

华为GaussDB的SQL语句

CREATE OR REPLACE PROCEDURE sp_get_uni_id(
    in_name    IN   VARCHAR(50),
    in_step    IN   INTEGER,
    out_value  OUT  INTEGER
)
AS
BEGIN
  UPDATE ID_CODEID SET ID_VALUE = ID_VALUE+in_step WHERE ID_NAME = in_name;
  SELECT ID_VALUE - in_step + 1 INTO out_value FROM ID_CODEID WHERE ID_NAME = in_name;
  IF out_value IS NULL
  THEN
    INSERT INTO ID_CODEID (ID_NAME, ID_VALUE) VALUES (in_name, in_step);
    SET out_value = 1;
  END IF;
END;