oracle 多表关联Word文档下载推荐.docx
《oracle 多表关联Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《oracle 多表关联Word文档下载推荐.docx（11页珍藏版）》请在冰点文库上搜索。
null，
5.customer_type
null
6.）
1）最简单的形式
1.--经确认customers表中所有customer_id小于1000均为'
北京'
2.--1000以内的均是公司走向全国之前的本城市的老客户:
）
3.update
4.set
city_name='
5.where
customer_id<
1000
2）两表（多表）关联update
--仅在where字句中的连接
1.--这次提取的数据都是VIP，且包括新增的,所以顺便更新客户类别
2.update
customersa
--使用别名
3.set
customer_type='
01'
--01为vip，00为普通
4.where
exists（select
1
5.from
tmp_cust_cityb
6.where
b.customer_id=a.customer_id
7.）
3）两表（多表）关联update
--被修改值由另一个表运算而来
1.update
2.set
city_name=（select
b.city_name
from
tmp_cust_cityb
where
b.customer_id=a.customer_id）
3.where
4.from
7.--update超过2个值
8.update
9.set
（city_name,customer_type）=（select
b.city_name,b.customer_type
10.from
11.where
12.where
13.from
14.where
15.）
注意在这个语句中，
=（select
b.city_name,b.customer_type
b.customer_id=a.customer_id）
与
（select
1
是两个独立的子查询，查看执行计划可知，对b表/索引扫描了2篇；
如果舍弃where条件，则默认对A表进行全表
更新，但由于
1.select
有可能不能提供"
足够多"
值，因为tmp_cust_city只是一部分客户的信息，所以报错（如果指定的列--city_name可以为NULL则另当别论）：
1.01407,00000,
"
cannotupdate（%s）toNULL"
2.//*Cause:
3.//*Action:
一个替代的方法可以采用：
city_name=nvl（（select
b.customer_id=a.customer_id）,a.city_name）
或者
1.set
b.customer_id=a.customer_id）,'
未知'
--当然这不符合业务逻辑了
4）上述3）在一些情况下，因为B表的纪录只有A表的20-30%的纪录数，
考虑A表使用INDEX的情况，使用cursor也许会比关联update带来更好的性能：
serveroutput
on
2.
3.declare
4.cursor
city_cur
is
5.select
customer_id,city_name
6.from
7.order
by
customer_id;
8.begin
9.for
my_cur
in
city_curloop
10.
11.update
12.set
city_name=my_cur.city_name
13.where
customer_id=my_cur.customer_id;
14.
15./**此处也可以单条/分批次提交，避免锁表情况**/
16.--ifmod（city_cur%rowcount,10000）=0then
17.--dbms_output.put_line（'
----'
）;
18.--commit;
19.--endif;
20.end
loop;
21.end;
5）关联update的一个特例以及性能再探讨
在oracle的update语句语法中，除了可以update表之外，也可以是视图，所以有以下1个特例：
a.city_name,b.city_name
as
new_name
2.from
customersa,
3.tmp_cust_cityb
5.）
6.set
city_name=new_name
这样能避免对B表或其索引的2次扫描，但前提是A（customer_id）b（customer_id）必需是uniqueindex或primarykey。
否则报错：
1.01779,00000,
cannotmodifyacolumnwhichmapstoanonkey-preservedtable"
Anattemptwasmade
to
insert
or
update
columns
of
a
join
view
which
3.//map
anon-key-preserved
table.
4.//*Action:
Modify
theunderlyingbasetablesdirectly.
6）oracle另一个常见错误
回到3）情况,由于某些原因，tmp_cust_citycustomer_id不是唯一index/primarykey
当对于一个给定的a.customer_id
返回多余1条的情况，则会报如下错误：
1.01427,00000,
single-rowsubqueryreturnsmorethanonerow"
一个比较简单近似于不负责任的做法是
b.customer_id=a.customer_id
and
rownum=1）
如何理解
01427
错误，在一个很复杂的多表连接update的语句，经常因考虑不周，出现这个错误，
仍已上述例子来描述，一个比较简便的方法就是将A表代入值表达式中,使用groupby
和
having
字句查看重复的纪录
1.（select
b.customer_id,b.city_name,count（*）
tmp_cust_cityb,customersa
4.group
b.customer_id,b.city_name
5.having
count（*）>
=2
关于ORACLE的UPDATE更新多表的问题
有以下几种方式可以实现:
一种是：
updatetable1
set（field1,field2...）=
（SelectField1,field2....
fromtable2
wheretable1.field1=table2.field1）
wheretable1.field1in（selectfield1fromtable2）
二种是：
将table1,table2相关联字段建立主键Primarykey或Unionkey
Update（SelectTable1.field1,table1.field2,table2.field1,table2.field2
fromtable1,table2
whereTable1.Field1=table2.field1）
Settable1.table2=table2.table2,
table1.fieldn=table2.tablen,
......
table1的Field1和table2的field1将需要建立主键或唯一索引才行
三种是：
mergeintotable1
usingtable2
on（table1.field1=table2.field1and....）
whenmatchedthenUpdatesettable1.field2=table2.field2,
table1.fieldn=table2.fieldn,
whennotmatchedthen[doingotherthing]
其余方法可使用游标
这两天给新同事安排了一个工作，即做一个update的级联更新，在实际操作中发现了一个问题。
就是对于oracle的更新的语法，大部分人尤其是学过SqlServer的人在使用oracle的时候对于oracle的更新会有许多的疑问。
就此记下，以便日后查阅
updateaseta.col1=（selectb.col1
fromb
whereb.col2=a.col2）
whereexists
（select*frombwherea.col2=b.col2）
/**oracle与sqlserver更新update的用法有不同，oracle是怎么处理的呢
updateaseta.col1=100
将所有行全部的特定列col1更新为特定值
updateaseta.col1=100wherea.col2<
10将满足col2条件的行的col1列的值更新为特定的值
updateaseta.col1=a.col1+a.col2wherea.col2<
10同一个表中的简单计算更新
updateaseta.col1=（selectb.col1frombwherea.col2=b.col2）
whereexists（select*frombwherea.col2=b.col2）级联更新,将满足a.col2=b.col2的行的a.col1更新为对应的
b.col1的值。
当且仅当a=b时可以将where条件去掉。
这个更新还可以这样理解：
updateaseta.col1=（selectb.col1frombwherea.col2=b.col2）表示对于a中所有行满足a.col2=b.col2
的进行更新，不满足条件的也更新，只不过找不到对应的值，只能将空值赋之，如果此时a.col1不允许为空那么会报插入空值错误。
所以只有加上where条件，才能将a.col2<
>
b.col2的那些在a中的数据得以幸存（不被更新为空）。
oracle的INSERT、UPDATE、MERGE
ORACLE
2009-12-2108:
40:
55
阅读68
评论0
字号：
大中小
订阅
/*用COURSE表为模板，创建一张新表COURSE_NEW，并且包括COURSE表的所有记录*/
CREATETABLECOURSE_NEWASSELECT*FROMCOURSE;
/*清空COURSE_NEW表中的所有记录*/
TRUNCATETABLECOURSE_NEW;
/*往COURSE_NEW表中增加以下记录：
NO
COURSE_NAME
A001
ORACLE数据库管理
A002
SQLSERVER安全指南
A003
Hibernate全攻略
A004
.NET
*/
INSERTINTOCOURSE_NEW（NO,COURSE_NAME）
SELECT'
A001'
'
ORACLE数据库管理'
FROMDUAL
UNION
A002'
SQLSERVER安全指南'
A003'
Hibernate全攻略'
UNION
A004'
.NET'
FROMDUAL;
COMMIT;
/*根据COURSE表中的NO字段，用COURSE_NEW更新COURSE表*/
UPDATE
（SELECT/*+BYPASS_UJVC*/A.NO,A.COURSE_NAME,B.NOASBNO,B.COURSE_NAMEASBNAME
FROMCOURSEA,COURSE_NEWB
WHEREA.NO=B.NO）
SETNO=BNO,COURSE_NAME=BNAME
/*分别使用INSERT/UPDATE和MERGE命令实现，用COURSE_NEW更新COURSE表中的记录，如果存在，则更新，不存在则INSERT*/
--用MERGE实现如下：
INSERTINTOCOURSE_NEW（NO,COURSE_NAME）--为了便于操作，先在COURSE_NEW中插入一条记录
A005'
HCNE网络工程师'
MERGEINTOCOURSEA
USINGCOURSE_NEWBON（A.NO=B.NO）
WHENMATCHEDTHEN
UPDATESETA.COURSE_NAME=B.COURSE_NAME
WHENNOTMATCHEDTHEN
INSERT（A.NO,A.COURSE_NAME）
VALUES（B.NO,B.COURSE_NAME）;
--用INSERT/UPDATE实现如下
INSERTINTOCOURSE_NEW（NO,COURSE_NAME）--为了便于操作，再在COURSE_NEW中插入一条记录
A006'
CCNA网络工程师'
--利用UPDATE对于编号相同的字段进行更新
UPDATECOURSEASET（NO,COURSE_NAME）=
（SELECTB.NO,B.COURSE_NAME
FROMCOURSE_NEWB
WHEREA.NO=B.NO）
WHERE
EXISTS
（SELECT1FROMCOURSE_NEWB
WHEREA.NO=B.NO）;
--利用INSERT对于原表中没有的进行添加
INSERTINTOCOURSE
SELECT*FROMCOURSE_NEWA
WHERENOTEXISTS（SELECT1FROMCOURSEBWHEREA.NO=B.NO）;
/*用一组语句代替对实现对全表的更新操作*/
--先用COURSE_TEST记录COURSE表的状态，以便在删除COURSE之后记录相关字段
CREATETABLECOURSE_TESTASSELECT*FROM
COURSE
WHEREROWNUM<
1;
INSERT/*+APPEND*/INTOCOURSE_TEST
SELECT*FROMCOURSEWHEREROWNUM<
5;
--删除COURSE表
TRUNCATETABLECOURSE;
--更新COURSE表
INSERT/*+APPEND*/INTOCOURSE
SELECTA.NO,A.COURSE_NAMEFROMCOURSE_NEWA,COURSE_TESTBWHEREA.NO=B.NO;
升级会员 