5. Oracle之存储过程和MERGE INTO语句

一、merge into语句

　　1、merge into语句的功能：我们操作数据库的时候，有时候会遇到insert或者update这种需求。我们操纵代码时至少需要写一个插入语句和更新语句并且还得单独写方法效验数据是否存在，这种操作完全可以用merge into语句代替，不仅省时省力而且条理更清晰，一个sql语句直接完成插入，如果有相同主键进行更新操作。

使用场景：判断ｂ表和ａ表是否满足on中条件，如果满足则用b表去更新a表，如果不满足，则将b表数据插入a表或者更多的操作。

　　2、具体sql：下边sql是我在工作中最常使用的，功能是对接口表（表b）中通过批次id查到的合同进行对正式表（表a）插入和更新。除此之外，还可以根据你的想实现功能进行各种条件更新和插入。只update或者只insert，带条件的update或带条件的insert，全插入insert实现，带delete的update(觉得可以用3来实现)

merge into后是更新的表，using是对接口表进行筛选，（如果有重复数据，仅选取一行插入，用order by 控制）。on中是具体的条件（表中标识字段，字段编码）满足执行 when matched then 下的语句
不满足则执行when not matched then 后语句:

merge into tablea a 
using (
    (select l.*,
                  row_number() over(partition by t.flex_value order by 1) as rn
    from tableb l
    where t.batch_id = #{batchid} ) l
    and l.rn = 1 )  b
on ( a.flex_value = b.flex_value )
 when matched then
  update 

a.flex_value_set_name = b.flex_value_set_name,
a.validation_type = b.validation_type,

when not matched then
  insert (

a.flex_value_set_name = b.flex_value_set_name,
a.validation_type = b.validation_type)

二、oracle的存储过程

　　1、定义：存储过程(stored procedure):就是一组用于完成特定数据库功能的sql语句集，该sql语句集经过,编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数,来调用并执行它，从而完成一个或一系列的数据库操作。

　　2、创建：oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

我在工作中常用的一个存储过程结构如下：

--存储过程校验信息，三个入参，一个输入批次。输出分别是错误编码，和错误信息。

　　procedure validate_archive_itf(p_batchid in varchar2,
　　　　　　　　　　　　　　　　　　　　p_flag out number,
　　　　　　　　　　　　　　　　　　　　p_msg out varchar2) is
　　　　　　　　　cursor cms_archive_itf(batchid varchar2) is
　　　　　　select rowid,
　　　　　　　　contract_no,
　　　　　　　　archive_stutas,
　　　　　　　　error_info,
　　　　　　　　arc_time
　　　　　　from cms_archive_ift cai
　　　　　　where cai.batch_id = batchid;
　　l_error_msg varchar2(255); --定义变量错误信息
　　l_tenant_id varchar2(255);--定义变量租户id
　　l_contract_serial_no varchar2(255);--定义变量
begin
　　　　for ret in cms_archive_itf(p_batchid) loop
　　　　l_error_msg := null;--给传入三个参数赋默认值
　　　　p_flag := 1;
　　　　p_msg := null;

　　--对输入字段非空效验　　
　　if (ret.arc_time is null or ret.arc_time = '') then
　　　　l_error_msg := l_error_msg || 'last_update_date不能为空;';
　　end if;
　　--判断非空校验是否成功，不成功继续继续下一个。如果有错误更新接口表
　　if l_error_msg is not null then
　　p_flag := -99;
　　update cms_archive_ift
　　set error_code = '01', error_msg = l_error_msg
　　where rowid = ret.rowid;
　　continue;
　　end if;
　　--如果成功通过条件效验
　　if p_flag = 1 then
　　begin
　　insert into cms_archive_info
　　　　(tenant_id,
　　　　contract_id,
　　　　archive_id)
　　　　values
　　　　　　( ret.tenant_id,
　　　　　　　(select contract_id
　　　　　　　　from cms_contract_info
　　　　　　　　where contract_no = ret.contract_no),
　　　　　　　　sys_guid() )

　　　　　　exception--异常信息
　　　　　　when others then
　　　　　　dbms_output.put_line(substr(sqlerrm, 1, 200));
　　　　　　p_flag := -99;
　　　　　　p_msg := substr(sqlerrm, 1, 200);
　　　　　　end;
　　　　　　end if;
　　　　　　end loop;
　　　　　　end;