Oracle查询优化-04插入、更新与删除数据

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle查询优化-04插入、更新与删除数据，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

4.1 插入新记录

问题

向表中插入一条新的记录。

解决方案

使用带有values子句的insert语句来插入一行。

  1. insert into dept(deptno,dname,loc) values(19,'xgj','BEIJING');

讨论

作为一种简便方式，在insert语句中，可以省略字段列表，

然而，如果语句中没有列出要插入行中的目标字段，则必须要插入表中的所有列，需要注意的，在插入值列表中所列出的值的顺序，必须与select * 查询语句所列出的列顺序完全一致。

4.2 插入默认值

问题

定义表时可以为某些列定义默认值。 

  1. create table test_xgj ( c1 varchar2(10) default '默认1',c2 varchar2(10) default '默认2',c3 varchar2(10) default '默认3',c4 date default sysdate );

解决方案

  1. sql> insert into test_xgj(c1,c2,c3)
  2.  values (default,null,'手输入');
  3.  
  4. sql> commit ;
  5.  
  6. sql> select * from test_xgj;
  7.  
  8. C1              C2      C3      C4
  9. ---------- ---------- ---------- -----------
  10. 默认1                    手输入     2017-03-05

讨论

在值列表中的default关键字为相应列插入默认值，默认值在创建表时定义。 所有的dbms中都可以使用此关键字。

注意事项：

  1. 如果insert语句中没有包含默认值的列，则会添加默认值，如 C4
  2. 如果包含默认值的列，需要用default关键字，才回添加默认值，如C1
  3. 如果已经显示的设定了NULL或者其他值，则不会生成默认值，如C2，C3

4.3 阻止对某几列插入

问题

举个例子，如果我们希望C4列的默认值为SYSDATE，这种列一般是为了记录数据生成的时间，不允许手工录入，该怎么办么？

解决方案

我们可以建立一个不包含C4列的View，新增数据时通过这个VIEW就可以。

  1. sql> create  or replace view test_xgj_view as 
  2.                 select c1,c3 from test_xgj ;
  3.  
  4.  
  5. sql> insert into test_xgj_view(c1,c3) values ('默认值2',NULL,'C3');
  6. sql> commit ;
  7.  
  8.  
  9. --查询下 test_xgj表，发现 C4虽然没有插入，但是因为设置了default值，所以也有值
  10. sql> select a.* from test_xgj  a ;
  11.  
  12. C1             C2         C3          C4
  13. ---------- ---------- ---------- -----------
  14. 默认值2                  C3        2017-03-05
  15. 默认1                    手输入     2017-03-05

讨论

注意：

通过view新增数据，不能再使用关键字default .

  1. sql> insert into test_xgj_view(c1,c3) values (default,'C3');
  2.  
  3. insert into test_xgj_view(c1,'C3')
  4.  
  5. ORA-32575: Explicit column default is not supported for modifying views

4.3 复制表的定义及数据

解决方案

我们可以用一下语句 复制表EMP

  1. create table emp2 as select * from emp ;

也可以先复制表的定义，然后再新增数据

  1. create table emp2 as select * from emp where 1=2;

注意： 复制的表不包含默认值等约束信息，使用这种方式复制表后，需要重建默认值及索引和约束等信息。

