← Каталог
Справочник по SQL — 12. Хранимые модули
Фрагмент из «Справочник по SQL»: 12. Хранимые модули.
-- ANSI-совместимый стиль (PostgreSQL)
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE SQL
AS $$
SELECT a + b;
$$;
-- PL/pgSQL (многострочный)
CREATE FUNCTION factorial(n INT)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
res BIGINT := 1;
i INT;
BEGIN
FOR i IN 1..n LOOP
res := res * i;
END LOOP;
RETURN res;
END;
$$;
-- Возвращающая таблицу (PostgreSQL)
CREATE FUNCTION get_employees_by_dept(did INT)
RETURNS TABLE(id INT, name TEXT, salary NUMERIC)
LANGUAGE sql
AS $$
SELECT id, name, salary
FROM employees
WHERE dept_id = did;
$$;
-- Использование: SELECT * FROM get_employees_by_dept(5);-- ANSI-совместимый стиль (PostgreSQL)
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE SQL
AS $$
SELECT a + b;
$$;
-- PL/pgSQL (многострочный)
CREATE FUNCTION factorial(n INT)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
res BIGINT := 1;
i INT;
BEGIN
FOR i IN 1..n LOOP
res := res * i;
END LOOP;
RETURN res;
END;
$$;
-- Возвращающая таблицу (PostgreSQL)
CREATE FUNCTION get_employees_by_dept(did INT)
RETURNS TABLE(id INT, name TEXT, salary NUMERIC)
LANGUAGE sql
AS $$
SELECT id, name, salary
FROM employees
WHERE dept_id = did;
$$;
-- Использование: SELECT * FROM get_employees_by_dept(5);-- PostgreSQL ≥11
CREATE PROCEDURE transfer_funds(from_acc INT, to_acc INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE accounts SET balance = balance - amount WHERE id = from_acc;
UPDATE accounts SET balance = balance + amount WHERE id = to_acc;
COMMIT; -- разрешено только в PROCEDURE (не в FUNCTION!)
END;
$$;
CALL transfer_funds(1, 2, 100.00);
-- T-SQL
CREATE PROCEDURE TransferFunds
@from_acc INT,
@to_acc INT,
@amount DECIMAL(18,2)
AS
BEGIN
BEGIN TRANSACTION;
UPDATE Accounts SET Balance -= @amount WHERE Id = @from_acc;
UPDATE Accounts SET Balance += @amount WHERE Id = @to_acc;
COMMIT;
END;
EXEC TransferFunds 1, 2, 100.00;-- PostgreSQL ≥11
CREATE PROCEDURE transfer_funds(from_acc INT, to_acc INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE accounts SET balance = balance - amount WHERE id = from_acc;
UPDATE accounts SET balance = balance + amount WHERE id = to_acc;
COMMIT; -- разрешено только в PROCEDURE (не в FUNCTION!)
END;
$$;
CALL transfer_funds(1, 2, 100.00);
-- T-SQL
CREATE PROCEDURE TransferFunds
@from_acc INT,
@to_acc INT,
@amount DECIMAL(18,2)
AS
BEGIN
BEGIN TRANSACTION;
UPDATE Accounts SET Balance -= @amount WHERE Id = @from_acc;
UPDATE Accounts SET Balance += @amount WHERE Id = @to_acc;
COMMIT;
END;
EXEC TransferFunds 1, 2, 100.00;