理解SQL中的逻辑、可重用性和完整性；存储过程、函数和事务

Source: Dev.to

SQL 因数据查询和操作而广为人知，但系统会随着规模扩大而增长：数据量变大、过程变得重复、操作变得敏感。SQL 包含的特性使其可以被视为一门完整的编程语言。本文讨论存储过程、函数和事务——它们各自承担不同的职责。

Stored Procedures

存储过程是一组存放在数据库中的 SQL 语句，作为一个整体执行。它通常用于 UPDATE、INSERT、DELETE 等任务，并通过传入所需参数来调用。

Example procedure

CREATE OR REPLACE PROCEDURE increase_salary(p_dept TEXT, p_percent NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary + (salary * p_percent / 100)
    WHERE department = p_dept;
END;
$$;

Calling the procedure

CALL increase_salary('IT', 10);

Functions

函数封装可复用的逻辑，返回一个值，并且可以在查询中用于数据选择。它们可以出现在 SELECT、WHERE 以及其他子句中。

Example function

CREATE OR REPLACE FUNCTION get_avg_salary(p_dept TEXT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN (
        SELECT AVG(salary)
        FROM employees
        WHERE department = p_dept
    );
END;
$$;

Using the function

SELECT name, salary
FROM employees
WHERE salary > get_avg_salary(department);

Transactions

事务将一组操作组合在一起，如果其中任何一步失败，整个执行会回滚。这样可以通过防止部分更新来保证数据完整性。

Typical transaction

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

Rollback on failure

ROLLBACK;

How They Work Together

下面是一个简单的场景，结合了函数、过程和事务。

Function: get_balance

CREATE OR REPLACE FUNCTION get_balance(acc_id INT)
RETURNS NUMERIC AS $$
BEGIN
    RETURN (SELECT balance FROM accounts WHERE id = acc_id);
END;
$$ LANGUAGE plpgsql;

Procedure: transfer_money

CREATE OR REPLACE PROCEDURE transfer_money(from_id INT, to_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    IF get_balance(from_id) < amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;

Transaction that calls the procedure

BEGIN;
CALL transfer_money(1, 2, 100);
COMMIT;

Conclusion

SQL 提供的功能远超简单查询。存储过程、函数和事务等特性使其成为在数据库内部直接管理和分析数据的强大工具。掌握这些构造能够显著提升数据完整性、可复用性以及整体效率。