Процедуры и транзакции

Все темы Data Analyst

Что такое временная таблица и как она используется?

Временная таблица – это тип таблицы, которая создается и существует только на время сеанса или транзакции. Она не хранится в базе данных постоянно и удаляется автоматически.

Временные таблицы можно использовать для хранения промежуточных результатов или для разбиения сложных запросов на более простые шаги. Они особенно полезны, когда запрос требует нескольких шагов или сложных вычислений, так как помогают повысить производительность запроса и упростить его синтаксис.

Временные таблицы можно создать с помощью оператора CREATE TEMPORARY TABLE. Они могут быть созданы в памяти или на диске, в зависимости от системы базы данных и конфигурации.

Временные таблицы можно использовать как обычные таблицы в SQL-запросах и заполнять данными с помощью операторов INSERT. Их также можно объединять с другими таблицами или использовать в подзапросах.

Одним из распространенных вариантов использования временных таблиц является хранение и обработка промежуточных результатов в сложных запросах, особенно в тех, которые включают соединения или агрегирование. Например, временная таблица может использоваться для хранения результатов операции соединения, которые затем могут быть использованы для дальнейших манипуляций или соединения с другими таблицами на последующих этапах запроса

В чем разница между транзакцией и batch?

В SQL транзакция – это единая логическая единица работы, включающая один или несколько операторов SQL. Транзакция выполняется атомарно. Это значит, что все операторы внутри транзакции должны быть либо зафиксированы, либо отменены как единое целое.

Транзакции обеспечивают согласованность и целостность данных в базе, позволяя группировать несколько операций и выполнять их как единое целое. Если какое-либо из утверждений в транзакции завершается неудачей, вся транзакция отменяется, и база данных возвращается в прежнее состояние.

С другой стороны, batch (пакет) – это набор операторов SQL, которые передаются в базу данных для выполнения в виде группы. В отличие от транзакций, пакеты не обеспечивают такого же уровня атомарности или гарантии согласованности. Каждый оператор в пакете выполняется отдельно, и любые ошибки или исключения обрабатываются независимо. Пакеты обычно используются для таких задач, как загрузка данных в базу данных, запуск отчетов или выполнение рутинных задач обслуживания.

Что такое хранимая процедура и каковы её преимущества?

Хранимая процедура — это предварительно скомпилированный набор операторов SQL, который хранится в базе данных и может быть выполнен одной командой. Хранимые процедуры предназначены для упрощения сложных операций, повышения производительности и усиления безопасности, позволяя администраторам контролировать доступ к важным операциям БД.

Вот пример простой хранимой процедуры, которая извлекает данные из базы:

CREATE PROCEDURE get_customers
AS
BEGIN
SELECT * FROM customers;
END

Эта хранимая процедура извлекает все строки из таблицы “customers” и возвращает их вызывающей стороне. Процедура может быть выполнена одной командой:

EXEC get_customers;

Что такое транзакции и зачем они нужны?

Транзакция объединяет одну или несколько операций в атомарное целое: её изменения фиксируются вместе либо отменяются. Частичный откат к savepoint возможен внутри ещё не завершённой транзакции.

Транзакции важны для управления БД, поскольку они обеспечивают целостность и непротиворечивость данных. Объединяя связанные операции в транзакции, база данных может гарантировать, что либо все операции будут завершены успешно, либо ни одна из них. Это часто называют “ACID” свойством транзакции:

1. Атомарность. Транзакция рассматривается как единая, неделимая единица работы, и либо все её операции завершаются успешно, либо ни одна из них.

2. Согласованность. Транзакция переводит базу данных из одного согласованного состояния в другое. При этом любые ограничения, такие как первичный или внешний ключи, сохраняются.

3. Изоляция. Транзакции выполняются независимо друг от друга, и их последствия не видны другим транзакциям до тех пор, пока они не завершатся.

4. Долговечность. Как только транзакция зафиксирована, её изменения становятся постоянными и способными пережить любые последующие сбои, отключения питания или другие прерывания.

Пример транзакции SQL:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;
COMMIT TRANSACTION;

В этой транзакции выполняются два оператора для перевода 100 единиц валюты со счета 123 на счет 456. Оператор BEGIN TRANSACTION начинает транзакцию, а оператор COMMIT TRANSACTION завершает её. Если во время транзакции произойдет ошибка, например сбой питания или нарушение ограничений, то база данных автоматически откатит транзакцию и отменит все внесенные изменения.

Что такое функция и чем она отличается от хранимой процедуры?

Функция SQL – это заранее определенный фрагмент кода, который выполняет конкретную задачу, например, возвращает значение или манипулирует данными. Функции могут принимать входные параметры, выполнять вычисления и возвращать значения, основанные на входных параметрах и других факторах. Их можно использовать в запросах, а также комбинировать с другими функциями для создания более сложных выражений.

Функции обычно используются для решения следующих задач:

• Выполнение вычислений, например, сложение или вычитание значений

• Работа с текстом, например, преобразование строк в верхний или нижний регистр

• Возврат определенных типов данных, например, даты или времени

• Выполнение логических операций, например, проверка того, является ли значение нулевым

Вот несколько примеров часто используемых функций SQL:

• COUNT: возвращает количество строк, соответствующих заданному условию в таблице

• AVG: вычисляет среднее значение из набора числовых значений

• MAX: возвращает наибольшее значение из набора значений

• MIN: возвращает наименьшее значение из набора значений

Хотя и функции, и хранимые процедуры предоставляют возможность инкапсулировать код SQL для повторного использования, у них есть несколько ключевых различий:

• Возвращаемое значение. Функции всегда возвращают значение, в то время как хранимые процедуры могут этого не делать.

• Параметры функций и процедур зависят от СУБД. Например, PostgreSQL допускает OUT и INOUT параметры у функций.

• Выполнение. SQL-функции выполняются как часть оператора, в то время как хранимые процедуры выполняются как независимые единицы кода.

• Сложность. Функции обычно проще и направлены на выполнение конкретных задач, в то время как хранимые процедуры могут быть более сложными и включать в себя несколько операторов SQL и логику программирования.

Что такое триггер и как он используется?

Триггеры используются для автоматического запуска серии операторов в ответ на определенные события. Эти события могут включать такие действия, как вставка, обновление или удаление таблицы.

По сути это тип хранимой процедуры, которая выполняется автоматически в ответ на определенные события или изменения в базе данных. События, вызывающие триггер, могут включать операции вставки, обновления или удаления таблицы.

Триггеры обычно используются для соблюдения бизнес-правил, поддержания ссылочной целостности или регистрации изменений в БД.

Как работает SQL-триггер? Когда происходит событие, СУБД автоматически вызывает триггер, который затем выполняет набор операторов SQL, определенных в триггере.

Триггеры определяются для каждой таблицы и создаются с помощью оператора CREATE TRIGGER. Они могут запускаться до или после наступления события и выполняться один раз для каждой затронутой строки либо один раз для каждого оператора.