Insert into a query

2018-07-02 20:08:07

When just looking up Oracle's INSERT syntax, I noticed you can insert into a query, eg

insert into (select * from dept) (deptno, dname) values (99, 'new department');

Can anybody shed some light on what this is meant for? What can I achieve with inserting into a query that I can't with inserting into the table directly?

UPDATE: So far it seems this is just an alternative syntax, so I can write

insert into (select deptno, dname from dept) values (99, 'new department');

instead of

insert into dept (deptno, dname) values (99, 'new department');

Same thing, same execution plan. It doesn't matter whether the query returns a record or not. This:

insert into (select deptno, dname from dept where 1 = 0) values (99, 'new department');

leads again to the same execution plan. So we might assume that it really doesn't matter what the subquery looks like as long as we only select columns from one table. But no. This:

insert into (select deptno, dname from dept cross join some_table)
values (99, 'new department');

leads to "ORA-01779: cannot modify a column which maps to a non key-preserved table" or "ORA-01732: data manipulation operation not legal on this view".

I have the impression that Oracle decided to allow inserting into a query, because they allow inserting into views, for what else is a subquery here then an ad hoc view? So when you can insert into a view, surely they let you insert into an ad hoc view, too, but nobody in their right mind would ever use this syntax, of course :-)

But maybe I am wrong? Maybe this syntax does offer something that I am not yet aware of? If so tell me :-)

The subquery defines the columns of the table into which the rows are to be inserted. As oracle's doc (12c) says:

Specify the name of the [...] column or columns returned by a subquery, into which rows are to be inserted. If you specify a view or object view, then the database inserts rows into the base table of the view.

Example

create table test_isq (
    pk integer not null primary key, data_1 varchar2(40), data_2 varchar2(40), data_3 varchar2(40)
);

-- ok
insert into (select pk, data_2, data_3 from test_isq) (pk, data_2) values ( 1, 'Test');
insert into (select pk, data_2, data_3 from test_isq) values ( 2, 'Another', 'Test' );

-- fail
insert into (select data_1 from test_isq) values ( 'This', 'one', 'fails');
insert into (select data_1 from test_isq) (pk, data_1) values ( 42, 'Nope');

drop table test_isq;

Inserting into a subquery allows restricting results using WITH CHECK OPTION .

For example, let's say you want to allow any department name except for "new department". This example using a different value works fine:

SQL> insert into
  2  (select deptno, dname from dept where dname <> 'new department' WITH CHECK OPTION)
  3  values (98, 'old department');

1 row created.

But if that bad value is inserted it throws an error:

SQL> insert into
  2  (select deptno, dname from dept where dname <> 'new department' WITH CHECK OPTION)
  3  values (99, 'new department');
(select deptno, dname from dept where dname <> 'new department' WITH CHECK OPTION)
                           *
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation

I've never seen this feature used in the wild. Views have this option so it makes sense that you should be able to do the same thing with a subquery. I'm not sure why anyone would want to do this though, it's just as easy to put the limit on the SELECT statement that feeds the INSERT. And if the INSERT uses VALUES it's trivial to convert it to a SELECT statement.

You have to really dig into the syntax diagrams to see this feature: insert --> single_table_insert --> subquery --> query_block --> table_reference --> query_table_expression --> subquery_restriction_clause.

That is because INSERT into SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected.

链接地址: http://www.djcxy.com/p/91524.html

上一篇: 在使用React渲染IE 11时，Textarea占位符未显示

下一篇: 插入到查询中