SQL PROCEDURE与函数

SQL PROCEDURE与函数

存储过程 Stored Procedure

用来存储SQL代码，在开发时使用。

PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- DELIMITER 修改分隔符
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;
END$$
DELIMITER ;

-- CALL 调用
CALL get_clients();

-- 例子
DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
    SELECT *
    FROM sql_invoicing.invoices
    WHERE (invoice_total - payment_total) > 0;
END$$
DELIMITER ;

call get_invoices_with_balance();
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          1 | 91-953-3396 |         2 |        101.79 |          0.00 | 2019-03-09   | 2019-03-29 | NULL         |
|          2 | 03-898-6735 |         5 |        175.32 |          8.18 | 2019-06-11   | 2019-07-01 | 2019-02-12   |
|          3 | 20-228-0335 |         5 |        147.99 |          0.00 | 2019-07-31   | 2019-08-20 | NULL         |
|          4 | 56-934-0748 |         3 |        152.21 |          0.00 | 2019-03-08   | 2019-03-28 | NULL         |
|          5 | 87-052-3121 |         5 |        169.36 |          0.00 | 2019-07-18   | 2019-08-07 | NULL         |
|          6 | 75-587-6626 |         1 |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 | 2019-01-03   |
|          7 | 68-093-9863 |         3 |        133.87 |          0.00 | 2019-09-04   | 2019-09-24 | NULL         |
|          8 | 78-145-1093 |         1 |        189.12 |          0.00 | 2019-05-20   | 2019-06-09 | NULL         |
|          9 | 77-593-0081 |         5 |        172.17 |          0.00 | 2019-07-09   | 2019-07-29 | NULL         |
|         10 | 48-266-1517 |         1 |        159.50 |          0.00 | 2019-06-30   | 2019-07-20 | NULL         |
|         11 | 20-848-0181 |         3 |        126.15 |          0.03 | 2019-01-07   | 2019-01-27 | 2019-01-11   |
|         13 | 41-666-1035 |         5 |        135.01 |         87.44 | 2019-06-25   | 2019-07-15 | 2019-01-26   |
|         15 | 55-105-9605 |         3 |        167.29 |         80.31 | 2019-11-25   | 2019-12-15 | 2019-01-15   |
|         16 | 10-451-8824 |         1 |        162.02 |          0.00 | 2019-03-30   | 2019-04-19 | NULL         |
|         17 | 33-615-4694 |         3 |        126.38 |         68.10 | 2019-07-30   | 2019-08-19 | 2019-01-15   |
|         18 | 52-269-9803 |         5 |        180.17 |         42.77 | 2019-05-23   | 2019-06-12 | 2019-01-08   |
|         19 | 83-559-4105 |         1 |        134.47 |          0.00 | 2019-11-23   | 2019-12-13 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+

-- 删除PROCEDURE
DROP PROCEDURE PROCEDURE名称;

参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 类似其他编程语言创建函数时的传入参数
-- 可以选定参数数据类型与长度
DROP PROCEDURE IF EXISTS get_clients_by_state;

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

-- 例子
DROP PROCEDURE IF EXISTS get_invoices_by_client;

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

CALL get_invoices_by_client(1);
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          6 | 75-587-6626 |         1 |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 | 2019-01-03   |
|          8 | 78-145-1093 |         1 |        189.12 |          0.00 | 2019-05-20   | 2019-06-09 | NULL         |
|         10 | 48-266-1517 |         1 |        159.50 |          0.00 | 2019-06-30   | 2019-07-20 | NULL         |
|         16 | 10-451-8824 |         1 |        162.02 |          0.00 | 2019-03-30   | 2019-04-19 | NULL         |
|         19 | 83-559-4105 |         1 |        134.47 |          0.00 | 2019-11-23   | 2019-12-13 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+

默认参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
DROP PROCEDURE IF EXISTS get_invoices_by_client;

DELIMITER $$
CREATE PROCEDURE get_invoices_by_client(p_client_id INT)
BEGIN
    IF p_client_id IS NULL THEN
        SET p_client_id = 1;
    END IF;

    SELECT * FROM invoices i
    WHERE i.client_id = p_client_id;
END $$
DELIMITER ;

-- 例子
DROP PROCEDURE IF EXISTS get_payments;

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

