SQL-Ex blog

Отметим, что по умолчанию Oracle неявно начинает транзакцию после каждого оператора. По этой причине для управления транзакциями вы должны использовать операторы COMMIT или ROLLBACK после того, как вы попытаетесь внести изменения в содержимое таблицы (транзакция начинается, даже если никакие данные не изменяются, как в случае наличия предложения WHERE, которое не возвращает FALSE ни для одной строки. Подробнее о транзакциях в Oracle смотрите в документации).

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

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


• ROLLBACK: Этот оператор используется для отмены изменений, сделанных во время транзакции. При выполнении оператора ROLLBACK все изменения, совершенные во время транзакции, отменяются, и база данных восстанавливается к своему предыдущему состоянию. После выполнения оператора ROLLBACK начинается новая транзакция.

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

Вот пример таблицы "Employee": вы можете использовать следующий скрипт SQL для создания таблицы "Employee". Мы будем использовать эту таблицу для изучения команд вставки, обновления и удаления.

Замечание. В следующем операторе CREATE TABLE мы используем ограничение NOT NULL в Oracle для столбцов Name, LastName и Dob, которое гарантирует, что столбец не может содержать NULL-значения. Эти ограничения обычно встраиваются в определение столбца оператора CREATE TABLE. Когда вы определяете один или более столбцов в качестве первичного ключа таблицы, ограничение NOT NULL включается автоматически, что запрещает вставку значений NULL в такой столбец. Если вы попытаетесь вставить NULL-значение в столбец первичного ключа или в столбец, для которого задано ограничение NOT NULL, Oracle выдаст ошибку.

Кроме того, ограничение Oracle SQL устанавливает значение по умолчанию, которое автоматически присваивается столбцу, когда вставляется новая строка, и значение для этого столбца не задано. Значение по умолчанию задается при создании таблицы с помощью ключевого слова DEFAULT. Мы определили значение DEFAULT 'unknown' для столбца "Department".

Скрипт SQL для создания таблицы "Employee".

CREATE TABLE Employee (

    EmployeeId INT PRIMARY KEY,

    Name VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    FirstName VARCHAR(50) NOT NULL,

    Department VARCHAR(50) DEFAULT 'Unknown',

    Dob DATE NOT NULL,

    Salary DECIMAL(10, 2) NOT NULL 

);

Оператор INSERT

В этом разделе я рассмотрю типичные случаи использования оператора INSERT. Оператор INSERT в Oracle очень похож на аналогичный оператор на других платформах РСУБД, но есть также несколько довольно полезных отличий.

Базовый синтаксис оператора INSERT

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

Базовая команда INSERT используется для добавления новых данных в таблицу Oracle. Команда имеет следующий синтаксис:

INSERT INTO table_name (column1, column2, ..., columnN)

VALUES (value1, value2, ..., valueN);

Здесь table_name - имя таблицы, куда вы хотите вставить данные, column1 ... columnN - имена столбцов, в которые вы хотите вставить данные. За ключевым словом VALUES следует список разделяемых запятой значений, которые соответствуют столбцам, указанным ранее.

В этом разделе мы рассмотрим следующие способы написания операторов INSERT.

• Однострочный оператор INSERT.


• Операторы INSERT ALL.


• Оператор INSERT из SELECT.

Однострочный оператор INSERT

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

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName,

      Department, Dob, Salary)

VALUES ('1000', 'Allen Cox', 'Cox', 'Allen', 'IT', 

      TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000);

COMMIT;

Этот оператор добавит новую строку в таблицу "Employee" с заданными значениями.

Теперь давайте посмотрим, что произойдет, если мы не укажем столбец "Department" в нашем операторе INSERT, поскольку мы уже определили значение по умолчанию для столбца "Department" в операторе CREATE TABLE.

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName,

      Dob, Salary)

VALUES ('1001', 'Allen Cox', 'Cox', 'Allen',  

     TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000); 

COMMIT;

