4.1 插入新記錄

問(wèn)題

向表中插入一條新的記錄。

解決方案

使用帶有values子句的insert語(yǔ)句來(lái)插入一行。

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

討論

作為一種簡(jiǎn)便方式，在insert語(yǔ)句中，可以省略字段列表，

然而，如果語(yǔ)句中沒(méi)有列出要插入行中的目標(biāo)字段，則必須要插入表中的所有列，需要注意的，在插入值列表中所列出的值的順序，必須與select * 查詢語(yǔ)句所列出的列順序完全一致。

---

4.2 插入默認(rèn)值

問(wèn)題

定義表時(shí)可以為某些列定義默認(rèn)值。

create table test_xgj ( c1 varchar2(10) default '默認(rèn)1', c2 varchar2(10) default '默認(rèn)2', c3 varchar2(10) default '默認(rèn)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 ---------- ---------- ---------- ----------- 默認(rèn)1 手輸入 2017-03-05

討論

在值列表中的default關(guān)鍵字為相應(yīng)列插入默認(rèn)值，默認(rèn)值在創(chuàng)建表時(shí)定義。 所有的dbms中都可以使用此關(guān)鍵字。

注意事項(xiàng)：

如果insert語(yǔ)句中沒(méi)有包含默認(rèn)值的列，則會(huì)添加默認(rèn)值，如 C4

如果包含默認(rèn)值的列，需要用default關(guān)鍵字，才回添加默認(rèn)值，如C1

如果已經(jīng)顯示的設(shè)定了NULL或者其他值，則不會(huì)生成默認(rèn)值，如C2，C3

---

4.3 阻止對(duì)某幾列插入

問(wèn)題

舉個(gè)例子，如果我們希望C4列的默認(rèn)值為SYSDATE，這種列一般是為了記錄數(shù)據(jù)生成的時(shí)間，不允許手工錄入，該怎么辦么？

解決方案

我們可以建立一個(gè)不包含C4列的View，新增數(shù)據(jù)時(shí)通過(guò)這個(gè)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 ('默認(rèn)值2',NULL ,'C3'); SQL> commit ;--查詢下 test_xgj表，發(fā)現(xiàn) C4雖然沒(méi)有插入，但是因?yàn)樵O(shè)置了default值，所以也有值 SQL> select a.* from test_xgj a ;C1 C2 C3 C4 ---------- ---------- ---------- ----------- 默認(rèn)值2 C3 2017-03-05 默認(rèn)1 手輸入 2017-03-05

討論

注意：

通過(guò)view新增數(shù)據(jù)，不能再使用關(guān)鍵字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 復(fù)制表的定義及數(shù)據(jù)

解決方案

我們可以用一下語(yǔ)句 復(fù)制表EMP

create table emp2 as select * from emp ;

也可以先復(fù)制表的定義，然后再新增數(shù)據(jù)

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

---

注意： 復(fù)制的表不包含默認(rèn)值等約束信息，使用這種方式復(fù)制表后，需要重建默認(rèn)值及索引和約束等信息。

舉例：

SQL> desc test_xgj; Name Type Nullable Default Comments ---- ------------ -------- ------- -------- C1 VARCHAR2(10) Y '默認(rèn)1' C2 VARCHAR2(10) Y '默認(rèn)2' C3 VARCHAR2(10) Y '默認(rèn)3' C4 DATE Y sysdate --第一種方式 SQL> create table test_xgj_2 as select * from test_xgj ;Table createdSQL> 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 createdSQL> 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 限制數(shù)據(jù)錄入

如果視圖定義包括條件（譬如 WHERE 子句）并且其意圖是確保任何引用該視圖的 INSERT 或 UPDATE 語(yǔ)句都應(yīng)用 WHERE 子句，則必須使用 WITH CHECK OPTION 定義該視圖。這個(gè)選項(xiàng)可以確保數(shù)據(jù)庫(kù)中正在修改的數(shù)據(jù)的完整性。如果在 INSERT 或 UPDATE 操作期間違反了條件，則返回 SQL 錯(cuò)誤。

舉例說(shuō)明：

我們創(chuàng)建一個(gè)視圖，并使用了with check option來(lái)限制了視圖。 然后我們來(lái)看一下視圖包含的結(jié)果

SQL> create or replace view xgj2 as3 select empno,ename from emp where ename like 'J%'4 with check option;View createdSQL> select * from xgj;EMPNO ENAME ----- ----------7566 JONES7900 JAMESSQL> update xgj set ename='XGJ' where empno=7566;update xgj set ename='XGJ' where empno=7566ORA-01402: view WITH CHECK OPTION where-clause violation--如果更新符合with check point的 ，則可以。 SQL> update xgj set ename='Jack' where empno=7566;1 row updatedSQL> rollback;Rollback complete

ORA-01402: 視圖 WITH CHECK OPTIDN 違反 where 子句的錯(cuò)誤,為什么呢？

這是因?yàn)榍懊嫖覀冊(cè)趧?chuàng)建視圖時(shí)指定了witch check option關(guān)鍵字，這也就是說(shuō)，更新后的每一條數(shù)據(jù)仍然要滿足創(chuàng)建視圖時(shí)指定的where條件，所以我們這里發(fā)生了錯(cuò)誤ORA-01402。

但是需要說(shuō)明的時(shí) ，雖然指定了with check option，我們還是可以刪除視圖中的數(shù)據(jù)。例如上例中，我們可以使用

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

