表弟在公司负责数据维护的工作.

上次见面时,他跟我说,他们老板给了他1天的时间去统计公司的数据,结果他用了几分钟,就把数据整理了出来,然后快乐地度过了一天.

一向拖拉的表弟,这一次竟然变得如此高效.

在我的一再追问下,终于明白是怎么一回事了.

原来,表弟平日在维护数据时,会进行各种数据操作,有的操作很简单,分分钟就搞定了,而有的复杂的操作,则要调整数十分钟,甚至更长.

每次,表弟为了以后操作方便,就把这些数据操作都封装到 **存储过程 **里.

这样,下次进行类似的数据操作时,只需要简地单调用存储过程就好了.

而这一次老板要求他统计的数据,他刚好有能对应的存储过程.

所以,能够轻松应对.

那么,这一次,我们来了解一下存储过程的相关内容.

1 什么是存储过程

存储过程StoredProcedure是一段sql代码的封装,类似于编程语言中的函数;

2 存储过程的作用

2.1 sql代码的重复利用

将sql代码进行封装,使用时调用存储过程,并可通过更改参数,进行同类数据的操作.

2.2 对数据的批量操作

通过流程控制,可以实现大批量数据的类似操作.

2.3 复杂流程的数据操作

通过流程控制,可以实现复杂的逻辑控制,并对不同的输入,进行不同的操作.

3 存储过程使用案例

接下来,我们用2个存储过程的基本案例,来掌握存储过程的基本知识点,快速入门.

3.1 输出HelloWorld的存储过程

第一个案例采用HelloWorld,是朴素的程序员一贯采用的做法.

先定义再使用,是编程界的基本法则之一,我们先来定义一个会输出HelloWorld的存储过程

DELIMITER //
CREATE DEFINER=root@localhost PROCEDURE proc_hello()
BEGIN
  SELECT 'Hello,world!';
END
//
DELIMITER ;

行1,声明了语句分隔符是'//'.
我们知道sql的默认分隔符是';',为了在第4行,MySql遇到';'后,能够继续解释后面的内容而不报错,我们需要先把分隔符替换为';'以外的.

行2,定义了一个名字为proc_hello的存储过程.其中
DEFINER=root@localhost
声明了定义者的信息,可写可不写.

行3-行5,BEGIN ... END的范围之内,指定要执行的sql语句.
sql语句中的;必不可少.

行6,表明存储过程的定义已经结束.

行7,将分隔符恢复成';'

注:

存储过程的结构实际只包含行2-行5的部分,行1,行6和行7,只是为了让MySql编译器能够完整解释整个存储过程.
如果在Navicat中,只需要行2-行5的声明部分,不需要额外写转换分隔符的代码.
在cmd中定义存储过程,需要把转换分隔符的内容也写进去,否则报错.
我们后面定义存储过程,就不写转换分隔符的部分了.

由上面的HelloWorld,我们总结出没有参数的存储过程最基本的结构如下:

CREATE [DEFINER={user}] PROCEDURE {存储过程名称}()
BEGIN
  {需要进行的sql操作;}
END

调用一个无参的存储过程的方式为:

CALL {存储过程名}();

我们来调用一下该存储过程:

mysql> CALL proc_Hello();
+--------------+
| Hello,world! |
+--------------+
| Hello,world! |
+--------------+

3.2 查询指定姓名的员工的在某一天的签到情况;

3.2.1 表结构介绍

1,t_user表,主键为:id,员工名字为:f_user_name.

2,t_sign,主键为:id,签到时间为:f_sign_time,员工id为:f_user_id.

员工每签一次到,就会在t_sign表中增加一条记录,记录自己的id和签到时间.

3.2.2 查询sql

如果我们使用sql查询一个名字叫'财11'在'2020-03-01'的签到情况,会使用一下sql:

SELECT * 
FROM t_user a
LEFT JOIN 
  t_sign b ON a.id = b.f_user_id
WHERE 
  a.f_user_name = '财11' 
  AND b.f_sign_time >= '2020-03-01' 
  AND b.f_sign_time < '2020-03-02'

如果我们要改查询时间,就不得不改两个地方.
如果以后还要查询,我们不得不再写一遍这个sql.
懒是社会进步的力量,为了能懒一下,我们还是使用存储过程吧.

3.2.3 定义存储过程

我们把它写进存储过程里:

CREATE DEFINER=root@localhost PROCEDURE proc_signInfoByUserNameAndDate(IN userName VARCHAR(10),IN date VARCHAR(10))
BEGIN
  SELECT * 
  FROM t_user a
  LEFT JOIN t_sign b ON a.id = b.f_user_id
  WHERE 
  a.f_user_name = userName 
  AND b.f_sign_time >= date 
  AND b.f_sign_time < DATE_ADD(STR_TO_DATE(date,'%Y-%m-%d'),INTERVAL 1 DAY);
END

3.2.4 存储过程参数的定义

该存储过程使用了两个参数:用户名字和查询日期,所以在第一行的()中,加上了两个参数的定义:

proc_signInfoByUserNameAndDate(IN userName VARCHAR(10),IN date VARCHAR(10))