Вы можете заметить, что столбец "Department" имеет значение по умолчанию 'Unknown', которое автоматически присваивается, поскольку мы опустили этот столбец в операторе INSERT.

Вывод:


Замечание. Указывать столбцы в операторе INSERT не обязательно. Поэтому допустимо следующее:

INSERT INTO Employee 

VALUES ('1001', 'Allen Cox', 'Cox', 'Allen',  

     TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000); 

COMMIT;

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

Вставка нескольких строк одновременно

Здесь мы рассмотрим несколько способов, с помощью которых вы сможете вставить больше одной строки за раз.

Множественные пакеты

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

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 

                      Department, Dob, Salary)

VALUES ('1002', 'Dave Carry', 'Carry',	'Dave',	'Sales', 

       TO_DATE('01-Jan-1992', 'DD-MM-YYYY'),70000);

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 

                      Department, Dob, Salary)

VALUES ('1003', 'Amit Singh','Singh',	'Amit',	'Sales', 

       TO_DATE('20-Oct-1991', 'DD-MM-YYYY'),50000); 

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 

                      Department, Dob, Salary) 

VALUES ('1004', 'Rhonda Grant', 'Grant',	'Rhonda', 

       'Marketing', 

       TO_DATE('15-May-1980', 'DD-MM-YYYY'),60000);

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 

                      Department, Dob, Salary) 

VALUES ('1005', 'Marvin Cox', 'Cox',	'Marvin', 'Marketing', 

       TO_DATE('15-Jan-1985', 'DD-MM-YYYY'),62000);

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 

                      Department, Dob, Salary) 

VALUES ('1006', 'Dave Grant', 'Grant',	'Dave', 'Sales', 

        TO_DATE('05-Oct-1980', 'DD-MM-YYYY'),90000);

 

COMMIT;

Повторное выполнение оператора SELECT с использованием таблицы "Employee" теперь даст такой результат:



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

Оператор INSERT ALL

Оператор INSERT ALL в Oracle позволяет вам вставлять несколько строк в одну или более таблиц, используя один оператор. Это полезно, когда вам нужно вставить данные в несколько таблиц на основе единственного источника данных.

Синтаксис:

INSERT ALL

    INTO table1 (column1, column2, ...) 

    VALUES (value1, value2, ...)

 

    INTO table2 (column1, column2, ...) 

    VALUES (value1, value2, ...)

 

    INTO table3 (column1, column2, ...) 

    VALUES (value1, value2, ...)

 

подзапрос;

В оригинальном операторе требуется, чтобы каждое выражение значения value1, value2 или value3 относились к столбцу, возвращаемому в списке select подзапроса. Важно отметить, что имена столбцов и значения должны совпадать в каждом предложении INTO и VALUES. Кроме того, число столбцов и их типы данных должны быть совместимыми по предложению INTO и соответствующему предложению VALUES.

Однако при использовании литеральных значений вместо значений, возвращаемых подзапросом, вы можете использовать следующий подзапрос:

SELECT * FROM dual;

Вот пример:

Предположим, что у нас имеется две таблицы "Employee" и "Address". Таблица "Employee" аналогична той, которую мы создали в начале статьи. Таблица "Address" имеет следующее определение:

CREATE TABLE Address (

   EmployeeId NUMBER PRIMARY KEY,

   Address_Line1 VARCHAR2(100),

   Address_Line2 VARCHAR2(100),

   City VARCHAR2(50),

   State VARCHAR2(50),

   Country VARCHAR2(50)

);

Допустим мы хотим вставить данные в обе таблицы с помощью одного оператора INSERT ALL. Мы можем выполнить это следующим образом:

INSERT ALL

  INTO Employee (EmployeeId, Name, LastName, FirstName,

              Department, Dob, Salary)

  VALUES ('1007', 'Ram Singh', 'Singh', 'Ram', 'IT', 

          TO_DATE('08-July-1982', 'DD-MM-YYYY'),75000) 

  INTO Address (EmployeeId, Address_Line1, City, 

                State, Country) 

  VALUES ('1007', '123 Main St', 'Anytown', 'CA', 'USA') 

  SELECT * FROM dual; 

