oracle function 动态sql 遍历结果集

用来组织动态sql以及从动态sql查询结果集中取数

的function

--传入模型和扫描标号
create or replace function F_GET_HYNSR(V_MXDM varchar2,V_SMBH varchar2) return varchar2 is
  TYPE ref_type IS REF CURSOR;
  V_NSRHS number;
  V_HY_ZD varchar2(20);
  V_HY_DM varchar2(20);
  V_SQL_NSR varchar2(2000);
  V_SQL_HY varchar2(2000);
  V_NSRHSTEMP number;
  V_SMBHS varchar2(2000);
  cur ref_type;
begin
  V_SMBHS:=''''||REPLACE(V_SMBH,',',''',''')||'''';
  V_SQL_HY:='select wd.tj_zd,wd.tj_z  from fxfx_fxfa_wdtj wd,fxfx_sm_fxfa_dz smdz
  where wd.tj_b = ''DIM_HY'' and wd.fa_bh = smdz.fa_bh and smdz.sm_bh in ('||V_SMBHS||') and smdz.mxdm = '''||V_MXDM||'''';
  dbms_output.put_line(V_SQL_HY);
  open cur for V_SQL_HY; 
  V_NSRHS:=0;
  FETCH cur into V_HY_ZD,V_HY_DM; 
  while cur%FOUND LOOP 
  V_SQL_NSR := 'select count(1) from dim_nsr nsr where nsr.'||V_HY_ZD||'='''||V_HY_DM||'''';
  dbms_output.put_line(V_SQL_NSR);
  EXECUTE IMMEDIATE V_SQL_NSR into V_NSRHSTEMP;
  dbms_output.put_line(V_NSRHSTEMP);
  V_NSRHS:=V_NSRHS+V_NSRHSTEMP;
  dbms_output.put_line(V_NSRHS);
  FETCH cur into V_HY_ZD,V_HY_DM; 
  END LOOP;
  CLOSE cur;
  return V_NSRHS;
end F_GET_HYNSR;






--传入方案编号
create or replace function F_GET_HYNSR(V_FABH varchar2) return varchar2 is
  TYPE ref_type IS REF CURSOR;
  V_NSRHS number;
  V_HY_ZD varchar2(20);
  V_HY_DM varchar2(20);
  V_SQL_NSR varchar2(2000);
  V_NSRHSTEMP number;
  V_FABHS varchar2(2000);
  cur ref_type;
begin
  V_FABHS:=''''||REPLACE(V_FABH,',',''',''')||'''';
  --dbms_output.put_line(V_FABHS);
  open cur for 'select wd.tj_zd,wd.tj_z  from fxfx_fxfa_wdtj wd
  where wd.tj_b = ''DIM_HY'' and wd.fa_bh in ('||V_FABHS||')'; 
  V_NSRHS:=0;
  FETCH cur into V_HY_ZD,V_HY_DM; 
  while cur%FOUND LOOP 
  V_SQL_NSR := 'select count(1) from dim_nsr nsr where nsr.'||V_HY_ZD||'='''||V_HY_DM||'''';
  --dbms_output.put_line(V_SQL_NSR);
  EXECUTE IMMEDIATE V_SQL_NSR into V_NSRHSTEMP;
  --dbms_output.put_line(V_NSRHSTEMP);
  V_NSRHS:=V_NSRHS+V_NSRHSTEMP;
  --dbms_output.put_line(V_NSRHS);
  FETCH cur into V_HY_ZD,V_HY_DM; 
  END LOOP;
  CLOSE cur;
  return V_NSRHS;
end F_GET_HYNSR;