MySQL视图、存储过程、函数

视图

USE sql_invoicing;

-- 创建视图
CREATE VIEW sales_by_client AS
    SELECT 
        client_id,
        name,
        SUM(invoice_total) AS total_sales
    FROM clients c
    JOIN invoices i USING(client_id)
    GROUP BY client_id;

-- 删除视图
DROP VIEW sales_by_client

-- 使用同数据表相同

--更改视图
CREATE OR REPLACE VIEW clients_balance AS
    SELECT
    ......

可更新视图

如果一个视图的原始查询语句中没有如下元素：
1. DISTINCT 去重
2. GROUP BY/HAVING/聚合函数 (后两个通常是伴随着 GROUP BY 分组出现的)
3. UNION 纵向连接

则该视图是可更新视图，可以增删改，否则只能查。

另外，增还要满足附加条件：视图必须包含底层原表的所有必须字段 

WITH CHECK OPTION 子句

在视图的原始查询语句最后加上WITH CHECK OPTION，可以防止执行那些会让视图中某些行（记录）消失的修改语句。

存储过程

USE sql_invoicing;

--删除
DROP PROCEDURE IF EXISTS get_clients;
-- IF EXISTS避免因为此过程不存在而报错

-- 创建
DELIMITER $$

    CREATE PROCEDURE 过程名()  
        BEGIN
            ……;
            ……;
            ……;
        END$$

DELIMITER ;

--使用
CALL get_clients()
-- CALL sql_invoicing.get_clients()

参数验证

BEGIN
    IF payment_amount <= 0 THEN
        SIGNAL SQLSTATE '22003' 
            SET MESSAGE_TEXT = 'Invalid payment amount';    
    END IF;

    UPDATE ......
END

输出参数

CREATE PROCEDURE get_unpaid_invoices_for_client(
        client_id INT,
        OUT invoice_count INT,
        OUT invoice_total DECIMAL(9, 2)
        -- 默认是输入参数，输出参数要加OUT
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoice_count, invoice_total
    -- SELECT后跟上INTO语句将SELECT选出的值传入输出参数中
    FROM invoices i
    WHERE 
        i.client_id = client_id AND
        payment_total = 0;
END

使用输出参数： 

set @invoice_count = 0;
set @invoice_total = 0;
call sql_invoicing.get_unpaid_invoices_for_client(3, @invoice_count, @invoice_total);
select @invoice_count, @invoice_total;

 变量

用户或会话变量 SET @变量名 = xx
本地变量 DECLARE 变量名 数据类型 [DEFAULT 默认值]

 函数

-- 删除函数
DROP FUNCTION [IF EXISTS] 函数名

CREATE FUNCTION get_risk_factor_for_client
(
    client_id INT
) 
RETURNS INTEGER
-- DETERMINISTIC 常量
READS SQL DATA
-- MODIFIES SQL DATA 增删改
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;

    SELECT SUM(invoice_total), COUNT(*)
    INTO invoices_total, invoices_count
    FROM invoices i
    WHERE i.client_id = client_id;

    SET risk_factor = invoices_total / invoices_count * 5;
    RETURN IFNULL(risk_factor, 0);       
END