存储过程与函数
什么是存储过程？有哪些优缺点？
存储过程是一个预编译的SQL语句， 优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。 如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
优点 4`
1）存储过程是预编译过的，执行效率高。 2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。 3）安全性高，执行存储过程需要有一定权限的用户。 4）存储过程可以重复使用，减少数据库开发人员的工作量。
缺点 4`
1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。 2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。 3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。 4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。
关键语法
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`声明语句结束符，可以自定义:
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 存储函数名(参数)` </pre>
参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如： CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
- IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
- OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
- INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
in
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`delimiter
设置参数，执行结果
set @in_data=1;
call param_in(@in_data)` </pre>
out
out的时候是无法接收到输入的，像下面的结果，一开始进来的数据是为null的
1、编写函数
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`delimiter $
create procedure param_out(out out_data int)
begin
先打印结果
select out_data;
设置结果
set out_data=1111;
再输出结果
select out_data;
end
$` </pre>
inout
输入和输出都可以
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`delimiter
set @inout_data=1;
call param_inout(@inout_data)` </pre>
控制语句
(1). 变量作用域
主要是begin和end的内外层，内外层的变量的作用域不同
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`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` </pre>
打印结果：
(2). 条件语句
if-then-else的用法
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`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("未知")` </pre>
call if_else_test("男")的执行结果:
简单case
简单case,单纯条件，没有范围
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`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);` </pre>
范围case
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`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);` </pre>
(3). 循环语句
while do end while
格式： while 条件 do 执行sql end while
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`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);` </pre>
<figcaption style="margin-top: 5px; text-align: center; color: #888; font-size: 14px;">image.png</figcaption>
repeat···· end repeat
repeat --循环体 until 循环条件 end repeat;
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`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)` </pre>
<figcaption style="margin-top: 5px; text-align: center; color: #888; font-size: 14px;">image.png</figcaption>
loop ·····endloop
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;"> `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);` </pre>
<figcaption style="margin-top: 5px; text-align: center; color: #888; font-size: 14px;">image.png</figcaption>
(4). iterate
跳过 类似于continue;
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`# 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);` </pre>
<figcaption style="margin-top: 5px; text-align: center; color: #888; font-size: 14px;">image.png</figcaption>
结合select案例
1、创建表
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">CREATE TABLEstudent(idvarchar(64) NOT NULL,namevarchar(255) DEFAULT NULL,class_idvarchar(64) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; </pre>
2、创建存储过程|函数
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`# 函数定义开头
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
使用$代表函数结束
$` </pre>
3、测试
<pre class="custom" data-tool="mdnice编辑器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`# 先插入数据
insert into student(id,name,class_id) values(1,"walker","11")
设置变量，调用函数
set @s_name="walker";
call select_student(@s_name)` </pre>
参考： MySQL 存储过程
