欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

存储过程生成主键

程序员文章站 2022-05-09 19:02:06
...

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