oracle数据库函数实例(返回结果集,通过管道)
--创建返回类型
CREATE OR REPLACE TYPE
"HKF_CITY_TYPE"
AS OBJECT
(
personType varchar2(50),
city1 number,
city2
number,
city3 number,
city4 number,
city5 number,
city6
number,
city7 number,
city8 number,
city9 number,
city10
number,
city11 number,
city12 number,
city13 number,
city14
number,
city15 number,
city16 number,
city17 number,
city18
number,
city19 number,
city20 number,
city21 number,
city22
number,
city23 number,
city24 number,
city25 number,
c_sum
number
);
---创建符合类型
CREATE OR REPLACE TYPE
"HKF_CITY_UNIT"
AS TABLE OF hkf_city_type;
---创建函数
create or replace function
f_hkf_busDevelop(
beginTime in varchar2,
endTime in
varchar2)
return hkf_city_unit PIPELINED
as
num_tmp number;
num_tmp2 number := 0;
num_tmp3 number;
sum_num number := 0;
str_tmp1 varchar2(50);
str_tmp2 varchar2(50);
str_sql
varchar2(5000);
str_cond varchar2(120):='';
--万能游标
cur_ref
sys_refcursor;
--定义数组类型,INDEX BY BINARY_INTEGER表示以数字为索引的数组
type
tmp_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
my_array
tmp_array;
begin
if beginTime is not null then
str_cond
:= str_cond || ' and rd.stat_date >= to_date(''' || beginTime ||
''',''yyyy-mm-dd'')';
end if;
if endTime is not null
then
str_cond := str_cond || ' and rd.stat_date <= to_date(''' ||
EndTime || ''',''yyyy-mm-dd'')';
end if;
select count(1)
into num_tmp from sys_organise_info oi where length(oi.organise_code) =
8;
--select 25 into num_tmp from dual;
if num_tmp <=0
then
pipe row(null);
return;
end if;
str_sql := ' select ut.user_type_name,
oi.organise_name,
aa.c
from sys_organise_info oi,
sys_organsie_level_assist la,
sys_user_type
ut,
(select nvl(a.c,0)
c,
b.organise_code,
b.type_code
from (select count(distinct
rd.emp_no) c,
rd.organise_code4,
rd.user_typecode
from stat_recomend_day
rd
where length(rd.organise_code4) =
8
and rd.open_suc_total != 0 ' ||
str_cond || '
group by
organise_code4,user_typecode) a,
(select
oi.organise_code,
ut.type_code
from sys_organise_info
oi,
sys_user_type
ut
where length(oi.organise_code) = 8)
b
where a.organise_code4(+) =
b.organise_code
and a.user_typecode(+)
= b.type_code) aa
where aa.organise_code =
oi.organise_code
and oi.organise_id =
la.organsie_id
and aa.type_code =
ut.type_code
order by
ut.type_code,la.sortnum';
open cur_ref for str_sql;
loop
fetch cur_ref into str_tmp1,str_tmp2,num_tmp3;
exit
when cur_ref%notfound;
num_tmp2 := (num_tmp2 + 1);
sum_num
:= sum_num + num_tmp3;
my_array(num_tmp2) := num_tmp3;
if
num_tmp2 = num_tmp then
for num_tmp4 in num_tmp2 + 1 .. 25
loop
my_array(num_tmp4) := 0;
end loop;
-- 各个局点根据地市的数量，来填写下面。因为hkf_city_type对象的列数量生成是根据
-- select count(1)
from sys_orgnaise_info where length(organise_code) = 8.
--通过管道返回多行
pipe
row(hkf_city_type(str_tmp1,
my_array(1),my_array(2),my_array(3),my_array(4),my_array(5),
my_array(6),my_array(7),my_array(8),my_array(9),my_array(10),
my_array(11),my_array(12),my_array(13),my_array(14),my_array(15),
my_array(16),my_array(17),my_array(18),my_array(19),
my_array(20),my_array(21),my_array(22),my_array(23),my_array(24),my_array(25),sum_num));
num_tmp2 := 0;
sum_num := 0;
end if;
end
loop;
close cur_ref;
num_tmp2 := 0;
sum_num :=
0;
str_sql := 'select ''合计'',
oi.organise_name,
nvl(a.c,0)
c
from (select count(distinct rd.emp_no) c,
rd.organise_code4
from stat_recomend_day
rd
where length(rd.organise_code4) = 8 ' ||
str_cond || '
and rd.open_suc_total !=
0
group by organise_code4)
a,
sys_organise_info oi,
sys_organsie_level_assist la
where a.organise_code4(+) =
oi.organise_code
and oi.organise_id =
la.organsie_id
and length(oi.organise_code) =
8
order by la.sortnum';
open cur_ref for
str_sql;
loop
fetch cur_ref into
str_tmp1,str_tmp2,num_tmp3;
exit when cur_ref%notfound;
num_tmp2 := (num_tmp2 + 1);
sum_num := sum_num + num_tmp3;
my_array(num_tmp2) := num_tmp3;
if num_tmp2 = num_tmp
then
for num_tmp4 in num_tmp2 + 1 .. 25 loop
my_array(num_tmp4) := 0;
end loop;
--
各个局点根据地市的数量，来填写下面。因为hkf_city_type对象的列数量生成是根据
-- select count(1)
from sys_orgnaise_info where length(organise_code) = 8.
pipe
row(hkf_city_type(str_tmp1,
my_array(1),my_array(2),my_array(3),my_array(4),my_array(5),
my_array(6),my_array(7),my_array(8),my_array(9),my_array(10),
my_array(11),my_array(12),my_array(13),my_array(14),my_array(15),
my_array(16),my_array(17),my_array(18),my_array(19),
my_array(20),my_array(21),my_array(22),my_array(23),my_array(24),my_array(25),sum_num));
num_tmp2 := 0;
sum_num := 0;
end if;
end
loop;
close cur_ref;
exception
when others
then
dbms_output.put_line(sqlerrm || 'str_sql = ' ||
str_sql);
pipe row(null);
end;
