存储过程入门

1 存储过程是什么？

带有逻辑的sql语句

2 存储过程有哪些优点？

​ 2.1 快！ 对于较为复杂的sql，存储过程执行速度更快

​ 2.2 减少服务器压力

​ 2.3 稳定
缺点暂时不提

3 一些简单的存储过程分享

3.1创建一个简单查询的存储过程

DELIMITER $
CREATE PROCEDURE simple_procedure()
BEGIN
SELECT id FROM student;
END $

#调用存储过程
call simple_procedure();

#删除存储过程
drop PROCEDURE simple_procedure;

3.2一个带输入参数的简单查询

DELIMITER $
CREATE PROCEDURE simple_in(in aid int)
BEGIN
SELECT id FROM student where id = aid;
END $

call simple_in(1)
drop PROCEDURE simple_in;

3.3一个带输出参数的简单查询

DELIMITER $
CREATE PROCEDURE simple_out(out str varchar(20))
BEGIN
set str=“this is simple out”;
END $

call simple_out(@str);
select @str
drop PROCEDURE simple_in;

3.3一个输入+输出的存储过程

DELIMITER $
CREATE PROCEDURE simple_in_and_out(in aid int, out str2 varchar(20))
BEGIN
select id from student where id = aid;
set str=“this is simple in out”;
END $
call simple_in_and_out(2, @str2)
select @str2
drop PROCEDURE simple_in_and_out;

3.4一个带if语句的存储过程

输入为1,输出aid is 1。否则输出aid is not 1

DELIMITER $
CREATE PROCEDURE simple_if(in aid int, out str_if varchar(20))
BEGIN
if aid =1 then
set str_if=“aid is 1”;
else
set str_if=“aid is not 1”;
end if;
END $

call simple_if(1, @str3);
select @str3
call simple_if(2, @str4);
select @str4
drop PROCEDURE simple_if;

3.5一个带循环的存储过程

计算0到10的和

delimiter $
create procedure simple_while(in num int,out result int)
begin
declare i int default 1;
declare sum int default 0;
while i<=num do
set sum = sum+i;
set i = i+1;
end while;
set result = sum;
END $

call simple_while(10,@result);
select @result
drop PROCEDURE simple_while;

简单插入语句

delimiter $
create procedure simple_insert(in num int)
begin
DECLARE count int default 10;
while num<=count do
INSERT INTO stu ( name, time) VALUES
(‘123123412’+num, NOW());
set num = num+1;
end while;
END $

call simple_insert(1);
drop procedure simple_insert;