在 平时更新数据时，经常有这样一种更新，即将目标表中的数据与源表对比，如果存在记录，则根据源表中的值更新目标表中的数据，如果不存在的话，则新增入目标 表中。我们当然可以使用两条语句来处理这类数据。但这其中有可能会出现异常。因此，Oracle在9i版本新增了MERGE语句，来合并UPDATE和 INSERT语句。 该语句可以在同一语句中执行两步操作，可以减少执行多条insert 和update语句。merge是一个确定性的语句，即不会在同一条merge语句中去对同一条记录多次做修改操作。
具体语法为：
MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
{ table | view | subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;
1.into 子句
在into子句中指定所要修改或者插入数据的目标表
2.using 子句
在using子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询语句。
3.on 子句
在on子句中指定执行插入或者修改的满足条件。在目标表中符合条件的每一行，oracle用数据源中的相应数据修改这些行。对于不满足条件的那些行，oracle则插入数据源中相应数据。
4.when matched | not matched
用该子句通知oracle如何对满足或不满足条件的结果做出相应的操作。可以使用以下的两类子句。
5.merge_update子句
merge_update子句执行对目标表中的字段值修改。当在符合on子句条件的情况下执行。如果修改子句执行，则目标表上的修改触发器将被触发。
限制：当修改一个视图时，不能指定一个default值
6.merge_insert 子句
merge_insert子句执行当不符合on子句条件时，往目标表中插入数据。
下面列出merge的基本用法 （此处目标表和源表表结构相同）
1) matched 和not matched 同时使用
merge into 目标表 a
using 源表 b on (关联条件 a.字段1 = b.字段1)
when MATCHED then
update set a.字段2=b.字段2,......
when NOT MATCHED then
insert(a.字段2,a.字段3)
values(b.字段2,b.字段3);
2) 只有not matched clause,也就是只插入不更新
merge into 目标表 a
using 源表b on (关联条件 a.字段1 = b.字段1)
when NOT MATCHED then
insert(a.字段2,a.字段3)
values(b.字段2,b.字段3);
3) 只有matched clause, 也就是只更新不插入
merge into 目标表 a
using 源表 b on (关联条件 a.字段1 = b.字段1)
when MATCHED then
update set a.字段2=b.字段2,......
另
外，merge命令的update部分可以包含一个delete子句。delete子句（有其自己的where子句）可以删除目标表中被merge更新的
行。delete...where子句可以计算更新值，而不是目标表中的初始值。如果目标表中的行符合delete...where条件但不在merge
所作用（就如on条件所定义的）的行集范围内，就不会删除。
具体语法为：
using 源表 b on (关联条件 a.字段1 = b.字段1)
when MATCHED then
update set a.字段2=b.字段2,......
delete where (a.字段5=b.字段5) ;
注意：此处的源表可以不仅仅是一张表，也可以是查询出来的结果集。此命令在db2中也同样适用。
首先创建示例表：
create table PRODUCTS
(
PRODUCT_ID
INTEGER,
PRODUCT_NAME
VARCHAR2(60),
CATEGORY
VARCHAR2(60)
);
insert into PRODUCTS values (1501, 'VIVITAR 35MM',
'ELECTRNCS');
insert into
PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into
PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into
PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into
PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;
create table NEWPRODUCTS
(
PRODUCT_ID
INTEGER,
PRODUCT_NAME
VARCHAR2(60),
CATEGORY
VARCHAR2(60)
);
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA',
'ELECTRNCS');
insert into
NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into
NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into
NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;
1、可省略的UPDATE或INSERT子句
在Oracle 9i,
MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g,
你可以省略UPDATE或INSERT子句中的一个.
下面的例子根据表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:
SQL> MERGE INTO products
p
2 USING
newproducts np
3 ON
(p.product_id = np.product_id)
4 WHEN
MATCHED THEN
5
UPDATE
6 SET
p.product_name = np.product_name,
7 p.category
= np.category;
3 rows merged.
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
----------
-------------------- ----------
1501 VIVITAR
35MM ELECTRNCS
1502 OLYMPUS
CAMERA ELECTRNCS
1600 PLAY
GYM TOYS
1601 LAMAZE
TOYS
1666 HARRY
POTTER TOYS
SQL>
SQL> ROLLBACK;
Rollback
complete.
SQL>
在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行.
它们的产品名字和种 类被更新为表newproducts中的值. 下面例子省略UPDATE子句,
把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中,
对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理.
从这个例子你能看到PRODUCT_ID=1700的行被插入到表PRODUCTS中.
SQL> MERGE INTO products
p
2 USING
newproducts np
3 ON
(p.product_id = np.product_id)
4 WHEN NOT
MATCHED THEN
5
INSERT
6 VALUES
(np.product_id, np.product_name,
7
np.category);
1 row merged.
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
----------
-------------------- ----------
1501 VIVITAR
35MM ELECTRNCS
1502 OLYMPUS
IS50 ELECTRNCS
1600 PLAY
GYM TOYS
1601 LAMAZE
TOYS
1666 HARRY
POTTER DVD
1700 WAIT
INTERFACE BOOKS
2、带条件的Updates和Inserts子句
你能够添加WHERE子句到UPDATE或INSERT子句中去,
来跳过update或insert操作对某些行的处理. 下面例子根据表NEWPRODUCTS来更新表PRODUCTS数据,
但必须字段CATEGORY也得同时匹配上:
SQL> MERGE INTO products
p
2 USING
newproducts np
3 ON
(p.product_id = np.product_id)
4 WHEN
MATCHED THEN
5
UPDATE
6 SET
p.product_name = np.product_name
7 WHERE
p.category = np.category;
2 rows merged.
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
----------
-------------------- ----------
1501 VIVITAR
35MM ELECTRNCS
1502 OLYMPUS
CAMERA ELECTRNCS
1600 PLAY
GYM TOYS
1601 LAMAZE
TOYS
1666 HARRY
POTTER DVD
SQL>
SQL> rollback;
在这个例子中,
产品ID为1502,1601和1666匹配ON条件但是1666的category不匹配. 因此MERGE命令只更新两行数据.
下面例子展示了在Updates和Inserts子句都使用WHERE子句:
SQL> MERGE INTO products
p
2 USING
newproducts np
3 ON
(p.product_id = np.product_id)
4 WHEN
MATCHED THEN
5
UPDATE
6 SET
p.product_name = np.product_name,
7 p.category
= np.category
8 WHERE
p.category = 'DVD'
9 WHEN NOT
MATCHED THEN
10
INSERT
11 VALUES
(np.product_id, np.product_name, np.category)
12 WHERE
np.category != 'BOOKS'
SQL> /
1 row merged.
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
----------
-------------------- ----------
1501 VIVITAR
35MM ELECTRNCS
1502 OLYMPUS
IS50 ELECTRNCS
1600 PLAY
GYM TOYS
1601 LAMAZE
TOYS
1666 HARRY
POTTER TOYS
SQL>
注意由于有WHERE子句INSERT没有插入所有不匹配ON条件的行到表PRODUCTS.
3、无条件的Inserts
你能够不用连接源表和目标表就把源表的数据插入到目标表中.
这对于你想插入所有行到目标表时是非常有用的. Oracle 10g现在支持在ON条件中使用常量过滤谓词. 举个常量过滤谓词例子ON
(1=0). 下面例子从源表插入行到表PRODUCTS, 不检查这些行是否在表PRODUCTS中存在:
SQL> MERGE INTO products
p
2 USING
newproducts np
3 ON
(1=0)
4 WHEN NOT
MATCHED THEN
5
INSERT
6 VALUES
(np.product_id, np.product_name, np.category)
7 WHERE
np.category = 'BOOKS'
SQL> /
1 row merged.
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
----------
-------------------- ----------
1501 VIVITAR
35MM ELECTRNCS
1502 OLYMPUS
IS50 ELECTRNCS
1600 PLAY
GYM TOYS
1601 LAMAZE
TOYS
1666 HARRY
POTTER DVD
1700 WAIT
INTERFACE BOOKS
6 rows
selected.
SQL>
4、新增加的DELETE子句
Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN
MATCHED THEN UPDATE子句中包含DELETE子句.
DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE
WHERE条件但不匹配ON条件的行不会被从表中删除.
下面例子验证DELETE子句.
我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行.
SQL> MERGE INTO products
p
2 USING
newproducts np
3 ON
(p.product_id = np.product_id)
4 WHEN
MATCHED THEN
5
UPDATE
6 SET
p.product_name = np.product_name,
7 p.category
= np.category
8 DELETE
WHERE (p.category = 'ELECTRNCS')
9 WHEN NOT
MATCHED THEN
10
INSERT
11 VALUES
(np.product_id, np.product_name, np.category)
SQL> /
4 rows merged.
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
----------
-------------------- ----------
1501 VIVITAR
35MM ELECTRNCS
1600 PLAY
GYM TOYS
1601 LAMAZE
TOYS
1666 HARRY
POTTER TOYS
1700 WAIT
INTERFACE BOOKS
SQL>
产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE
WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700
的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE
WHERE条件, 所以被更新为表NEWPRODUCTS中的值.
**************************************************************************************************
定义游标类型
CREATE OR REPLACE PACKAGE 包名 IS
创建作者：
创建日期：
功能描述：定义ref cursor变量类型
实现逻辑：
TYPE TYPE_CURSOR IS REF
CURSOR; --定义游标变量
包体：
CREATE OR REPLACE PACKAGE 包名 IS
procedure 存过名(
.........
) as
CURSOR_MODULEID TYPE_CURSOR; --模块游标
begin
OPEN CURSOR_MODULEID FOR
SELECT DISTINCT (MODULEID)
FROM SYS_USERROLE A, SYS_ROLEPERMIT B
WHERE USERID = I_USER
AND A.ROLEID = B.ROLEID;
......
end;
或
CURSOR cur_prod is
select branch,remainprinamt
from prod_daily_prin_branch
where prodid=is_prodid
and cdate = is_cdate;
begin
open cur_prod;
loop
fetch cur_prod into vs_brach,vn_prinamt;
exit when cur_prod%notfound;
--销售费
vn_sale := vn_prinamt*vn_syield/vn_basis;
merge into bm_yieldassign a
using
(select is_cdate as cdate
,vs_assetpool as assetpool
,vs_brach as branch
,0 as
assetofamt
--资产提供额
,0 as
hostamt
--托管费
,0 as
assetamt
--资产推介费
,decode(vs_mktdeptno,'L',vn_prinamt,0) as reprinamt --零售销售额
,decode(vs_mktdeptno,'L',vn_sale,0) as
resamt
--零售销售费
,decode(vs_mktdeptno,'T',vn_prinamt,0) as typrinamt --同业销售额
,decode(vs_mktdeptno,'T',vn_sale,0) as
tysamt
--同业销售费
,decode(vs_mktdeptno,'G',vn_prinamt,0) as cwprinamt --公司销售额
,decode(vs_mktdeptno,'G',vn_sale,0) as
cwsamt
--公司销售费
from dual) b
on (a.cdate = b.cdate
and a.assetpool=b.assetpool
and a.branch = b.branch)
when matched then
update set a.reprinamt =
nvl(a.reprinamt,0)+b.reprinamt,
a.resamt = nvl(a.resamt,0) + b.resamt,
a.typrinamt = nvl(a.typrinamt,0) + b.typrinamt,
a.tysamt = nvl(a.tysamt,0) + b.typrinamt,
a.cwprinamt = nvl(a.cwprinamt,0) + b.cwprinamt,
a.cwsamt = nvl(a.cwsamt,0) + b.cwsamt
when not matched then
insert(cdate,assetpool,branch
,assetofamt,hostamt,assetamt
,reprinamt,resamt
,typrinamt,tysamt
,cwprinamt,cwsamt
,lstmntuser,lstmntdate)
values(b.cdate,b.assetpool,b.branch
,b.assetofamt,b.hostamt,b.assetamt
,b.reprinamt,b.resamt
,b.typrinamt,b.tysamt
,b.cwprinamt,b.cwsamt
,is_operator,sysdate);
end
loop;
close
cur_prod;