/********************************************************************************************
-- Title : [ORA10g] 동적 쿼리 생성하는 함수 예
-- Key word : dynamic query function
********************************************************************************************/
/****************
-- CASE I
****************/
CREATE OR REPLACE function MyMaster.fn_get_query
return varchar2
authid current_user
as
iQuery varchar2(4000);
iRowcnt number;
cursor cur_get_dt_cols
is
select row_number() over(order by s_end_dt) as seq
, s_end_dt as dt
from MyMaster.s_rqst_list
where s_prcs_stts_cd in ('1','3')
group by s_end_dt;
begin
select count(*) into iRowcnt
from (select s_end_dt "dt"
from MyMaster.s_rqst_list a
where s_prcs_stts_cd in ('1','3')
group by s_end_dt
) a;
iQuery := 'select case when tech is null then ''총합계'' '
|| ' else (select to_char(s_cd_cntnt) from MyMaster.s_cd_dic '
|| ' where s_cd_sect = ''CD016'' and s_cd_num = a.tech) '
|| ' end "기술분야" '
|| ' , case when enum is null then ''-'' '
|| ' else (select to_char(s_emp_nm) from MyMaster.s_emp_info '
|| ' where s_emp_num = a.enum) '
|| ' end "작업자" ';
for i in cur_get_dt_cols loop
iQuery := iQuery || ' , sum(case when dt = ''' || i.dt || ''' then 1 else 0 end) "' || i.dt || '"';
end loop;
for j in cur_get_dt_cols loop
if j.seq = 1 then
iQuery := iQuery || ' , sum(case when dt = ''' || j.dt || ''' then 1 else 0 end) ';
elsif j.seq = iRowcnt then
iQuery := iQuery || ' + sum(case when dt = ''' || j.dt || ''' then 1 else 0 end) "총합계" ';
else
iQuery := iQuery || ' + sum(case when dt = ''' || j.dt || ''' then 1 else 0 end) ';
end if;
end loop;
iQuery := iQuery || 'from ( '
|| ' select s_srch_tech_fld_cd tech '
|| ' , s_emp_num enum '
|| ' , s_end_dt dt '
|| ' from MyMaster.s_rqst_list '
|| ' where s_prcs_stts_cd in (''1'',''3'') '
|| ' group by s_srch_tech_fld_cd, s_emp_num, s_end_dt '
|| ' order by s_srch_tech_fld_cd, s_emp_num, s_end_dt '
|| ' ) a '
|| 'group by grouping sets((tech), (tech, enum), ()) '
|| 'order by tech, enum; ';
return iQuery;
exception
when no_data_found then return ('no_data_found');
when too_many_rows then return ('too_many_rows');
when others then raise;
/
/****************
-- CASE II
****************/
CREATE OR REPLACE function SCOTT.fn_dynamic_query
( afield in varchar2
, atable in varchar2
, awhere in varchar2 default null
, abind in varchar2
) return varchar2
--authid current_user
as
ivar varchar2(4000);
iquery varchar2(4000);
begin
-- exit if field is null
if afield is null then
return null;
end if;
-- exit if table is null
if atable is null then
return null;
end if;
iquery := 'select ' || afield || ' from ' || atable;
if (awhere is not null) then
iquery := iquery ||' where '|| awhere;
end if;
execute immediate iquery into ivar using abind;
return ivar;
exception
when no_data_found then return ('no_data_found');
when too_many_rows then return ('too_many_rows');
when others then return ('error');
end;
/