第五章视图、变量、函数、存储过程与触发器

朴灿烈づ我的快乐病毒、 2023-01-19 10:59 24阅读 0赞

第五章  视图与存储过程

**目录**

1 视图

一、创建视图

1、什么是视图？

2、为什么采用视图？

3、视图的特点

4、视图的使用场景

5、视图的优缺点

6、 语法格式

二、动态更新视图

1、更新视图

2、查看视图

三、可更新视图

1、为什么我们要创建可更新视图？

2、可更新视图的本质是什么？

3、视图可更新的限制？

2 游标

3 变量

一、系统变量

1、全局变量global

2、会话变量session

3、显示系统变量

二、自定义变量

1、用户变量

声明赋值(3种形式)

更新值（2种方式）

使用变量

2、局部变量

变量声明

变量更新

变量的使用

用户变量和局部变量的区别

4 自定义函数

一、标量值函数

1、创建函数

2、调用函数

二、内联表格值函数

内联表格值函数定义格式

注意事项

三、多语句表值函数（工作中使用最多的就是这种）

1、他俩有啥区别啊？

2、函数语法格式

三、修改和删除函数

1、使用alter语句修改函数

2、使用drop语句删除

5 存储过程

一、存储过程的概念

二、存储过程的优缺点

三、创建存储过程

无参存储过程创建

有参的存储过程创建（重点~）

四、调用存储过程

五、查看、删除存储过程

六、存储过程与函数的区别

6 触发器（了解一下）

一、什么是触发器？

二、触发器的作用？

三、DML触发器

1、SQL触发器语法

2、简单的触发器

3、判断值后调用触发器

4、查看触发器

5、删除触发器

6、少用触发器

--------------------

# 1 视图 #

--------------------

## 一、创建视图 ##

### 1、什么是视图？ ###

> 为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。
> 
> **所谓视图，本质上是一种虚拟表**，在**物理上是不存在的**，其**内容与真实的表相似**，包含一系列带有名称的列和行数据。
> 
> 但是，**视图并不在数据库中以储存的数据值形式存在**。**行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成**。
> 
> 视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

--------------------

### 2、为什么采用视图？ ###

> **1、遇到一些场景，不需要重写大量的重复SQL语句**
> 
> **2、基于视图来查询，就不需要写复杂的sql语句，sql语句更加精炼。**
> 
> **3、有些数据并不适合我们直接修改，为了保护数据的安全性，我们需要在新的虚拟表上进行操作。**

### 3、视图的特点 ###

> **视图的特点如下:**
> 
>  *  **视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。**
>  *  **视图是由基本表(实表)产生的表(虚表)。**
>  *  **视图的建立和删除不影响基本表。**
>  *  **对视图内容的更新(添加，删除和修改)直接影响基本表。**
>  *  **当视图来自多个基本表时，不允许添加和删除数据。**

视图的操作包括创建视图，查看视图，删除视图和修改视图。

--------------------

### 4、视图的使用场景 ###

> **视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。**
> 
> **下面是视图的常见使用场景：**
> 
> **重用SQL语句；**
> 
> **简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；**
> 
> **使用表的组成部分而不是整个表；**
> 
> **保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；**
> 
> **更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。**

--------------------

### 5、视图的优缺点 ###

**视图的优点**

> 1.  查询简单化。视图能简化用户的操作
> 2.  数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
> 3.  逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

**视图的缺点**

> 1.  性能。**数据库必须把视图的查询转化成对基本表的查询**，**如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。**
> 2.  修改限制。**当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改**。事实上，当从视图中插入或者删除时，情况也是这样。**对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的**
> 3.  这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\\SUM\\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）

--------------------

### **6、 语法格式** ###

> **CREATE VIEW  自定义视图名  AS   后面跟SELECT 语句**

上才艺~

USE sql_invoicing;
    CREATE VIEW clients_balance AS --此步为创建视图
    SELECT  
    i.client_id
    ,c.`name`
    ,SUM(i.invoice_total - i.payment_total) AS balance
    FROM invoices i
    JOIN clients c
    USING(client_id)
    GROUP BY i.client_id, c.`name`;

--------------------

## 二、动态更新视图 ##

**视图要经常根据我们的需要不断的是变化，所以就需要我们不断的对他进行修改和删除。**

### **1、更新视图** ###

**两种形式：**

> **1、ALTER VIEW  自定义视图名  AS   后面跟SELECT 语句**

