oracle动态sql结果集union,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;