mysql 存储过程 查询后修改_mysql增删改查存储过程

----------------------------------------------------------分页--------------------------------------------------

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