MySQL 存储过程及创建时间维度表

爱被打了一巴掌 2023-01-13 04:18 227阅读 0赞

## 一、概述存储过程 ##

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

**\# SQL语句：先编译后执行**

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

1.1、优点

*  存储过程可封装，并隐藏复杂的商业逻辑。
 *  存储过程可以回传值，并可以接受参数。
 *  存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
 *  存储过程可以用在数据检验，强制实行商业逻辑等。

1.2、缺点

*  存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
 *  存储过程的性能调校与撰写，受限于各种数据库系统。

**1.3、优点另一种描述(为什么要用存储过程？)：**

①将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

②批量处理：SQL+循环，减少流量，也就是“跑批”

③统一接口，确保数据的安全

相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。

## 二、存储过程的创建和调用 ##

*  存储过程就是具有名字的一段代码，用来完成一个特定的功能。
 *  创建的存储过程保存在数据库的数据字典中。

### 2.1、创建存储过程 ###

>     CREATE
>         [DEFINER = { user | CURRENT_USER }]
>     　PROCEDURE sp_name ([proc_parameter[,...]])
>         [characteristic ...] routine_body
>     
>     proc_parameter:
>         [ IN | OUT | INOUT ] param_name type
>     
>     characteristic:
>         COMMENT 'string'
>       | LANGUAGE SQL
>       | [NOT] DETERMINISTIC
>       | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
>       | SQL SECURITY { DEFINER | INVOKER }
>     
>     routine_body:
>     　　Valid SQL routine statement
>     
>     [begin_label:] BEGIN
>     　　[statement_list]
>     　　　　……
>     END [end_label]

\#创建数据库，备份数据表用于示例操作

>     create table `MATCHES`(
>      `MATCHNO` bigint(8) DEFAULT NULL,
>      `TEAMNO` bigint(8) DEFAULT NULL,
>      `PLAYERNO` bigint(8) DEFAULT NULL,
>      `WON` bigint(8) DEFAULT NULL,
>      `LOST` bigint(8) DEFAULT NULL
>     )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
>     
>     插入数据
>     
>     insert into MATCHES values(1,1,6,3,1);
>     insert into MATCHES values(7,1,57,3,0);
>     insert into MATCHES values(8,1,8,0,3);
>     insert into MATCHES values(9,2,27,3,2);
>     insert into MATCHES values(11,2,112,2,3);
>     或者 
>     insert into MATCHES values('1','1','6','3','1');
>     insert into MATCHES values('7','1','57','3','0');
>     insert into MATCHES values('8','1','8','0','3');
>     insert into MATCHES values('9','2','27','3','2');
>     insert into MATCHES values(1'1','2','112','2','3');

示例：创建一个存储过程，删除给定球员参加的所有比赛

>     CREATE PROCEDURE test.delete_matches(IN p_playerno INTEGER)
>     BEGIN
>     	DELETE FROM MATCHES
>            WHERE playerno = p_playerno;
>     END

解析：

默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀；

在定义过程时，使用DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个$$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

### 2.2、调用存储过程：call sp\_name\[(传参)\]; ###

>     mysql> select * from MATCHES;
>     +---------+--------+----------+-----+------+
>     | MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
>     +---------+--------+----------+-----+------+
>     |       1 |      1 |        6 |   3 |    1 |
>     |       7 |      1 |       57 |   3 |    0 |
>     |       8 |      1 |        8 |   0 |    3 |
>     |       9 |      2 |       27 |   3 |    2 |
>     |      11 |      2 |      112 |   2 |    3 |
>     +---------+--------+----------+-----+------+
>     5 rows in set (0.00 sec)
>     
>     mysql> call delete_matches(57);
>     Query OK, 1 row affected (0.03 sec)
>     
>     mysql> select * from MATCHES;
>     +---------+--------+----------+-----+------+
>     | MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
>     +---------+--------+----------+-----+------+
>     |       1 |      1 |        6 |   3 |    1 |
>     |       8 |      1 |        8 |   0 |    3 |
>     |       9 |      2 |       27 |   3 |    2 |
>     |      11 |      2 |      112 |   2 |    3 |
>     +---------+--------+----------+-----+------+
>     4 rows in set (0.00 sec)

