mysql系列存储过程与函数

什么是存储过程？有哪些优缺点？

存储过程是一个预编译的SQL语句， 优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点 4`

1）存储过程是预编译过的，执行效率高。2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。3）安全性高，执行存储过程需要有一定权限的用户。4）存储过程可以重复使用，减少数据库开发人员的工作量。

缺点 4`

1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

关键语法

声明语句结束符，可以自定义:
DELIMITER $$ 或 DELIMITER //


声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)  

存储过程开始和结束符号:
BEGIN .... END    

变量赋值:
SET @p_in=1  

变量定义:
DECLARE l_int int unsigned default 4000000; 

创建mysql存储过程、存储函数:
create procedure 存储过程名(参数)

存储过程体:
create function 存储函数名(参数)

参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形…])

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

in

delimiter $$
create procedure param_in(in in_data int)
begin
select in_data;
set in_data=135561616;
select in_data;
end 
$$


# 设置参数，执行结果
set @in_data=1;
call param_in(@in_data)

out

out的时候是无法接收到输入的，像下面的结果，一开始进来的数据是为null的

1、编写函数

delimiter $$
create procedure param_out(out out_data int)
begin
# 先打印结果
select out_data;
# 设置结果
set out_data=1111;
# 再输出结果
select out_data;
end
$$

2、结果 结果1：结果2：

inout

输入和输出都可以

delimiter $$
create procedure param_inout(inout inout_data int)
begin
select inout_data;
set inout_data=135561616;
select inout_data;
end 
$$

set @inout_data=1;
call param_inout(@inout_data)

控制语句

(1). 变量作用域

主要是begin和end的内外层，内外层的变量的作用域不同

create procedure action_scope()

begin 
declare s1 varchar(32) default 'i am out';
select s1;

# 内层
begin
declare s1 varchar(32) default 'i am in';
select s1;
end;

# 再次输出
select s1;
end;

# 执行
call action_scope

打印结果：

(2). 条件语句

if-then-else的用法

create procedure if_else_test(in f_gender varchar(32))
begin
if f_gender="男" then
select "1";
elseif f_gender="女" then
select "0";
else
select "-1";
end if;
end;


call if_else_test("男")
call if_else_test("女")
call if_else_test("未知")

call if_else_test("男")的执行结果:

call if_else_test(“女”) 的执行结果:call if_else_test(“未知”)的执行结果:

简单case

简单case,单纯条件，没有范围

create procedure case_when_test(in var int)
begin
case var
when 0 then
select "等于0";
when 1 then
select "大于0";
when -1 then
select "小于0";
else
select "未知";
end case;
end;

call case_when_test(0);
call case_when_test(1);
call case_when_test(-1);
call case_when_test(1111);

范围case

create procedure case_when_scope(in var int)
begin
case 
when var=0 then
select "等于0";
when var>0 then
select "大于0";
when var<0 then
select "小于0";
else
select "未知";
end case;
end;

call case_when_scope(0);
call case_when_scope(11);
call case_when_scope(-100);

(3). 循环语句

while do end while

格式：while 条件 do 执行sql end while

create procedure while_test(in times int)
begin 
declare i int;
set i=0;
while i<times do
select i;
set i=i+1;
end while;
end;

# 执行
call while_test(5);

repeat···· end repeat

repeat     –循环体 until 循环条件   end repeat;

create procedure repeat_until_test(in times int)
begin 
declare i int;
set i=0;
repeat
select i;
set i=i+1;
until i>=times
end repeat;
end;

call repeat_until_test(5)

loop ·····endloop

create procedure loop_test(in var int)
begin 
declare i int;
set i=0;
# 定义标志，之后再离开标志即可
loop_label:
loop
select i;
set i=i+1;

if i>var then
leave loop_label;
end if;

end loop;
end;


call loop_test(5);

(4). iterate

跳过  类似于continue;

# iterate
create procedure iterate_test(in var int)
begin 
declare i int;
set i=0;

loop_label:
loop

# 当i=3的时候，跳出循环，继续
if i=3 then
set i=i+1;
iterate loop_label;
end if;

select i;
set i=i+1;

if i>var then
leave loop_label;
end if;

end loop;
end;


call iterate_test(5);

结合select案例

1、创建表

CREATE TABLE `student` (
  `id` varchar(64) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `class_id` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、创建存储过程|函数

# 函数定义开头
delimiter $$
# create procedure 函数名(in|out|input 参数名 参数类型)
# 参数类型 如果是varchar的话要表明长度，如果是int的话就不需要
create procedure select_student(in s_name varchar(32))
# begin和end之前写sql语句
begin
 select * from student s
 where s.name=s_name;
end
# 使用$$代表函数结束
$$

3、测试

# 先插入数据
insert into student(id,name,class_id) values(1,"walker","11")

# 设置变量，调用函数
set @s_name="walker";
call select_student(@s_name)

参考：MySQL 存储过程

admin普通

打赏 收藏 海报 链接