Хранимые процедуры

-- Создание процедуры
CREATE OR REPLACE PROCEDURE transfer_money(
    p_from_account INT,
    p_to_account INT,
    p_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_balance DECIMAL;
BEGIN
    -- Проверка остатка на счете отправителя
    SELECT balance INTO v_balance 
    FROM accounts 
    WHERE id = p_from_account 
    FOR UPDATE;
    
    IF v_balance < p_amount THEN
        RAISE EXCEPTION 'Недостаточно средств';
    END IF;
    
    -- Списание и зачисление
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
    
    -- Запись в журнал
    INSERT INTO transactions (from_account, to_account, amount, created_at)
    VALUES (p_from_account, p_to_account, p_amount, NOW());
    
    COMMIT;
END;
$$;

-- Вызов процедуры
CALL transfer_money(1, 2, 100.00);

-- Создание процедуры
CREATE PROCEDURE transfer_money
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @balance DECIMAL(10,2);
    
    BEGIN TRANSACTION;
    
    -- Проверка остатка
    SELECT @balance = balance 
    FROM accounts WITH (UPDLOCK) 
    WHERE id = @from_account;
    
    IF @balance < @amount
    BEGIN
        ROLLBACK;
        RAISERROR('Недостаточно средств', 16, 1);
        RETURN;
    END;
    
    -- Списание и зачисление
    UPDATE accounts SET balance = balance - @amount WHERE id = @from_account;
    UPDATE accounts SET balance = balance + @amount WHERE id = @to_account;
    
    -- Запись в журнал
    INSERT INTO transactions (from_account, to_account, amount, created_at)
    VALUES (@from_account, @to_account, @amount, GETDATE());
    
    COMMIT TRANSACTION;
END;

-- Вызов процедуры
EXEC transfer_money @from_account = 1, @to_account = 2, @amount = 100.00;

-- Создание процедуры
DELIMITER //

CREATE PROCEDURE transfer_money(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10,2)
)
BEGIN
    DECLARE v_balance DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Проверка остатка
    SELECT balance INTO v_balance 
    FROM accounts 
    WHERE id = p_from_account 
    FOR UPDATE;
    
    IF v_balance < p_amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Недостаточно средств';
    END IF;
    
    -- Списание и зачисление
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
    
    -- Запись в журнал
    INSERT INTO transactions (from_account, to_account, amount, created_at)
    VALUES (p_from_account, p_to_account, p_amount, NOW());
    
    COMMIT;
END//

DELIMITER ;

-- Вызов процедуры
CALL transfer_money(1, 2, 100.00);

-- Oracle различает процедуры и функции
CREATE OR REPLACE PROCEDURE transfer_money(
    p_from_account IN accounts.id%TYPE,
    p_to_account IN accounts.id%TYPE,
    p_amount IN accounts.balance%TYPE
) AS
    v_balance accounts.balance%TYPE;
BEGIN
    -- Проверка остатка (с блокировкой строки)
    SELECT balance INTO v_balance 
    FROM accounts 
    WHERE id = p_from_account 
    FOR UPDATE;
    
    IF v_balance < p_amount THEN
        RAISE_APPLICATION_ERROR(-20001, 'Недостаточно средств');
    END IF;
    
    -- Списание и зачисление
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
    
    -- Запись в журнал
    INSERT INTO transactions (from_account, to_account, amount, created_at)
    VALUES (p_from_account, p_to_account, p_amount, SYSDATE);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END transfer_money;
/

-- Вызов процедуры (в анонимном блоке)
BEGIN
    transfer_money(1, 2, 100.00);
END;
/

CREATE OR REPLACE PROCEDURE get_user_stats(
    p_user_id INT,
    OUT p_order_count INT,
    OUT p_total_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), SUM(amount)
    INTO p_order_count, p_total_amount
    FROM orders
    WHERE user_id = p_user_id;
END;
$$;

-- Вызов с OUT-параметрами
CALL get_user_stats(1, NULL, NULL);  -- NULL на месте OUT-параметров

CREATE PROCEDURE add_tax
    @amount DECIMAL(10,2) INOUT,
    @tax_rate DECIMAL(5,2)
AS
BEGIN
    SET @amount = @amount * (1 + @tax_rate / 100);
END;

-- Использование
DECLARE @price DECIMAL(10,2) = 100.00;
EXEC add_tax @amount = @price INOUT, @tax_rate = 20;
SELECT @price;  -- 120.00

