管道函数相关_1

---create custom object
CREATE or replace TYPE unit_type AS OBJECT
( unit1 VARCHAR2(50)
,unit2 VARCHAR2(50)
,unit3 VARCHAR2(50)
,unit4 VARCHAR2(50)
);

---create table object by type Object
CREATE TYPE unit AS TABLE OF unit_type;

---create table
create table t_unit (unitname varchar2(50));

---insert into data
begin
insert into t_unit (UNITNAME) values ('a');
insert into t_unit (UNITNAME) values ('b');
insert into t_unit (UNITNAME) values ('c');
insert into t_unit (UNITNAME) values ('d');
insert into t_unit (UNITNAME) values ('e');
insert into t_unit (UNITNAME) values ('f');
insert into t_unit (UNITNAME) values ('g');
insert into t_unit (UNITNAME) values ('h');
insert into t_unit (UNITNAME) values ('i');
insert into t_unit (UNITNAME) values ('j');
insert into t_unit (UNITNAME) values ('k');
insert into t_unit (UNITNAME) values ('l');
insert into t_unit (UNITNAME) values ('m');
insert into t_unit (UNITNAME) values ('n');
insert into t_unit (UNITNAME) values ('o');
insert into t_unit (UNITNAME) values ('p');
insert into t_unit (UNITNAME) values ('r');
insert into t_unit (UNITNAME) values ('s');
insert into t_unit (UNITNAME) values ('t');
insert into t_unit (UNITNAME) values ('u');
insert into t_unit (UNITNAME) values ('v');
insert into t_unit (UNITNAME) values ('w');
insert into t_unit (UNITNAME) values ('x');
insert into t_unit (UNITNAME) values ('y');
insert into t_unit (UNITNAME) values ('z');
insert into t_unit (UNITNAME) values ('q');
insert into t_unit (UNITNAME) values ('1');
insert into t_unit (UNITNAME) values ('2');
insert into t_unit (UNITNAME) values ('3');
insert into t_unit (UNITNAME) values ('4');
commit;
end;

 

---check data
select * from t_unit;

---create pipe function , important step
create or replace function autolinefeed
return unit PIPELINED
as
temp_str varchar2(32767):='';
temp_index number:=0;
temp_count number:=0;
temp_sum number:=0;
temp_unit1 varchar2(50);
temp_unit2 varchar2(50);
temp_unit3 varchar2(50);
temp_unit4 varchar2(50);
begin
select count(*) into temp_sum from t_unit;
if temp_sum<=0 then
pipe row(null);
return;
end if;
for v_unit in (select * from t_unit) loop
temp_count:=(temp_count+1);
if (mod(temp_count,4)=1) then
temp_unit1 :=v_unit.unitname;
elsif (mod(temp_count,4)=2) then
temp_unit2 :=v_unit.unitname;
elsif (mod(temp_count,4)=3) then
temp_unit3 :=v_unit.unitname;
else
temp_unit4 :=v_unit.unitname;
end if;
if (mod(temp_count,4)=0) then
pipe row(unit_type(temp_unit1,temp_unit2,temp_unit3,temp_unit4));
else
if (temp_sum=temp_count) then
if (mod(temp_count,4)=1) then
pipe row(unit_type(temp_unit1,'','',''));
elsif (mod(temp_count,4)=2) then
pipe row(unit_type(temp_unit1,temp_unit2,'',''));
elsif (mod(temp_count,4)=3) then
pipe row(unit_type(temp_unit1,temp_unit2,temp_unit3,''));
else
pipe row(unit_type(temp_unit1,temp_unit2,temp_unit3,temp_unit4));
end if;
end if;
end if;
end loop;
exception
when others then
pipe row(null);
end;

 

select * from table(autolinefeed);