解析：

在存储过程中设置了需要传参的变量p\_playerno，调用存储过程的时候，通过传参将57赋值给p\_playerno，然后进行存储过程里的SQL操作。

### 2.3、存储过程体 ###

>存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等

>过程体格式：以begin开始，以end结束(可嵌套)

>     BEGIN
>     　　BEGIN
>     　　　　BEGIN
>     　　　　　　statements; 
>     　　　　END
>     　　END
>     END

注意：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

### 2.4、为语句块贴标签 ###

[begin_label:] BEGIN
    　　[statement_list]
    END [end_label]

例如：

>     label1: BEGIN
>     　　label2: BEGIN
>     　　　　label3: BEGIN
>     　　　　　　statements; 
>     　　　　END label3 ;
>     　　END label2;
>     END label1

标签有两个作用：

①增强代码的可读性

②在某些语句(例如:leave和iterate语句)，需要用到标签

## 三、存储过程的参数（in输入参数、out输入参数、inout输入参数） ##

存储过程可以有0个或多个参数，用于存储过程的定义。

3种参数类型：

IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

**3.1、in输入参数**

**Navicat下面创建存储过程，如下图所示：**

> CREATE PROCEDURE in\_param(in p\_in int)  
> BEGIN  
>      select p\_in;  
>      set p\_in=2;  
>      select P\_in;  
> END

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70][]

**MySQL命令行创建存储过程如下：**

**必须以delimiter $$开头和 delimiter;结尾，否则会报错，如下**

>     MySQL [test]> CREATE PROCEDURE in_param(in p_in int)
>         -> BEGIN
>         ->  select p_in;
>     ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
>     MySQL [test]>      set p_in=2;
>     ERROR 1193 (HY000): Unknown system variable 'p_in'
>     MySQL [test]>      select P_in;
>     ERROR 1054 (42S22): Unknown column 'P_in' in 'field list'
>     MySQL [test]> END
>         -> ;
>     ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
>     
>     
>     MySQL [test]> delimiter $$
>     MySQL [test]> CREATE PROCEDURE in_param(in p_in int)
>         -> BEGIN
>         ->  select p_in;
>         ->      set p_in=2;
>         ->      select P_in;
>         -> END$$
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> delimiter ;
>     MySQL [test]> set @p_in=1;
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> call in_param(@p_in);
>     +------+
>     | p_in |
>     +------+
>     |    1 |
>     +------+
>     1 row in set (0.00 sec)
>     
>     +------+
>     | P_in |
>     +------+
>     |    2 |
>     +------+
>     1 row in set (0.00 sec)
>     
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> select @p_in;
>     +-------+
>     | @p_in |
>     +-------+
>     |     1 |
>     +-------+
>     1 row in set (0.00 sec)
>     
>     MySQL [test]>

\#以上可以看出，p\_in在存储过程中被修改，但并不影响@p\_id的值，因为前者为局部变量、后者为全局变量。

**3.2、out输出参数**

**Navicat 可视化创建**

> **CREATE PROCEDURE out\_param(out p\_out int)  
> BEGIN  
>     select p\_out;  
>     set p\_out=2;  
>     select p\_out;  
> END**

MySQL命令创建存储过程示例

>     MySQL [test]> delimiter $$
>     MySQL [test]> create procedure out_param(out p_out int)
>         ->       begin
>         ->         select p_out;
>         ->         set p_out=2;
>         ->         select p_out;
>         ->       end$$
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> delimiter ;
>     MySQL [test]> set @p_out=1;
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> call out_param(@p_out);
>     +-------+
>     | p_out |
>     +-------+
>     |  NULL |
>     +-------+
>     1 row in set (0.00 sec)
>     　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
>     
>     +-------+
>     | p_out |
>     +-------+
>     |     2 |
>     +-------+
>     1 row in set (0.00 sec)
>     
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> select @p_out;
>     +--------+
>     | @p_out |
>     +--------+
>     |      2 |
>     +--------+
>     1 row in set (0.00 sec)
>     　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

