工作中有时会遇到用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;