--在机构库表中根据机构代码查询如果可以查询到就把机构名称更新成最新的,如果查询不到就直接做插入操作。
begin
update test_institution set INSTITUTION_NAME='中石油资产有限公司' where institution_code ='T0001';
if sql%notfound then
insert into test_institution (OBJID, INSTITUTION_VIP, INSTITUTION_CODE, INSTITUTION_TYPE, INSTITUTION_STATUS, INSTITUTION_NAME, CREATETIMESTAMP, UPDATETIMESTAMP, VERSION) values ((select max(objid)+1 from test_institution), 1, 'T0001', '0', 0, '中石油资产有限公司', sysdate, sysdate, 0);
end if;
end;
/
特别注意:
如果是很多个sql要一起执行中间要用/分割开来,例如下面的:
begin
update test_institution set INSTITUTION_NAME='中石油资产有限公司' where institution_code ='T0001';
if sql%notfound then
insert into test_institution (OBJID, INSTITUTION_VIP, INSTITUTION_CODE, INSTITUTION_TYPE, INSTITUTION_STATUS, INSTITUTION_NAME, CREATETIMESTAMP, UPDATETIMESTAMP, VERSION) values ((select max(objid)+1 from test_institution), 1, 'T0001', '0', 0, '中石油资产有限公司', sysdate, sysdate, 0);
end if;
end;
/
begin
update test_institution set INSTITUTION_NAME='中石油资产有限公司2' where institution_code ='T0002';
if sql%notfound then
insert into test_institution (OBJID, INSTITUTION_VIP, INSTITUTION_CODE, INSTITUTION_TYPE, INSTITUTION_STATUS, INSTITUTION_NAME, CREATETIMESTAMP, UPDATETIMESTAMP, VERSION) values ((select max(objid)+1 from test_institution), 1, 'T0002', '0', 0, '中石油资产有限公司2', sysdate, sysdate, 0);
end if;
end;
/