> **2、CREATE OR REPLACE VIEW 自定义视图名  AS   后面跟SELECT 语句**

> **至于UPDATE、INSERT INTO 等等，和表的用法一致，现在就不赘述了，后期有时间补上~~~**

**话不多说~上才艺！**

USE sql_invoicing;
    -- 更改、刷新组 也可以用”DROP VIEW 表名 “的方式来删除
    CREATE OR REPLACE VIEW clients_balance AS   
    SELECT  
    i.client_id
    ,c.`name`
    ,SUM(i.invoice_total - i.payment_total) AS balance
    FROM invoices i
    JOIN clients c
    USING(client_id)
    GROUP BY i.client_id, c.`name`;

--------------------

### 2、查看视图 ###

**DESC 视图名；**

**SHOW CREATE VIEW 视图名；**

**推荐使用，第二个视图的结构显示比较杂乱。（后续有时间可以研究原理）**

--------------------

## 三、可更新视图 ##

### 1、为什么我们要创建可更新视图？ ###

**1、因为有时受限于权限，我们只能查询数据而无法对数据进行更新、操作，**

**这时我们可以通过创建视图把原表的信息搬运或是简单加工后搬运至一个新的虚拟表中，在这个虚拟表中我们就可以对其进行一些更新及操作。**

**2、视图可以减小数据库设计改动的影响**

这句话怎么理解？很简单! **设想我们在数据库中建立了很多查询语句，这些语句涉及多个表，当这些表中某一个列或者多个列等发生变化时，其查询语句也需要改变**。

这样就会异常麻烦。因此我们通过建立视图，把所有的查询都基于视图，**当底层表变化时，我们只需要修改视图就可以完成同步更新**，这样抽象化的处理模式会更加简便高效。

### **2、可更新视图的本质是什么？** ###

> **对视图的更新最终会转换成对基本表的更新，并非所有的视图都可更新。**

> **这里需要提出一个概念，行列子集视图（该视图是从单个基本表导出的，且去掉了基本表达某些行和列）是可更新的。**

**除此之外，理论上可分为可更新和不可更新的视图。**

> **目前的关系型数据库仅提供了对行列子集视图进行更新，但有以下限制。**

--------------------

### 3、视图可更新的限制？ ###

> **若视图是基于多个表使用联接操作而导出的，那么对这个视图执行更新操作时，每次只能影响其中的一个表。**

**1、查询列表中含有子查询、表达式（比如聚合函数、四则运算等）等不允许对视图进行Insert、update操作（但可以进行delete操作）。**

举个例，如果当前视图只是某个基本表中的一部分或总体，而此时对视图修改，实质上就是对库函数进行更新，例如sum()函数，你在视图中插入数据，在基本表找不到或者逻辑不通，mysql无法吧这个更新操作同步到其基础表中，就会报错。

**2、FORM子句中连接2个表及以上，不可更新。即from后引用的是不可更新的视图，通常为join和union。**

这是因为如果要更新这样的视图，那么就会涉及到多个基本表的同时修改，这会导致数据库的存取非常复杂，因此，在现有的关系数据库系统中，只支持来自单个表的视图的更新。

**3、使用GROUP BY或HAVING子句，即进行分组操作后的视图不能更新。**

这个原理其实差不多。分组过后、或者分组后筛选的的视图，其视图中的记录大多小于原基本表中的记录数，此时视图中的一条记录对应原表中的多条记录，因此在对原表进行修改时，无法找对视图和原表的对应关系。这在逻辑上是行不通的，因此不能更新。

**4、如果更新视图的字段来自字段表达式或者是常数，则不允许对视图进行Insert、update操作（但可以进行delete操作）**。

因为，假设用字段表达式或者是常数对视图进行了更新，那么视图的相关行或列就与基本表中的相关行或列不一致，或者在基本表中根本找不到，对视图的更新实质上是对基本表的更新，因此和基本表的不一致的视图是毫无意义的。（不允许Insert操作也是同样的道理）

**5、视图定义汇总使用DISTINCT子句不能更新。**

原理与**使用GROUP BY或HAVING子句相似。**

