第一部分：Stored Procedures - 存储过程

我们使用储存过程来储存和管理SQL代码

大多数DBMS可以对储存过程里的代码做一些优化
因此在储存过程里的SQL代码有时候可以执行起来更快

此外 和视图一样 储存过程能加强数据安全性
例如 我们可以取消对所有表的直接访问权限
并让很多操作，比如插入、更新和删除数据由储存过程来完成
然后可以指定能够执行特定储存过程的某一人
这样会限制用户对我们数据的操作范围
比如 我们可以防止一些用户删除我们的数据

第二部分：Creating a Stored Procedures - 创建一个存储过程

CREATE PROCEDURE get_clients()
--- 后面会讲如何在()处添加参数 给储存过程传递值
BEGIN
    SELECT * FROM clients;
END
--- BEGIN和END关键字之间的内容 称为存储过程的主体(body)

--- 通常创建的储存过程都会有好几条语句
--- 所以每条语句都需要分号终结
--- 即使我们这里只有一条语句
--- 这是MySQL特别要求的
--- 下面为完整过程：

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;
END $$

DELIMITER ;

--- 使用DELIMITER语句改变默认分隔符
--- DELIMITER: 这是新的分隔符 把这些所有语句当作一个整体
--- 最后 要把默认分隔符改回分号
--- 在其他的DBMS比如SQL Server 你不需要改动默认分隔符

--- 我们可以在'Stored Procedures'中通过点击图标来调用或者执行这个过程
--- 这样就返回了我们数据库中的所有客户
--- 我们也可以使用CALL语句来调用存储过程
CALL get_clients()

EXERCISE：

Part2 - Creating a Stored Procedures - 创建一个存储过程
-- Create a stored procedure called
-- get_invoices_with_balance
-- to return all the invoices with a balance > 0

SOLUTION:

SELECT *
FROM invoices
WHERE invoice_total - payment_total > 0

--- 我们也可以用视图

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
    SELECT *
    FROM invoices_with_balance
    WHERE balance > 0;
END $$

DELIMITER ;

第三部分：Creating Procedures Using MySQLWorkbench - 使用MySQL工作台创建存储过程

--- 在导航面板，右键点击Stored Procedures -〉Create Stored Procedure
--- 在这个窗口 就不用担心需要改默认分隔符了

第四部分：Dropping Procedures - 删除存储过程

DROP PROCEDURE get_clients

--- 如果你再次执行语句 会得到错误提示
--- 因为我们试图删除一个并不存在的过程 MySQL会显示错误
--- 为了防止错误出现 我们可以使用IF EXISTS关键字
like
DROP PROCEDURE IF EXISTS get_clients

--- 和视图一样 最好把删除和创建每一个存储过程的代码存储在不同的SQL文件中
--- 并把文件放在Git那样的源代码控制下
--- 这样就可以和其他组员共享Git存储库
--- 任何人都可以用他们电脑上的所有相关视图和存储过程来再建这个数据库
like

DROP PROCEDURE IF EXISTS get_clients;

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;
END $$

DELIMITER ;

第五部分：Parameters - 参数

--- 我们一般使用参数为存储过程传递值
--- 但我们也可以使用参数为调用程序赋值

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state()
BEGIN
    SELECT * FROM clients;
END $$

DELIMITER ;

--- 我们想让存储过程获取州名 并返回位于那个州的客户
--- 所以在括号中间要添加一个参数
--- 称为‘state’，并把类型设置为char(2)

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    SELECT * FROM clients;
    WHERE state = state;
END $$

DELIMITER ;

--- 我们在存储过程中如果有很多个参数，就用逗号分隔
--- CHAR(2)是什么意思呢？
--- 它表示了有两个字符的字符串
--- 比如加州(CA)或者纽约(NY)
--- 还有VARCHAR表示可变长度的字符串
--- 这在存储姓名、电话号码、信息的时候很有用
--- 它们都是可变长度字符串
--- 多数时候我们使用VARCHAR 除非我们能确定字符串有固定长度

--- WHERE 这句 筛选器不会起任何作用 因为我们在用同一个值同他本身列的值对比
--- 无论如何这个条件当然都是对的
--- 所以这里我们需要把‘state’列和这里定义的‘state’参数区分开
--- 有以下几种方法
--- 
--- 有些人喜欢给系数前增加前缀或者后缀 像p_state CHAR(2) p -> parameter
--- 或者pState S大写
--- 另外一些人喜欢添加后缀 比如state_param
--- 
--- 更加干净利落的方式是参数位置保持不变
--- 并给表格起一个别名 c
--- WHERE c.state = state;
--- 这就很清楚我们是在把州这一列的值和定义的州这个参数做对比了
like

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    SELECT * FROM clients c
    WHERE c.state = state;