存储过程参数的定义规则为:

{存储过程名}([{参数的传递类型} {参数名称} {参数存储类型},...])

其中,参数的传递类型有3种: IN,OUT,INOUT;

这三种传递类型有什么区别,会在后面进行说明,我们先来看一下如何调用这个有参的存储过程.

3.3.5 有参存储过程的调用

调用方式为:

CALL {存储过程名}({value1},...);

调用结果如下:

mysql> CALL proc_signInfoByUserNameAndDate('财11','2020-03-01');
+----+-------------+-----------+------+-----------+---------------------+
| id | f_user_name | f_dept_id | id   | f_user_id | f_sign_time         |
+----+-------------+-----------+------+-----------+---------------------+
|  1 | 财11        |         6 |    1 |         1 | 2020-03-01 08:39:15 |
|  1 | 财11        |         6 |    2 |         1 | 2020-03-01 18:00:12 |
+----+-------------+-----------+------+-----------+---------------------+

这样,只需要定义一次,以后再进行类似的查询,那就方便多了.

4 变量

4.1 用户变量

在MySql客户端中使用的变量叫做用户变量,一般起名字时用'@'开头.
我们可以用两种方式来定义用户变量:

4.1.1 将SELECT语句的结果直接赋给变量

SELECT {value} INTO {var};

eg,将'Hello'赋给用户变量@a:

mysql> SELECT 'Hello' INTO @a;
mysql> SELECT @a;
+-------+
| @a    |
+-------+
| Hello |
+-------+

4.1.2 用SET关键字定义

SET {var} = {value};

eg1,将'World'赋给用户变量'@b':

mysql> SET @b = 'World';
mysql> SELECT @b;
+-------+
| @b    |
+-------+
| World |
+-------+

eg2,将算术表达式的值赋给变量@c:

mysql> SET @c = 1+2+4;
mysql> SELECT @c;
+------+
| @c   |
+------+
|    7 |
+------+

4.2 局部变量

在存储过程中定义的变量,叫做局部变量.
在存储过程中定义局部变量的语法为:

DECLARE {var} {dataType} [DEFAULT {value}];
-- eg:
-- 声明一个初始值为'Hello'的变量h:
 DECLARE h VARCHAR(20) DEFAULT 'Hello';
-- 声明一个初始值为2的整型变量i:
DECLARE i INT DEFAULT 2;

4.3 存储过程中使用用户变量

存储过程的过程体中,可以直接使用用户变量:

SET @a = 1;
CREATE PROCEDURE p1()  SET @a = @a+2;
CREATE PROCEDURE p2()  SET @a = @a+3;
CALL p1();
CALL p2();

SELECT @a;
+-------+
| @a    |
+-------+
|     6 |
+-------+

p1,将@a的值+2,此时@a=3;
p2,将@a的值+3,最终@a=6.

5 参数的传递类型

前文说到在定义有参的存储过程时,需要声明参数的传递类型.
一共有3种传递类型:IN,OUT,INOUT,下面我们来说说他们的区别.

5.1 IN-值传递

SET @i = 1;
CREATE PROCEDURE pIn(IN i INT)  SET i = 100;
CALL pIn(@i);
SELECT  @i;
+-------+
| @i    |
+-------+
|     1 |
+-------+

用户变量@i传值给存储过程的形参i时,只把值传给了i.
@i和i分别代表两个存储空间,互不干扰.
当i修改为100后,@i还是1不变.

5.2 INOUT-指针传递

SET @i = 1;
CREATE PROCEDURE pInOut(INOUT i INT)  SET i = 100;
CALL pInOut(@i);
SELECT  @i;
+-------+
| @i    |
+-------+
|   100 |
+-------+

对于INOUT类型的传值,传递的是指针.
i和@i共用一个存储空间,所以在存储空间中,将i赋值100,@i指向的存储空间的值也是100.

5.3 OUT-指针传递,并置null

SET @i = 1;
CREATE PROCEDURE pOut(OUT i INT) 
BEGIN
SELECT i;
SET i = 100;
SELECT i;
END
CALL pOut(@i);
SELECT  @i;
-- 第一次输出:
+-------+
|  i    |
+-------+
|  null |
+-------+
-- 第二次输出:
+-------+
|  i    |
+-------+
|   100 |
+-------+
-- 第三次输出
+-------+
| @i    |
+-------+
|   100 |
+-------+

OUT类型的传值,本质上和INOUT类型一样,也是传递指针;但是在传递指针之后,会进行一步操作,将变量的值置为null.
所以,第一次输出为null,后面两次输出为100.

最后,大家明白了吧,在本章的存储过程的第二个例子中,定义了两个IN类型的参数:

proc_signInfoByUserNameAndDate(IN userName VARCHAR(10),IN date VARCHAR(10))

因为只需要把值传递进去就可以了.

文章推荐:

密码学 | 数字签名让情敌送情书,网友:心太大了...

iOS | app签名图解苹果app签名流程,深入分析表弟:我也看懂了!

MySql | 存储过程(基础) 老板要我一天统计的数据,我3分钟做完了