概述

大多数SQL语句都是针对一个或多个表的单条语句。但并非所有业务都这么简单,经常会有复杂的操作需要多条语句才能完成。

比如用户购买一个商品,要删减库存表,要生成订单数据,要保存支付信息等等,他是一个批量的语句执行行为。

存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然它们的作用不仅限于批处理。

优点:
提高代码的复用性:把一些通用操作内容封装到一个存储过程中,可以不断的给业务功能复用。

简化操作:避免在业务中写大量的代码

提高效率:减少执行次数和数据库服务器连接次数。

提高安全性:通过存储过可以减少对基础数据的误操作,参数化的存储过程一定程度上可以防止SQL注入式攻击,而且可以将Grant、Deny以及Revoke权限应用于存储过程。

说存储过程之前,先来了解两个重要的知识点:自定义变量 和 delimiter关键字。

自定义变量

概念

变量由用户自定义的,而非系统已经存在的。

使用步骤

第一步声明;第二步赋值;第三步使用(调用、比较和运算)

分类

包含用户变量和局部变量,我们一个个来看:

用户变量

作用域

针对当前会话有效,作用域同会话变量。

用户变量可以在任何地方使用,既可以是包含的begin和end,也可以是在这之外。

使用

声明并初始化
1 set @variable=value; 2 or 3 set @variable:=value; 4 or 5 select @variable:=value;

这边需要注意:使用了@符号来定义 变量,set中=号前面冒号是可选的,select方式=前面必须有冒号。

赋值方式

一种方式就是跟声明并初始化一致,直接set、select进行赋值,

另外一种就是直接从其他表、视图或变量中查询并赋值,如下:

1 select columnname into @variable from tname; 

这边需要注意两种select的使用方式

实践一下

 1 mysql> set @var1='num1';  2 set @var2:='num2';  3 select @var3:='num3';  4 select @var1,@var2,@var3;  5 Query OK, 0 rows affected  6  7 Query OK, 0 rows affected  8  9 +---------------+ 10 | @var3:='num3' | 11 +---------------+ 12 | num3 | 13 +---------------+ 14 1 row in set 15 16 +-------+-------+-------+ 17 | @var1 | @var2 | @var3 | 18 +-------+-------+-------+ 19 | num1 | num2 | num3 | 20 +-------+-------+-------+ 21 1 row in set

局部变量

作用域

declare用于定义局部变量,在存储过程和函数中通过declare定义变量在begin…end中,且在语句之前。并且可以通过重复定义多个变量

declare变量的作用范围同编程里面类似,在这里一般是在对应的begin和end之间。在end之后这个变量就没有作用了,不能使用了。这个同编程一样。

使用

声明语法
1 declare variable type [default default_value]

declare 变量名 变量类型,后面的 [ 默认值] 为可选;

赋值方式
1 set variable=value; 2 set variable:=value; 3 select variable:=value; 4 5 或者 6 7 select cname into variable from tname; 

注意自定义变量和局部变量的区别,一个前面有@符号,一个没有。

查看变量的值

1 select variable

实践一下

 1 mysql>  2 /*这边声明脚本的结束符为// */  3 DELIMITER //  4 DROP PROCEDURE IF EXISTS sp_avg;  5 CREATE PROCEDURE sp_avg()  6 BEGIN  7 /*声明了一个局部变量 avg_score*/  8 DECLARE avg_score int;  9 select AVG(score) into avg_score from students; 10 select avg_score; 11 -- Todo 12 END // 13 /*重置脚本的结束符为; */ 14 DELIMITER ; 15 Query OK, 0 rows affected 16 17 mysql> 18 /*调用存储过程*/ 19 call sp_avg(); 20 +-----------+ 21 | avg_score | 22 +-----------+ 23 | 87 | 24 +-----------+ 25 1 row in set 26 27 Query OK, 0 rows affected



	
变量类型 作用域 定义位置 语法格式
用户变量 当前会话都有效 会话的任一地方 @符号,无需指定类型
局部变量 所属定义的begin end之间 begin...end中的第一个位置,紧跟在begin后面 不加@符号,需指定类型


