数据库操作：视图、存储过程与函数的应用解析-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_43560633/article/details/119726925

视图、存储过程、函数

-- 创建或覆盖覆盖视图
CREATE OR REPLACE 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, name;
    
-- 如果存在，删除视图
DROP VIEW IF EXISTS sales_by_client;

-- 创建或覆盖覆盖视图
CREATE OR REPLACE VIEW clients_balance AS
    SELECT 
        client_id,
        c.name,
        SUM(invoice_total - payment_total) AS balance
    FROM clients c
    JOIN invoices USING(client_id)
    GROUP BY client_id, c.name; -- 不要漏掉c.name虽然MySQL不会报错

注意：创建视图不能使用order by，在MySQL中不会报错，但也没有排序的效果

 -- 定义视图时不要使用ORDER BY子句，mysql不会报错但order by 不会起作用
CREATE OR REPLACE VIEW cus(customer_id, full_name) AS
    SELECT customer_id, CONCAT(first_name, ' ', last_name)
    FROM customers
    ORDER BY customer_id DESC;

更新视图

-- 准备视图
CREATE OR REPLACE VIEW invoices_with_balance AS
    SELECT 
        invoice_id, 
        number, 
        client_id, 
        invoice_total, 
        payment_total, 
        invoice_date,
        invoice_total - payment_total AS balance,  -- 余额
        due_date, 
        payment_date
    FROM invoices
    WHERE (invoice_total - payment_total) > 0;

-- 删除
DELETE FROM invoices_with_balance WHERE invoice_id = 1

-- 修改
UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id = 2

-- 增加
-- 必须满足原始表的约束才能插入，因为视图的实质是select语句
-- 如上面这个视图就无法插入数据

不可更新视图


CREATE OR REPLACE VIEW invoices_with_balance AS
    SELECT 
        invoice_id, 
        number, 
        client_id, 
        invoice_total, 
        payment_total, 
        invoice_date,
        invoice_total - payment_total AS balance,  -- 余额
        due_date, 
        payment_date
    FROM invoices
    WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION; -- 使用update语句会报错

存储过程

-- 创建存储过程
DELIMITER $$ -- 更改分隔符 ; --> $$

    CREATE PROCEDURE get_clients()  
        BEGIN
            SELECT * FROM clients; -- 存储过程内部的分割符，每一语句都要使用;分割
        END$$ -- 存储过程外面的分隔符

DELIMITER ; -- 还原分隔符

-- 执行存储过程
CALL get_clients();

-- 创建存储过程
DELIMITER $$

    CREATE PROCEDURE get_invoices_with_balance()
        BEGIN
            SELECT *
            FROM invoices_with_balance -- 存储过程中使用视图
            WHERE balance > 0;
        END$$

DELIMITER ;

-- 调用存储过程
CALL get_invoices_with_balance();

-- 如果存在，删除存储过程
DROP PROCEDURE IF EXISTS get_clients_by_state;

-- gist: 返回指定地址的顾客信息信息
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') -- 必须有参数，即使为NULL

存储过程实例

USE sql_invoicing;

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 + = 才是赋值，只有 = 是比较语句 相等于 == 
        SET state = 'CA'; -- 更改参数的值，在执行后面的SQL语句 
    END IF;
    SELECT * FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;

-- 返回指定州的客户信息，如果传入NULL，返回所有顾客信息
BEGIN
    SELECT * FROM clients c
    WHERE c.state = IFNULL(state, c.state)
END$$

CREATE PROCEDURE get_payments
(
    client_id INT,  -- 不用写成INT(4)
    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$$

-- 获取特定顾客所有未支付过的发票记录（即 payment_total = 0 的发票记录）
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

参数验证

IF payment_amount <= 0 THEN
        SIGNAL SQLSTATE '22003' 
            SET MESSAGE_TEXT = 'Invalid payment amount';  -- 相当java的throw Exception
    END IF;

输出参数，会话变量

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 -- 使用into给out变量赋值
    FROM invoices i
    WHERE 
        i.client_id = client_id AND
        payment_total = 0;
END$$

-- 调用
set @invoice_count = 0; -- set @xx 是会话类型/用户类型的参数，存在时间是整个会话
set @invoice_total = 0;
call sql_invoicing.get_unpaid_invoices_for_client(3, @invoice_count, @invoice_total);
select @invoice_count, @invoice_total;

本地变量

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 SUM(invoice_total), COUNT(*)
    INTO invoices_total, invoices_count
    FROM invoices;

    -- 用SET语句给risk_factor计算赋值
    SET risk_factor = invoices_total / invoices_count * 5;

    -- 展示最终结果risk_factor
    SELECT risk_factor;     
END$$

函数

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

-- 如果存在，删除函数
DROP FUNCTION IF EXISTS get_risk_factor_for_client;