第15课 聊聊存储过程

存储过程

了解几个容易混淆的概念

  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,outinout的区别何在呢?

假设我们想计算一个数字的平方, 我们分别用inoutin,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;
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 213,047评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,807评论 3 386
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 158,501评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,839评论 1 285
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,951评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,117评论 1 291
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,188评论 3 412
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,929评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,372评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,679评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,837评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,536评论 4 335
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,168评论 3 317
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,886评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,129评论 1 267
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,665评论 2 362
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,739评论 2 351

推荐阅读更多精彩内容

  • ORA-00001: 违反唯一约束条件 (.) 错误说明:当在唯一索引所对应的列上键入重复值时,会触发此异常。 O...
    我想起个好名字阅读 5,266评论 0 9
  • SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,...
    MIN_ZJM阅读 639评论 0 1
  • 转载自这里 存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Sto...
    杜七阅读 2,386评论 4 27
  • 这是16年5月份编辑的一份比较杂乱适合自己观看的学习记录文档,今天18年5月份再次想写文章,发现简书还为我保存起的...
    Jenaral阅读 2,739评论 2 9
  • 1.踢球 一人抱瑜伽球,另一人用大腿上部踢瑜伽球锻炼大腿上部肌肉和腰部力量,左右各18次 2.甩壶铃 用腰部力量甩...
    momo_bb0a阅读 154评论 0 0