> ** 注意：**
> 
> **1.视图不可更新的原因：**因为视图对其属性值进行了其他形式上的计算的改变，而**对视图的更改最终表现为对表的更改。而表中不存在视图的某一属性，或属性的性质不相同，则无法更改，这是一种视图机制。**
> 
> **2. 视图更新操作可能会引起数据行的消失，我们该如何避免这个情况呢？只需要在语句末尾加上WITH CHECK  OPTION 即可。**
> 
> **3. RESTRICT删除和CASCAD删除：**视图删除后视图的定义将从数据字典中删除，如果在该视图上还导出了其他视图，**删除这些视图需要级联删除。**
> 
> 4. 视图的更新：一般行列子集视图是可以更新的，因为它只是去掉了基本表的某些行或者列，并且保留了主码，对视图的更新通过视图消解可以转化为对基本表的更新。

--------------------

# 2 游标 #

什么是游标？

> 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。
> 
> 用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

--------------------

# 3 变量 #

> **变量分为**
> 
>  *  **系统变量**
>  *  **自定义变量**

--------------------

## **一、系统变量** ##

**系统变量分为**

*  **全局变量global**
 *  **会话变量session**

--------------------

### **1、全局变量global** ###

> **服务器层面**
> 
> **必须拥有super权限**
> 
> **作用域为整个服务器，针对所有搜索连接(会话)有效**
> 
> **但是服务器一重启就会重置**

--------------------

### **2、会话变量session** ###

> 针对每个连接的客户端
> 
> **作用域为当前的SQL搜索连接(会话)**

> ** 说明：系统变量由系统提供，不用自定义，即不用DECLARE声明。**

--------------------

### 3、**显示系统变量** ###

显示所有的系统变量

> **SHOW  \[global|session\]  variables；**

查看指定变量

> 1.  **SELECT  @@\[global|session\].变量名;   **          --如果没有显示声明,则默认是session 
> 2.  **SELECT  @@变量名;   **  --如果没有显示声明,则默认是session

> **重点：你知道 1与2的区别吗？****如采用"@@****\[global|session\]"****形式，则必须在变量名前使用“.” 来连接。**

显示满足条件的系统变量

> **SHOW  \[global|session\]  variables  \[LIKE\]  '%自定义字符%'  ;**  --如果不显示声明，默认是session

--------------------

**系统变量赋值**

> **SET  \[global|session\]  变量名 = 值;    **

> **SET  @@\[global|session\].变量名 = 值 ;**

> **注意：在****系统变量赋值时，SET后面变量一定要指定global或session，即两者二选一，不然容易发生错误。**

--------------------

## 二、自定义变量 ##

自定义变量是指为了完成某个查询，**用户自己定义的变量**，用以存储或暂时存放数据的变量，

**主要分为2种：**

*  **用户变量**
 *  **局部变量**

--------------------

### **1、用户变量** ###

通常采用**“SET @变量名 = 值 ；”**格式，**不用通过DECLARE声明。**

> **其作用域：仅在当前SQL连接(会话)生效。**（**什么被释放掉呢？？？后面考虑一下~**）  
> **作用位置：会话中的任意位置**

--------------------

### **声明赋值(3种形式)** ###

> **SET @变量名 = 值 ；**
> 
> **SET @变量名 := 值 ；**
> 
> **SELECT @变量名 := 值 。**

--------------------

### **更新值（2种方式）** ###

**方式一：**

> **SET @变量名 = 值 ；**
> 
> **或  SET @变量名 := 值 ;**
> 
> **或  SELECT @变量名 := 值 ;**

**方式二：**

> **SELECT  值  INTO  @变量名   FROM  表 ;**

--------------------

### 使用变量 ###

**SELECT @变量名 ；**

--------------------

### **2、局部变量** ###

> **作用域：仅仅在定义他的beign end中有效。**
> 
> **位置：只能beign end中，而且只能放在第一句**(**如果要定义多个局部变量从第一行依次往下放置即可**)

--------------------

### **变量声明** ###

> **DECLARE   变量名   类型 ;**
> 
> **DECLARE   变量名   类型   DEFAULT    值 ;**

--------------------

### **变量更新** ###

**方式一：**

>  *  **SET  变量名  =  值 ；**
>  *  **SET 变量名 :=  值 ;**
>  *  **SELECT @变量名  =  值 ;   --这个@是去不掉的,为啥呢？先记住吧**

**方式二：**

> **SELECT 值  INTO  变量名  FROM 表;**

--------------------

### **变量的使用** ###

> **SELECT  局部变量名 ;**

### 用户变量和局部变量的区别 ###

![20210512101256919.png][]

--------------------

# 4 自定义函数 #

