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