数组循环判断例子

create or replace function checksectionidisperm
(in_SectionId in number,sqlstr in varchar2)
return number is
 
  TYPE idarray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  Result number; --反回值 true 表示该包区的所有网格都在权限网格范围之内

  v_acc_nbr number(10);
  perm_grid_ids idarray;
  section_grid_ids idarray;
  tempCount number:=1;
  tempflag boolean;
  errmsg varchar2(2000);
  type ref_cursor is ref cursor;
  v_cursor ref_cursor;
begin
  Result:=0;
  --根据传过来的sqlstr获取所有有权限的网格ID

  open v_cursor for sqlstr;
  loop
    fetch v_cursor into v_acc_nbr;
        exit when v_cursor%notfound;
        perm_grid_ids(tempCount) := v_acc_nbr;
        tempCount := tempCount +1;
  end loop;
  close v_cursor;
  --如果权限网格ID为空则直接反回false

  if(perm_grid_ids.count=0) then
      goto label_end;
  end if;
  --根据包区ID获取该包区下的所有网格

  tempCount := 1;
  open v_cursor for 'select r.grid_id from TB_PTY_GRID_SECTION_REL r where r.section_id = '||in_SectionId;
  loop
    fetch v_cursor into v_acc_nbr;
        exit when v_cursor%notfound;
        section_grid_ids(tempCount):=v_acc_nbr;
        tempCount := tempCount +1;
  end loop;
  close v_cursor;
  --判断包区的网格是不是都在权限网格之内

  if(section_grid_ids.count>0) then
          for i in 1..section_grid_ids.count loop
              Result:=1;
              tempflag:=false;
              for j in 1..perm_grid_ids.count loop
                if(section_grid_ids(i)=perm_grid_ids(j)) then
                    tempflag:=true;
                    exit;
                end if;
              end loop;
              if(tempflag=false) then
                 Result:=0;
                 goto label_end;
              end if;
          end loop;
  else
       Result:=0;
  end if;
  <<label_end>>
  return Result;
  exception when others then
      if(v_cursor%isopen) then
        close v_cursor;
      end if;
  errmsg := 'ky' || sqlerrm;
  --dbms_output.put_line('ky' || sqlerrm);

  return 0;
end checksectionidisperm;


注意对比  数组的两种用法

--创建了一个数组

CREATE OR REPLACE TYPE VARCHAR_ARRAY AS table OF varchar2(300)

create or replace function checksectionidisperm
(in_SectionId in number,sqlstr in varchar2)
return number is
 
  --TYPE idarray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  Result number; --反回值 true 表示该包区的所有网格都在权限网格范围之内

  v_acc_nbr number(10);
  perm_grid_ids VARCHAR_ARRAY;
  section_grid_ids VARCHAR_ARRAY;
  tempCount number:=1;
  tempflag boolean;
  errmsg varchar2(2000);
  type ref_cursor is ref cursor;
  v_cursor ref_cursor;
begin
  Result:=0;
  perm_grid_ids := VARCHAR_ARRAY();   --和上面的对比
  section_grid_ids:=VARCHAR_ARRAY();
  --根据传过来的sqlstr获取所有有权限的网格ID

  open v_cursor for sqlstr;
  loop
    fetch v_cursor into v_acc_nbr;
        exit when v_cursor%notfound;
        perm_grid_ids.extend;        --和上面的对比
        perm_grid_ids(tempCount) := v_acc_nbr;
        tempCount := tempCount +1;
  end loop;
  close v_cursor;
  --如果权限网格ID为空则直接反回false

  if(perm_grid_ids.count=0) then
      goto label_end;
  end if;
  --根据包区ID获取该包区下的所有网格

  tempCount := 1;
  open v_cursor for 'select r.grid_id from TB_PTY_GRID_SECTION_REL r where r.section_id = '||in_SectionId;
  loop
    fetch v_cursor into v_acc_nbr;
        exit when v_cursor%notfound;
        section_grid_ids.extend;
        section_grid_ids(tempCount):=v_acc_nbr;
        tempCount := tempCount +1;
  end loop;
  close v_cursor;
  --判断包区的网格是不是都在权限网格之内

  if(section_grid_ids.count>0) then
          for i in 1..section_grid_ids.count loop
              Result:=1;
              tempflag:=false;
              for j in 1..perm_grid_ids.count loop
                if(section_grid_ids(i)=perm_grid_ids(j)) then
                    tempflag:=true;
                    exit;
                end if;
              end loop;
              if(tempflag=false) then
                 Result:=0;
                 goto label_end;
              end if;
          end loop;
  else
       Result:=0;
  end if;
  <<label_end>>
  return Result;
  exception when others then
      if(v_cursor%isopen) then
        close v_cursor;
      end if;
  errmsg := 'ky' || sqlerrm;
  --dbms_output.put_line('ky' || sqlerrm);

  return 0;
end checksectionidisperm;