oracle之insert语句总结
insert语句总结
16.1 第⼀类，insert语句：单⾏插⼊
1）SQL> create table a (id int,name char(10) default 'aaa'); //name列指定了default值
2）SQL> insert into a values(1,'abc'); //表a后没有所选列，values必须指定所有字段的值。
3）SQL> insert into a values(2,default); //同上，name字段⽤default占位。
4）SQL> insert into a values(3,null); //表a后没有所选列，name字段⽤null占位。
5）SQL> insert into a (id) values(4); //表a后有选择字段，未选定的字段如果指定了default，则以default的值代替null
6）SQL> insert into (select id from a) values (5); //这种形式本质同上，只不过表a的形式以结果集代之。
7）SQL> insert into a values(6,(select dname from dept where deptno=10)); //values⾥的某列使⽤了subquery引⽤另⼀个表的数据。注意：
1）insert语句会有约束的问题，不符合约束条件的insert不能成功。
2）default不但可以⽤于insert语句, 也可以⽤于update语句（考点）
3）values后⾯不可以跟多列⼦查询。
SQL> insert into a values(select deptno,dname from dept where deptno=10);
insert into a values(select deptno,dname from dept where deptno=10)
*
第 1 ⾏出现错误:
ORA-00936: 缺失表达式
更正⼀下：
SQL> insert into a values((select deptno from dept where deptno=10), (select dname from dept where deptno=10));
已创建 1 ⾏。
SQL> select * from a;
ID NAME
---------- ----------
1 abc
2 aaa
3
4 aaa
5 aaa
6 ACCOUNTING
SQL> commit;
7）insert WITH CHECK OPTION的⽤法
SQL> insert into (select id from a where id<100 WITH CHECK OPTION) values (20);
SQL> select * from a;
ID NAME
---------- ----------
1 abc
2 aaa
3
4 aaa
5 aaa
6 ACCOUNTING
20 aaa
SQL> rollback;
SQL> insert into (select id from a where id<100 WITH CHECK OPTION) values (101);
insert into (select id from a where id<100 WITH CHECK OPTION) values (101)
sql约束条件大于0*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
看看这句话的另⼀种情况：
SQL> insert into (select name from a where id<100 WITH CHECK OPTION) values ('NBA');
insert into (select name from a where id<100 WITH CHECK OPTION) values ('NBA')
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
上例是想说明如果插⼊的列不在where条件⾥，则不允许插⼊。（考点）。
SQL> insert into (select id,name from a where id<100 WITH CHECK OPTION) values (10,'tim');
SQL> insert into (select name from a where id<100) values ('NBA'); //不加WITH CHECK OPTION则在插⼊时不会检查。
关于WITH CHECK OPTION 关键字考点较多，这⾥是OCP教材中的⼀个例⼦，看看它在DML语句中的实际意义：
conn hr/hr
SELECT l.location_id,l.untry_id FROM locations l JOIN countries c ON (l.country_untry_id) JOIN regions USING(region_id) WHERE region_name='Europe';
LOCATION_ID CITY CO
----------- ------------------------------ --
1000 Roma IT
1100 Venice IT
2400 London UK
2500 Oxford UK
2600 Stretford UK
2700 Munich DE
2900 Geneva CH
3000 Bern CH
3100 Utrecht NL
INSERT INTO (SELECT location_id,city,country_id FROM locations WHERE country_id IN
(SELECT country_id FROM countries NATURAL JOIN regions
WHERE region_name='Europe')
WITH CHECK OPTION)
VALUES (3500,'Berlin','DE');
已创建 1 ⾏。
INSERT INTO (SELECT location_id,city,country_id FROM locations WHERE country_id IN
(SELECT country_id FROM countries NATURAL JOIN regions
WHERE region_name='Europe')
WITH CHECK OPTION)
VALUES (3500,'NEW YORK','US');
INSERT INTO (SELECT location_id,city,country_id FROM locations WHERE country_id IN
*
第 1 ⾏出现错误:
ORA-00001: 违反唯⼀约束条件 (HR.LOC_ID_PK)
其实这个WITH CHECK OPTION和我们之前讲的视图中的WITH CHECK OPTION是⼀个意思，⽐较⼀下：
CREATE OR REPLACE VIEW euro_city AS
SELECT location_id,city,country_id FROM locations WHERE country_id IN
(SELECT country_id FROM countries NATURAL JOIN regions
WHERE region_name='Europe')
WITH CHECK OPTION;
INSERT INTO euro_city VALUES (3600,'NEW YORK','US')
ORA-01402: 视图 WITH CHECK OPTION where ⼦句违规
总结：
这样的语法看起来很特殊，其实select⼦句是不会真正执⾏的，它只是规定了insert语句对某些字段的约束形式⽽已。即如果不满⾜subquery ⾥的where条件的话，就不允许插⼊。关于DML语句中嵌⼊subquery的⽤法，要注意有些指定是强制的。
⽐如这⼀句
SQL> update (select empno,job,sal from emp) set sal=8000 where job=(select job from emp where empno=7788);
update后接subquery，⽤于限制修改列。这⾥sal列和job列必须包含在subquery内，否则set sal=8000 和 where job=()就⽆法识别了。
下⾯的例⼦也类似，where 中限定sal和deptno, 那么在subquery中也必须声明⼀下。
SQL> delete (select empno,sal,deptno from emp) where sal>3000 and deptno=10;
16.2 第⼆类，insert ⼀次插⼊多⾏语法上去掉了values选项。
SQL> create table b as select * from a where 1>2; //建⽴⼀个空表b。结构来⾃a表， where 1>2 使没有符合的记录被筛选出来.
SQL> insert into b select * from a where name='aaa'; //插⼊的是结果集，注意没有values选项。
SQL> select * from b;
ID NAME
---------- ----------
2 aaa
4 aaa
5 aaa
SQL> insert into b(id) select id from a where id in(1,3); //使⽤⼦查询（结果集）插⼊，对位，注意b表没有default。
SQL> select * from b;
ID NAME
---------- ----------
2 aaa
4 aaa
5 aaa
1
3
16.3 第三类，Multitable insert ⼀条INSERT语句可以完成向多张表的插⼊任务。
insert all与insert first
1.创建表T并初始化测试数据，此表作为数据源。
create table t (x number(10), y varchar2(10));
insert into t values (1,'a');
insert into t values (2,'b');
insert into t values (3,'c');
insert into t values (4,'d');
insert into t values (5,'e');
insert into t values (6,'f');
commit;
2.查看表T的数据
SQL>select * from t;
X Y
---------- ----------
1 a
2 b
3 c
4 d
5 e
6 f
6 rows selected.
3.创建表T1和T2，作为我们要插⼊的⽬标表。
SQL>create table t1 as select * from t where 0=1;
Table created.
SQL>create table t2 as select * from t where 0=1;
Table created.
16.3.1 第⼀种多表插⼊⽅法INSERT ALL （不分先后，各插各的）
unconditional insert all（⽆条件insert all)
1）完成INSERT ALL插⼊
SQL>insert all into t1 into t2 select * from t;
12 rows created.
这⾥之所以显⽰插⼊了12条数据，实际上表⽰在T1表中插⼊了6条，T2表插⼊了6条，⼀共是12条数据。
2）验证T1表中被插⼊的数据。
SQL>select * from t1;
3）验证T2表中被插⼊的数据。
SQL>select * from t2;
OK，完成INSERT ALL命令的使命。
conditional insert all（有条件insert all)
SQL> insert all when x>=3 then into t1 when x>=2 then into t2 select * from t;
已创建9⾏。
16.3.2 第⼆种多表插⼊⽅法INSERT FIRST
conditional insert first(有条件insert first)
1）清空表T1和T2
SQL> truncate table t1;
SQL> truncate table t2;
2）完成INSERT FIRST插⼊
SQL> insert first when x>=3 then into t1 when x>=2 then into t2 select * from t;
处理逻辑是这样的，⾸先检索T表查X列值⼤于等于3的数据插⼊到T1表，然后将前⼀个查询中出现的数据排除后再查T表，到X列值⼤于等于2的数据再插⼊到T2表，注意INSERT FIRST的真正⽬的是将同样的数据只插⼊⼀次。
3）验证T1表中被插⼊的数据。
SQL> select * from t1;
X Y
---------- ----------
3 c
4 d
5 e
6 f
4）验证T2表中被插⼊的数据。
SQL> select * from t2;
X Y
-
--------- ----------
2 b
16.3.3 第三种，旋转Insert (pivoting insert)
Pivoting INSERT 有⾏变列的功能
create table sales_source_data (
employee_id number(6),
week_id number(2),
sales_mon number(8,2),
sales_tue number(8,2),
sales_wed number(8,2),
sales_thur number(8,2),
sales_fri number(8,2)
);
insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);
create table sales_info (
employee_id number(6),
week number(2),
sales number(8,2)
);
看上⾯的表结构，现在将要sales_source_data表中的数据转换到sales_info表中，这种情况就需要使⽤旋转Insert
⽰例如下:
insert all
into sales_info values(employee_id,week_id,sales_mon)
into sales_info values(employee_id,week_id,sales_tue)
into sales_info values(employee_id,week_id,sales_wed)
into sales_info values(employee_id,week_id,sales_thur)
into sales_info values(employee_id,week_id,sales_fri)
select employee_id,week_id,sales_mon,sales_tue,
sales_wed,sales_thur,sales_fri
from sales_source_data;
SQL> select * from sales_info;
EMPLOYEE_ID WEEK SALES
----------- ---------- ----------
176 6 2000
176 6 3000
176 6 4000
176 6 5000
176 6 6000
从该例⼦可以看出，所谓旋转Insert是⽆条件 insert all 的⼀种特殊应⽤, 将⼀个表中的⾏转换成另⼀个表中的列，这种应⽤被oracle官⽅，赋予了⼀个pivoting insert的名称，即旋转insert.
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论