存储过程

了解几个容易混淆的概念

1. 存储过程
2. 视图
3. 事务
4. 函数

视图(view):

可以理解成临时表, 如果你每次都需要连表查询, 而且sql老长老长了, 那就试试视图吧, 你会感谢我的.

事务(transaction):

一组sql语句, 要么全部执行成功, 要么都不执行

函数(function):

执行特定功能的代码段, 和存储过程(procedure)很像
区别在于, 函数有且只有个返回值, 存储过程对返回值的个数没有要求, 没有返回值也是可以的

了解存储过程

存储过程的概念

• 存储过程和函数(执行特定功能的代码段)很像, 语法略有不同,
• 存储过程主要用来增删改, 视图主要用来查

存储过程的优缺点

优点:

1. 提高效率
2. 代码复用
3. 减少开发人员的工作量
4. 对存储过程设置权限, 安全性较高

缺点:

• 用起来方便, 改起来费劲...

存储过程参数介绍

• 函数有参数, 存储过程也有参数
• 定义参数时, 需要有参数名, 参数类型, 参数方向
• 存储过程的参数方向有三个(in, out, inout)
  • in: 只把值传给存储过程
  • out: 只从存储过程中返回值
  • inout: 参数在存储过程中进行运算, 然后返回

如果没有写方向, 默认是in

设计存储过程

创建存储过程

使用存储过程, 为教师表添加数据, 只需要姓名和职称

创建教师表