delimiter 关键字的使用

简介

delimiter是mysql分隔符,在mysql客户端中分隔符默认是分号;。如果一次输入的语句较多,并且语句中间有分号,这时需要新指定一个特殊的分隔符。

其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。默认情况下,delimiter是分号;。在命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令。

详细解释:

其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。

默认情况下,delimiter是分号;。在命令行客户端中,如果有一行命令以分号结束, 那么回车后,mysql将会执行该命令。如输入下面的语句 :

1 mysql> select * from tname;  

然后回车,那么MySQL将立即执行该语句。

使用

但有时候,不希望MySQL这么做。在为可能输入较多的语句,且语句中包含有分号。 这种情况下,就需要事先把delimiter换成其它符号,如//、$$或者;;。

更改结束标志的定义如下:

1 mysql>delimiter // 

举个例子:创建一个存储过程,在创建该存储过程之前,将delimiter分隔符转换成符号“//”,最后在转换回符号“;”。

1 /*将结束标志符更改为// */ 2 delimiter // 3 /*创建函数或存储过程*/ 4 -- Todo,这边写下你的sql语句 5 end // 6 /*重置脚本的结束符为; */ 7 delimiter ; 

上面就是,先将分隔符设置为 //, 直到遇到下一个 //,才整体执行语句。

执行完后,最后一行, delimiter ; 将mysql的分隔符重新设置为分号;

如果不修改的话,本次会话中的所有分隔符都以// 为准。

存储过程操作

存储过程的操作包含创建

创建存储过程

1 create procedure 存储过程名([参数模式] 参数名 参数类型) 2 begin 3  存储过程体 4 end

参数模式有3种:

in:该参数可以作为输入,也就是该参数需要调用方传入值。

out:该参数可以作为输出,也就是说该参数可以作为返回值。

inout:该参数既可以作为输入也可以作为输出,也就是说该参数需要在调用的时候传入值,又可以作为返回值。

参数模式默认为IN,一个存储过程可以有多个输入、多个输出、多个输入输出参数。

所以创建存储过程的时候参数可能存在一下几种情况:

无参情况

编写存储过程

 1 /*设置结束符设置为// */  2 DELIMITER //  3 /*存储过程如果存在先删除*/  4 DROP PROCEDURE IF EXISTS sp_test1;  5 /*创建无参数存储过程sp_test1*/  6 CREATE PROCEDURE sp_test1()  7 BEGIN  8 update students set score = (score+1) where studentname='lala';  9 END // 10 /*将结束符重新设置为;*/ 11 DELIMITER; 

调用实现:对比数据可确定调用成功

 1 mysql> select * from students;  2 +-----------+-------------+-------+---------+  3 | studentid | studentname | score | classid |  4 +-----------+-------------+-------+---------+  5 | 1 | brand       | 97.5 | 1 |  6 | 2 | helen       | 96.5 | 1 |  7 | 3 | lyn         | 96 | 1 |  8 | 4 | sol         | 97 | 1 |  9 | 7 | b1          | 81 | 2 | 10 | 8 | b2          | 82 | 2 | 11 | 13 | c1          | 71 | 3 | 12 | 14 | c2          | 72.5 | 3 | 13 |        19 | lala        | 53    |       0 | 14 +-----------+-------------+-------+---------+ 15 9 rows in set 16 17 mysql> call sp_test1(); 18 Query OK, 1 row affected 19 20 mysql> select * from students; 21 +-----------+-------------+-------+---------+ 22 | studentid | studentname | score | classid | 23 +-----------+-------------+-------+---------+ 24 | 1 | brand       | 97.5 | 1 | 25 | 2 | helen       | 96.5 | 1 | 26 | 3 | lyn         | 96 | 1 | 27 | 4 | sol         | 97 | 1 | 28 | 7 | b1          | 81 | 2 | 29 | 8 | b2          | 82 | 2 | 30 | 13 | c1          | 71 | 3 | 31 | 14 | c2          | 72.5 | 3 | 32 |        19 | lala        | 54    |       0 | 33 +-----------+-------------+-------+---------+ 34 9 rows in set
带in参数