**3.3、inout输入参数**

**Navicat创建存储过程**

> CREATE PROCEDURE inout\_param(inout p\_inout int)  
> BEGIN  
>     select p\_inout;  
>     set p\_inout=2;  
>     select p\_inout;  
> END;

**MySQL命令行创建存储过程**

>     MySQL [test]> delimiter $$
>     MySQL [test]> create procedure inout_param(inout p_inout int)
>         ->       begin
>         ->         select p_inout;
>         ->         set p_inout=2;
>         ->         select p_inout;
>         ->       end$$
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> delimiter ;
>     MySQL [test]> set @p_inout=1;
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> call inout_param(@p_inout);
>     +---------+
>     | p_inout |
>     +---------+
>     |       1 |
>     +---------+
>     1 row in set (0.00 sec)
>     
>     +---------+
>     | p_inout |
>     +---------+
>     |       2 |
>     +---------+
>     1 row in set (0.00 sec)
>     
>     Query OK, 0 rows affected (0.00 sec)
>     
>     MySQL [test]> select @p_inout;
>     +----------+
>     | @p_inout |
>     +----------+
>     |        2 |
>     +----------+
>     1 row in set (0.00 sec)
>     
>     MySQL [test]>

\#调用了inout\_param存储过程，接受了输入的参数，也输出参数，改变了变量

**注意：**

①如果过程没有参数，也必须在过程名后面写上小括号

例：CREATE PROCEDURE sp\_name (\[proc\_parameter\[,...\]\]) ……

②确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

**墙裂建议：**

> 　　>输入值使用in参数；
> 
> 　　>返回值使用out参数；
> 
> 　　>inout参数就尽量的少用。

## 四、利用MySQL存储过程生成日期维表和数据 ##

### 4.1、创建MySQL存储过程： ###