END $$

DELIMITER ;

---
CALL get_clients_by_state('CA')
--- 这样就返回了位于加州的客户
--- 如果我们不提供任何值 -> 就会报错
--- 因为在MySQL中 所有系数都是必填的

EXERCISE：

Part5 - Parameters - 参数
-- Write a stored procedure to return invoices
-- for a given client
-- 至于这个参数的类型 参考发票表的client_id列的类型
-- 
-- get_invoices_by_client

SOLUTION:

DROP PROCEDURE IF EXISTS get_invoices_by_client;

DELIMITER $$
CREATE PROCEDURE get_invoices_by_client
(
    client_id INT
)
BEGIN
    SELECT *
    FROM invoices i
    WHERE i.client_id = client_id;
END $$

DELIMITER ;

第六部分：Parameters with Default Value - 带默认值的参数

这个部分来学习如何为参数配置默认值，继续使用之前的例子

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    SELECT * FROM clients c
    WHERE c.state = state;
END $$

DELIMITER ;

--- 现在让我们自己制定一个规则 如果存储过程调用者无法明确具体在哪个州
--- 那么就默认返回'加州'的客户

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    IF state IS NULL THEN
        SET state = 'CA';
    END IF;
    
    SELECT * FROM clients c
    WHERE c.state = state;
END $$

DELIMITER ;

--- 分隔线 ---

CALL get_clients_by_state(NULL)
--- 调用get_clients_by_state，把NULL作为参数
--- 会返回加州的客户
--- 注意这里我们必须明确一个值，即使是空值
--- 因为如果我们空着不管 MySQL会不高兴

--- 分隔线 ---

--- 回到我们的过程，再变动下规则
--- 如果我们不只是返回位于加州的客户 而是返回所有客户怎么样
--- 方法一是
--- 不再为参数一个默认值 我们可以写如下的分段查询

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    IF state IS NULL THEN
        SELECT * FROM clients;
    ELSE
        SELECT * FROM clients c
        WHERE c.state = state;
    END IF;
END $$

DELIMITER ;

--- 分隔线：方法二 ---

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    SELECT * FROM clients c
    WHERE c.state = IFNULL(state, c.state);
END $$

DELIMITER ;

--- IFNULL函数，如果第一个值是空值，这个函数就会返回第二个值
--- 所以如果这里把参数设置成空值 这个函数就会返回c.state
--- 我们的WHERE子句本质上就会变成
--- WHERE c.state = c.state
--- 这个条件是永远正确的 因为任何值都等于值本身
--- 使用这个技巧就可以选择性的筛选数据

EXERCISE：

Part6 - Parameters with Default Value - 带默认值的参数
-- Write a stored procedure called get_payments
-- with two parameters
-- 
-- client_id => INT
-- payment_menthod_id => TINYINT
-- 这两种数据类型都是用来存储整数的
-- 其中TINYINT占用1字节的内存
-- 可以用来存储0-255的数字
-- INT占用4字节 可以用来存储更大的数字
-- 这两个参数都不是必填的 所以如果这两个参数都传递空值
-- 过程应该能够返回数据库里的所有付款记录
-- 如果提供client_id 要只返回这个客户的付款
-- 如果这两个参数都赋值了 就应该返回指定客户使用指定付款方式支付的所有付款
-- 

SOLUTION:

DROP PROCEDURE IF EXISTS get_payments;

DELIMITER $$
CREATE PROCEDURE get_payments
(
    client_id INT,
    payment_method_id TINYINT
)
BEGIN
    SELECT *
    FROM payments p
    WHERE p.client_id = IFNULL(client_id, p.client_id) AND
          p.payment_method = IFNULL(payment_method_id, p.payment_method);
END $$

DELIMITER ;

--- 这里需要输入两个条件
--- 因为我们有两种筛选数据的方法
--- 第一种针对客户
---- 如果client_id是空值 就取和client_id列一样的中
--- 第二种针对付款方式
---- 注意这张表中该列的名称是'payment_method'而不是'payment_method_id'