编写存储过程:

 1 /*设置结束符为// */  2 DELIMITER //  3 /*存储过程如果存在先删除*/  4 DROP PROCEDURE IF EXISTS sp_test2;  5 /*创建存储过程sp_test2*/  6 CREATE PROCEDURE sp_test2(sname varchar(20),score DECIMAL(10,2),classid int)  7 BEGIN  8 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);  9 END // 10 /*将结束符重新置为;*/ 11 DELIMITER ; 

调用实现:

 1 mysql> set @uname='wzh1',@score=100,@classid=8;  2 call sp_test2(@uname,@score,@classid);  3 Query OK, 0 rows affected  4  5 Query OK, 1 row affected  6  7 mysql> select * from students;  8 +-----------+-------------+-------+---------+  9 | studentid | studentname | score | classid | 10 +-----------+-------------+-------+---------+ 11 | 1 | brand | 97.5 | 1 | 12 | 2 | helen | 96.5 | 1 | 13 | 3 | lyn | 96 | 1 | 14 | 4 | sol | 97 | 1 | 15 | 7 | b1 | 81 | 2 | 16 | 8 | b2 | 82 | 2 | 17 | 13 | c1 | 71 | 3 | 18 | 14 | c2 | 72.5 | 3 | 19 | 19 | lala | 54 | 0 | 20 | 20 | wzh1        | 100 | 8 | 21 +-----------+-------------+-------+---------+ 22 10 rows in set 
带out参数

编写存储过程

 1 /*设置结束符为// */  2 DELIMITER //  3 /*如果存储过程存在则删除*/  4 DROP PROCEDURE IF EXISTS sp_test3;  5 /*创建存储过程sp_test2*/  6 CREATE PROCEDURE sp_test3(sname varchar(20),score DECIMAL(10,2),classid int,out lastid int)  7 BEGIN  8 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);  9 select lastid = @@identity; 10 END // 11 /*将结束符重新置为;*/ 12 DELIMITER ;

调用实现

 1 mysql> set @uname='wzh3',@score=104,@classid=10;  2 call sp_test3(@uname,@score,@classid,@lastid);  3 select @lastid;  4 Query OK, 0 rows affected  5  6 Query OK, 1 row affected  7  8 +---------+  9 | @lastid | 10 +---------+ 11 | 22 | 12 +---------+ 13 1 row in set 14 15 mysql> select * from students; 16 +-----------+-------------+-------+---------+ 17 | studentid | studentname | score | classid | 18 +-----------+-------------+-------+---------+ 19 | 1 | brand | 97.5 | 1 | 20 | 2 | helen | 96.5 | 1 | 21 | 3 | lyn | 96 | 1 | 22 | 4 | sol | 97 | 1 | 23 | 7 | b1 | 81 | 2 | 24 | 8 | b2 | 82 | 2 | 25 | 13 | c1 | 71 | 3 | 26 | 14 | c2 | 72.5 | 3 | 27 | 19 | lala | 54 | 0 | 28 | 20 | wzh1 | 100 | 8 | 29 | 21 | wzh2 | 101 | 9 | 30 | 22 | wzh3 | 104 | 10 | 31 +-----------+-------------+-------+---------+ 32 12 rows in set 
带inout参数

自己试试吧,小伙子们

调用存储过程

1 call 存储过程名称(参数列表);

注意:调用存储过程关键字是call

如上所示 ,所有的call都是这样的额

删除存储过程

1 drop procedure [if exists] 存储过程名称;

存储过程只能一个个删除,不能批量删除。

if exists:表示存储过程存在的情况下删除,我们上面演示的存储过程都是判断如果存在就先删除。

修改存储过程

存储过程不能修改,若涉及到修改的,可以先删除,然后重建。

查看存储过程

1 show create procedure 存储过程名称;

