存储过程
预编译集合 一个单元处理
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)