CALL get_payments(NULL, NULL);
+------------+-----------+------------+------------+--------+----------------+
| payment_id | client_id | invoice_id | date       | amount | payment_method |
+------------+-----------+------------+------------+--------+----------------+
|          1 |         5 |          2 | 2019-02-12 |   8.18 |              1 |
|          2 |         1 |          6 | 2019-01-03 |  74.55 |              1 |
|          3 |         3 |         11 | 2019-01-11 |   0.03 |              1 |
|          4 |         5 |         13 | 2019-01-26 |  87.44 |              1 |
|          5 |         3 |         15 | 2019-01-15 |  80.31 |              1 |
|          6 |         3 |         17 | 2019-01-15 |  68.10 |              1 |
|          7 |         5 |         18 | 2019-01-08 |  32.77 |              1 |
|          8 |         5 |         18 | 2019-01-08 |  10.00 |              2 |
+------------+-----------+------------+------------+--------+----------------+

CALL get_payments(1, 1);
+------------+-----------+------------+------------+--------+----------------+
| payment_id | client_id | invoice_id | date       | amount | payment_method |
+------------+-----------+------------+------------+--------+----------------+
|          2 |         1 |          6 | 2019-01-03 |  74.55 |              1 |
+------------+-----------+------------+------------+--------+----------------+

CALL get_payments(NULL, 2);
+------------+-----------+------------+------------+--------+----------------+
| payment_id | client_id | invoice_id | date       | amount | payment_method |
+------------+-----------+------------+------------+--------+----------------+
|          8 |         5 |         18 | 2019-01-08 |  10.00 |              2 |

参数验证

More SQLSTATE check: https://www.ibm.com/docs/en/i/7.5?topic=codes-listing-sqlstate-values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
DROP PROCEDURE IF EXISTS make_payment;

DELIMITER $$
CREATE PROCEDURE make_payment(
    p_client_id INT,
    p_invoice_id INT,
    p_payment_date DATE,
    p_payment_amount DECIMAL(9,2),
    p_payment_method TINYINT
)
BEGIN
    IF p_payment_amount <= 0 THEN
        SIGNAL SQLSTATE '22003' SET MESSAGE_TEXT = 'Invalid payment amount';
    END IF;

    INSERT INTO payments(
        client_id,
        invoice_id,
        date,
        amount,
        payment_method
    )
    VALUE(
        p_client_id,
        p_invoice_id,
        IFNULL(p_payment_date, CURDATE()),
        p_payment_amount,
        IFNULL(p_payment_method, 1)
    );
END$$
DELIMITER ;

CALL make_payment('1', '19', NULL, 100, NULL);
+------------+-----------+------------+------------+--------+----------------+
| payment_id | client_id | invoice_id | date       | amount | payment_method |
+------------+-----------+------------+------------+--------+----------------+
|          1 |         5 |          2 | 2019-02-12 |   8.18 |              1 |
|          2 |         1 |          6 | 2019-01-03 |  74.55 |              1 |
|          3 |         3 |         11 | 2019-01-11 |   0.03 |              1 |
|          4 |         5 |         13 | 2019-01-26 |  87.44 |              1 |
|          5 |         3 |         15 | 2019-01-15 |  80.31 |              1 |
|          6 |         3 |         17 | 2019-01-15 |  68.10 |              1 |
|          7 |         5 |         18 | 2019-01-08 |  32.77 |              1 |
|          8 |         5 |         18 | 2019-01-08 |  10.00 |              2 |
|          9 |         1 |         19 | 2023-08-27 | 100.00 |              1 |
+------------+-----------+------------+------------+--------+----------------+

CALL make_payment('1', '19', NULL, -100, NULL);
ERROR 1644 (22003): Invalid payment amount

输出参数 OUT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 用户变量 只持续该会话期间
SET @invoices_count = 0

-- 本地变量 当存储任务完成就清空
DELIMITER $$
CREATE PROCEDURE get_risk_factore ()
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 / invoices_count * 5;

    SELECT risk_factor;
END$$
DELIMITER ;

CALL get_risk_factore();
+-------------+
| risk_factor |
+-------------+
|      761.94 |
+-------------+

函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DELIMITER $$
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 / invoices_count * 5;

    RETURN IFNULL(risk_factor, 0);
END$$
DELIMITER ;

SELECT client_id, name, get_risk_factor_for_client(client_id)
FROM clients;
+-----------+-------------+---------------------------------------+
| client_id | name        | get_risk_factor_for_client(client_id) |
+-----------+-------------+---------------------------------------+
|         1 | Vinte       |                                   803 |
|         2 | Myworks     |                                   509 |
|         3 | Yadel       |                                   706 |
|         4 | Kwideo      |                                     0 |
|         5 | Topiclounge |                                   817 |
+-----------+-------------+---------------------------------------+
Linux
SQL PROCEDURE与函数
https://www.kirisamekano.com/2023/08/27/SQL7/
作者
Karol
发布于
2023年8月27日
许可协议