在SQL server中不仅提供了系统自带的函数，如日期函数、聚合函数、字符串函数等，还提供了自定义函数。

用户自定义函数的类型，主要分为两大类：

> 1、**标量值函数**（返回一个标量值）
> 
> 2、**表格值函数**（返回一个结果集即返回多个值）
> 
>  *  内联表格值函数
>  *  多语句表值函数

--------------------

## 一、标量值函数 ##

### 1、创建函数 ###

CREATE FUNCTION 函数名(参数列表)  --CREATE FUNCTION 函数名称（@参数名 参数类型）
    RETURNS 返回类型  
    [WITH ENCRYPTION]                --如果指定了 encryption 则函数被加密
    [AS]
    BEGIN
      function_body                  --函数体(即 Transact-SQL 语句)
      RETURN 返回值；                
    END

> ** 注意：参数列表一定要按照“@参数名 参数类型”来写，即加上@。**
> 
> **过程体中不用加。**

--------------------

### 2、调用函数 ###

> **SELECT 函数名（参数列表）；**

**举例说明，假设我输入一个时间，期望得到名字，那怎么创建这个函数呢？**

CREATE FUNCTION dbo.func_date_get_name(@date_into varchar(8)) 
    RETURNS varchar(20)   --返回返回值的数据类型
    [WITH ENCRYPTION]
    [AS]
    BEGIN
    	declare result_name varchar(20)  --声明变量
    	select  result_name = Value_name 
        from test_ceshi 
        where statdate = @date_into；
    
      /*上句等同于select Value_name INTO result_name 
    from test_ceshi where statdate = @date_into */ 
    
        RETURN  result_name；
    END$
    --调用函数
    select dbo.func_date_get_name('20180808') AS name; --AS是另命名该查询结果列名，也可以不写。

--------------------

### **二、内联表格值函数** ###

特点：内联表格值函数**支持在WHERE子句中使用参数。**

