第15课 聊聊存储过程
2019-04-08 本文已影响0人
猫哥的技术博客
存储过程
了解几个容易混淆的概念
- 存储过程
- 视图
- 事务
- 函数
视图(view):
可以理解成临时表, 如果你每次都需要连表查询, 而且sql老长老长了, 那就试试视图吧, 你会感谢我的.
事务(transaction):
一组sql语句, 要么全部执行成功, 要么都不执行
函数(function):
执行特定功能的代码段, 和存储过程(
procedure)很像
区别在于, 函数有且只有个返回值, 存储过程对返回值的个数没有要求, 没有返回值也是可以的
了解存储过程
存储过程的概念
- 存储过程和函数(执行特定功能的代码段)很像, 语法略有不同,
- 存储过程主要用来
增删改,视图主要用来查
存储过程的优缺点
优点:
- 提高效率
- 代码复用
- 减少开发人员的工作量
- 对存储过程设置权限, 安全性较高
缺点:
- 用起来方便, 改起来费劲...
存储过程参数介绍
- 函数有参数, 存储过程也有参数
- 定义参数时, 需要有
参数名,参数类型,参数方向 - 存储过程的参数方向有三个(
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;
