大佬教程收集整理的这篇文章主要介绍了Oracle查询优化-04插入、更新与删除数据,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
向表中插入一条新的记录。
使用带有values子句的insert语句来插入一行。
insert into dept(deptno,dname,loC) values(19,'xgj','BEIJING');
作为一种简便方式,在insert语句中,可以省略字段列表,
然而,如果语句中没有列出要插入行中的目标字段,则必须要插入表中的所有列,需要注意的,在插入值列表中所列出的值的顺序,必须与SELEct * 查询语句所列出的列顺序完全一致。
定义表时可以为某些列定义默认值。
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中都可以使用此关键字。
注意事项:
举个例子,如果我们希望C4列的默认值为sysdate,这种列一般是为了记录数据生成的时间,不允许手工录入,该怎么办么?
我们可以建立一个不包含C4列的VIEw,新增数据时通过这个VIEW就可以。
sql> create or replace vIEw test_xgj_vIEw as
SELEct c1,c3 from test_xgj ;
sql> insert into test_xgj_vIEw(c1,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,c3) values (default,'C3');
insert into test_xgj_vIEw(c1,'C3')
ORA-32575: Explicit column default is not supported for modifying vIEws
@H_663_197@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
如果视图定义包括条件(譬如 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 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其实是不会实际执行的。
@H_301_469@4.5多表插入语句Oracle从9i开始可以用一条insert语句实现向多个表中插入数据
Oracle Insert all有三种情况:
一、无条件 INSERT ALL
二、条件 INSERT ALL
三、条件 INSERT FirsT
语法:
INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause](@H_506_489@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_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,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 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 子句。
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才会起作用。
ORA-02298 未找到父项关键字
delete from emp where not exists ( SELEct null from dept where dept.deptno=emp.deptno );
数据如下:
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只保留一行,改如何办呢?
处理数据需谨慎,要确认更改结果后再提交。
下面介绍三种方法
先查询下数据,确认无误
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);
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);
查看要删除的数据
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插入、更新与删除数据全部内容,希望文章能够帮你解决Oracle查询优化-04插入、更新与删除数据所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。