TEST:

CALL get_payments(NULL, NULL)
-- 理论上我们这里赋的值叫做参数 - 实际参数
-- 很多开发者并不知道 参数arguments(实际参数)和参数parameters(形参)的区别
-- 形参是占位符 或者我们在过程或者函数中定义的小小坑位
-- 我们定义了两个形参 client_id and payment_method_id
-- 我们提供给这些形参数的值叫做实参
CALL get_payments(5, NULL)

第七部分：Parameter Validation - 参数验证

--- 我们也可以使用过程来插入、更新和删除数据
--- 第七部分会创建一个过程来更新发票
--- 作为其中一个步骤我们会学习参数验证
--- 确保我们的过程 不会意外地往数据库存储错误数据

DROP PROCEDURE IF EXISTS make_payment;

DELIMITER $$
CREATE PROCEDURE get_payments
(
    invoice_id INT,
    payment_amount DECIMAL(9, 2),
    payment_date DATE
)
BEGIN
    UPDATE invoices i
    SET
        i.payment_total = payment_amount,
        i.payment_date = payment_date
    WHERE i.invoice_id = invoice_id;
END $$

DELIMITER ;

--- payment_amount这个参数为 小数数据类型：DECIMAL(9, 2)
--- 第一个参数代表位数，第二个参数代表小数点后的位数
--- 
--- 这里我们只想更新两列
--- 因为客户付款的时候，我们不想意外更新发票号码或者总额或者发票日期
--- 我们只想更新付款总计和付款日期列

TEST:
call sql_invoicing.make_payment(2, 100, '2019-01-01');

--- 再回到发票表 验证我们的发票是否正确更新
--- 但是如果我们在调用过程的时候传递了一个负值 怎么办？
--- 目前的 语句 会存储成-100，所以需要做validation

like

DROP PROCEDURE IF EXISTS make_payment;

DELIMITER $$
CREATE PROCEDURE get_payments
(
    invoice_id INT,
    payment_amount DECIMAL(9, 2),
    payment_date DATE
)
BEGIN
    IF payment_amount <= 0 THEN
        SIGNAL SQLSTATE '22003' SET MESSAGE_TEXT = 'Invalid payment amount';
    END IF;

    UPDATE invoices i
    SET
        i.payment_total = payment_amount,
        i.payment_date = payment_date
    WHERE i.invoice_id = invoice_id;
END $$

DELIMITER ;

--- 如果写了太多验证逻辑
--- 你的存储过程最终会变得很复杂并难以维护
--- 比如 这里我不打算check参数是否为空值
--- 因为payment_amount本就不允许空值
--- 所以如果你给这个过程传递了空值
--- MySQL会自动标注这一错误
--- 所以尽量利用最少的验证逻辑 只保留最关键的那些

第八部分：Output Parameters - 输出参数