举例：

  1. sql> desc test_xgj;
  2. Name Type    Nullable Default Comments 
  3. ---- ------------ -------- ------- -------- 
  4. C1 VARCHAR2(10) Y       '默认1'          
  5. C2 VARCHAR2(10) Y       '默认2'          
  6. C3 VARCHAR2(10) Y       '默认3'          
  7. C4 DATE   Y       sysdate          
  8.  
  9.  
  10.  
  11. --第一种方式
  12. sql> create table test_xgj_2 as select * from test_xgj ;
  13.  
  14. Table created
  15.  
  16. sql> desc test_xgj_2;
  17. Name Type    Nullable Default Comments 
  18. ---- ------------ -------- ------- -------- 
  19. C1 VARCHAR2(10) Y                        
  20. C2 VARCHAR2(10) Y                        
  21. C3 VARCHAR2(10) Y                        
  22. C4 DATE   Y                        
  23.  
  24. sql> 
  25.  
  26.  
  27.  ---第二种方式
  28. sql> create table test_xgj_copy as select * from test_xgj where 1=2 ;
  29.  
  30. Table created
  31.  
  32. sql> desc test_xgj_copy ;
  33. Name Type    Nullable Default Comments 
  34. ---- ------------ -------- ------- -------- 
  35. C1 VARCHAR2(10) Y                        
  36. C2 VARCHAR2(10) Y                        
  37. C3 VARCHAR2(10) Y                        
  38. C4 DATE   Y

4.4 用with check option 限制数据录入

如果视图定义包括条件（譬如 WHERE 子句）并且其意图是确保任何引用该视图的 INSERT 或 UPDATE 语句都应用 WHERE 子句，则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件，则返回 sql 错误。

举例说明：

我们创建一个视图，并使用了with check option来限制了视图。 然后我们来看一下视图包含的结果

  1. sql> create or replace view  xgj
  2.   2  as
  3.   3  select empno,ename from emp where ename like 'J%'
  4.   4  with check option;
  5.  
  6. View created
  7.  
  8. sql> select  * from xgj;
  9.  
  10. EMPNO ENAME
  11. ----- ----------
  12.  7566 JONES
  13.  7900 JAMES
  14.  
  15.  
  16. sql> update xgj set ename='XGJ' where empno=7566;
  17.  
  18. update xgj set ename='XGJ' where empno=7566
  19.  
  20. ORA-01402: view WITH CHECK OPTION where-clause violation
  21.  
  22.  
  23.  
  24. --如果更新符合with check point的 ，则可以。
  25. sql> update xgj set ename='Jack' where empno=7566;
  26.  
  27. 1 row updated
  28.  
  29. sql> rollback;
  30.  
  31. Rollback complete

ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句的错误,为什么呢？

这是因为前面我们在创建视图时指定了witch check option关键字，这也就是说，更新后的每一条数据仍然要满足创建视图时指定的where条件，所以我们这里发生了错误ORA-01402。

但是需要说明的时 ，虽然指定了with check option，我们还是可以删除视图中的数据。例如上例中，我们可以使用

  1. delete from xgj where empno = 7566;

INSERT WITH CHECK OPTION的用法

  1. 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');

这样的语法看起来很特殊,其实是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。

如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入。

如果不加WITH CHECK OPTION则在插入时不会检查。

这里注意，subquery其实是不会实际执行的。

4.5多表插入语句

oracle从9i开始可以用一条insert语句实现向多个表中插入数据

Oracle Insert all有三种情况：
一、无条件 INSERT ALL
二、条件 INSERT ALL
三、条件 INSERT FIRST

语法：

  1. INSERT [ALL] [conditional_insert_clause]
  2. [insert_into_clause values_clause](subquery)

subquery:子查询语句，可以是任何合法的select语句

conditional_insert_clause如下：

  1. [ALL][FIRST]
  2. [WHEN condition THEN][insert_into_clause values_clause]
  3. [ELSE] [insert_into_clause values_clause]

无条件 INSERT ALL

语法

  1. INSERT ALL insert_into_clause values_clause_1 [insert_into_clause values_clause_2] …… Subquery；

示例：

  1. INSERT ALL INTO sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal) INTO mgr_history(emp_id,manager_id,sal) SELECT employee_id empid,hire_date hiredate,salary sal,manager_id mgr FROM employees WHERE employee_id>200;

1、指定所有跟随着的多表 insert_into_clauses 执行无条件的多表插入；
2、对于每个由子查询返回的行， Oracle 服务器执行每一个 insert_into_clause一次。

