游标和管道函数应用实例

语法糖：

declare cursor xx is 

select +QueryTable;

begin

  open xx;

  loop

    fetch xx into 

   var_1,var_2.....var_n;//n由前面select的字段数决定

end loop;

close xx; //有始有终

end;

 

create or replace function ValidEquPartId(
       var_equ_part_str            varchar2 default '',
       var_movd_qty                number    
)
return varchar2
as
       var_count                   Integer;
       var_equ_part_id             varchar2(50);
begin
  --Format
  var_count := 0;
    
  declare cursor equ_part is
  select distinct column_value from table(str_split(translate(var_equ_part_str,'.。/，，\',',,,,,,'),','));
  begin
    Open equ_part;
    loop fetch equ_part into
      var_equ_part_id;
      exit when equ_part%notfound;
    
    select count(1) into var_count
    from (
    select distinct * from table(str_split(translate(var_equ_part_str,'.。/，，\',',,,,,,'),',')));

    
    if var_movd_qty <> var_count or var_equ_part_id is null then  --判断编号数量和领用数量是否一致
      return 'diff';
    end if;
    
    select count(1)
    into var_count
    from equ_mstr
    where 1=1
    and equ_part_id = var_equ_part_id;

    if var_count <= 0 then
      return var_equ_part_id;
    end if;
    
  end loop;
  close equ_part;
  end;

  return '';
exception
  when others then return var_equ_part_id;
end;