存储过程生成主键

存储过程生成主键 MySQL delimiter $$CREATE PROCEDURE generateKeys(in pm_name varchar(20))begindeclare curr_Key varchar(20);declare next_Key varchar(20);declare prefix_Value varchar(5);declare suffix_value int;select x.nextValue,x.DefaultPref

存储过程生成主键 MySQL

delimiter $$
CREATE PROCEDURE generateKeys(in pm_name varchar(20))
begin
    declare curr_Key varchar(20);
    declare next_Key varchar(20);
    declare prefix_Value varchar(5);
    declare suffix_value int;

    select x.nextValue,x.DefaultPrefix,x.suffixValue into curr_Key,prefix_Value,suffix_Value from pm_tableprimaryinfo x where x.table_name=pm_name;

    if (curr_Key is null or curr_Key='') THEN
        set suffix_Value='0';
    else
        set suffix_Value = suffix_Value+1;
    END if;
    set curr_Key = concat(prefix_Value, suffix_Value);


    update pm_tableprimaryinfo x set x.nextValue=curr_Key, x.suffixValue=suffix_Value where x.table_name=pm_name;

    SELECT curr_Key;

END

call generateKeys(tableName);

CREATE TABLE `pm_tableprimaryinfo` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `table_name` VARCHAR(30) NULL DEFAULT NULL,
    `primaryName` VARCHAR(30) NULL DEFAULT NULL,
    `nextValue` VARCHAR(50) NULL DEFAULT NULL,
    `DefaultPrefix` VARCHAR(30) NULL DEFAULT NULL,
    `suffixValue` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)