4.1 插入新记录

问题

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

解决方案

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

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

讨论

作为一种简便方式，在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 
);

解决方案

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

讨论

在值列表中的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

讨论

注意：

通过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

4.3 复制表的定义及数据

解决方案

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

create table emp2 as select * from emp ;

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

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

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

举例：

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

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

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

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

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

delete from xgj where empno =   7566;

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

这样的语法看起来很特殊,其实是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)

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

conditional_insert_clause如下：

[ALL][FIRST]
[WHEN condition THEN][insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]

无条件 INSERT ALL

语法

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

示例：

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、指定所有跟随着的多表 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；

示例:

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、指定 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、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
);

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>

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

delete from xgj a where  exists (select null  from xgj  b where a.name = b.name  and  b.id > a.id );

利用这种方式删除数据时，需要重建组合索引。

create index idx_name_id on xgj(name,id)；

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

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

create index idx_name on xgj(name);

通过分析函数根据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的数据

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查询优化-04插入、更新与删除数据

本文链接：https://blog.quwenai.cn/post/6130.html

作者授权：除特别说明外，本文由 toobug 原创编译并授权 TOOBUG信息安全网刊载发布。

本文链接：https://blog.quwenai.cn/post/6130.html

分享到：

正文

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

4.1 插入新记录

问题

解决方案

讨论

4.2 插入默认值

问题

解决方案

讨论

4.3 阻止对某几列插入

问题

解决方案

讨论

4.3 复制表的定义及数据

解决方案

4.4 用with check option 限制数据录入

INSERT WITH CHECK OPTION的用法

4.5多表插入语句

无条件 INSERT ALL

条件 INSERT ALL

条件 INSERT FIRST

4.6删除违反参照完整性的记录

4.7删除名称重复的记录

问题

解决方案

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

利用rowid来代替其中的id

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

相关阅读

Oracle/MySQL/PostgreSQL数据库获取表的主键及字段信息(整理)

Oralce/MySQL/SQLServer/PostgreSQL 多种数据类型建表和插入数据脚本(测试专用)

Oracle数据库日常维护相关命令整理

BaseAdapter优化深入分析

发表评论取消回复

还没有评论，来说两句吧...

目录[+]

4.1 插入新记录

问题

解决方案

讨论

4.2 插入默认值

问题

解决方案

讨论

4.3 阻止对某几列插入

问题

解决方案

讨论

4.3 复制表的定义及数据

解决方案

4.4 用with check option 限制数据录入

INSERT WITH CHECK OPTION的用法

4.5多表插入语句

无条件 INSERT ALL

条件 INSERT ALL

条件 INSERT FIRST

4.6删除违反参照 完整性的记录

4.7删除名称重复的记录

问题

解决方案

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

利用rowid来代替其中的id

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

相关阅读

Oracle/MySQL/PostgreSQL数据库获取表的主键及字段信息(整理)

Oralce/MySQL/SQLServer/PostgreSQL 多种数据类型建表和插入数据脚本(测试专用)

Oracle数据库日常维护相关命令整理

BaseAdapter优化深入分析

发表评论取消回复

还没有评论，来说两句吧...

目录[+]

4.6删除违反参照完整性的记录