mysql存储过程的增删_MySql增删改查存储过程

创建存储过程关键字： PROCEDURE

同时要以Begin开始，End来结尾

定义参数时要以(参数1，参数1的类型，......)这种格式去定义，先参数在定义参数类型，用逗号隔开

/*显示*/

create PROCEDURE HomeShow()

BEGIN

select * from class;

END

/*链接查询显示*/

create  PROCEDURE HomeShow()

BEGIN

select * from student join class on student.CID=class.CID;

END

/*存储过程-添加*/

create PROCEDURE HomeAdd(Name1 varchar(220),Age1 int,Sex1 int,CID1 int)

BEGIN

INSERT INTO student (Name,Age,Sex,CID) VALUES(Name1,Age1,Sex1,CID1);

END

/*存储过程-删除*/

create PROCEDURE HomeDel(ID1 int)

BEGIN

delete from student where ID=ID1;

END

/*修改*/

create PROCEDURE HomeUpt(Name1 varchar(220),Age1 int,Sex1 int,CID1 int,ID int)

BEGIN

update student SET Name=Name1,Age=Age1,Sex=Sex1,CID=CID1 WHERE ID=ID1;

END

/*分页*/

create  PROCEDURE ShowPage2(DataStr varchar(200),Page int,PageSize int,out PageCount int)

BEGIN

SET @SqlStrsql=CONCAT( ' select student.*,class.cname from student join class on student.CID=class.CID where  ', DataStr ,' LIMIT ', (Page-1)*PageSize,' , ',PageSize);

PREPARE Strsql from @SqlStrsql;

EXECUTE Strsql;

DEALLOCATE PREPARE Strsql;

SET @SqlStr=CONCAT( ' select count(*) into @Count from student join class on student.CID=class.CID where  ', DataStr );

PREPARE Str from @SqlStr;

EXECUTE Str;

DEALLOCATE PREPARE Str;

select @Count INTO PageCount;

END