SQL数据库存储过程

添加修改

create PROCEDURE sp_insert_1(
pid int,
pname varchar(200),
page varchar(200),
pscore int,
out code int,
out msg varchar(200)
)
lb:BEGIN
DECLARE t_error int DEFAULT 0;
DECLARE CONTINUE HANDLER for SQLEXCEPTION set t_error=1;
start TRANSACTION;
IF pid=0
THEN /*添加*/
set @exist=(select count(*) from studentweek3 where name =pname);
IF @exist>0 THEN/*存在*/
set code=1;set msg='用户名已存在';
ROLLBACK;LEAVE lb;
ELSE/*不存在*/
insert into studentweek3(name,age,score) values(pname,page,pscore);
END IF;
ELSE/*修改*/
update studentweek3 set name=pname,age=page,score=pscore where id=pid;
END IF;
/*错误判断*/
IF t_error=1 THEN set code=1;set msg='保存失败';ROLLBACK;
ELSE
set code=0;set msg='保存成功';COMMIT;
END IF;
END

 

分页

create PROCEDURE sp_pager(pageindex int,pagesize int,out pagecount int,out datacount int,name varchar(50),sex varchar(50),xuehao int)
BEGIN
set @pageindex=pageindex;
set @pagesize=pagesize;
set @pagecount=0;
set @datacount=0;
set @name=name;
set @sex=sex;
set @xuehao=xuehao;
/*分页sql*/
set @pagesql=concat(
"select * from student where 1=1 ",
if(@name='','',concat(' and name like "%',@name,'%" ')),
if(@sex='','',concat(' and sex="',@sex,'"')),
if(@xuehao=0,'',concat(' and id="',@xuehao,'"')),
' limit ',(@pageindex-1)*@pagesize,',',@pagesize,''
);
PREPARE s1 from @pagesql;
EXECUTE s1;/*执行分页查询*/
/*数据总数输出变量*/
set @countSql=concat('set @datacount=(select count(*) from student where 1=1 ',
if(@name='','',concat(' and name like "%',@name,'%" ')),
if(@sex='','',concat(' and sex="',@sex,'"')),
if(@xuehao=0,'',concat(' and id="',@xuehao,'"')),
')');
PREPARE s2 from @countsql;
EXECUTE s2;
set datacount=@datacount;/*数据总数输出参数赋值*/
/*页总数输出变量赋值*/
set @pagecount=CEIL(@datacount*1.0/@pagesize);
set pagecount=@pagecount;/*赋值输出变量页面总数*/
END