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은 단순 조회를 넘어서는 다양한 기능을 제공합니다. 저장 프로시저, 함수, 트랜잭션과 같은 기능을 활용하면 데이터베이스 내에서 직접 데이터를 관리하고 분석하는 강력한 도구가 됩니다. 이러한 구조를 숙달하면 데이터 무결성, 재사용성 및 전체적인 효율성을 크게 향상시킬 수 있습니다.