COMMIT;

В этом примере мы вставляем данные в две таблицы, "Employee" и "Address". Оператор SELECT * FROM dual в конце требуется, поскольку оператор INSERT ALL требует наличие подзапроса (в этом случае оператор SELECT) в конце.

Первое предложение INTO указывает, что мы хотим вставить данные в таблицу "Employee", а второе - в таблицу "Address". Предложения VALUES после INTO указывают данные, которые мы хотим вставить.

После выполнения этого оператора таблица "Employee" будет содержать следующую строку:



А таблица "Address" будет содержать следующую строку:

Пример подзапроса в INSERT ALL

Пусть у нас есть две таблицы "Name" и "Salary", и мы хотим вставить записи в обе таблицы с помощью одного оператора INSERT ALL. Мы будем вставлять зарплату и имя сотрудников из таблицы "Employee".

Вы можете создать таблицы "Name" и "Salary", используя следующий код:

CREATE TABLE Name (

  EmployeeId NUMBER PRIMARY KEY,

  LastName VARCHAR(50) NOT NULL,

  FirstName VARCHAR(50) NOT NULL,

  Dob DATE NOT NULL

);

 

CREATE TABLE Salary (

  EmployeeId NUMBER PRIMARY KEY,

  Salary DECIMAL (10, 2) NOT NULL 

); 

Теперь напишем оператор INSERT для вставки данных.

INSERT ALL

    INTO Name   (EmployeeId, LastName , FirstName , Dob)

         VALUES (EmployeeId, LastName , FirstName , Dob) 

    INTO Salary (EmployeeId , Salary)

         VALUES (EmployeeId , Salary) 

SELECT EmployeeId, LastName , FirstName , Dob, Salary

FROM Employee ;

После выполнения этого оператора INSERT ALL данные в таблице "Name" выглядят так:



Вывод таблицы "Salary":

Оператор INSERT из SELECT

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

Синтаксис оператора INSERT INTO SELECT:

INSERT INTO table_name (column1, column2, ..., columnN) 

SELECT column1, column2, ..., columnN 

FROM исходная_таблица 

WHERE условие;

Как и в случае использования однострочного оператора INSERT, table_name - это имя таблицы, куда вы хотите вставить данные, а column1 ... columnN - имена столбцов в этой таблице.

За ключевым словом SELECT следует список столбцов из таблицы-источника, имя которой следует за ключевым словом FROM. Ключевое слово WHERE не является обязательным и позволяет вам отфильтровать данные данные до их вставки в новую таблицу.

Например, следующий оператор SQL скопирует все строки из таблицы "Employee", у которых значением столбца "Department" является "Sales", в новую таблицу с именем "Sales_Employee".

Используйте следующий код SQL для создания таблицы "Sales_Employee".

CREATE TABLE Sales_Employee (

    EmployeeId  varchar(15) Primary key,

    Name varchar(15),

    Dob DATE,

    Salary NUMBER(8,0)

);

Используйте следующий код SQL для вставки данных в таблицу "Sales_Employee".

INSERT INTO Sales_Employee(EmployeeId, Name, Dob, Salary) 

SELECT EmployeeId, Name, Dob, Salary 

FROM Employee

WHERE Department = 'Sales';

 

COMMIT;

Приведенный выше оператор SQL поместит три записи в таблицу "Sales_Employee". Вывод оператора SELECT из таблицы "Sales_Employee" выглядит подобным образом:

Вставка данных без явного использования списка столбцов

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

В предыдущем примере мы предоставляли значения для всех столбцов таблицы. Этот способ позволяет вам выбирать только те столбцы, для которых вы хотите создать данные (допуская NULL и/или значения по умолчанию для других столбцов). Если вы не включаете все столбцы, то должны быть уверены, что значения следуют в том же порядке, в котором перечислены столбцы в объявлении таблицы.

