mysql存储过程

存储过程

预编译集合  一个单元处理

CREATE PROCEDURE sp_name routne_body

过程体由合法的SQL语句构成

过程体可以使任意SQL语句

创建没有参数的存储过程

create procedure sp1() select version()

调用：

call sp1();

创建带有参数的存储过程

create procedure removeUserById(IN pid INT UNSIGNED)

begin 

delete from users where id = pid;

end 

调用：call removeUserById(3)

alter procedure 

drop procedure spname if exsit

 

 

具有返回值 

create procedure removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)

begin

delete from users where id = p_id;

select count(id) from users INTO userNums;

end 

call removeUserAndReturnUserNums(27,@nums);

select @nums;

 

set @i = 0;用户变量   

select row count() 得到改变的行数

dilimiter // 修改定界符

 

根据年龄删除   返回删除的用户，并且返回剩余的用户  三个参数

 

create procedure removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)

begin 

delete from users where age = p_age;

select row count() INTO deleteUsers;

select count(id) from users INTO userCounts;

end 

call removeUserByAgeAndReturnInfos(20,@a,@b)