### 内联表格值函数定义格式 ###

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
    --CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
    RETURNS table    --返回一个表
    [WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
    [AS]
    BEGIN
        RETURN （一条SQL语句）;
    END$

> ### **注意事项** ###
> 
> **1、table前的是RETURNS ， 而不是RETURN。**
> 
> **2、RETURNS返回的是一个表，不是一个数据值。**
> 
> **3、内联表格值函数的函数体中只含有一条语句，而当函数体中只有一条语句时，BEGIN和END可以不用写。(BEGIN和END中的就是函数体)**
> 
> **4、RETURN后面跟的就是那唯一的SQL语句。（比较绕，但是我尽力了）**

好了，说累了！上才艺（网上拿别人的例子，嘿嘿~）

CREATE FUNCTION dbo.func_date_get_table(@date_into varchar(8)) 
    RETURNS table
    AS
    RETURN  select statdate,Value_name from test_ceshi where statdate = @date_into；
     
    
    --调用方法
    select * from dbo.func_date_get_table('20180808') ;

![70][]

--------------------

## **三、**多语句表值函数（**工作中使用最多的就是这种**） ##

**多语句表值函数**跟**内联表值函数**都是**表值函数**，**它们返回的结果都是Table类型**。

### **1、他俩有啥区别啊？** ###

>  *  函数体中SQL语句的数量不同：**内联表值函数只有1条，多语句表值函数有2条。**
>  *   **但是他们返回的TABLE的列的属性能否需要提前决定？多语句表值函数需要！真的是“函数”如其名。**

>  *  **多语句表值函数****通过多条语句来创建Table类型的数据**
>  *  **多语句表值函数需要指定具体的Table类型的结构**，也就是说**返回的Table要提前定义好哪些字段返回**
>  *  **多语句表值函数函数体内允许存在多条SQL语句，而不仅仅是一句**
>  *  再来看**内联表值函数**，其返回结果是**由函数体内的SELECT语句来决定，因此其函数体中只能含有一句SQL语句**

--------------------

### 2、函数语法格式 ###

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
    --CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
    RETURNS @Table_Variable_Name table (Column_1 culumn_type,Column_2 culumn_type...,Column_n culumn_type)    
    --RETURNS @表变量名 table （列1，列2......列n） --（列1，列2......列n）这是为了规定结果表中有哪些列
    [WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
    [AS]
    BEGIN
         函数体(即 Transact-SQL 语句)
     RETURN  
    END

> **注意：**
> 
> **1、****RETURNS  @表变量名  table （列1，列2......列n）                        --其中（列1，列2......列n）这是为了规定结果表中有哪些列**
> 
> **2、RETURN返回的是规定好了列的结果表。这些结果由SQL语句来完成。**
> 
> **3、最后只需要RETURN，RETURN后面不跟任何变量。**

上才艺~你懂得~~~嘿嘿。

例1：整张表插入。

CREATE FUNCTION dbo.func_date_get_table_test(@date_into varchar(8)) 
    RETURNS @table_test table(date varchar(8),ID varchar(20),name varchar(20))
    AS
    begin
      insert @table_test select  statdate,value_id,Value_name from test_ceshi where statdate = @date_into
       RETURN 
    end
     
    select * from dbo.func_date_get_table_test(20180808);

例2：向表中一行一行插入数据。

create function dbo.Test()
    returns @temp table (
        name varchar(20),
        sex char(2),
        age int
    )
    as
    begin
    insert into @temp (name,sex,age) values ('多语句','嘛',18)
    insert into @temp (name,sex,age) select name,sex,age from student where age > 18
    return 
    end

--------------------

## **三、**修改和删除函数 ##

### **1、使用alter语句修改函数** ###

alter function 函数名（参数）
    returns table
    as
    return(一条SQL语句)；  --修改或删除操作是在SQL语句中完成的。

> **注意：**
> 
> **1、只需要将CREATE换成ALTER即可。**
> 
> **2、修改或删除操作是在SQL语句中完成的。 **

--------------------

### **2、使用drop语句删除** ###

DROP FUNTION 函数名；

--------------------

# 5 存储过程 #

在数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全 。

--------------------

## 一、存储过程的概念 ##

**存储过程 (Stored Procedure)** 是在大型数据库系统中 , **一组为了完成特定功能的 预先编译好的SQL 语句集 , 存储在数据库中** ，类似于java中的方法；

经过第一次编译后再次调用不需要再次编译 ；

**用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它** ；

存储过程是数据库中的一个重要对象 ; 存储过程中可以包含 **`逻辑控制语句`** 和 **`数据操纵语句` **, 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值。

--------------------

## 二、存储过程的优缺点 ##

**优点 :**

1.  由于应用程序随着时间推移会不断更改 , 增删功能 , **SQL 语句会变得更复杂** , 存储过程能够封装此类代码、进行模块化设计 ，更加便于调用;
2.  由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中 , **所以存储过程运行要比单个的 SQL 语句块要快 ;**
3.  由于在调用时只需用提供**存储过程名**和**必要的参数信息** , 所以在一定程度上也可以**减少网络流量 , 减少网络负担** ;
4.  可维护性高 , **更新存储过程通常比更改 , 测试以及重新部署程序集****需要较少的时间和精力** ;
5.  代码精简一致 , **一个存储过程可以用于应用程序代码的不同位置** ;
6.  **增强安全性 :**
    
     *  通过向用户**授予对存储过程 (而不是基于表) 的访问权限** , 它们可以提供对特定数据的访问 ;
     *  提高代码安全 , **防止 `SQL注入` **(但未彻底解决 , 例如将数据操作语言 `DML` 附加到输入参数) ;
     *  SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的一部分 , 可以验证用户提供的值类型 (但也不是万无一失 , 还是应该传递至数据库前得到附加验证) ;

**缺点 :**

1.  重新编译问题，如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数等等 ;
2.  **可移植性差** , 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。
3.  如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

> **听不懂？没关系，日后再来看一遍，你就明白了（优缺点在使用的过程中会显现，多实践！！！）~**

--------------------

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

有两种创建存储过程的方式：无参 or 含参

*  ### 无参存储过程创建 ###

先说下其语法结构：太简单了，自己理解吧~

DELIMITER $$  --重新定义结束符；在navicat中可不用写；
    --创建存储过程
    CREATE PROCEDURE  自定义过程名（）
    BEGIN
            SELECT 子句1；
            SELECT 子句2；
            ......
            SELECT 子句n；
    END $$
    
    DELIMITER ；  --重新定义结束符

> **MySQL默认以";"为分隔符**，如果没有声明分隔符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER $$”声明当前段分隔符，让编译器把两个"$$"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

> **过程体：**过程体的开始与结束使用BEGIN与END进行标识，**即过程体就是BEGIN和END之间**。
> 
> SELECT子句在过程体内部，**彼此之间以'；'为结束标志。**

*  ### **有参的存储过程创建（重点~）** ###

咱先看下其语法结构：

DELIMITER $$  --重新定义结束符；在navicat中可不用写；
    --创建存储过程
    CREATE PROCEDURE  自定义过程名(
        [IN|OUT|INOUT] 参数1 参数数据类型
        [IN|OUT|INOUT] 参数2 参数数据类型
        ......
        [IN|OUT|INOUT] 参数n 参数数据类型)
    BEGIN
            SELECT 子句1；
            SELECT 子句2；
            ......
            SELECT 子句n；
    END $$
    
    DELIMITER ；  --重新定义结束符

> MySql 支持
> 
>  *  **IN (传递给存储过程)**
>  *  **OUT (从存储过程传出)** 
>  *  **INOUT (对存储过程传入和传出)** 类型的**参数**；
> 
> **存储过程的代码位于 BEGIN 和 END 语句内** , 它们是一系列 SQL 语句 , 用来检索值 , 然后**把检索到的结果保存到相应的变量** (**通过指定INTO关键字**) ;

> **参数列表声明时，要按以下格式声明： \[IN|OUT|INOUT\]  参数名 参数类型；**

说了半天，迷了吧~  来，上才艺！

DELIMITER $$  --重新定义结束符；在navicat中可不用写；
    --创建存储过程
    CREATE PROCEDURE GetScores(
    	in name varchar(4),
    	out Score int,
    	out avgScore decimal(8,2)
    )
    BEGIN
    	select score into Score 
        from student s
        where s.name = name;
    	select avg(score) into avgScore   --其实这两句可以合并，但是为了让你理解多个select子句我觉得还是分开写。好吗？
        from student s 
        where s.name = name;
    END $$
    
    DELIMITER ；  --重新定义结束符

> **注意：**
> 
> **若变量没有在参数列表中声明，必须在过程体内声明，即**
> 
> CREATE PROCEDURE GetScores(）
> 
> BEGIN
> 
>          **DECLARE  参数名P  参数类型  DEFAULT  默认值；**
> 
>          **SELECT  result  INTO  参数名P**
> 
> END $

**若变量在参数列表中声明了，则直接赋值即可，即**

> **CREATE PROCEDURE GetScores(OUT  num\_result  INT)**
> 
> BEGIN
> 
>          SELECT  result  **INTO  num\_result **
> 
> END $

--------------------

## 四、调用存储过程 ##

**对于无参的存储过程比较简单~**

语法格式如下：

> **CALL 存储过程名();**

**而含参的存储过程，则复杂了一些！因为无论是\[IN|OUT|INOUT\]都需要传入参数~**

不过不用怕，干就完了！

**调用此存储过程 , 必须填入变量**

因此，我们需要先声明变量，在存储过程及函数外申请的变量是用户变量。

> **用户变量都必须以 `@` 开始 ****, 如下所示 :**
> 
> **SET  @m = 值;**
> 
> **SET  @n = 值;**
> 
> 我们这里设@m属于in类型参数， @n属于out类型参数， 则其存储过程的调用过程为：
> 
> **CALL 存储过程名(@m，@n  );**

当然我们也可以这样写;

> **SET  @m = 值;**
> 
> **CALL 存储过程名(@m，@n  );**
> 
> **@n:****这里其实是省略了设置变量的步骤，即** **SET  @n = 值;**** **

从其他地方找了一个复杂案例，我觉得还OK， 我比较懒~

**案例，根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :**

create procedure GetPriceByID(
    	in prodID int,
    	in isDisc boolean,
    	out prodPrice decimal(8,2)
    ) 
    begin
    	declare tmpPrice decimal(8,2);
    	declare prodDiscRate decimal(8,2);
    	set prodDiscRate = 0.88;
    
    	select price from products
    	where id = prodID
    	into tmpPrice;
    
    	if isDisc then
    		select tmpPrice*prodDiscRate into tmpPrice;
    	end if;
    
    	select tmpPrice into prodPrice;
    end$

--------------------

## 五、查看、删除存储过程 ##

> **查看：SHOW CREATE PRUCEDURE 存储过程名;**
> 
> **删除：DROP  PRUCEDURE 存储过程名;**

--------------------

## 六、存储过程与函数的区别 ##

![20210512105643755.png][]

--------------------

# 6 触发器（了解一下） #

## 一、什么是触发器？ ##

触发器是用户**定义在关系表上的一类由事件驱动**的**特殊的存储过程**。

> **触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。**

--------------------

## **二、触发器的作用？** ##

> **sql有时多张表具有联动关系，或者自己一张表要保持一定的更新。**
> 
> **当对一张表，进行更新、删除、插入等操作时，会需要对另外一张表进行改动操作或者对本身要有其他操作。这时就需要创建sql触发器。**
> 
> **触发器经常用于加强数据的完整性约束和业务规则等**。
> 
>  **触发器创建语法四要素：(DML触发)（重要）**  
> **        1.监视地点(table)  
>         2.监视事件(insert/update/delete)   
>         3.触发时间(after/before)   
>         4.触发事件(insert/update/delete)**

在MySQL数据库中有如下六种触发器：

*  Before Insert
 *  After Insert
 *  Before Update
 *  After Update
 *  Before Delete
 *  After Delete

触发器大致分为两种：**DDL触发器和DML触发器**，相对应的具体定义如下。

--------------------

## 三、DML触发器 ##

### **1、SQL触发器语法** ###

create trigger triggerName  
    after/before insert/update/delete on 表名|视图名   
    for each row   #这句话在mysql是固定的  
    begin  
        sql语句;  
    end $$

其中：**trigger\_time**是**触发器的名称**。

**before：**在检查约束前触发。

**after：**在检查约束后触发。

**trigger\_event**是触发器的**触发事件**，包括**insert、update和delete。**

> **注意：**
> 
> **1、对同一个表相同触发时间的相同触发事件，只能定义一个触发器。**
> 
> **2、其中‘表名|视图名’是触发器所放置的地点，很重要，别弄混了~**

--------------------

### 2、简单的触发器 ###

**以UPDATE触发器为例。**我们在sql\_invoicing数据库中payments表中创建一个触发器，以实现当payments表中插入数据时，invoices表中payment\_total列可以自动更新。更新规则为‘累加’。

上才艺~

先在一个SQL窗口创建触发器：

CREATE TRIGGER payments_after_update
    	AFTER INSERT ON payments 
    	FOR EACH ROW 
    BEGIN 
    	UPDATE sql_invoicing.invoices
    	SET   payment_total = payment_total  + NEW.amount
    	WHERE invoice_id = NEW.invoice_id;
    END $$

> **注意：触发器名称起名小窍门**：**表名\_ 事件\_SQL事件**
> 
> **其中，表名：待设置触发器的表**
> 
> **事件：AFTER \\BEFORE 等**
> 
> **SQL事件：UPDATE\\INSERT\\DELETE**

然后，payments表中插入一条新的数据。

INSERT INTO payments()
    VALUES(DEFAULT, 5, 3, '2019-02-12', 30, 1)

运行结果如下，注意看invoices表中payment\_total列自动更新了呀~

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dxal83MTA4NTI3NDc_size_16_color_FFFFFF_t_70][]

--------------------

### 3、判断值后调用触发器 ###

看起来很难，但是其实比较简单啦~~

就是在SQL语句块中增加一些判断语句啦~ 骨架上填肉!!!

DROP TRIGGER IF EXISTS `payments_after_update`;   --删除原触发器
    DELIMITER $$ --修改分隔符
    CREATE TRIGGER payments_after_update
    	AFTER INSERT ON payments 
    	FOR EACH ROW 
    BEGIN
        IF NEW.amount >= 30 then    --可以使用old和new来引用触发器中发生变化的记录内容。
    	UPDATE sql_invoicing.invoices
    	SET   payment_total = payment_total  + NEW.amount
    	WHERE invoice_id = NEW.invoice_id;
        END IF;  
    END $$

我增加的判断条件是，只有当 payments表中插入数据的amount列大于等于30时，invoices表中payment\_total列才进行自动累加。结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dxal83MTA4NTI3NDc_size_16_color_FFFFFF_t_70 1][]

让我们再插入一条amount列为40（大于30）的新数据。

INSERT INTO payments
    VALUES(DEFAULT, 5, 3, '2019-02-12', 40, 1)

观察查询结果：wow，it' s amazing~

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dxal83MTA4NTI3NDc_size_16_color_FFFFFF_t_70 2][]

