首先来看两张表:
drop table if EXISTS FIELD_INDEX
CREATE TABLE FIELD_INDEX
(
SCHEMA_NAME VARCHAR(100) NOT NULL,
FIELD_NAME VARCHAR(100) NOT NULL,
NEXT_INDEX INT NOT NULL,
CONSTRAINT FIELD_INDEX_PK PRIMARY KEY (SCHEMA_NAME,FIELD_NAME)
);
insert into field_index values('web_user','user_id','1000');
select * from field_index;
drop table if EXISTS web_user
CREATE TABLE WEB_USER(
USERID VARCHAR(30) NOT NULL,
USERNAME VARCHAR(30) NOT NULL,
NICKNAME VARCHAR(30) NOT NULL,
EMAIL VARCHAR(50),
PASSWORD VARCHAR(30) NOT NULL,
REGISTTIME TIMESTAMP(14) NOT NULL DEFAULT NOW(),
STATUSID VARCHAR(30) DEFAULT '1',
CONSTRAINT WEBUSER_PK PRIMARY KEY (USERID)
)
然后来看下存储过程:
drop procedure if EXISTS getNextIndex;
CREATE PROCEDURE getNextIndex(in tablename varchar(100),in columnname varchar(100),out nextindex varchar(100))
BEGIN
declare stopFlag int;
DECLARE CUR_1 cursor for select next_index from field_index where schema_name = tablename and field_name = columnname ;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
set nextindex = -1;
open cur_1;
REPEAT
fetch cur_1 into nextindex;
until stopFlag = 1
end REPEAT ;
CLOSE cur_1;
if nextindex = -1 then
insert into field_index values(tablename,columnname,'10001');
set nextindex = '10000';
else
update field_index set next_index = next_index + 1 where schema_name = tablename and field_name = columnname ;
end if;
select nextindex;
END;
call getNextIndex('web_user','user_id',@sss);
call getNextIndex('web_user','user_id_ss',@sss);
存储过程中我们也可以使用prepareStmt的功能。
drop procedure if EXISTS getNextIndex;
CREATE PROCEDURE getNextIndex(in schema_name varchar(100),in field_name varchar(100))
BEGIN
DECLARE sqls varchar(100) ;
declare schemaname varchar(4000);
declare fieldname varchar(4000);
set schemaname = schema_name;
set fieldname = field_name;
set sqls = concat('select * from field_index' );
set sqls = concat(sqls,' where SCHEMA_NAME = ? and field_name = ? ');
set @ssql = sqls;
set @param1 = schemaname;
set @param2 = fieldname;
prepare stmt from @ssql;
execute stmt USING @param1,@param2;
END;
call getNextIndex('web_user','user_id');