> create procedure p\_dim\_date(in start\_date VARCHAR(20),in date\_count int)  
> begin  
>       declare i int;  
>       set i=0;  
>             DELETE from dim\_date;  
>             while i<date\_count do  
>                 INSERT into dim\_date  
>                 (date\_id,date,date\_cn,WEEK\_CN,WEEK\_OF\_YEAR\_CN,MONTH,MONTH\_CN,QUARTER,QUARTER\_CN,YEAR\_CN)  
>                         SELECT   
>                         REPLACE(start\_date,'-','') date\_id,  
>                         DATE\_FORMAT(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') date,  
>                         DATE\_FORMAT(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),'%Y年%m月%d日') date\_cn,  
>                         case dayofweek(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s')) when 1 then '星期日' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end WEEK\_CN,  
>                         DATE\_FORMAT(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%u周') WEEK\_OF\_YEAR\_CN,  
>                         DATE\_FORMAT(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m') MONTH,  
>                         DATE\_FORMAT(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%m月') MONTH\_CN,  
>                         CONCAT(DATE\_FORMAT(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'Q',quarter(STR\_TO\_DATE( start\_date,'%Y-%m-%d %H:%i:%s'))) QUARTER,  
>                         CONCAT(DATE\_FORMAT(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'年第',quarter(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s')),'季度') QUARTER\_CN,  
>                         DATE\_FORMAT(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),'%Y年') YEAR\_CN  
>                         from dual;  
>                 set i=i+1;  
>                 set start\_date = DATE\_FORMAT(date\_add(STR\_TO\_DATE(start\_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');  
>             end while;  
> end;

### 4.2、创建MySQL表日期维度表，并执行存储过程 ###

> CREATE TABLE \`dim\_date\` (  
>   \`date\_id\` bigint(8) DEFAULT NULL,  
>   \`date\` date DEFAULT NULL,  
>   \`date\_cn\` varchar(20) DEFAULT NULL,  
>   \`WEEK\_CN\` varchar(20) DEFAULT NULL,  
>   \`WEEK\_OF\_YEAR\_CN\` varchar(20) DEFAULT NULL,  
>   \`MONTH\` varchar(20) DEFAULT NULL,  
>   \`MONTH\_CN\` varchar(20) DEFAULT NULL,  
>   \`QUARTER\` varchar(20) DEFAULT NULL,  
>   \`QUARTER\_CN\` varchar(20) DEFAULT NULL,  
>   \`YEAR\_CN\` varchar(20) DEFAULT NULL  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
> 
> \--调用执行存储过程，产生数据
> 
> call p\_dim\_date("2018-01-01",30);

### 4.3、查询验证数据 ###

> SELECT \* from test.dim\_date
> 
> date\_id  |date       |date\_cn     |WEEK\_CN |WEEK\_OF\_YEAR\_CN |MONTH   |MONTH\_CN  |QUARTER |QUARTER\_CN |YEAR\_CN |  
> \---------|-----------|------------|--------|----------------|--------|----------|--------|-----------|--------|  
> 20180101 |2018-01-01 |2018年01月01日 |星期一     |2018年第01周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180102 |2018-01-02 |2018年01月02日 |星期二     |2018年第01周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180103 |2018-01-03 |2018年01月03日 |星期三     |2018年第01周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180104 |2018-01-04 |2018年01月04日 |星期四     |2018年第01周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180105 |2018-01-05 |2018年01月05日 |星期五     |2018年第01周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180106 |2018-01-06 |2018年01月06日 |星期六     |2018年第01周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180107 |2018-01-07 |2018年01月07日 |星期日     |2018年第01周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180108 |2018-01-08 |2018年01月08日 |星期一     |2018年第02周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180109 |2018-01-09 |2018年01月09日 |星期二     |2018年第02周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180110 |2018-01-10 |2018年01月10日 |星期三     |2018年第02周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180111 |2018-01-11 |2018年01月11日 |星期四     |2018年第02周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180112 |2018-01-12 |2018年01月12日 |星期五     |2018年第02周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180113 |2018-01-13 |2018年01月13日 |星期六     |2018年第02周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180114 |2018-01-14 |2018年01月14日 |星期日     |2018年第02周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180115 |2018-01-15 |2018年01月15日 |星期一     |2018年第03周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180116 |2018-01-16 |2018年01月16日 |星期二     |2018年第03周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180117 |2018-01-17 |2018年01月17日 |星期三     |2018年第03周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180118 |2018-01-18 |2018年01月18日 |星期四     |2018年第03周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180119 |2018-01-19 |2018年01月19日 |星期五     |2018年第03周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180120 |2018-01-20 |2018年01月20日 |星期六     |2018年第03周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180121 |2018-01-21 |2018年01月21日 |星期日     |2018年第03周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180122 |2018-01-22 |2018年01月22日 |星期一     |2018年第04周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180123 |2018-01-23 |2018年01月23日 |星期二     |2018年第04周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180124 |2018-01-24 |2018年01月24日 |星期三     |2018年第04周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180125 |2018-01-25 |2018年01月25日 |星期四     |2018年第04周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180126 |2018-01-26 |2018年01月26日 |星期五     |2018年第04周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180127 |2018-01-27 |2018年01月27日 |星期六     |2018年第04周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180128 |2018-01-28 |2018年01月28日 |星期日     |2018年第04周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180129 |2018-01-29 |2018年01月29日 |星期一     |2018年第05周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |  
> 20180130 |2018-01-30 |2018年01月30日 |星期二     |2018年第05周       |2018-01 |2018年第01月 |2018Q1  |2018年第1季度  |2018年   |

**参考:**  
https://www.runoob.com/w3cnote/mysql-stored-procedure.html  
https://www.cnblogs.com/geaozhang/p/6797357.html

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70]: /images/20221022/1dd749f1d6c94eafaf0813fd713e24d9.png