Understanding Logic, Reusability and Integrity On SQL ; Procedures, Functions and Transactions.

Source: Dev.to

SQL is widely known for data querying and manipulation, but systems grow: data becomes larger, processes become repetitive, and operations become sensitive. SQL includes features that let it be considered a fully‑fledged programming language. This article discusses procedures, functions, and transactions—each serving a distinct purpose.

Stored Procedures

A stored procedure is a set of SQL statements stored in the database and executed as a unit. It is typically used for tasks such as UPDATE, INSERT, DELETE, etc., and is invoked by calling it with the required parameters.

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

Functions encapsulate reusable logic that returns a value and can be used inside queries for data selection. They can appear in SELECT, WHERE, and other clauses.

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

Transactions group a set of operations so that if any part fails, the entire execution is rolled back. This guarantees data integrity by preventing partial updates.

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

Below is a simple scenario that combines a function, a procedure, and a transaction.

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 offers capabilities far beyond simple querying. Features such as stored procedures, functions, and transactions make it a powerful tool for managing and analyzing data directly within the database. Mastering these constructs greatly enhances data integrity, reusability, and overall efficiency.