DROP PROCEDURE IF EXISTS get_unpaid_invoices_for_client;

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client
(
    client_id INT
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
    FROM invoices i
    WHERE i.client_id = client_id
        AND payment_total = 0;
END $$

DELIMITER ;

DROP PROCEDURE IF EXISTS get_unpaid_invoices_for_client;

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client
(
    client_id INT,
    OUT invoices_count INT,
    OUT invoices_total DECIMAL(9, 2)
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = client_id
        AND payment_total = 0;
END $$

DELIMITER ;

--- 现在默认情况下 存储过程的所有这些参数都是输入参数
--- 也就是说 我们只能在给过程传递值的时候才可以使用它们
--- 所以这里我们需要给这两个参数附上OUT关键字前缀
--- 这会把参数标记为输出参数
--- 我们就可以从过程中获取到这些值
--- 
--- 现在我们需要稍微修改一下选择语句
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
--- 所以我们读取第一行的数据 然后复制到这些输出参数
--- 

set @invoices_count = 0;
set @invoices_total = 0;
call sql_invoicing.get_unpaid_invoices_for_client(3, @invoices_count, @invoices_total);
select @invoices_count, @invoices_total;

--- 来看一下生成的代码
--- 首先我们要定义两个变量 invoices_count and invoices_total
--- 我们称它们为用户定义变量
--- 变量其实就是我们可以用来存储单一值的对象
--- 我们需要用@符号来作前缀来定义变量
--- 这里使用SET语句，我们定义了2两个变量，初始值设为0
--- 然后当调用这个过程的时候 我们需要传递这些变量
--- 这些变量用SET语句定义，并用@符号作为前缀，又被称作User or session variables
--- 所以我们的第一个参数3 就是client_id就为3
--- 另外的参数就是我们先前定义的变量了
--- 调用过程以后，我们需要用选择语句来读取这些值 并且在此显示

第九部分：Variables - 变量

set @invoices_count = 0;

--- 这是我们使用SET语句定义的变量，并且加上了@符号前缀
--- 通常 我们会在调用有输出参数的存储过程时使用这些变量
--- 我们传递这些变量 来获取输出参数值
--- 这些变量在整个客户会话过程中被保存
--- 客户从MySQL断线的时候，这些变量又被清空
--- 所以我们把它们称为“User or session variables”
--- 用SET语句定义它们，并且用@符号作为前缀
--
--- MySQL中还有另外一种变量，叫“Local variable”
--- 这些变量是我们可以在存储过程或者函数内定义的
--- 这些本地变量不会在整个User session过程中被保存
--- 一旦我们的存储过程完成执行任务
--- 这些变量就被清光了
--- 通常我们使用这类型变量在我们的存储过程中执行计算任务
--
CREATE PROCEDURE get_risk_factor()
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;
    
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices;
    
    SET risk_factor = invoices_total / invoice_count * 5;
    
    SELECT risk_factor;
    
-- risk_factor = invoices_total / invoice_count * 5

-- 首先我们要在BEGIN语句后面声明一下变量
-- 所以使用DECLARE语句声明变量，然后规定它的类型
-- 当前这样的写法表示 一个有小数点的数字，最多9位数，小数点后有两位小数
-- 我们可以选择给予这个变量一个默认值，不然默认值就为空值了

-- 这里不给第二、三个变量一个默认值的原因是：要用SELECT语句来设定它们

-- 以上就是我们如何在存储过程中声明并使用本地变量
-- 这些本地变量只有在我们的存储过程中才有意义
-- 只要我们声明它们，它们就可以被使用了
-- 然后一旦我们执行完存储过程，它们就被抹去了
END

第十部分：Functions - 函数

-- 函数和存储过程挺像的
-- 区别是：函数只能返回单一值
-- 所以比起存储过程，它们无法返回拥有多行和多列的结果集
-- 所以如果你想返回单一值，就可以创建函数

-- 创建函数来计算risk_factor
-- 右键Functions -》 Create Function
-- 创建函数的语法和创建存储过程的语法很相似
-- 在括号中定义我们的参数，比如客户ID，类型为INT
-- 然后是RETURNS语句，这是函数和存储过程地主要区别
-- 它明确了这个函数返回的值的类型

-- 在RETURNS语句之后，要紧跟着设置函数属性
-- 每个MySQL都至少需要一个属性
-- 一个是DETERMINISTIC -》意思是 如果我们给予这个函数同样的一组值
-- 它就永远会返回一样的值
-- 这在不想根据数据库中的数据返回值的时候很有用
-- 因为数据会变
-- 一个现实生活中的应用场景是：一个计算发货或者税或者订单的函数
-- 你可能有一个基于业务规则的公式 比方说：税是订单总额的10%
-- 这个函数获取订单总额 然后返回税额
-- 这是具有确定性的，也就是说，一样的输入对象总是会返回一样的输出对象

-- 另一个属性是READS SQL DATA(读取SQL数据)
-- 它的意思是 函数中会配置SELECT语句 用以读取一些数据

-- MODIFIES SQL DATA(修改SQL数据)
-- 它的意思是 函数中有插入、更新或者删除函数

-- 在这个例子中，我们的函数没有确定性
-- 因为如果你输入同一个client_id
-- 它可能返回不同值
-- 过段时间这位客户可能会支付其他发票

-- 我们也不会修改任何内容

CREATE FUNCTION `get_risk_factor_for_client`
(
    client_id INT
)
RETURNS INTEGER
READS SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;
    
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = client_id;
    
    SET risk_factor = invoices_total / invoice_count * 5;
    
    RETURN risk_factor;
END

-- 调用函数
SELECT
    client_id,
    name,
    get_risk_factor_for_client(client_id) AS risk_factor
FROM clients

-- 和View一级存储过程类似的是
-- 最好把你的函数存在SQL文件，并放在源代码里

第十一部分：Other Conventions - 其他约定

在本教程中，存储过程的书写方式是get_risk_factor