drop table if exists teacher;

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL auto_increment primary key,
  `salary` int(11) NOT NULL,
  `title` char(20) NOT NULL,
  `name` char(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher`
    (`id`, `salary`, `title`, `name`) 
    VALUES 
    (1, 1500, '初级讲师', '教师1');
INSERT INTO `teacher`
    (`id`, `salary`, `title`, `name`) 
    VALUES 
    (2, 5500, '高级讲师', '教师2');
INSERT INTO `teacher`
    (`id`, `salary`, `title`, `name`) 
    VALUES 
    (3, 6500, '金牌讲师', '教师3');
INSERT INTO `teacher`
    (`id`, `salary`, `title`, `name`) 
    VALUES 
    (4, 7500, '教授', '教师4');
INSERT INTO `teacher`
    (`id`, `salary`, `title`, `name`) 
    VALUES 
    (5, 8500, '金牌教授', '教师5');

创建存储过程

drop procedure if exists add_teacher;
create procedure add_teacher(
    in teacher_name varchar(20),
    in teacher_title varchar(20)
) begin
INSERT INTO `teacher`
    (`salary`, `title`, `name`) 
VALUES 
    (3500, teacher_title, teacher_name);
end;

调用存储过程

call add_teacher('张三','特高级教师');
call add_teacher('李四','特高级教师他爹');

以上是创建并用存储过程的实例.

其中我们使用了in类型的参数, 接下来试试out

再看个例子, 添加数据后, 返回该表一共有多少条记录, 也就是说
添加教师后, 可以查看已经有多少老师了

创建存储过程

drop procedure if exists add_teacher;
create procedure add_teacher(
    in teacher_name varchar(20),
    in teacher_title varchar(20),
    out teacher_count int) 
begin
  INSERT INTO `teacher`
    (`salary`, `title`, `name`) 
    VALUES 
    (3500, teacher_title, teacher_name);
  select count(*) into teacher_count from teacher;
end;

调用存储过程

call add_teacher('张三','特高级教师', @count);
select @count;
call add_teacher('李四','特高级教师他爹', @count);
select @count;

着重讲一下 inout

表示该参数, 既用于输入, 有用于输出;
比如发年终奖(14薪),最后一个月, 发3个月的工资
我们的需求, 输入一个数字(每年的薪水, 14薪, 还是15薪), 返回最后一个月需要发多少钱

drop procedure if exists count_salary;
create procedure count_salary(
    in month int, 
    inout salary int
) begin 
  select salary*(month - 11) into salary;
end;

set @salary = 2000;
call count_salary(14,@salary);
select @salary;

为了方便理解, 我的变量使用中文...

drop procedure if exists 计算工资;
create procedure 计算工资(
    in 月份 int, 
    inout 月工资 int
) begin 
  select 月工资*(月份 - 11) into 月工资;
end;

set @月工资 = 2000;
call 计算工资(15,@月工资);
select @月工资;

in,out 和 inout的区别何在呢?

假设我们想计算一个数字的平方, 我们分别用inout 和 in,out来试一下

drop procedure if exists count_square;
create procedure count_square(inout a int) BEGIN 
select a*a into a;
end;

set @a = 3;
call count_square(@a);
select @a;

同样提供中文版

drop procedure if exists 计算平方;
create procedure 计算平方(inout 数字 int) BEGIN 
select 数字*数字 into 数字;
end;

set @数字 = 3;
call 计算平方(@数字);
select @数字;

drop procedure if exists count_square2;
create procedure count_square2(in a int, out b int) begin 
select a*a into b;
end;

call count_square2(3,@b);
select @b;

drop procedure if exists 再计算平方;
create procedure 再计算平方(in 第一个数字 int, out 第二个数字 int) begin 
select 第一个数字*第一个数字 into 第二个数字;
end;

call 再计算平方(3,@第二个数字);
select @第二个数字;

查看存储过程信息

show procedure status like 'salary';

show create procedure add_teacher;

删除存储过程

drop procedure salary;

变量(声明, 赋值, 使用, 作用域)

• 系统变量
  • 全局变量(global) 只要不重启, 都适用
  • 会话变量(session) 只能是当前会话
• 自定义变量
  • 全局/用户变量(当前会话)
  • 局部/局部变量(当前函数)

查看变量(全部)

-- 第一条和第三条效果一样
show variables;
show global variables;
show session variables;

查看变量(搜索)

-- 第一条和第三条效果一样
show variables like 'auto%';
show global variables like 'auto%';
show session variables like 'auto%';

查看变量(一个)
select @@global|[session].系统变量;

-- 第一句和第三句一样
select @@autocommit;
select @@global.autocommit;
select @@session.autocommit;

全局变量, 重启失效, 除非改配置文件

修改变量/变量赋值

语法

set global|[session] 系统变量名 = 值;
set @@global|[session].系统变量名 = 值;

set autocommit = 0;
-- 相当于
-- set session autocommit = 0
-- 或者
-- set @@session.autocommit = 0;
-- 全局
-- set @@global.autocommit = 0;
-- set global autocommit = 0;

select @@autocommit;
select @@session.autocommit;
select @@global.autocommit;

用户自定义变量

• 全局变量(当前会话)

set @variableName = value;
set @variableName := value;
select @variableName := value;

set @hello_world = 'hello world !!!!';
select @hello_world;

set @hello_world := 'hello world !!!!';
select @hello_world;

select @hello_world := 'hello world !!!!';
select @hello_world;

查询赋值

set @count = 0;
select count(*) into @count from teacher;
select @count;

局部变量(begin end)

drop PROCEDURE if EXISTS hello_world;

create PROCEDURE hello_world() begin 
declare result int;
select count(*) into result from teacher;
select result;
end;

call hello_world;

不能在begin end外使用

drop PROCEDURE if EXISTS hello_world;

create PROCEDURE hello_world() begin 
declare result int;
select count(*) into result from teacher;
select result;
end;

call hello_world;
select result;

注意, 必须是begin后的第一行

drop PROCEDURE if EXISTS hello_world;

create PROCEDURE hello_world() begin 
select 1+1;
declare result int;
select count(*) into result from teacher;
select result;
end;

call hello_world;

@变量名可以在begin end中使用

drop PROCEDURE if EXISTS hello_world;
set @result = 0;
create PROCEDURE hello_world() begin 
select count(*) into @result from teacher;
select @result;
end;

call hello_world;
select @result;