条件 INSERT ALL

  1. 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. Insert All 
  2. when id>5 then into z_test1(id,name) values(id,name) 
  3. when id<>2 then into z_test2(id) values(id) 
  4. else into z_test3 values(name) 
  5. select id,name from xgj;

1、指定 conditional_insert_clause 来执行一个条件多表插入；
2、Oracle 服务器通过相应的 WHEN 条件过滤每一个 insert_into_clause，确定是否执行这个 insert_into_clause；
3、一个单个的多表插入语句可以包含最多 127 个 WHEN 子句。

条件 INSERT FIRST

FIRST和ALL的区别在于当遇到第一个求值为true的子句之后，停止对WHEN子句求值，而ALL不论求值是否为true。

语法

  1. 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、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 未找到父项关键字

  1. delete from emp where not exists ( select null from dept where dept.deptno=emp.deptno );

4.7删除名称重复的记录

问题

数据如下：

  1. sql> create table xgj (id integer,name varchar(10));
  2.  
  3. Table created
  4. sql> INSERT INTO xgj VALUES (1,'NAPOLEON');
  5.  
  6. 1 row inserted
  7. sql> INSERT INTO xgj VALUES (2,'DYNAMITE');
  8.  
  9. 1 row inserted
  10. sql> INSERT INTO xgj VALUES (3,'DYNAMITE');
  11.  
  12. 1 row inserted
  13. sql> INSERT INTO xgj VALUES (4,'SHE SELLS');
  14.  
  15. 1 row inserted
  16. sql> INSERT INTO xgj VALUES (5,'SEA SHELLS');
  17.  
  18. 1 row inserted
  19. sql> INSERT INTO xgj VALUES (6,'SEA SHELLS');
  20.  
  21. 1 row inserted
  22. sql> INSERT INTO xgj VALUES (7,'SEA SHELLS');
  23.  
  24. 1 row inserted
  25. sql> commit ;
  26.  
  27. Commit complete
  28.  
  29. sql>

DYNAMITE 和 SEA SHELLS 重复，现在要求表中重复的name只保留一行，改如何办呢？

解决方案

处理数据需谨慎，要确认更改结果后再提交。

下面介绍三种方法

通过name相同，id不同的方式来判断

先查询下数据，确认无误

  1. select * from xgj a where exists (select null from xgj b where a.name = b.name and b.id > a.id);
  2.  
  3.  ID        NAME
  4. -------- ----------
  5.  2       DYNAMITE
  6.  5       SEA SHELLS
  7.  6       SEA SHELLS
  1. 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)；

利用rowid来代替其中的id

  1. select * from xgj a where exists (select null from xgj b where a.name = b.name and b.rowid > a.rowid);
  2.  
  3. delete from xgj a where exists (select null from xgj b where a.name = b.name and b.rowid > a.rowid);

因为不需要关联id列，我们只需要建立单列索引。

  1. create index idx_name on xgj(name);

通过分析函数根据name分组生成序号，然后删除序号大于1的数据

查看要删除的数据 

  1. select  rowid as rid,name,row_number() over(partition by name order by id) as seq
  2.   from xgj;
  3.  
  4.  
  5. RID                  NAME        SEQ
  6. ------------------ ---------- ----------
  7. AAAzBkAAIAAOet8AAB  DYNAMITE          1
  8. AAAzBkAAIAAOet8AAC  DYNAMITE          2
  9. AAAzBkAAIAAOet8AAA  NAPOLEON          1
  10. AAAzBkAAIAAOet8AAE  SEA SHELLS          1
  11. AAAzBkAAIAAOet8AAF  SEA SHELLS          2
  12. AAAzBkAAIAAOet8AAG  SEA SHELLS          3
  13. AAAzBkAAIAAOet8AAD  SHE SELLS          1
  14.  
  15. 7 rows selected

删除数据： 利用分析函数取出重复的数据后删除序号>1的数据

  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);

当然了 还有其他方法。。

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问