-- PostgreSQL
CREATE OR REPLACE PROCEDURE search_products(
    p_category VARCHAR DEFAULT NULL,
    p_min_price DECIMAL DEFAULT 0,
    p_max_price DECIMAL DEFAULT 999999
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- тело процедуры
END;
$$;

-- Вызов с использованием значений по умолчанию
CALL search_products();  -- все параметры по умолчанию
CALL search_products(p_category => 'books');  -- только категория

-- PostgreSQL
DECLARE
    v_counter INT := 0;
    v_name VARCHAR(100);
    v_balance DECIMAL DEFAULT 0.00;
    v_is_active BOOLEAN;

-- PostgreSQL
IF v_balance < 0 THEN
    RAISE EXCEPTION 'Отрицательный баланс';
ELSIF v_balance = 0 THEN
    v_status := 'empty';
ELSE
    v_status := 'positive';
END IF;

-- SQL Server (CASE выражение и IF)
IF @balance < 0
BEGIN
    RAISERROR('Отрицательный баланс', 16, 1);
    RETURN;
END
ELSE IF @balance = 0
    SET @status = 'empty';
ELSE
    SET @status = 'positive';

-- MySQL
IF v_balance < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Отрицательный баланс';
ELSEIF v_balance = 0 THEN
    SET v_status = 'empty';
ELSE
    SET v_status = 'positive';
END IF;

-- CASE в любых СУБД (для вычисления значения)
v_status := CASE 
    WHEN v_balance < 0 THEN 'negative'
    WHEN v_balance = 0 THEN 'empty'
    ELSE 'positive'
END;

-- PostgreSQL (LOOP с выходом)
LOOP
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 10;
    -- тело цикла
END LOOP;

-- PostgreSQL (WHILE)
WHILE v_counter <= 10 LOOP
    -- тело цикла
    v_counter := v_counter + 1;
END LOOP;

-- PostgreSQL (FOR по диапазону)
FOR i IN 1..10 LOOP
    -- тело цикла
END LOOP;

-- PostgreSQL (FOR по результату запроса)
FOR rec IN SELECT id, name FROM users LOOP
    RAISE NOTICE 'User: %, %', rec.id, rec.name;
END LOOP;

-- SQL Server
WHILE @counter <= 10
BEGIN
    -- тело цикла
    SET @counter = @counter + 1;
END;

-- MySQL
WHILE v_counter <= 10 DO
    -- тело цикла
    SET v_counter = v_counter + 1;
END WHILE;

CREATE OR REPLACE PROCEDURE safe_transfer(
    p_from INT, p_to INT, p_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN  -- вложенный блок для обработки ошибок
        -- основная логика
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
        
    EXCEPTION
        WHEN check_violation THEN
            RAISE NOTICE 'Ошибка проверки ограничения';
            ROLLBACK;
        WHEN foreign_key_violation THEN
            RAISE NOTICE 'Неверный идентификатор счета';
            ROLLBACK;
        WHEN OTHERS THEN
            RAISE NOTICE 'Неизвестная ошибка: %', SQLERRM;
            ROLLBACK;
    END;
END;
$$;

CREATE PROCEDURE safe_transfer
    @from INT, @to INT, @amount DECIMAL
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        
        UPDATE accounts SET balance = balance - @amount WHERE id = @from;
        UPDATE accounts SET balance = balance + @amount WHERE id = @to;
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        
        -- Логирование ошибки
        INSERT INTO error_log (error_message, error_number, error_line)
        VALUES (
            ERROR_MESSAGE(),
            ERROR_NUMBER(),
            ERROR_LINE()
        );
        
        -- Перебросить ошибку выше
        THROW;
    END CATCH
END;

CREATE PROCEDURE safe_transfer(
    p_from INT, p_to INT, p_amount DECIMAL
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        -- Можно логировать ошибку
        RESIGNAL;
    END;
    
    START TRANSACTION;
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
    COMMIT;
END;

-- PostgreSQL: процедура выполняется с правами владельца
ALTER PROCEDURE transfer_money OWNER TO trusted_role;
GRANT EXECUTE ON PROCEDURE transfer_money TO public;

-- Пользователи могут вызвать процедуру, но не имеют доступа к таблицам accounts

-- Безопасно: параметризованный запрос
CREATE PROCEDURE get_user(p_user_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT * FROM users WHERE id = p_user_id;
END;
$$;

-- Опасно: динамический SQL без экранирования
CREATE PROCEDURE search_users(p_name VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE 'SELECT * FROM users WHERE name = ''' || p_name || '''';
    -- Если p_name = "'; DROP TABLE users; --" — катастрофа
END;
$$;

-- PostgreSQL
RAISE NOTICE 'Значение переменной: %', v_variable;
RAISE DEBUG 'Отладочная информация';

-- SQL Server
PRINT 'Значение переменной: ' + CAST(@variable AS VARCHAR);

-- MySQL (использование переменных сессии)
SET @debug_message = CONCAT('Значение: ', v_variable);

-- PostgreSQL (с расширением pg_stat_statements)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE query LIKE '%transfer_money%'
ORDER BY mean_time DESC;

-- SQL Server (DMV)
SELECT 
    OBJECT_NAME(object_id) AS procedure_name,
    execution_count,
    total_worker_time / execution_count AS avg_cpu_time
FROM sys.dm_exec_procedure_stats
ORDER BY avg_cpu_time DESC;

-- Плохо: COMMIT внутри цикла
CREATE PROCEDURE process_orders()
AS $$
BEGIN
    FOR rec IN SELECT * FROM orders WHERE status = 'pending' LOOP
        UPDATE orders SET status = 'processed' WHERE id = rec.id;
        COMMIT;  -- Не делайте так!
    END LOOP;
END;
$$;

-- Плохо: имя параметра совпадает с именем столбца
CREATE PROCEDURE update_user(id INT, name VARCHAR)
AS $$
BEGIN
    UPDATE users SET name = name WHERE id = id;  -- name = name? id = id?
END;
$$;