В этом случае синтаксис INSERT INTO должен быть следующим:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

 

COMMIT;

Итак, в качестве примера мы можем написать следующий оператор для добавления строк из "Employee" в таблицу "Sales_Employee":

INSERT INTO Sales_Employee 

SELECT EmployeeId, Name, Dob, Salary From Employee

WHERE Department = 'Sales';

 

COMMIT;

Это допустимый способ написания оператора INSERT, но хорошей практикой является указание списка столбцов при вставке данных в таблицу Oracle. Указывая список столбцов, вы явно указываете столбцы, в которые будут вставляться данные, а также их порядок.

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

Имеется несколько причин, объясняющих, почему указание списка столбцов является хорошей практикой:

• Ясность: Указывая список столбцов, вы делаете понятным, какие столбцы заполняются данными. Это делает ваш код более читабельным, и его легче понять.


• Обслуживание: Указание списка столбцов облегчает обслуживание кода в долгосрочной перспективе. Если вам или другому разработчику потребуется впоследствие модифицировать код или структуры данных, будет легче разобраться, если столбцы перечислены явно.


• Производительность: В некоторых случаях отсутствие списка столбцов может ухудшить производительность. Если вы не указываете список столбцов, Oracle придется выполнить дополнительную работу для определения того, в какие столбцы следует вставлять данные. Это может замедлить вставку в несколько раз, особенно для больших таблиц.

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

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

Оператор UPDATE

Оператор UPDATE используется в Oracle SQL для модификации данных в таблице. Это позволяет вам менять значения одного или нескольких столбцов таблицы на основе заданных условий.

Синтаксис UPDATE:

Базовый синтаксис оператора UPDATE имеет вид:

UPDATE table_name 

SET column1 = scalar_expression1, 

    column2 = scalar_expression2,

     ..., 

    columnN = scalar_expression2 

WHERE condition;

Здесь

• table_name - имя таблицы, которую вы хотите обновить.


• column_name - имя столбца, который вы хотите обновить.


• scalar_expression - выражение, которое вычисляет новое значение для столбца. Это может быть константа, вызов функции, ссылка на столбец, арифметическое выражение или строковое выражение.


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

Примеры:

Предположим, что вы хотите обновить зарплату сотрудника с "EmployeeId" 1002 до 80000.

До обновления данные в таблице "Employee" выглядят следующим образом:



Вы можете использовать соедующий оператор UPDATE.

UPDATE Employee 

SET Salary = 80000 

WHERE EmployeeId = 1002;

 

COMMIT;

После выполнения оператора UPDATE данные в таблице "Employee" будут выглядеть следующим образом. Как видно, значение в столбце Salary обновилось до 80000.



В качестве примера обновления нескольких столбцов давайте отредактируем значения столбцов "Salary", “Department" и “LastName", используя один оператор UPDATE. До выполнения оператора UPDATE данные в таблице "Employee" выглядят так:

UPDATE Employee 

SET Salary = 70000, 

    Department='Sales',

    LastName='Brown' 

WHERE EmployeeId = 1002;

 

COMMIT;

После выполнения оператора UPDATE вы можете заметить, что значения столбцов "Salary", “Department" и “LastName" для "EmployeeId" 1002 стали такими:



Теперь давайте использовать арифметическое выражение для обновления "Salary" в таблице "Employee".

Перед выполнением оператора UPDATE данные в таблице "Employee" выглядят так:



Теперь выполним следующий оператор UPDATE:

UPDATE Employee

SET Salary = Salary * 1.1

WHERE department = 'Sales';

 

COMMIT;

Мы используем арифметическое выражение для обновления столбца зарплаты для всех сотрудников в отделении Sales. Предложение SET устанавливает для столбца "Salary" текущее значение умноженное на 1,1, что соответствует увеличению зарплаты на 10%.

Обновление с использованием подзапроса

