0%

MySQL笔记(8)--存储过程(Stored Procedures)

发表于 更新于 分类于 Valine：

1 优点

Store and organize SQL
Faster execution
Data security

2 创建一个存储过程

1
2
3
4
5
6
7
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;	-- body of procedure
END$$

DELIMITER ;				-- 将分隔符定义回分号

DELIMITER 重定义分隔符, 也就是将新分隔符包含的内容视为一个unit, 而不是将”;”结束的一个个语句视为unit.

调用存储过程:

1
CALL get_clients()

3 删除存储过程

1
DROP PROCEDURE IF EXISTS get_clients;

创建存储过程的基本框架

1
2
3
4
5
6
7
8
9
DROP PROCEDURE IF EXISTS get_clients;

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

DELIMITER ;

4 参数

1
2
3
4
5
6
7
8
9
10
11
12
13
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 ;

调用:

1
CALL get_clients_by_state('CA')

4.1 带默认值的参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 ;

调用：

1
CALL get_clients_by_state(NULL)

IF-ELSE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 ;

更简洁的写法：

1
2
3
4
5
6
7
8
9
10
11
12
13
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(state, c.state) 如果 state = NULL，则返回 c.state，而 c.state = c.state 永远为 TRUE

4.2 参数验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DROP PROCEDURE IF EXISTS make_payment;

DELIMITER $$
CREATE PROCEDURE make_payment(
    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 ;

4.3 输出参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 ;

调用：

1
2
3
4
5
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;

5 变量

1
2
3
4
5
-- User or session variables
set @invoices_count = 0;

-- Local variable
-- 在存储过程或函数中定义

Demo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP PROCEDURE IF EXISTS get_risk_factor;

DELIMITER $$
CREATE PROCEDURE get_risk_factor()
BEGIN
    -- Local variables
    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 / invoices_count * 5;
    
    SELECT risk_factor;
END$$

DELIMITER ;

6 函数

函数：只能返回单一值
存储过程：可以返回拥有多行多列的结果集

👉 函数属性

  • DETERMINISTIC: 一样的输入总是会返回一样的输出
  • READS SQL DATA: 函数中有读sql数据的内容
  • MODIFIES SQL DATA：函数中有修改sql数据的内容 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP FUNCTION IF EXISTS get_risk_factor_for_client;

DELIMITER $$
CREATE FUNCTION get_risk_factor_for_client
(
    client_id INT
) 
RETURNS int
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 / invoices_count * 5;
    RETURN IFNULL(risk_factor, 0);
END$$

DELIMITER ;

使用函数：

1
2
3
4
5
SELECT
    client_id,
    name,
    get_risk_factor_for_client(client_id) AS risk_factor
FROM clients