pipelined函数实例

 

最近用户提供一份如下格式数据的EXCEL
地市公司 学校名称 学生数
AA公司 巴马一小 0
AA公司 NULL 0
AA公司 NULL 0
AA公司 NULL 0
AA公司 NULL 0
AA公司 NULL 500
BB公司 巴马二小 0
BB公司 NULL 0
BB公司 NULL 0
BB公司 NULL 0
BB公司 NULL 0
BB公司 NULL 255
. . .
. . .
. . .
最后需要实现的效果如下：
地市公司 学校名称 学生数
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 500
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 255
. . .
. . .
. . .
EXCEL有上万笔记录,手工处理可以实现,但需要花费长时间,
试着使用ORACLE中的pipelined函数实现,pipelined函数需要返回的是集合数据类型
所以事先我们需要创建集合.
创建对象类型
SQL> create or replace type xiaox_type as object
(
area_code varchar2(100)
,school_name varchar2(100)
,sf_number number
);
/

类型已经创建。

创建集合
SQL> create or replace type xiaox_type_test as table of xiaox_type
2 /

类型已创建。

SQL>create or replace function fn_pipeline_test
return xiaox_type_test
pipelined
is
v_xiaox_type xiaox_type:=xiaox_type(null,null,null); -- 初始化集合
v_sch_name varchar2(100);
v_sch_name_tmp varchar2(100):=null;
v_area_code varchar2(100);
v_sf number;
cursor fetch_data is select
area_code
,school_name
,sf_number
from hw_hxl.tp_hxl_xiaox_200812_cp_tmp
;
begin
open fetch_data;
fetch fetch_data into v_area_code,v_sch_name,v_sf;
while fetch_data%found loop

if v_sch_name is not null then
v_sch_name_tmp:= v_sch_name;
begin
v_xiaox_type.area_code:= v_area_code;
v_xiaox_type.school_name := v_sch_name;
v_xiaox_type.sf_number := v_sf;
end;
else
v_xiaox_type.area_code:= v_area_code;
v_xiaox_type.school_name := v_sch_name_tmp;
v_xiaox_type.sf_number := v_sf;
end if;
pipe row(v_xiaox_type);
fetch fetch_data into v_area_code,v_sch_name,v_sf;
end loop;
commit;
close fetch_data;
end
;
/
函数已创建.

调用fn_pipeline_test函数
select * from table(fn_pipeline_test);
得到如下输出效果:
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 500
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 255
. . .
. . .
. . .

注:针对以上用户提供的数据还可以使用AWK处理:
将用户提供过来的EXCEL文件另存为null.txt,
处理后的数据保存为null_deal.txt
awk -F't' 'BEGIN{OLD_SCHOOL="";}
{
if(length($2)>0) {
OLD_SCHOOL=$2;
}
printf"%st%st%sn", $1, OLD_SCHOOL, $3;
}' null.txt>null_deal.txt

test$head -12 null_deal.txt
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 0
AA公司 巴马一小 500
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 0
BB公司 巴马二小 255