1.首先要创建一个类型集合；
create or replace type row_month_report as object---声明一种类型
(
m_month varchar2(30),
m_SluiceName varchar2(30),
m_OneSluiceCount number,
m_TwoSluiceCount number,
m_OneUpEmptyCount number,
m_OneDownemptyCount number,
m_TwoUpemptyCount number,
m_TwoDownemptyCount number,
------------------------这是写要返回的字段集变量
)
2. create or replace type table_month_report as table of row_month_report;-----把类型当作表使用
3. 编写Oracle 方法：
create or replace function fun_MontnReport(SLUICEID IN VARCHAR2,
D in VARCHAR2) return table_month_report ---返回自定义的类型
pipelined as
vv row_month_report;
m_SluiceName varchar2(30);
daychar VARCHAR2(50); --日期
yearchar VARCHAR2(50); --年份
m_OneSluiceCount number := 0;
m_TwoSluiceCount number := 0;
m_OneUpEmptyCount number := 0;
m_OneDownemptyCount number := 0;
m_TwoUpemptyCount number := 0;
m_TwoDownemptyCount number := 0;
begin
---查询语句，并把值赋给变量如：
select nvl(sum(t.SLUICECOUNT), 0)
into m_OneSluiceCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sonsluiceoid = '1#'
and t.sluiceoid = SLUICE; --1#闸运行闸次
.....................
4.最后把查到的值赋给自定义的类型( 就相当于给表格插入数据一样）
vv:=row_month_report(
daychar,
m_SluiceName,
m_OneSluiceCount,
m_TwoSluiceCount,
m_OneUpEmptyCount,
m_OneDownemptyCount,
m_TwoUpemptyCount,
m_TwoDownemptyCount
.........................
)values
(
daychar,
m_OneSluiceCount ,
m_TwoSluiceCount ,
m_OneUpEmptyCount
m_OneDownemptyCount ,
m_TwoUpemptyCount ,
m_TwoDownemptyCount
...............
);
pipe row(vv);
return;
end ;