pl/sql

 create procedure A

(batchno in varchar ) is test tb_on_ontract%rowtype;

 begin

 merge into tb_ct_tract ct

 using

(select fc.*, dij.party_id as c_party_id, dij.se_name from tb_fc_fy_tract fc, tb_csm_ra dij where fc.create_date = batchno and fc.customer_num = dij.customer_num(+)) ic on (ct.tract_n = ic.tract_n)

 

这个一个神奇的更新语句，比普通的update语句性能要好的多。。。详细的意思：MERGE --USING--ON--。。。这个是基本的语法结构，写merge语句必须的写的关键字。。具体说明上面的存储过程：

1.select fc.*, dij.party_id as c_party_id, dij.se_name from tb_fc_fy_tract fc, tb_csm_ra dij where fc.create_date = batchno and fc.customer_num = dij.customer_num(+)

这句是查询出的结果作为数据源去更新tb_ct_tract 表用，查询出的结果用ic作为的临时表名，

batchno是你执行存储过程的时候传进来的数值，

fc.customer_num = dij.customer_num(+)，是一个右连接，是指把fc.customer_num = dij.customer_num相同的记录列出来后，还要列出dij表中多余的customer_num2.把merge简化就变成了通过ic表（上面已经解释）的值去更新tb_ct_tract 表，条件就是(ct.tract_n = ic.tract_n)；

3.顺便说一下create procedure A(batchno in varchar ) is test tb_on_ontract%rowtype; begin这都是创建存储过程的基本语法结构，

test tb_on_ontract%rowtype;意思是定义的test类型是同tb_on_ontract%rowtype的类型一致；是不是应该制定到那个表的那个字段啊？