Oracle 查询，返回记录集，不是用游标，不用创建临时表

最新推荐文章于 2023-05-29 21:30:00 发布

weixin_30576827

最新推荐文章于 2023-05-29 21:30:00 发布

阅读量152

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/xgxhellboy/archive/2012/09/28/2707123.html

版权

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

...............

);