Следующий оператор обновит столбец "Salary" для всех строк в таблице "Employee", у которых столбец "Department" равен 'Sales', на среднюю зарплату сотрудников в отделении Sales.

UPDATE Employee

SET Salary = (

  SELECT AVG(Salary)

  FROM Employees

  WHERE Department = 'Sales'

)

WHERE Department = 'Sales';

 

COMMIT;

После выполнения оператора UPDATE вы заметите следующее изменение в столбце Salary таблицы “Employee”.

Советы по использованию оператора UPDATE

1. Уделяйте внимание предложению WHERE: Используйте предложение WHERE для указания строк, которые вы хотите обновить. Это улучшает производительность и предотвращает случайные обновления ошибочных строк. Если вы забудете предложение WHERE, то рискуте обновить все строки в таблице.


2. Создавайте резервные копии ваших данных: Будьте осторожны при использовании оператора UPDATE в производственной среде. Всегда проверяйте, что у вас имеется резервная копия ваших данных перед любыми операциями модификации. Это сможет помочь вам восстановить данные в случае ошибочных действий.


3. Используйте подзапросы с осторожностью: Подзапросы могут стать мощным инструментом при работе с оператором UPDATE, т.к. они позволяют обновлять данные на основании результатов другого запроса. Однако подзапросы могут также замедлить выполнение и интенсивно использовать ресурсы, особенно если они используются для обновления большого числа строк. Используйте подзапросы только в случае необходимости.


4. Сначала тестируйте ваши операторы UPDATE: Перед выполнением оператора UPDATE на производственной базе данных важно протестировать его на небольшом наборе данных, чтобы гарантировать корректную работу. Это поможет вам выявить синтаксические ошибки или другие проблемы, прежде чем вы внесете изменения в производственные данные.

Оператор DELETE

Оператор DELETE используется для удаления одной или более строк из таблицы.

Базовый синтаксис DELETE

DELETE FROM table_name 

WHERE condition;

Здесь table_name - имя таблицы, из которой вы хотите удалить строки, а condition - это выражение, которое принимает значение истина или ложь для каждой строки в таблице. Если для строки условие истинно, строка будет удалена.

Например, следующий оператор SQL должен удалить все строки из таблицы "Employee", для которых столбец "Department” содержит ‘Sales’.

DELETE FROM Employee 

WHERE Department = 'Sales';

 

COMMIT;

Если вы хотите удалить все строки из таблицы, то можете опустить предложение WHERE:

DELETE FROM table_name;

Этот оператор удалит все строки из указанной таблицы.

Например следующий оператор SQL должен удалить все строки из таблицы "Employee".

DELETE FROM Employee;

COMMIT;

Удаление данных при использовании подзапроса

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

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

DELETE FROM table_name1 

WHERE column_name IN (SELECT column_name 

                      FROM table_name2 

                      WHERE condition);

Здесь table_name1 - имя целевой таблицы, а table_name2 - таблицы источника. Подзапрос в круглых скобках используется для выборки строк на удаление из целевой таблицы на основе заданного условия (condition).

Например, следующий оператор SQL удалит всех сотрудников из таблицы "Employee", чья зарплата больше 60000 в таблице "Sales_Employee".

DELETE FROM Employee 

WHERE Employeeid IN (SELECT Employeeid 

                     FROM Sales_Employee 

                     WHERE SALARY > 60000);

 

COMMIT;

Советы по использованию оператора DELETE

1. Не забывайте о предложении WHERE, которое задает условие для удаления строк. Если опустить предложение WHERE, вы рискуете удалить все строки в таблице.


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


3. Используйте оператор TRUNCATE вместо DELETE, когда вам нужно удалить все строки из таблицы. TRUNCATE значительно быстрее по сравнению с DELETE. (Заметим, что TRUNCATE имеет отличные от DELETE ограничения безопасности. Более подробно об этом смотрите в документации Oracle.)

Ссылки по теме

1. Малоизвестные факты о явных транзакциях
2. Что такое ad hoc запрос?
3. Оператор TRUNCATE TABLE