----------------------------------------------------------分页--------------------------------------------------
create PROCEDURE P_GetStudentList
(
_TrueName varchar(50),
_Age int,
_PageIndex int,
_PageSize int,
out _TotalCount int
)
BEGIN
DECLARE _sql VARCHAR(200) DEFAULT 'select * from v_userlist ';-- 空格
DECLARE _countSql VARCHAR(200) DEFAULT 'select count(ID) from v_userlist ';
DECLARE _whereSql VARCHAR(200) DEFAULT ' where 1=1 ';
if(_TrueName is not null and _TrueName<>'') THEN
set _whereSql=CONCAT(_whereSql,' and TrueName like ''%',_TrueName,'%''');
END IF;
if(_Age>0) THEN
set _whereSql=CONCAT(_whereSql,' and Age=',_Age);
END if;
-- 算总记录数
set _countSql=CONCAT(_countSql,_whereSql,' into @totalCount');-- 将总记录数放在会话变量@totalCount
set @_countSql=_countSql;
PREPARE pre_count_sql from @_countSql;-- 预编译
EXECUTE pre_count_sql;
set _TotalCount=@totalCount;-- 保存总记录数
DEALLOCATE PREPARE pre_count_sql;
-- 拼接分页数据
set _whereSql=CONCAT(_whereSql,' limit ',(_PageIndex-1)*_PageSize,',',_PageSize);
set _sql=CONCAT(_sql,_whereSql);
SET @_sql=_sql;
PREPARE pre_sql from @_sql;
EXECUTE pre_sql;
DEALLOCATE PREPARE pre_sql;
END;
--------------------------------------插入-----------------------------------
CREATE PROCEDURE test_proc_ins(
IN i_id INT,
IN i_name VARCHAR(100),
OUT o_ret INT)
BEGIN
start transaction;
INSERT INTO testproc VALUES (i_id, i_name);
INSERT INTO testproc VALUES (i_id+1,i_name);
commit; -- 语句1，提交后，事务已结束
set o_ret = 1;
start transaction; -- 再启一个事务
INSERT INTO testproc VALUES (i_id+2,i_name); -- 语句2
INSERT INTO testproc VALUES (i_id+2,i_name); -- 语句3
set o_ret = 2;
commit; -- 数据正常的情况下，需要再次commit以结束事务
END;
-----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------添加---------------------------------------------------------------------------
create PROCEDURE InsertUser(
uName varchar(100),
lName varchar(100),
rId int
)
BEGIN
START TRANSACTION;
insert into userinfo(UserName,loginName,roleId) values(uName,lName,rId);
COMMIT;
END
call InsertUser('asd','asd',1);
----------------------------------------------------------删除-------------------------------------------------------------------------
create PROCEDURE DeleteUser(
uId int
)
BEGIN
START TRANSACTION;
delete from userinfo where Id=uId;
COMMIT;
END
---------------------------------------------------------修改------------------------------------------------------------------------
create PROCEDURE UpdateUser(
uName varchar(100),
lName varchar(100),
rId int,
uId int
)
BEGIN
START TRANSACTION;
Update userinfo set UserName=uName,loginName=lName,roleId=rId where Id=uId;
COMMIT;
END
-------------------------------------------------------查询分页------------------------------------------------------------------------
create PROCEDURE PageUser(
uName varchar(100),
pageIndex int,
pageSize int
)
BEGIN
DECLARE rid int;
set rid=((pageIndex-1)*pageSize);
select * from userinfo where UserName like uName LIMIT rid,pageSize;
END
---------------------------------------------------------添加修改-----------------------------------------------------------------------
create PROCEDURE UpdateAddUser(
uName varchar(100),
lName varchar(100),
rId int,
uId int
)
BEGIN
IF(uId>0) THEN
Update userinfo set UserName=uName,loginName=lName,roleId=rId where Id=uId;
END IF;
IF(uId=0) THEN
insert into userinfo(UserName,loginName,roleId) values(uName,lName,rId); END IF;END