存储过程与函数
什么是存储过程？有哪些优缺点？
存储过程是一个预编译的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)