這樣的語(yǔ)法看起來(lái)很特殊,其實(shí)是insert進(jìn)subquery里的這張表里，只不過(guò)如果不滿足subquery里的where條件的話，就不允許插入。

如果插入的列有不在subquery作為檢查的where條件里，那么也會(huì)不允許插入。

如果不加WITH CHECK OPTION則在插入時(shí)不會(huì)檢查。

這里注意，subquery其實(shí)是不會(huì)實(shí)際執(zhí)行的。

---

4.5多表插入語(yǔ)句

oracle從9i開(kāi)始可以用一條insert語(yǔ)句實(shí)現(xiàn)向多個(gè)表中插入數(shù)據(jù)

Oracle Insert all有三種情況：
一、無(wú)條件 INSERT ALL
二、條件 INSERT ALL
三、條件 INSERT FIRST

語(yǔ)法：

INSERT [ALL] [conditional_insert_clause] [insert_into_clause values_clause](subquery)

subquery:子查詢語(yǔ)句，可以是任何合法的select語(yǔ)句

conditional_insert_clause如下：

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

無(wú)條件 INSERT ALL

語(yǔ)法

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 執(zhí)行無(wú)條件的多表插入；
2、對(duì)于每個(gè)由子查詢返回的行， Oracle 服務(wù)器執(zhí)行每一個(gè) 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 來(lái)執(zhí)行一個(gè)條件多表插入；
2、Oracle 服務(wù)器通過(guò)相應(yīng)的 WHEN 條件過(guò)濾每一個(gè) insert_into_clause，確定是否執(zhí)行這個(gè) insert_into_clause；
3、一個(gè)單個(gè)的多表插入語(yǔ)句可以包含最多 127 個(gè) WHEN 子句。

條件 INSERT FIRST

FIRST和ALL的區(qū)別在于當(dāng)遇到第一個(gè)求值為true的子句之后，停止對(duì)WHEN子句求值，而ALL不論求值是否為true。

語(yǔ)法

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 服務(wù)器對(duì)每一個(gè)出現(xiàn)在語(yǔ)句順序中的 WHEN 子句求值；
2、如果第一個(gè) WHEN 子句的值為 true，Oracle 服務(wù)器對(duì)于給定的行執(zhí)行相應(yīng)的 INTO 子句，并且跳過(guò)后面的 WHEN 子句(后面的when語(yǔ)句都不再考慮滿足第一個(gè)When子句的記錄，即使該記錄滿足when語(yǔ)句中的條件)。

注：多表 INSERT 語(yǔ)句上的約束
a、你只能在表而不能在視圖上執(zhí)行多表插入；
b、你不能執(zhí)行一個(gè)多表插入到一個(gè)遠(yuǎn)程表；
c、在執(zhí)行一個(gè)多表插入時(shí)，你不能指定一個(gè)表集合表達(dá)式；
d、在一個(gè)多表插入中，所有的 insert_into_clauses 不能組合指定多于 999 個(gè)目列；
e、只有當(dāng)所有insert_into_clauses中的表數(shù)據(jù)都沒(méi)有發(fā)生更新時(shí)，Rollback才會(huì)起作用。

---

4.6刪除違反參照 完整性的記錄

ORA-02298 未找到父項(xiàng)關(guān)鍵字

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

---

4.7刪除名稱重復(fù)的記錄

問(wèn)題

數(shù)據(jù)如下：

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 completeSQL>

DYNAMITE 和 SEA SHELLS 重復(fù)，現(xiàn)在要求表中重復(fù)的name只保留一行，改如何辦呢？

---

解決方案

處理數(shù)據(jù)需謹(jǐn)慎，要確認(rèn)更改結(jié)果后再提交。

下面介紹三種方法

通過(guò)name相同，id不同的方式來(lái)判斷

先查詢下數(shù)據(jù)，確認(rèn)無(wú)誤

select *from xgj awhere exists (select nullfrom xgj bwhere a.name = b.nameand b.id > a.id);ID NAME -------- ----------2 DYNAMITE5 SEA SHELLS6 SEA SHELLS delete from xgj a where exists (select null from xgj b where a.name = b.name and b.id > a.id );

利用這種方式刪除數(shù)據(jù)時(shí)，需要重建組合索引。

create index idx_name_id on xgj(name,id)；

---

利用rowid來(lái)代替其中的id

select *from xgj awhere exists (select nullfrom xgj bwhere a.name = b.nameand b.rowid > a.rowid);delete from xgj awhere exists (select nullfrom xgj bwhere a.name = b.nameand b.rowid > a.rowid);

因?yàn)椴恍枰P(guān)聯(lián)id列，我們只需要建立單列索引。

create index idx_name on xgj(name);

---

通過(guò)分析函數(shù)根據(jù)name分組生成序號(hào)，然后刪除序號(hào)大于1的數(shù)據(jù)

查看要?jiǎng)h除的數(shù)據(jù)

select rowid as rid , name, row_number() over(partition by name order by id) as seqfrom 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 17 rows selected

刪除數(shù)據(jù)： 利用分析函數(shù)取出重復(fù)的數(shù)據(jù)后刪除序號(hào)>1的數(shù)據(jù)

delete from xgjwhere rowid in (select ridfrom (select rowid as rid,name,row_number() over(partition by name order by id) as seqfrom xgj)where seq > 1);

當(dāng)然了 還有其他方法。。

---

總結(jié)

以上是生活随笔為你收集整理的Oracle查询优化-04插入、更新与删除数据的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。