oracle中MERGE INTO方法的使用，合并两张表

工作中有时会遇到用sql将两张表数据合并的情况，今天学到了一个MERGE INTO的 方法。方法的定义如下：

 MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values);

举例：
A表中要插入B表id和”_FOLDER_ID_PERSON”拼成的字符串作为id的一条数据，如B表中一条记录id为admin，那么A表中需要有一条数据id为admin_FOLDER_ID_PERSON，那么可以这么写：

--oracle
merge into com_basicinfo A using upm_user B 
on (A.id = (B.id || '_FOLDER_ID_PERSON'))
when not matched then
  insert(id,name,owner)values(B.id ||'_FOLDER_ID_PERSON',B.name,B.id);

当然，这种需求也可以用存储过程来完成：

declare 
     cursor user_info is select u.id, u.name from upm_user u; 
     cid NVARCHAR2(64);
     num NUMBER(10);
 begin 
     for d in user_info loop 
        cid := d.id || '_FOLDER_ID_PERSON';
        select count(1) into num from com_basicinfo where id = cid;
        if (num = 0) then
        aclcontent := d.id || ',10,Co-owner';
       insert into com_basicinfo (id，name,owner) values(cid,d.name,d.id); 
       end if;
     end loop; 
 commit; 
 end;