存储过程生成主键

存储过程生成主键 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`)
)