一、登录校验接口
/*
1、登录校验接口
*/
delimiter $$
DROP PROCEDURE IF EXISTS`test_db1`.`sp_logincheck`$$
create procedure sp_logincheck(IN user_name1varchar(20),IN user_pwd1 varchar(50))
label1:BEGIN
DECLAREv_rows INT ;
DECLAREv_user_pwd varchar(50) DEFAULT '' ;
DECLAREv_user_fullname varchar(50) DEFAULT '';
DECLAREerrcode INT DEFAULT 0 ;
DECLAREerrmsg varchar(100) DEFAULT '' ;
SELECTuser_pwd,user_fullname INTO v_user_pwd,v_user_fullname FROM t_login WHEREuser_name = user_name1 ;
selectfound_rows() into v_rows ;
IFv_rows = 0 THEN
SETerrcode = 1 ;
SETerrmsg = '用户名不存在' ;
ELSE
IFv_user_pwd <> user_pwd1 THEN
SETerrcode = 2 ;
SETerrmsg = '密码错误' ;
ENDIF ;
ENDIF ;
SELECTerrcode,errmsg,v_user_fullname user_fullname ;
END$$
delimiter ;
二、获取登录者姓名接口
select user_fullname from t_login whereuser_name= ?
三、登录账号分页查询接口
/*
2、登录账号分页查询接口
*/
delimiter $$
DROP PROCEDURE IF EXISTS`test_db1`.`sp_userpagelist`$$
create procedure sp_userpagelist(inuser_name1 varchar(20),in pageindex int,in pagesize int,in sortbyvarchar(20),in descending tinyint,out rowscount int,out pagescount int,outerrmsg varchar(100))
BEGIN
declarev_sql varchar(3000) DEFAULT '' ;
declarev_sortby_sql varchar(1000) DEFAULT '' ;
seterrmsg = '' ;
SETuser_name1 = TRIM(user_name1) ;
setsortby = trim(sortby) ;
--过滤语句
IFlength(user_name1) > 0 THEN
SETv_sql = CONCAT('select sql_calc_found_rows * from t_login where user_name like''%',user_name1,'%'' ') ;
ELSE
SETv_sql = CONCAT('select sql_calc_found_rows * from t_login ') ;
ENDIF ;
--##拼接排序语句
ifsortby is not null and length(sortby) > 0 then
ifdescending = 1 then
setv_sortby_sql = concat(' order by ',sortby,' desc') ;
else
setv_sortby_sql = concat(' order by ',sortby) ;
endif ;
else
ifdescending = 1 then
setv_sortby_sql = ' order by user_name desc ' ;
else
setv_sortby_sql = ' order by user_name ' ;
endif ;
endif ;
--形成最终语句
iflength(v_sortby_sql) > 0 then
setv_sql = concat(v_sql,v_sortby_sql) ;
endif ;
setv_sql = concat(v_sql,' limit ',(pageindex-1)*pagesize,',',pagesize) ;
--##执行形成结果集返回
set@sqlselect = v_sql ;
preparestmtselect from @sqlselect ;
executestmtselect ;
deallocateprepare stmtselect;
selectfound_rows() into rowscount ;
setpagescount = ceiling((rowscount+0.0)/pagesize);
selectrowscount,pagescount,errmsg ;
end$$
delimiter ;
四、学生信息分页查询接口
/*
3、学生信息分页查询接口
*/
delimiter $$
DROP PROCEDURE IF EXISTS`test_db1`.`sp_studentpagelist`$$
create procedure sp_studentpagelist(instudent_name1 varchar(50),IN student_sex1 varchar(10),in pageindex int,inpagesize int,in sortby varchar(20),in descending tinyint,out rowscount int,outpagescount int,out errmsg varchar(100))
BEGIN
declarev_sql varchar(3000) DEFAULT '' ;
declarev_sortby_sql varchar(1000) DEFAULT '' ;
declarev_where_sql varchar(1000) DEFAULT '' ;
seterrmsg = '' ;
SETstudent_name1 = TRIM(student_name1) ;
SETstudent_sex1 = TRIM(student_sex1) ;
setsortby = trim(sortby) ;
--过滤语句
IFlength(student_name1) > 0 THEN
SETv_where_sql = CONCAT('student_name like ''%',student_name1,'%'' ') ;
ENDIF ;
IFlength(student_sex1) > 0 THEN
IFlength(v_where_sql) > 0 THEN
SETv_where_sql = CONCAT(v_where_sql,' and student_sex like ''%',student_sex1,'%''') ;
ELSE
SETv_where_sql = CONCAT('student_sex like ''%',student_sex1,'%'' ') ;
ENDIF ;
ENDIF ;
IFlength(v_where_sql) > 0 THEN
SETv_sql = CONCAT('select sql_calc_found_rowsstudent_num,student_name,student_sex,date_format(student_birth, ''%Y-%m-%d'')student_birth from t_student where ',v_where_sql) ;
ELSE
SETv_sql = CONCAT('select sql_calc_found_rowsstudent_num,student_name,student_sex,date_format(student_birth, ''%Y-%m-%d'')student_birth from t_student ') ;
ENDIF ;
--##拼接排序语句
ifsortby is not null and length(sortby) > 0 then
ifdescending = 1 then
setv_sortby_sql = concat(' order by ',sortby,' desc') ;
else
setv_sortby_sql = concat(' order by ',sortby) ;
endif ;
else
ifdescending = 1 then
setv_sortby_sql = ' order by student_num desc ' ;
else
setv_sortby_sql = ' order by student_num ' ;
endif ;
endif ;
--形成最终语句
iflength(v_sortby_sql) > 0 then
setv_sql = concat(v_sql,v_sortby_sql) ;
endif ;
setv_sql = concat(v_sql,' limit ',(pageindex-1)*pagesize,',',pagesize) ;
--##执行形成结果集返回
set@sqlselect = v_sql ;
preparestmtselect from @sqlselect ;
executestmtselect ;
deallocateprepare stmtselect;
selectfound_rows() into rowscount ;
setpagescount = ceiling((rowscount+0.0)/pagesize);
selectrowscount,pagescount,errmsg ;
end$$
delimiter ;
五、登录账号增删改接口
/*
4、登录账号增删改接口
*/
delimiter $$
DROP PROCEDURE IF EXISTS `test_db1`.`sp_updateuser`$$
create procedure sp_updateuser(IN optypetinyint,IN user_name1 varchar(20),IN user_pwd1 varchar(50),IN user_fullname1varchar(50))
label1:BEGIN
DECLAREerrcode INT DEFAULT 0 ;
DECLAREerrmsg varchar(100) DEFAULT '' ;
declarecontinue handler for SQLEXCEPTION set errcode = 2;
IFoptype = 1 THEN
--新增
IFEXISTS(SELECT * FROM t_login WHERE user_name = user_name1) THEN
SETerrcode = 1 ;
SETerrmsg = '用户名已经存在' ;
ELSE
INSERTINTO t_login(user_name,user_pwd,user_fullname)VALUES(user_name1,user_pwd1,user_fullname1) ;
ENDIF ;
ENDIF ;
IFoptype = 2 THEN
--修改
IFNOT EXISTS(SELECT * FROM t_login WHERE user_name = user_name1) THEN
SETerrcode = 2 ;
SETerrmsg = '用户名不存在' ;
ELSE
UPDATEt_login SET user_pwd = user_pwd1,user_fullname=user_fullname1 WHERE user_name =user_name1 ;
ENDIF ;
ENDIF ;
IFoptype = 3 THEN
--删除
IFNOT EXISTS(SELECT * FROM t_login WHERE user_name = user_name1) THEN
SETerrcode = 3 ;
SETerrmsg = '用户名不存在' ;
ELSE
DELETEFROM t_login WHERE user_name = user_name1 ;
ENDIF ;
ENDIF ;
SELECTerrcode,errmsg ;
END$$
delimiter ;
六、学生信息增删改接口
/*
5、学生信息增删改接口
*/
delimiter $$
DROP PROCEDURE IF EXISTS `test_db1`.`sp_updatestudent`$$
create procedure sp_updatestudent(IN optypetinyint,IN student_num1 bigint,IN student_name1 varchar(50),IN student_sex1varchar(10),IN student_birth1 VARCHAR(10))
label1:BEGIN
DECLAREerrcode INT DEFAULT 0 ;
DECLAREerrmsg varchar(100) DEFAULT '' ;
declarecontinue handler for SQLEXCEPTION set errcode = 2;
IFoptype = 1 THEN
--新增
IFEXISTS(SELECT * FROM t_student WHERE student_num = student_num1) THEN
SETerrcode = 1 ;
SETerrmsg = '该学生已经存在' ;
ELSE
INSERTINTO t_student(student_num,student_name,student_sex,student_birth)VALUES(student_num1,student_name1,student_sex1,student_birth1) ;
ENDIF ;
ENDIF ;
IFoptype = 2 THEN
--修改
IFNOT EXISTS(SELECT * FROM t_student WHERE student_num = student_num1) THEN
SETerrcode = 2 ;
SETerrmsg = '该学生不存在' ;
ELSE
UPDATEt_student SET student_name = student_name1,student_sex=student_sex1,student_birth=student_birth1WHERE student_num = student_num1 ;
ENDIF ;
ENDIF ;
IFoptype = 3 THEN
--删除
IFNOT EXISTS(SELECT * FROM t_student WHERE student_num = student_num1) THEN
SETerrcode = 3 ;
SET errmsg = '该学生不存在' ;
ELSE
DELETEFROM t_student WHERE student_num = student_num1 ;
ENDIF ;
ENDIF ;
SELECTerrcode,errmsg ;
END$$
delimiter ;
网友评论