可以查看存储过程详细创建语句。

 1 mysql> show create procedure sp_test3;  2 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+  3 | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |  4 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+  5 | sp_test3 | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test3`(sname varchar(20),score DECIMAL(10,2),classid int,out lastid int)  6 BEGIN  7 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);  8 select LAST_INSERT_ID() into lastid;  9 END | utf8 | utf8_general_ci | utf8_general_ci | 10 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 11 1 row in set

小结

存储过程的优点开篇已经说过了,这边就不赘述了,个人使用的最大感触是,尽量不要在应用代码中写大量的脚本逻辑,做成存储过程或者函数会更高效简洁且易于维护。

MySQL全面瓦解16:存储过程相关的更多相关文章

  1. ORACLE 利用 REPLACE函数替换字段字符串

    REPLACE(string,s1,s2) string 希望被替换的字符或变量 s1 被替换的字符串 s2 要替换......

  2. docker+mysql集群+读写分离+mycat管理+垂直分库+负载均衡

    依然如此,只要大家跟着我的步骤一步步来,99.99999%是可以测试成功的centos6.8已不再维护,可能很多人的......

  3. MYSQL 字符集设置(终端的字符集)

    每次利用终端 创建数据库或者创建表的时候,字符集都是latin1(不指定字符集的时候)如下:查看当前数据库的字符集c......

  4. MYSQL数据库操作语句

    1.创建数据库CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name ......

  5. MySql8 WITH RECURSIVE递归查询父子集的方法

    背景开发过程中遇到类似评论的功能是,需要时用查询所有评论的子集。不同数据库中实现方式也不同,本文使用Mysql数据库......

  6. 深入了解MySQL主从复制的原理

    欢迎微信关注「SH的全栈笔记」0. 主从复制首先主从复制是什么?简单来说是让一台MySQL服务器去复制另一台MySQ......

  7. 白日梦的Elasticsearch系列笔记(一)基础篇

    目录一、导读1.1、认识ES1.2、安装、启动ES、Kibana、IK分词器二、核心概念2.1、Near Realt......

  8. PostgreSQL 使用raise函数打印字符串

    raise函数在PostgreSQL中,该函数用于打印字符串,类似于Java中的System.out.println......

  9. MongoDB备份(mongoexport)与恢复(mongoimport)

    1.备份恢复工具介绍:mongoexport/mongoimportmongodump/mongorestore(本......

  10. MySQL多版本并发控制——MVCC机制分析

    MVCC,即多版本并发控制(Multi-Version Concurrency Control)指的是,通过版本链维......

随机推荐

  1. pixi.js 自定义光标样式

    pixi 介绍Pixi是一个超快的2D渲染引擎,通过Javascript和Html技术创建动画或管理交互式图像,从而......

  2. 将不规则的Python多维数组拉平到一维的方法实现

    原始需求:例如有一个列表:l = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]希望把它转换成下......

  3. Android实现简单画图画板

    本文实例为大家分享了Android实现简单画图画板的具体代码,供大家参考,具体内容如下效果如图:布局文件:MainA......

  4. python中温度单位转换的实例方法

    温度有摄氏度和华氏度两个单位,我们通常使用的是摄氏度,对于转换成华氏度,很多小伙伴记不住公式。作为万能的计算机,它是......

  5. ASP.NET Core错误处理中间件[2]: 开发者异常页面

    异常页面的DeveloperExceptionPageMiddleware中间件,该中间件在捕捉到后续处理过程中抛出......

  6. JS实现公告上线滚动效果

    本文实例为大家分享了JS实现公告上线滚动效果的具体代码,供大家参考,具体内容如下实现的效果如下,新闻公告上下滚动。代......

  7. JS removeAttribute()方法实现删除元素的某个属性

    在 JavaScript 中,使用元素的 removeAttribute() 方法可以删除指定的属性。用法如下:re......

  8. docker+mysql集群+读写分离+mycat管理+垂直分库+负载均衡

    依然如此,只要大家跟着我的步骤一步步来,99.99999%是可以测试成功的centos6.8已不再维护,可能很多人的......

  9. c#定时执行程序代码

    在一般的项目中我们很少用到c#实现每隔规定时间自动执行程序代码,但是如果你经历的项目多,或者应用程序做的比较多的话,......

  10. Linux下安装svn教程

    前言最近买了新服务器,准备开始弄一些个人的开源项目。有了服务器当然是搞一波svn啦。方便自己的资料上传和下载。于是在......