SQL для начинающих в Big Data (ч. 2) 

1. Работа с транзакциями

Глава 1.1. Транзакции и их свойства (ACID)

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

Транзакции и их свойства (ACID)

Транзакция в базе данных представляет собой набор операций, которые должны быть выполнены как единое целое. Рассмотрим основные свойства транзакций, обычно обозначаемые аббревиатурой ACID:

1. Атомарность (Atomicity):
   Транзакция должна быть выполнена целиком или не выполнена вовсе. Не может быть ситуации, когда часть транзакции выполнена, а часть нет.
2. Согласованность (Consistency):
   Транзакция должна приводить базу данных из одного согласованного состояния в другое согласованное состояние. Например, если транзакция переводит деньги с одного счета на другой, сумма денег должна остаться неизменной.
3. Изолированность (Isolation):
   Другие транзакции не должны видеть промежуточные результаты выполнения текущей транзакции до ее завершения. Это гарантирует, что транзакции не мешают друг другу.
4. Долгосрочная стойкость (Durability):
   После успешного завершения транзакции ее изменения должны остаться в базе данных и не должны быть потеряны даже при сбое системы.

Давай посмотрим простой пример транзакции в контексте банковских операций:

Пример: Перевод денег между счетами

Представим, что у нас есть два счета: счет отправителя и счет получателя. Мы хотим перевести 100 единиц с счета отправителя на счет получателя.
Вот как может выглядеть транзакция:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'отправитель';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'получатель';
COMMIT TRANSACTION;

В данном примере:

• Мы начинаем транзакцию с BEGIN TRANSACTION.
• Затем уменьшаем баланс счета отправителя и увеличиваем баланс счета получателя на 100.
• После успешного выполнения обеих операций, мы завершаем транзакцию с COMMIT TRANSACTION, обеспечивая атомарность и долгосрочную стойкость транзакции.

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

1.2. Начало, фиксация и откат транзакций

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

Начало, фиксация и откат транзакций

1. Начало транзакции (BEGIN TRANSACTION):
   Для начала новой транзакции используется оператор BEGIN TRANSACTION. Этот оператор указывает, что последующие SQL-операции будут частью одной транзакции.
2. Фиксация транзакции (COMMIT TRANSACTION):
   Когда все операции транзакции успешно выполнены и данные находятся в состоянии, которое удовлетворяет бизнес-логику, мы фиксируем транзакцию с помощью оператора COMMIT TRANSACTION. Это сохраняет все изменения в базе данных.
3. Откат транзакции (ROLLBACK TRANSACTION):
   Если в процессе выполнения транзакции возникают проблемы или ошибки, мы можем откатить все изменения, сделанные в рамках транзакции, до начального состояния с помощью оператора ROLLBACK TRANSACTION.

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

BEGIN TRANSACTION;
INSERT INTO users (name, age) VALUES ('John Doe', 30);
INSERT INTO user_details (user_id, address) VALUES (LAST_INSERT_ID(), '123 Main St');
COMMIT TRANSACTION;

В данном примере:

• Мы начинаем транзакцию с BEGIN TRANSACTION.
• Затем добавляем запись о пользователе в таблицу users и его детали в таблицу user_details.
• Если все операции прошли успешно, мы фиксируем транзакцию с COMMIT TRANSACTION.

Тем не менее, если что-то пошло не так (например, возникла ошибка при вставке данных), мы можем откатить транзакцию и вернуть базу данных в исходное состояние:

ROLLBACK TRANSACTION;

Это позволит избежать сохранения некорректных или неполных данных в базе данных и обеспечит целостность информации.

2. Хранимые процедуры и функции

2.1. Создание и вызов хранимых процедур

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

Создание хранимой процедуры

Для создания хранимой процедуры используется оператор CREATE PROCEDURE. Процедура может принимать параметры и содержать блок SQL-кода, который определяет ее функциональность.

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

CREATE PROCEDURE SayHello()
BEGIN
    SELECT 'Hello, World!' AS Greeting;
END;

В данном примере:

• SayHello - это имя хранимой процедуры.
• BEGIN и END - определяют начало и конец блока кода процедуры.
• SELECT 'Hello, World!' AS Greeting - это SQL-запрос, который будет выполнен при вызове процедуры.

Вызов хранимой процедуры

Чтобы вызвать хранимую процедуру, используется оператор CALL. Пример вызова хранимой процедуры SayHello:

CALL SayHello();

Этот вызов выполнит код внутри хранимой процедуры SayHello и вернет строку "Hello, World!".

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

2.2. Параметры и возвращаемые значения в хранимых процедурах

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

Параметры в хранимых процедурах

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

CREATE PROCEDURE GetUser(IN userId INT, OUT userName VARCHAR(255))
BEGIN
    SELECT name INTO userName
    FROM users
    WHERE id = userId;
END;

В данном примере:

• GetUser - это имя хранимой процедуры.
• IN userId INT - это входной параметр, который ожидает целочисленное значение.
• OUT userName VARCHAR(255) - это выходной параметр, который будет содержать имя пользователя.

Вызов хранимой процедуры с параметрами

Чтобы вызвать хранимую процедуру с параметрами, мы передаем значения для входных параметров и переменные для выходных параметров.

Пример вызова хранимой процедуры GetUser:

CALL GetUser(123, @userName);

В результате выполнения этого вызова, переменная @userName будет содержать имя пользователя с идентификатором 123.

Возвращаемые значения из хранимых процедур

Хранимые процедуры также могут возвращать значения. Для этого используется ключевое слово RETURN.

Пример хранимой процедуры, возвращающей значение:

CREATE PROCEDURE GetTotalUsers()
BEGIN
    SELECT COUNT(*) AS TotalUsers
    FROM users;
END;

В данном примере:

• GetTotalUsers - это имя хранимой процедуры.
• SELECT COUNT(*) AS TotalUsers FROM users - это SQL-запрос, который возвращает количество пользователей в таблице users.

Вызывая эту процедуру, вы можете получить результат, используя конструкцию SELECT или присвоив его переменной:

CALL GetTotalUsers();

или

SELECT GetTotalUsers() AS TotalUsers;

Эти возможности делают хранимые процедуры мощным инструментом для обработки данных в базах данных.

2.3. Хранимые функции в SQL

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

Создание хранимой функции

Для создания хранимой функции используется оператор CREATE FUNCTION. Давайте рассмотрим пример создания простой хранимой функции, которая возвращает сумму двух чисел:

CREATE FUNCTION AddNumbers(a INT, b INT) RETURNS INT
BEGIN
    DECLARE result INT;
    SET result = a + b;
    RETURN result;
END;

В данном примере:

• AddNumbers - это имя хранимой функции.
• a INT и b INT - это параметры функции.
• RETURNS INT - это указание на тип возвращаемого значения.

Вызов хранимой функции

Для вызова хранимой функции используется оператор SELECT. Пример вызова хранимой функции AddNumbers:

SELECT AddNumbers(10, 20) AS SumResult;

В данном примере вызывается функция AddNumbers с параметрами 10 и 20, и она вернет сумму этих чисел.

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