Oracle常用函数(3)

注意:管道的方式必须使用空的return表示结束. 调用函数的方式如下：

select * from table(f_pipe(5));

2)、 普通的方式:

create or replace function f_normal(s number) return t_table as

rs t_table:= t_table(); begin

for i in 1..s loop rs.extend;

rs(rs.count) := obj_table(rs.count,'name'||to_char(rs.count));

--rs(rs.count).name := rs(rs.count).name || 'xxxx'; end loop; return rs; end f_normal;

初始化值后还可以想注视行那样进行修改. 调用方式如下:

select * from table(f_normal(5));

create table tb1(k number, v varchar2(10)); insert into tb1(k, v) values(100,'aaa'); insert into tb1(k, v) values(200,'bbb'); insert into tb1(k, v) values(200,'ccc'); select * from tb1;

create type row_type1 as object(k number, v varchar2(10)); create type table_type1 as table of row_type1;

create or replace function fun1 return table_type1 pipelined as v row_type1; begin

for myrow in (select k, v from tb1) loop v := row_type1(myrow.k, myrow.v); pipe row (v); end loop; return; end;

/**

oStr := '

insert into ZZ_GETMONTHACCUMULATION(oildomid,month,year) select oildomid

,'''||TenDaysTableColMonthName||''' ,'''||TenDaysTableColYearName||''' from

'''||TenDaysTableName||''' where

reportdate = '''||Reportdate;

oReturnTable.extend;

oReturnTable(oReturnTable.count).oOUID := '1'; oReturnTable(oReturnTable.count).oMonth := '1'; oReturnTable(oReturnTable.count).oYear := '1'; ***/