> 其他触发器都大同小异，这里不再赘述！

--------------------

### 4、查看触发器 ###

简单讲下，

语法结构如下：

SHOW TRIGGERS；
    SHOW TRIGGERS LIKE '%payments%'

> **需要具体查询某一触发器时，可通过 SHOW TRIGGERS LIKE '%payments%'   来实现。**

其结果如下：

![20201116200943509.png][]

--------------------

### 5、删除触发器 ###

删除触发器跟删除存储过程大致相同。

简单说下其语法结构：

> **DROP TRIGGER IF EXISTS  触发器名称；**

> **最好的办法应该是把触发器删除与创建语句放在同一个脚本文件中并录入一个源代码库中，这样我们可以看到历史数据库修改记录。**

--------------------

### 6、少用触发器 ###

问题1：[大型系统必须得要存储过程和触发器吗？][Link 1]

肯定不能一刀切的说能用或者不能用，不同类型的系统、不同的规模、不同的历史原因都会有不同的解决方案。  
        一般情况下，**Web应用的瓶颈常在DB上，所以会尽可能的减少DB做的事情，把耗时的服务做成Scale Out**，这种情况下，肯定不会使用存储过程；而如果只是一般的应用，DB没有性能上的问题，在适当的场景下，也可以使用存储过程。  
        **至于触发器，我是知道有这东西但从来没用过。我希望风险可控，遇到问题能够快速的找到原因，尽可能不会去使用触发器。**

