MySQL存储过程小记

首先来看两张表：

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');