https://www.cnblogs.com/xiaoqisfzh/p/5620890.html
translate函数
create or replace procedure PRO_CAR_LOTTORY_PRODUCT(return_code out varchar2) is
exe exception;
num number;
--把服务机构替换为,
cursor rec is
select p.gift_no,p.pub_org,length(translate(p.service_area,','||p.service_area,','))+1 len from picc_gift_info p
left join admin_auth_org q on q.org_id=p.pub_org where q.org_level='2';
begin
begin
for x in rec loop
select count(a.org_code) into num from picc_org_area_info_scl a where a.p_org_code = x.pub_org ;
if num <= x.len then
update picc_gift_info r set r.is_all = '1' where r.gift_no = x.gift_no;
else
update picc_gift_info r set r.is_all = '2' where r.gift_no = x.gift_no;
end if;
end loop;
commit;
end;
return_code := '00';
end PRO_CAR_LOTTORY_PRODUCT;