新博客http://www.cnblogs.com/zhjh256 欢迎访问
分类： Oracle
2008-01-06 21:47:59
基本方法
通常会见到的情况，某张表中缺少某些数据，某个用户将一部分数据通过文本文件传输过来，要求更新表中的数据。如下：
create table invoices (
invoice# number,
amount number,
po_xref varchar2(15));
insert into invoices (invoice#, amount) values (16535,45.67);
insert into invoices (invoice#, amount) values (16536,100.00);
insert into invoices (invoice#, amount) values (16537,66.99);
select * from invoices;
INVOICE# AMOUNT PO_XREF
---------- ---------- ---------------
16535 45.67
16536 100
16537 66.99
提供的文本文件数据如下：
16535,GH-78346DD
16537,F9-7456QAI
我们可以创建一个外部表进行访问：
create directory exttab as 'c:\';
grant read, write on directory exttab to public;
create table poxref (
invoice# number,
xref varchar2(15))
organization external
(type oracle_loader
default directory exttab
access parameters
(records delimited by newline
fields terminated by ','
missing field values are null)
location ('textfile.csv')
)
reject limit unlimited;
SQL> select * from poxref;
INVOICE# XREF
---------- ---------------
16535 GH-78346DD
16537 F9-7456QAI
然后我们就可以使用相关的子查询进行更新：
SQL> update invoices i set po_xref=(select xref from poxref where invoice#=i.invoice#);
3 rows updated
SQL> select * from invoices;
INVOICE# AMOUNT PO_XREF
---------- ---------- ---------------
16537 66.99 F9-7456QAI
16535 45.67 GH-78346DD
16536 100
这种情况下，我们可以看到更新两行。也可以扩展到相关删除：
SQL> delete from invoices i
where invoice# =(select invoice# from poxref where xref=i.po_xref);
2 rows deleted
相关子查询的问题是？
如下：
SQL> delete from invoices;
1 row deleted
SQL> COMMIT;
Commit complete
SQL> insert into invoices (invoice#, amount) values (16535,45.67);
1 row inserted
SQL> insert into invoices (invoice#, amount) values (16536,100.00);
1 row inserted
SQL> insert into invoices (invoice#, amount) values (16537,66.99);
1 row inserted
SQL> update invoices set PO_XREF='hjr' where invoice#=16536;
1 row updated
SQL> commit;
Commit complete
SQL> select * from invoices;
INVOICE# AMOUNT PO_XREF
---------- ---------- ---------------
16535 45.67
16536 100 hjr
16537 66.99
SQL> update invoices i set po_xref=(select xref from poxref where invoice#=i.invoice#);
3 rows updated
SQL> select * from invoices;
INVOICE# AMOUNT PO_XREF
---------- ---------- ---------------
16535 45.67 GH-78346DD
16536 100
16537 66.99 F9-7456QAI
可以看到，这里实际上更新了三行，而我们的意图是更新两行。因为内部选择的输出没有匹配16536的条目，外部更新语句将PO_XREF更新为NULL，因此会出现这种结果。
要修复该问题，可以执行以下查询：
SQL> rollback;
Rollback complete
SQL> update invoices i set po_xref=(select xref from poxref where invoice#=i.invoice#) where PO_XREF is null;
2 rows updated
SQL> select * from invoices;
INVOICE# AMOUNT PO_XREF
---------- ---------- ---------------
16535 45.67 GH-78346DD
16536 100 hjr
16537 66.99 F9-7456QAI
外查询的WHERE恰当的限制了更新的纪录。
还有一种方法是首先创建一个视图仅包含需要更新的数据，然后更新视图：
SQL> update invoices set PO_XREF=NULL where invoice# in (16535,16537);
2 rows updated
SQL> commit;
Commit complete
SQL> create view viewinvoice as select * from invoices where PO_XREF is null;
View created
SQL> update viewinvoice v set po_xref=(select xref from poxref p where invoice#=v.invoice#);
2 rows updated
SQL> select * from invoices;
INVOICE# AMOUNT PO_XREF
---------- ---------- ---------------
16535 45.67 GH-78346DD
16536 100 hjr
16537 66.99 F9-7456QAI
虽然这种方法可行，但是需要每次都创建额外的视图。
很幸运，不一定要创建视图，可以使用内嵌视图：
UPDATE (SELECT po_xref, xref
FROM invoices i, poxref p
WHERE i.invoice# = p.invoice#)
SET po_xref = xref;
可以使用这种方法更新，但是需要在相关的列上包含唯一约束。
否则将得到ORA-01779: 无法修改与非键值保存表对应的列错误。
还有一种通过使用未文档化提示的方法避免增加唯一索引实现：
UPDATE (SELECT /*+ BYPASS_UJVC */ po_xref, xref
FROM invoices i, poxref p
WHERE i.invoice# = p.invoice#)
SET po_xref = xref;
