Добавить строку в таблицу sql oracle
Обновлено: 21.11.2024
В этом учебном пособии по Oracle объясняется, как использовать инструкцию Oracle INSERT с синтаксисом и примерами. Мы также добавили несколько практических упражнений, которые вы можете попробовать сами.
Описание
Инструкция Oracle INSERT используется для вставки одной или нескольких записей в таблицу Oracle.
Синтаксис
Синтаксис оператора Oracle INSERT при вставке одной записи с использованием ключевого слова VALUES:
Или синтаксис инструкции Oracle INSERT при вставке нескольких записей с помощью инструкции SELECT:
Параметры или аргументы
table Таблица, в которую вставляются записи. столбец1, столбец2, . column_n Столбцы в таблице для вставки значений. выражение1, выражение2, . выражение_n Значения для присвоения столбцам в таблице. Таким образом, столбцу1 будет присвоено значение выражения1, столбцу2 будет присвоено значение выражения2 и т. д. . source_table Исходная таблица при вставке данных из другой таблицы. ГДЕ условия Необязательный. Условия, которые должны быть выполнены для вставки записей.
- При вставке записей в таблицу с помощью инструкции Oracle INSERT необходимо указать значение для каждого столбца NOT NULL.
- Вы можете опустить столбец в операторе Oracle INSERT, если столбец допускает значения NULL.
Пример. Использование ключевого слова VALUES
Самый простой способ создать запрос Oracle INSERT для вывода списка значений с использованием ключевого слова VALUES.
Этот оператор Oracle INSERT приведет к вставке одной записи в таблицу suppliers. Эта новая запись будет иметь supplier_id 5000 и supplier_name «Apple».
Пример — использование оператора SELECT
Вы также можете создавать более сложные операторы Oracle INSERT, используя операторы SELECT.
Помещая оператор SELECT в оператор INSERT, вы можете быстро выполнять множественные вставки.
С этим типом вставки вы можете проверить количество вставляемых строк. Вы можете определить количество строк, которые будут вставлены, выполнив следующую инструкцию Oracle SELECT перед выполнением вставки.
Часто задаваемые вопросы
Вопрос: я настраиваю базу данных с клиентами. Я знаю, что вы используете оператор Oracle INSERT для вставки информации в базу данных, но как мне убедиться, что я снова не ввожу ту же информацию о клиенте?
Ответ: Вы можете убедиться, что не вставляете повторяющуюся информацию, используя условие EXISTS.
Например, если у вас есть таблица с именем clients с первичным ключом client_id, вы можете использовать следующую инструкцию Oracle INSERT:
Этот оператор Oracle INSERT вставляет несколько записей с подвыборкой.
Если вы хотите вставить одну запись, вы можете использовать следующую инструкцию Oracle INSERT:
Использование двойной таблицы позволяет вам вводить свои значения в операторе select, даже если значения в настоящее время не хранятся в таблице.
Вопрос. Как вставить несколько строк явных данных в одну команду INSERT в Oracle?
Ответ. Ниже приведен пример того, как можно вставить 3 строки в таблицу suppliers в Oracle с помощью инструкции Oracle INSERT:
На основе таблицы contacts вставьте запись контакта, у которой contact_id – 1000, last_name – Smith, first_name – Jane, а адрес – 10 Где-то ул.:
Следующий оператор Oracle INSERT вставит эту запись в таблицу employees:
На основе таблиц contacts и customers вставьте в таблицу contacts всех клиентов, которые находятся в штат Флорида.
Следующий оператор Oracle INSERT вставит эту запись в таблицу suppliers:
Поскольку количество полей в таблицах contacts и customers одинаковое, и поля перечислены в том же порядке, вы можете написать решение следующим образом (хотя обычно рекомендуется перечислять имена столбцов на случай изменения определений таблиц):
Инструкция INSERT добавляет одну или несколько новых строк данных в таблицу базы данных. Полное описание оператора INSERT см. в Справочнике Oracle Database SQL.
Синтаксис
Описание ключевого слова и параметра
Другое (обычно короткое) имя таблицы или представления, на которые указывает ссылка.
имя_столбца[, имя_столбца].
Список столбцов в таблице или представлении базы данных. Столбцы могут быть перечислены в любом порядке, если выражения в предложении VALUES перечислены в том же порядке. Каждое имя столбца может быть указано только один раз. Если список не включает все столбцы таблицы, для каждого отсутствующего столбца устанавливается значение NULL или значение по умолчанию, указанное в операторе CREATE TABLE.
return_clause
Возвращает значения из вставленных строк, устраняя необходимость последующего ВЫБОРА строк. Вы можете получить значения столбца в переменные или в коллекции. Вы не можете использовать предложение RETURNING для удаленных или параллельных вставок. Если оператор не влияет ни на какие строки, значения переменных, указанных в предложении RETURNING, не определены. Синтаксис return_clause см. в разделе «Операция DELETE».
sql_expression
Любое выражение, допустимое в SQL. Например, это может быть литерал, переменная PL/SQL или запрос SQL, который возвращает одно значение. Для получения дополнительной информации см. Справочник по Oracle Database SQL. PL/SQL также позволяет использовать здесь переменную записи.
подзапрос
Инструкция SELECT, предоставляющая набор строк для обработки. Его синтаксис похож на синтаксис select_into_statement без предложения INTO. См. «Выражение SELECT INTO».
подзапрос3
Инструкция SELECT, возвращающая набор строк. Каждая строка, возвращаемая оператором select, вставляется в таблицу. Подзапрос должен возвращать значение для каждого столбца в списке столбцов или для каждого столбца в таблице, если списка столбцов нет.
table_reference
Таблица или представление, которые должны быть доступны при выполнении инструкции INSERT и для которых у вас должны быть права INSERT. Синтаксис table_reference см. в разделе «Операция DELETE».
ТАБЛИЦА (подзапрос2)
Операндом TABLE является оператор SELECT, возвращающий значение одного столбца, представляющее вложенную таблицу. Этот оператор указывает, что значение является коллекцией, а не скалярным значением.
ЗНАЧЕНИЯ (. )
Присваивает значения выражений соответствующим столбцам в списке столбцов. Если списка столбцов нет, первое значение вставляется в первый столбец, определенный оператором CREATE TABLE, второе значение вставляется во второй столбец и так далее. Для каждого столбца в списке столбцов должно быть одно значение. Типы данных вставляемых значений должны быть совместимы с типами данных соответствующих столбцов в списке столбцов.
Примечания по использованию
Литералы символов и дат в списке VALUES должны быть заключены в одинарные кавычки ('). Числовые литералы не заключаются в кавычки.
Неявный SQL курсора и атрибуты курсора %NOTFOUND , %FOUND , %ROWCOUNT и %ISOPEN позволяют получить доступ к полезной информации о выполнении оператора INSERT.
В этом разделе приведены примеры того, как добавлять строки таблицы с помощью операторов SQL для базы данных Oracle NoSQL INSERT и UPSERT.
Вы используете оператор INSERT для вставки или обновления одной строки в существующей таблице.
Если вы выполнили сценарий SQLBasicExamples, вы уже должны были создать таблицу с именем Users . У таблицы было такое определение:
Чтобы вставить новую строку в таблицу Users, используйте оператор INSERT следующим образом. Поскольку вы добавляете значения во все столбцы таблицы, вам не нужно явно указывать имена столбцов:
Чтобы вставить данные в некоторые, но не во все столбцы таблицы, явно укажите имена столбцов в операторе INSERT. Любым столбцам, которые вы не укажете, присваивается значение NULL или значение по умолчанию, указанное при создании таблицы:
Использование оператора UPSERT
Слово UPSERT сочетает в себе UPDATE и INSERT , описывая функцию оператора. Используйте оператор UPSERT, чтобы вставить строку там, где ее нет, или обновить строку новыми значениями, когда она существует.
Например, если вы уже вставили новую строку, как описано в предыдущем разделе, при выполнении следующего оператора возраст пользователя Ивана изменится на 27, а доход — на 60 000. Если вы не выполнили предыдущую инструкцию INSERT, инструкция UPSERT вставит новую строку с идентификатором пользователя 10 в таблицу Users.
Использование столбца IDENTITY
Вы можете использовать столбцы IDENTITY для автоматического создания значений для столбца таблицы каждый раз, когда вы вставляете новую строку таблицы. См. раздел «Столбец идентификаторов» в Справочном руководстве по SQL.
Вот несколько примеров использования инструкций INSERT для обеих разновидностей столбца IDENTITY:
ВСЕГДА СОЗДАЕТСЯ КАК ИДЕНТИЧНОСТЬ
ГЕНЕРИРУЕТСЯ ПО УМОЛЧАНИЮ [ON NULL] КАК ИДЕНТИЧНОСТЬ
Создайте таблицу с именем Employee_test, используя один столбец DeptId, который ВСЕГДА СОЗДАЕТСЯ КАК ИДЕНТИФИКАЦИОННАЯ ИНФОРМАЦИЯ. Этот столбец IDENTITY не является первичным ключом. Вставьте несколько строк в таблицу.
Инструкция INSERT вставляет следующие строки, при этом система генерирует значения 1, 2 и 3 для столбца IDENTITY DeptId .
Вставка – это форма оператора DML (языка манипулирования данными). Вы используете его для хранения значений в таблице.
В этом руководстве показано, как работает вставка с использованием этих двух таблиц:
Вставка одной строки
Вы можете добавлять в таблицу по одной строке с помощью предложения вставки. Это принимает форму:
Список столбцов является необязательным. Если вы опустите это, вы должны указать значение для каждого видимого столбца в таблице.
Например, следующий код добавляет в таблицу игрушек строку для игрушки с именем Miss Snuggles, окрашенной в розовый цвет с идентификатором toy_id 1:
Если количество значений не соответствует количеству и типам данных целевых столбцов, вы получите сообщение об ошибке. Вставка ниже предоставляет только два значения. Но в таблице три столбца. Поэтому он выдает исключение:
Это делает вставки без списка столбцов хрупкими. Если вы измените столбцы в таблице, вставка, скорее всего, не удастся. Гораздо лучше перечислить столбцы, для которых вы предоставляете значения. Например:
Это все равно будет работать, если кто-то добавит еще один столбец или удалит столбец цвета из игрушек. Во всех столбцах таблицы, для которых вы не укажете значение, будет храниться нуль.
Попробуйте!
Выполните следующий запрос, чтобы вставить строку в таблицу игрушек с идентификатором toy_id 3 и красным цветом:
Запрос должен вернуть следующую строку:
Если вы допустили ошибку и вам нужно сбросить таблицу, снова нажмите «Выполнить обязательный SQL»
Многострочная вставка
Вы можете добавить много строк в одну инструкцию. Для этого вы вставляете результат запроса.
Например, вставка ниже получает все идентификаторы toy_id из таблицы toys. Затем вставляет их в таблицу кирпичей:
Как и при вставке одной строки, количество столбцов в запросе и целевой таблице должно совпадать. Типы данных столбцов в каждой позиции также должны быть совместимы.
Таблица игрушек состоит из трех столбцов. Но запрос возвращает только один столбец. Таким образом, следующий оператор завершится ошибкой:
Поэтому, как и в случае с автономными запросами, рекомендуется указывать столбцы в предложении select.
Производительность: однострочная и многострочная
Как правило, команды SQL лучше объединять в как можно меньшее количество операторов. Это обеспечит наилучшую производительность.
Например, ниже сравнивается производительность однорядных и многорядных вставок. Оба добавляют 50 000 строк. Однорядный добавляет одну строку 50 000 раз. Многострочный добавляет 50 000 строк за один раз. Многострочная версия будет в 10–100 раз быстрее!
Поэтому используйте многострочные вставки там, где это возможно!
Попробуйте!
Запустите эти вставки:
Затем выполните этот оператор вставки, чтобы добавить строки для идентификаторов игрушек 4 и 5 в кубики. Он должен хранить значения toy_id в Brick_id. И цвета игрушек кирпичного цвета.
Запрос должен возвращать следующие строки:
Сохранение изменений DML
Чтобы сохранить изменения в базе данных, необходимо совершить фиксацию. Поэтому, чтобы сохранить вставленную строку, зафиксируйте ее впоследствии:
Ряд виден другим пользователям только после фиксации. До этого только ваш сеанс (подключение к базе данных) может видеть новые данные.
Многие инструменты имеют свойство автофиксации. Это сделает коммит за вас. Либо после каждого обращения к базе данных. Или когда сеанс заканчивается.
LiveSQL фиксируется после каждого нажатия кнопки "Выполнить".
Убедитесь, что вы проверили, как это установлено. Если автофиксация включена, а вы думаете, что это не так (или наоборот), это может привести к сохранению нежелательных изменений!
Как это сделать, зависит от ваших инструментов и языка программирования. Обратитесь к их документации, чтобы узнать, как установить свойство autocommit.
Отмена DML
Между вставкой строки и ее фиксацией ваш код может вызвать исключение. Таким образом, вы можете отменить изменение. Сделать это можно с помощью отката. Откат отменяет все изменения с момента последней фиксации.
Допустим, вы добавили строку для Pink Rabbit. Но поймите, что вы сделали ошибку, поэтому хотите удалить ее. Это делается следующим образом:
При выполнении отката база данных отменяет все изменения, внесенные с момента последней фиксации. Если вы совершаете коммит между вставкой и откатом, откат ничего не делает. И вам нужно запустить удаление, чтобы удалить строку.
Примечание. Убедитесь, что рабочий лист SQL содержит все операторы вставки и отката, когда вы нажимаете кнопку "Выполнить". Каждый раз, когда вы нажимаете кнопку «Выполнить», Live SQL выполняет неявную фиксацию в конце ваших операторов, поэтому, если вы запускаете каждый оператор по одному, вы не сможете выполнить откат.
Например, следующий код фиксирует изменение перед откатом, поэтому строка для Pink Rabbit остается в таблице:
Нажатие одной кнопки в приложении может привести к запуску множества операторов DML. Вы должны отложить фиксацию до тех пор, пока не обработаете их все. Это позволяет вам откатить изменения, если на полпути произойдет ошибка. Это гарантирует, что все остальные пользователи приложения либо увидят все изменения, либо не увидят ни одного из них.
Точки сохранения
Ваш код часто будет добавлять строки во многие таблицы одним действием. Если на полпути произошла ошибка, вы можете отменить некоторые (но не все) изменения с момента последней фиксации.
Чтобы помочь в этом, вы можете создавать точки сохранения. Это контрольно-пропускные пункты. Вы можете отменить все изменения, сделанные после него. И сохраните сделанные заранее.
Для этого сначала создайте контрольную точку с помощью команды savepoint. Дайте ему имя, чтобы обращаться к нему позже:
Примечание: exec является обязательным требованием для LiveSQL.Вам не нужен этот префикс в других средах.
Чтобы отменить изменения после точки сохранения, добавьте пункт «в точку сохранения» после отката. Укажите имя точки сохранения, к которой вы хотите вернуться. Эта отмена удалит изменения, которые вы сделали после этого:
Обратите внимание, что точки сохранения НЕ фиксируются! Если вы выполните безоговорочный откат, вы все равно отмените все изменения с момента последней фиксации. Даже те, которые были сделаны до точки сохранения.
Например, код ниже:
- Добавляет строку для toy_id 8
- Создает точку сохранения after_six
- Затем вставляет идентификатор игрушки 9
Откат к точке сохранения удаляет только строку для toy_id 9. Окончательный откат в конце также удаляет toy_id 8:
Вы можете создать много точек сохранения за один вызов. И откат к любому из них. Если вы откатитесь к точке сохранения, это уничтожит все, что сделано после того, которое вы назвали. Но вы все равно можете вернуться к предыдущим пунктам.
Например, в следующем примере создаются три точки сохранения. Но первый откат отменяет изменения после second_sp. Итак, Third_sp потерян, и вы больше не можете вернуться к нему:
Попробуйте!
Выполните следующий код, разместив фиксации, точки сохранения и откаты в разделах /* TODO */, чтобы:
- Зафиксировать вставку toy_id 8
- Добавить строки для идентификатора toy_id 9 и 10, но затем удалить строку для 10.
- Затем отмените вставку для toy_id 9
Первый запрос должен возвращать следующие строки:
И вторая эта строка:
Если вы допустили ошибку и вам нужно сбросить таблицу, снова нажмите «Выполнить обязательный SQL»
Вставка нескольких таблиц
Вы также можете вставлять строки во многие таблицы одним оператором. Это многотабличная вставка. Общий формат:
Как и при многострочной вставке, источником этих значений должен быть запрос. Он добавляет строку во все перечисленные вами таблицы. Вы даже можете вставлять в одну и ту же таблицу более одного раза!
В приведенном ниже запросе используется двойная таблица для выбора значения 0. Затем дважды вставьте его в кирпичи и один раз в игрушки:
Dual — это специальная однострочная таблица в базе данных Oracle. Вы можете использовать это для выбора значений или функций, не хранящихся в реальной таблице.
Условная вставка нескольких таблиц
При выполнении вставки в несколько таблиц может потребоваться выбрать, в какие таблицы добавлять строки во время выполнения. Вы можете сделать это, добавив предложение when перед «в таблицу». База данных добавит строки в эту таблицу только в том случае, если условие истинно.
Условные вставки в несколько таблиц бывают двух видов: все и сначала.
Когда вы указываете все, база данных оценивает каждое условие для каждой строки, возвращаемой запросом. Каждая строка входит в каждую таблицу, где условие истинно.
Вы также можете добавить предложение else. Это вставит строки, которые не соответствуют никаким другим условиям.
Первый
Insert сначала оценивает условия сверху вниз. Если строка соответствует многим условиям, она добавляет строку только к наивысшему истинному. Он пропускает все оставшиеся тесты.
Как и в случае с вставкой всего, вы можете использовать предложение else для перехвата строк, которые не соответствуют другим условиям.
Вы можете увидеть разницу между all и first в приведенном ниже коде. Строка для toy_id 11 имеет имя Cuteasaurus и синий цвет. Таким образом, он соответствует обоим этим тестам:
Поэтому вставьте все, что добавляет две копии этой строки в кирпичи. Но вставка сначала останавливает обработку, когда идентифицирует цвет как синий. Таким образом, он вставляет только одну копию:
В обоих утверждениях строка для toy_id 13 не соответствует критериям. Таким образом, он попадает в блок else.
В этом учебном пособии по SQL объясняется, как использовать оператор SQL INSERT с синтаксисом, примерами и практическими упражнениями.
Описание
Выражение SQL INSERT используется для вставки одной или нескольких записей в таблицу. Существует 2 синтаксиса для оператора INSERT в зависимости от того, вставляете ли вы одну запись или несколько записей.
Синтаксис
Синтаксис оператора INSERT при вставке одной записи в SQL:
Или синтаксис оператора INSERT при вставке нескольких записей в SQL:
Параметры или аргументы
table Таблица, в которую нужно вставить записи. столбец1, столбец2 Это столбцы в таблице для вставки значений. выражение1, выражение2 Это значения, которые нужно присвоить столбцам в таблице. Таким образом, столбцу1 будет присвоено значение выражения1, столбцу2 будет присвоено значение выражения2 и т. д. . source_tables Используется при вставке записей из другой таблицы. Это исходная таблица при выполнении вставки. ГДЕ условия Необязательный. Используется при вставке записей из другой таблицы. Это условия, которые должны быть выполнены для вставки записей.
- При вставке записей в таблицу с помощью инструкции SQL INSERT необходимо указать значение для каждого столбца NOT NULL. Вы можете опустить столбец из оператора INSERT только в том случае, если столбец допускает значения NULL.
DDL/DML для примеров
Если вы хотите следовать этому руководству, получите DDL для создания таблиц и DML для заполнения данных. Тогда попробуйте примеры в своей собственной базе данных!
Пример — использование инструкции INSERT для вставки одной записи
Самый простой способ использования инструкции INSERT — вставить одну запись в таблицу с помощью ключевого слова VALUES. Давайте рассмотрим пример того, как это сделать в SQL.
В этом примере у нас есть таблица с именем categories со следующими данными:
| category_id | category_name |
|---|---|
| 25 | Гастрономы |
| 50 | Производство |
| 75 | Пекарня |
| 100 | Общие товары |
| 125 | Технологии |
Давайте вставим новую запись категории. Введите следующую инструкцию SQL:
Будет вставлена 1 запись. Снова выберите данные из таблицы categories:
Вот результаты, которые вы должны увидеть:
| category_id | category_name |
|---|---|
| 25 | Гастрономы |
| 50 | Производство |
| 75 | Пекарня |
| 100 | Общие товары |
| 125 | Технологии | 150 | Разное |
В этом примере будет вставлена одна запись в таблицу categories. Эта новая запись будет иметь category_id 150 и category_name «Разное».
В этом примере, поскольку вы предоставляете значения для всех столбцов в таблице categories, вы можете опустить имена столбцов и вместо этого написать оператор INSERT следующим образом:
Однако это опасно делать по двум причинам. Во-первых, инструкция INSERT вызовет ошибку, если в таблицу categories будут добавлены дополнительные столбцы. Во-вторых, данные будут вставлены не в те столбцы, если порядок столбцов в таблице изменится. Поэтому, как правило, имена столбцов лучше указывать в операторе INSERT.
Пример — использование оператора INSERT для вставки нескольких записей
Помещая оператор SELECT в оператор INSERT, вы можете быстро выполнять множественные вставки. Давайте рассмотрим пример того, как это сделать.
В этом примере у нас есть таблица employees со следующими данными:
| employee_number | last_name | first_name | salary | dept_id |
|---|---|---|---|---|
| 1001 | Смит | Джон | 62000 | 500 |
| 1002 | Андерсон | Джейн | 57500 | 500 |
| 1003 | Эверест | Брэд | 71000 | 501 |
| 1004 | Хорват | Джек | 42000 | 501 |