- --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`)
- )
- --该片段来自于http://www.codesnippet.cn/detail/250220148802.html
来源: http://www.codesnippet.cn/detail/250220148802.html