1.PLSQL可以大大降低parse/exec 百分比；  
        2.存储过程可以自动完成静态SQL variable bind；  
        3.存储过程大大减少了JDBC网络传输与交互，速度快；  
        4.oracle 中存储过程内部commit为异步写，一定程度上减少了等redo日志落地时间；  
        5.存储过程最大问题就是给数据库开发工作压力太大，另外架构升级时候会比较难解耦；  
        6.触发器不推荐使用，触发操作能在业务层解决就在业务层解决，否则很难维护，而且容易产生死锁。

问题2：[为什么大家都不推荐使用MySQL触发器而用存储过程？][MySQL]

1.存储过程和触发器二者是有很大的联系的，**我的一般理解就是触发器是一个隐藏的存储过程，因为它不需要参数，不需要显示调用，往往在你不知情的情况下已经做了很多操作。**从这个角度来说，由于是隐藏的，**无形中增加了系统的复杂性，非DBA人员理解起来数据库就会有困难，因为它不执行根本感觉不到它的存在。**  
        2.再有，**涉及到复杂的逻辑的时候，触发器的嵌套是避免不了的，如果再涉及几个存储过程，再加上事务等等，很容易出现死锁现象**，再调试的时候也会经常性的从一个触发器转到另外一个，级联关系的不断追溯，很容易使人头大。其实，从性能上，触发器并没有提升多少性能，只是从代码上来说，可能在coding的时候很容易实现业务，所以我的观点是：**摒弃触发器！触发器的功能基本都可以用存储过程来实现。**  
        3.在编码中存储过程显示调用很容易阅读代码，触发器隐式调用容易被忽略。  
        4**.存储过程的致命伤在于移植性，存储过程不能跨库移植**，比如事先是在mysql数据库的存储过程，考虑性能要移植到oracle上面那么所有的存储过程都需要被重写一遍。

> **这种东西只有在并发不高的项目，管理系统中用。如果是面向用户的高并发应用，都不要使用。触发器和存储过程本身难以开发和维护，不能高效移植。触发器完全可以用事务替代。存储过程可以用后端脚本替代。**

[20210512101256919.png]: /images/20221021/4baec18fd8f14a8a8733a38e26ff09a6.png
[70]: /images/20221021/58f553602c2d4b9f8a4c8be7c2804d00.png
[20210512105643755.png]: /images/20221021/63add6cf522749509b1710e2fcee3fcb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dxal83MTA4NTI3NDc_size_16_color_FFFFFF_t_70]: /images/20221021/3bccab2855e24faeabd89b3e5d437789.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dxal83MTA4NTI3NDc_size_16_color_FFFFFF_t_70 1]: /images/20221021/ff40ebc9ec9c426eab3d3f542436be28.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dxal83MTA4NTI3NDc_size_16_color_FFFFFF_t_70 2]: /images/20221021/02eafc9e5c8641d2a85836d3a215785a.png
[20201116200943509.png]: /images/20221021/0dc52ab4ac74440ebb00328485e9fa62.png
[Link 1]: http://www.zhihu.com/question/19749126
[MySQL]: https://segmentfault.com/q/1010000004907411