Формат команды insert в oracle sql
Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT ; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора следующий:
Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE :
Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:
Если задать список столбцов, то можно изменить «естественный» порядок их следования:
Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:
Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два — NULL, а последний столбец — type — PC). Теперь мы могли бы написать:
В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию — PC. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL , запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL .
Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT :
Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать:
Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (см. синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде
Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK , ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:
Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:
Здесь, также как и ранее, можно указывать не все столбцы, если требуется использовать имеющиеся значения по умолчанию, например:
В данном случае в столбец type таблицы Product_D будет подставлено значение по умолчанию PC для всех вставляемых строк.
Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN !). Другими словами, если бы столбец type в таблице Product допускал бы NULL -значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Product_D.
Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании конструктора строки в предложении VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL . Так если нам требуется вставить несколько строк при помощи одного оператора INSERT , можно написать:
Использование UNION ALL предпочтительней UNION даже, если гарантировано отсутствие строк-дубликатов, так как в этом случае не будет выполняться проверка для исключения дубликатов.
Следует отметить, что вставка нескольких кортежей с помощью конструктора строк уже реализована в Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server 2008. С учетом этой возможности, последний запрос можно переписать в виде:
Заметим, что MySQL допускает еще одну нестандартную синтаксическую конструкцию, выполняющую вставку строки в таблицу в стиле оператора UPDATE:
Рассмотренный в начале параграфа пример с помощью этого оператора можно переписать так:
В этом учебном материале вы узнаете, как использовать оператор INSERT в SQL с синтаксисом и примерами.
Описание
SQL оператор INSERT используется для вставки одной или нескольких записей в таблицу. Существует два синтаксиса для оператора INSERT в зависимости от того, вставляете ли вы одну запись или несколько записей.
Синтаксис
Синтаксис SQL оператора INSERT при вставке одной записи в таблицу:
Или синтаксис SQL оператора INSERT при вставке нескольких записей в таблицу:
INSERT INTO table
(column1, column2, . )
SELECT expression1, expression2, .
FROM source_tables
[WHERE conditions];
Параметры или аргументы
table Таблица, в которую нужно вставить записи. column1 , column2 Это столбцы в table для вставки значений. expression1 , expression2 Эти значения присваиваются столбцам в таблице. Поэтому column1 будет присвоено значение expression1 , column2 будет присвоено значение expression2 и т.д. , source_tables Используется при вставке записей из другой таблицы. Это исходная таблица при выполнении вставки. WHERE conditions Необязательный. Используется при вставке записей из другой таблицы. Это те условия, которые должны быть соблюдены для вставки записей.
Примечание
- При вставке записей в таблицу с помощью SQL оператора INSERT, необходимо указать значение для каждого столбца NOT NULL. Вы можете опустить столбец из оператора INSERT, только если столбец допускает значения NULL.
Пример - использование оператора INSERT для вставки одной записи
Самый простой способ использовать оператор INSERT - вставить одну запись в таблицу с помощью ключевого слова VALUES. Давайте рассмотрим пример того, как сделать это в SQL.
В этом примере у нас есть таблица с categories со следующими данными:
category_id | category_name |
---|---|
25 | Deli |
50 | Produce |
75 | Bakery |
100 | General Merchandise |
125 | Technology |
Давайте вставим новую запись в category . Введите следующий SQL оператор:
Use the INSERT statement to add rows to a table, the base table of a view, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or the base table of an object view.
For you to insert rows into a table, the table must be in your own schema or you must have the INSERT object privilege on the table.
For you to insert rows into the base table of a view, the owner of the schema containing the view must have the INSERT object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the INSERT object privilege on the view.
If you have the INSERT ANY TABLE system privilege, then you can also insert rows into any table or the base table of any view.
You must also have the READ or SELECT object privilege on the table into which you want to insert rows if the table is on a remote database.
Conventional and Direct-Path INSERT
You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT . When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path INSERT , the database appends the inserted data after existing data in the table. Data is written directly into data files, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that has been created in parallel mode, direct-path INSERT is the default.
The manner in which the database generates redo and undo data depends in part on whether you are using conventional or direct-path INSERT :
Conventional INSERT always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force logging settings of the database.
Direct-path INSERT generates both redo and undo for metadata changes, because these are needed for operation recovery. For data changes, undo and redo are generated as follows:
Direct-path INSERT always bypasses undo generation for data changes.
If the database is not in ARCHIVELOG or FORCE LOGGING mode, then no redo is generated for data changes, regardless of the logging setting of the table.
If the database is in ARCHIVELOG mode (but not in FORCE LOGGING mode), then direct-path INSERT generates data redo for LOGGING tables but not for NOLOGGING tables.
If the database is in ARCHIVELOG and FORCE LOGGING mode, then direct-path SQL generate data redo for both LOGGING and NOLOGGING tables.
Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:
You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.
If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.
The target table cannot be of a cluster.
The target table cannot contain object type columns.
Direct-path INSERT is not supported for an index-organized table (IOT) if it has a mapping table, or if it is reference by a materialized view.
Direct-path INSERT into a single partition of an index-organized table (IOT), into a partitioned IOT with only one partition, or into an IOT that is not partitioned, will be done serially, even if the IOT was created in parallel mode or you specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.
The target table cannot have any triggers or referential integrity constraints defined on it.
The target table cannot be replicated.
A transaction containing a direct-path INSERT statement cannot be or become distributed.
You cannot query or modify direct-path inserted data immediately after the insert is complete. If you attempt to do so, an ORA-12838 error is generated. You must first issue a COMMIT statement before attempting to read or modify the newly-inserted data.
Oracle Database Administrator's Guide for a more complete description of direct-path INSERT
Oracle Database Utilities for information on SQL*Loader
Oracle Database SQL Tuning Guide for information on statistics gathering when inserting into an empty table using direct-path INSERT
Use the INSERT statement to add rows to a table, the base table of a view, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or the base table of an object view.
For you to insert rows into a table, the table must be in your own schema or you must have the INSERT object privilege on the table.
For you to insert rows into the base table of a view, the owner of the schema containing the view must have the INSERT object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the INSERT object privilege on the view.
If you have the INSERT ANY TABLE system privilege, then you can also insert rows into any table or the base table of any view.
You must also have the READ or SELECT object privilege on the table into which you want to insert rows if the table is on a remote database.
Conventional and Direct-Path INSERT
You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT . When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path INSERT , the database appends the inserted data after existing data in the table. Data is written directly into data files, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that has been created in parallel mode, direct-path INSERT is the default.
The manner in which the database generates redo and undo data depends in part on whether you are using conventional or direct-path INSERT :
Conventional INSERT always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force logging settings of the database.
Direct-path INSERT generates both redo and undo for metadata changes, because these are needed for operation recovery. For data changes, undo and redo are generated as follows:
Direct-path INSERT always bypasses undo generation for data changes.
If the database is not in ARCHIVELOG or FORCE LOGGING mode, then no redo is generated for data changes, regardless of the logging setting of the table.
If the database is in ARCHIVELOG mode (but not in FORCE LOGGING mode), then direct-path INSERT generates data redo for LOGGING tables but not for NOLOGGING tables.
If the database is in ARCHIVELOG and FORCE LOGGING mode, then direct-path SQL generate data redo for both LOGGING and NOLOGGING tables.
Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:
You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.
If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.
The target table cannot be of a cluster.
The target table cannot contain object type columns.
Direct-path INSERT is not supported for an index-organized table (IOT) if it has a mapping table, or if it is reference by a materialized view.
Direct-path INSERT into a single partition of an index-organized table (IOT), into a partitioned IOT with only one partition, or into an IOT that is not partitioned, will be done serially, even if the IOT was created in parallel mode or you specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.
The target table cannot have any triggers or referential integrity constraints defined on it.
The target table cannot be replicated.
A transaction containing a direct-path INSERT statement cannot be or become distributed.
You cannot query or modify direct-path inserted data immediately after the insert is complete. If you attempt to do so, an ORA-12838 error is generated. You must first issue a COMMIT statement before attempting to read or modify the newly-inserted data.
Oracle Database Administrator's Guide for a more complete description of direct-path INSERT
Oracle Database Utilities for information on SQL*Loader
Oracle Database SQL Tuning Guide for information on statistics gathering when inserting into an empty table using direct-path INSERT
Use the INSERT statement to add rows to a table, the base table of a view, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or the base table of an object view.
For you to insert rows into a table, the table must be in your own schema or you must have the INSERT object privilege on the table.
For you to insert rows into the base table of a view, the owner of the schema containing the view must have the INSERT object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the INSERT object privilege on the view.
If you have the INSERT ANY TABLE system privilege, then you can also insert rows into any table or the base table of any view.
You must also have the READ or SELECT object privilege on the table into which you want to insert rows if the table is on a remote database.
Conventional and Direct-Path INSERT
You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT . When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path INSERT , the database appends the inserted data after existing data in the table. Data is written directly into data files, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that has been created in parallel mode, direct-path INSERT is the default.
The manner in which the database generates redo and undo data depends in part on whether you are using conventional or direct-path INSERT :
Conventional INSERT always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force logging settings of the database.
Direct-path INSERT generates both redo and undo for metadata changes, because these are needed for operation recovery. For data changes, undo and redo are generated as follows:
Direct-path INSERT always bypasses undo generation for data changes.
If the database is not in ARCHIVELOG or FORCE LOGGING mode, then no redo is generated for data changes, regardless of the logging setting of the table.
If the database is in ARCHIVELOG mode (but not in FORCE LOGGING mode), then direct-path INSERT generates data redo for LOGGING tables but not for NOLOGGING tables.
If the database is in ARCHIVELOG and FORCE LOGGING mode, then direct-path SQL generate data redo for both LOGGING and NOLOGGING tables.
Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:
You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.
If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.
The target table cannot be of a cluster.
The target table cannot contain object type columns.
Direct-path INSERT is not supported for an index-organized table (IOT) if it has a mapping table, or if it is reference by a materialized view.
Direct-path INSERT into a single partition of an index-organized table (IOT), into a partitioned IOT with only one partition, or into an IOT that is not partitioned, will be done serially, even if the IOT was created in parallel mode or you specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.
The target table cannot have any triggers or referential integrity constraints defined on it.
The target table cannot be replicated.
A transaction containing a direct-path INSERT statement cannot be or become distributed.
You cannot query or modify direct-path inserted data immediately after the insert is complete. If you attempt to do so, an ORA-12838 error is generated. You must first issue a COMMIT statement before attempting to read or modify the newly-inserted data.
Oracle Database Administrator's Guide for a more complete description of direct-path INSERT
Oracle Database Utilities for information on SQL*Loader
Oracle Database SQL Tuning Guide for information on statistics gathering when inserting into an empty table using direct-path INSERT
Читайте также: