package body ted_p1104 is
function deptname_table_piped_v
( p_n_min in ted_p1104_v.n%type := null
, p_n_max in ted_p1104_v.n%type := null
, p_deptno in emp.deptno%type := null
, p_sort in varchar2 := null
, p_limit in number := null
)
return ted_p1104_v_tab
pipelined
is
c_lf constant varchar2(2) := chr(13) || chr(10);
c_sql constant varchar2(32767) :=
'select *
from ted_p1104_v
where 1 = 1'
|| c_lf
;
c_limit_in constant pls_integer := 25;
v_sql varchar2(32767) := c_sql;
r_cur sys_refcursor;
v_cur_count pls_integer := 0;
v_ted_p1104_v_tab ted_p1104_v_tab;
begin
if p_n_min is not null then
v_sql := v_sql || 'and n >= :p_n_min' || c_lf;
else
v_sql := v_sql || 'and (1=1 or :p_n_min is null)' || c_lf;
end if;
if p_n_max is not null then
v_sql := v_sql || 'and n <= :p_n_max' || c_lf;
else
v_sql := v_sql || 'and (1=1 or :p_n_max is null)' || c_lf;
end if;
if p_deptno is not null then
v_sql := v_sql || 'and dname = (select dname from dept where deptno = :p_deptno)' || c_lf;
else
v_sql := v_sql || 'and (1=1 or :p_deptno is null)' || c_lf;
end if;
if p_sort is not null then
v_sql := v_sql || 'order by ' || p_sort || c_lf;
end if;
open r_cur for v_sql
using p_n_min
, p_n_max
, p_deptno
;
loop
fetch r_cur
bulk collect
into v_ted_p1104_v_tab
limit c_limit_in
;
exit when v_ted_p1104_v_tab.count = 0;
for i in v_ted_p1104_v_tab.first .. v_ted_p1104_v_tab.last loop
v_cur_count := v_cur_count + 1;
exit when v_cur_count > p_limit;
pipe row( v_ted_p1104_v_tab(i) );
end loop;
exit when v_cur_count > p_limit;
end loop;
close r_cur;
return;
exception
when others then
dbms_output.put_line(v_sql);
if r_cur%isopen then
close r_cur;
end if;
raise_application_error(-20000, sqlerrm);
end deptname_table_piped_v;
function deptname_table_piped_c
( p_n_min in number := null
, p_n_max in number := null
, p_deptno in emp.deptno%type := null
, p_sort in varchar2 := null
, p_limit in number := null
)
return ted_p1104_v_tab
pipelined
is
c_limit_in constant pls_integer := 25;
v_cur_count pls_integer := 0;
v_ted_p1104_v_tab ted_p1104_v_tab;
cursor c_cur is
select a.n
, d.dname
from
(
select level as n
from dual
connect by level <= 100
) a
, dept d
where a.n = d.deptno (+)
and ( a.n >= p_n_min or p_n_min is null )
and ( a.n <= p_n_max or p_n_max is null )
and ( d.dname = (select dname from dept where deptno = p_deptno) or p_deptno is null )
order by decode( lower(p_sort)
, 'n' , to_char( a.n, '00000000000' )
, 'dname', d.dname
, null
)
;
begin
open c_cur;
loop
fetch c_cur
bulk collect
into v_ted_p1104_v_tab
limit c_limit_in
;
exit when v_ted_p1104_v_tab.count = 0;
for i in v_ted_p1104_v_tab.first .. v_ted_p1104_v_tab.last loop
v_cur_count := v_cur_count + 1;
exit when v_cur_count > p_limit;
pipe row( v_ted_p1104_v_tab(i) );
end loop;
exit when v_cur_count > p_limit;
end loop;
close c_cur;
return;
exception
when others then
if c_cur%isopen then
close c_cur;
end if;
raise_application_error(-20000, sqlerrm);
end deptname_table_piped_c;
end ted_p1104;