Oracle查询优化-04插入、更新与删除数据
4.1 插入新记录
问题
向表中插入一条新的记录。
解决方案
使用带有values子句的insert语句来插入一行。
insert into dept(deptno,dname,loc)
values(19,'xgj','BEIJING');
- 1
- 2
讨论
作为一种简便方式，在insert语句中，可以省略字段列表，
然而，如果语句中没有列出要插入行中的目标字段，则必须要插入表中的所有列，需要注意的，在插入值列表中所列出的值的顺序，必须与select * 查询语句所列出的列顺序完全一致。
4.2 插入默认值
问题
定义表时可以为某些列定义默认值。
create table test_xgj (
c1 varchar2(10) default '默认1',
c2 varchar2(10) default '默认2',
c3 varchar2(10) default '默认3',
c4 date default sysdate
);
- 1
- 2
- 3
- 4
- 5
- 6
解决方案
SQL> insert into test_xgj(c1,c2,c3)
values (default , null ,'手输入');
SQL> commit ;
SQL> select * from test_xgj;
C1 C2 C3 C4
---------- ---------- ---------- -----------
默认1 手输入 2017-03-05
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
讨论
在值列表中的default关键字为相应列插入默认值，默认值在创建表时定义。 所有的dbms中都可以使用此关键字。
注意事项：
- 如果insert语句中没有包含默认值的列，则会添加默认值，如 C4
- 如果包含默认值的列，需要用default关键字，才回添加默认值，如C1
- 如果已经显示的设定了NULL或者其他值，则不会生成默认值，如C2，C3
4.3 阻止对某几列插入
问题
举个例子，如果我们希望C4列的默认值为SYSDATE，这种列一般是为了记录数据生成的时间，不允许手工录入，该怎么办么？
解决方案
我们可以建立一个不包含C4列的View，新增数据时通过这个VIEW就可以。
SQL> create or replace view test_xgj_view as
select c1 , c2 ,c3 from test_xgj ;
SQL> insert into test_xgj_view(c1,c2,c3) values ('默认值2',NULL ,'C3');
SQL> commit ;
--查询下 test_xgj表，发现 C4虽然没有插入，但是因为设置了default值，所以也有值
SQL> select a.* from test_xgj a ;
C1 C2 C3 C4
---------- ---------- ---------- -----------
默认值2 C3 2017-03-05
默认1 手输入 2017-03-05
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
讨论
注意：
通过view新增数据，不能再使用关键字default .
SQL> insert into test_xgj_view(c1,c2,c3) values (default ,NULL ,'C3');
insert into test_xgj_view(c1,c2,c3) values (default ,NULL ,'C3')
ORA-32575: Explicit column default is not supported for modifying views
- 1
- 2
- 3
- 4
- 5
- 6
4.3 复制表的定义及数据
解决方案
我们可以用一下语句 复制表EMP
create table emp2 as select * from emp ;
- 1
也可以先复制表的定义，然后再新增数据
create table emp2 as select * from emp where 1=2;
- 1
注意： 复制的表不包含默认值等约束信息，使用这种方式复制表后，需要重建默认值及索引和约束等信息。
举例：
SQL> desc test_xgj;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
C1 VARCHAR2(10) Y '默认1'
C2 VARCHAR2(10) Y '默认2'
C3 VARCHAR2(10) Y '默认3'
C4 DATE Y sysdate
--第一种方式
SQL> create table test_xgj_2 as select * from test_xgj ;
Table created
SQL> desc test_xgj_2;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
C1 VARCHAR2(10) Y
C2 VARCHAR2(10) Y
C3 VARCHAR2(10) Y
C4 DATE Y
SQL>
---第二种方式
SQL> create table test_xgj_copy as select * from test_xgj where 1=2 ;
Table created
SQL> desc test_xgj_copy ;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
C1 VARCHAR2(10) Y
C2 VARCHAR2(10) Y
C3 VARCHAR2(10) Y
C4 DATE Y
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
4.4 用with check option 限制数据录入
如果视图定义包括条件（譬如 WHERE 子句）并且其意图是确保任何引用该视图的 INSERT 或 UPDATE 语句都应用 WHERE 子句，则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件，则返回 SQL 错误。
举例说明：
我们创建一个视图，并使用了with check option来限制了视图。 然后我们来看一下视图包含的结果
SQL> create or replace view xgj
2 as
3 select empno,ename from emp where ename like 'J%'
4 with check option;
View created
SQL> select * from xgj;
EMPNO ENAME
----- ----------
7566 JONES
7900 JAMES
SQL> update xgj set ename='XGJ' where empno=7566;
update xgj set ename='XGJ' where empno=7566
ORA-01402: view WITH CHECK OPTION where-clause violation
--如果更新符合with check point的 ，则可以。
SQL> update xgj set ename='Jack' where empno=7566;
1 row updated
SQL> rollback;
Rollback complete
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句的错误,为什么呢？
这是因为前面我们在创建视图时指定了witch check option关键字，这也就是说，更新后的每一条数据仍然要满足创建视图时指定的where条件，所以我们这里发生了错误ORA-01402。
但是需要说明的时 ，虽然指定了with check option，我们还是可以删除视图中的数据。例如上例中，我们可以使用
delete from xgj where empno = 7566;
- 1
INSERT WITH CHECK OPTION的用法
insert into (<select clause> WITH CHECK OPTION) values (...)
SQL> insert into (select object_id,object_name,object_type from xgj where object_id<1000 WITH CHECK OPTION)
2 values(999,'xxx','xxxx');
- 1
- 2
- 3
- 4
- 5
- 6
这样的语法看起来很特殊,其实是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。
如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入。
如果不加WITH CHECK OPTION则在插入时不会检查。
这里注意，subquery其实是不会实际执行的。
4.5多表插入语句
oracle从9i开始可以用一条insert语句实现向多个表中插入数据
Oracle Insert all有三种情况：
一、无条件 INSERT ALL
二、条件 INSERT ALL
三、条件 INSERT FIRST
语法：
INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause](subquery)
- 1
- 2
subquery:子查询语句，可以是任何合法的select语句
conditional_insert_clause如下：
[ALL][FIRST]
[WHEN condition THEN][insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
- 1
- 2
- 3
无条件 INSERT ALL
语法
INSERT ALL
insert_into_clause values_clause_1
[insert_into_clause values_clause_2]
……
Subquery；
- 1
- 2
- 3
- 4
- 5
示例：
INSERT ALL
INTO sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal)
INTO mgr_history(emp_id,manager_id,salary) values (empid,hiredate,sal)
SELECT employee_id empid,hire_date hiredate,salary sal,manager_id mgr
FROM employees
WHERE employee_id>200;
- 1
- 2
- 3
- 4
- 5
- 6
1、指定所有跟随着的多表 insert_into_clauses 执行无条件的多表插入；
2、对于每个由子查询返回的行， Oracle 服务器执行每一个 insert_into_clause一次。
条件 INSERT ALL
INSERT ALL
WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
……
[ELSE] [insert_into_clause values_clause]
Subquery；
- 1
- 2
- 3
- 4
- 5
- 6
示例:
Insert All
when id>5 then into z_test1(id, name) values(id,name)
when id<>2 then into z_test2(id) values(id)
else into z_test3 values(name)
select id,name from xgj;
- 1
- 2
- 3
- 4
- 5
1、指定 conditional_insert_clause 来执行一个条件多表插入；
2、Oracle 服务器通过相应的 WHEN 条件过滤每一个 insert_into_clause，确定是否执行这个 insert_into_clause；
3、一个单个的多表插入语句可以包含最多 127 个 WHEN 子句。
条件 INSERT FIRST
FIRST和ALL的区别在于当遇到第一个求值为true的子句之后，停止对WHEN子句求值，而ALL不论求值是否为true。
语法
INSERT FIRST
WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
……
[ELSE] [insert_into_clause values_clause]
Subquery；
- 1
- 2
- 3
- 4
- 5
- 6
1、Oracle 服务器对每一个出现在语句顺序中的 WHEN 子句求值；
2、如果第一个 WHEN 子句的值为 true，Oracle 服务器对于给定的行执行相应的 INTO 子句，并且跳过后面的 WHEN 子句(后面的when语句都不再考虑满足第一个When子句的记录，即使该记录满足when语句中的条件)。
注：多表 INSERT 语句上的约束
a、你只能在表而不能在视图上执行多表插入；
b、你不能执行一个多表插入到一个远程表；
c、在执行一个多表插入时，你不能指定一个表集合表达式；
d、在一个多表插入中，所有的 insert_into_clauses 不能组合指定多于 999 个目列；
e、只有当所有insert_into_clauses中的表数据都没有发生更新时，Rollback才会起作用。
4.6删除违反参照 完整性的记录
ORA-02298 未找到父项关键字
delete from emp
where not exists (
select null from dept where dept.deptno=emp.deptno
);
- 1
- 2
- 3
- 4
4.7删除名称重复的记录
问题
数据如下：
SQL> create table xgj (id integer, name varchar(10));
Table created
SQL> INSERT INTO xgj VALUES (1, 'NAPOLEON');
1 row inserted
SQL> INSERT INTO xgj VALUES (2, 'DYNAMITE');
1 row inserted
SQL> INSERT INTO xgj VALUES (3, 'DYNAMITE');
1 row inserted
SQL> INSERT INTO xgj VALUES (4, 'SHE SELLS');
1 row inserted
SQL> INSERT INTO xgj VALUES (5, 'SEA SHELLS');
1 row inserted
SQL> INSERT INTO xgj VALUES (6, 'SEA SHELLS');
1 row inserted
SQL> INSERT INTO xgj VALUES (7, 'SEA SHELLS');
1 row inserted
SQL> commit ;
Commit complete
SQL>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
DYNAMITE 和 SEA SHELLS 重复，现在要求表中重复的name只保留一行，改如何办呢？
解决方案
处理数据需谨慎，要确认更改结果后再提交。
下面介绍三种方法
通过name相同，id不同的方式来判断
先查询下数据，确认无误
select *
from xgj a
where exists (select null
from xgj b
where a.name = b.name
and b.id > a.id);
ID NAME
-------- ----------
2 DYNAMITE
5 SEA SHELLS
6 SEA SHELLS
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
delete from xgj a where exists (select null from xgj b where a.name = b.name and b.id > a.id );
- 1
利用这种方式删除数据时，需要重建组合索引。
create index idx_name_id on xgj(name,id)；
- 1
利用rowid来代替其中的id
select *
from xgj a
where exists (select null
from xgj b
where a.name = b.name
and b.rowid > a.rowid);
delete from xgj a
where exists (select null
from xgj b
where a.name = b.name
and b.rowid > a.rowid);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
因为不需要关联id列，我们只需要建立单列索引。
create index idx_name on xgj(name);
- 1
通过分析函数根据name分组生成序号，然后删除序号大于1的数据
查看要删除的数据
select rowid as rid ,
name,
row_number() over(partition by name order by id) as seq
from xgj;
RID NAME SEQ
------------------ ---------- ----------
AAAzBkAAIAAOet8AAB DYNAMITE 1
AAAzBkAAIAAOet8AAC DYNAMITE 2
AAAzBkAAIAAOet8AAA NAPOLEON 1
AAAzBkAAIAAOet8AAE SEA SHELLS 1
AAAzBkAAIAAOet8AAF SEA SHELLS 2
AAAzBkAAIAAOet8AAG SEA SHELLS 3
AAAzBkAAIAAOet8AAD SHE SELLS 1
7 rows selected
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
删除数据： 利用分析函数取出重复的数据后删除序号>1的数据
delete from xgj
where rowid in (select rid
from (select rowid as rid,
name,
row_number() over(partition by name order by id) as seq
from xgj)
where seq > 1);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
当然了 还有其他方法。。
文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。
原文链接：artisan.blog.csdn.net/article/details/68951502
- 点赞
- 收藏
- 关注作者
评论（0）