sql server 函数转Oracle 函数 返回类型为Table

 

sql 定义如下：

 create   FUNCTION   [Bill_Stat_test](@starDate char(10),@endDate char(10))  
 RETURNS   table
 AS
 RETURN  --查询内容
 (
select trade_code_b,GJ_COLLECTION_HEAD.APP_PERSON,Biz_type,num
 from  GJ_COLLECTION_HEAD
--当然这里可以加日期限制
 )
 
调用
 SELECT *
 FROM Bill_Stat_test('1999-01-01','2009-01-01')

Orale里面实现如下：

(1) 创建types 结构定义为OBJECT
CREATE OR REPLACE TYPE Bill_Stat_Type AS OBJECT
       (trade_code_b         VARCHAR2(50),
        APP_PERSON    VARCHAR2(200),
        Biz_type      VARCHAR2(100),
        num    integer)
       
(2)对类型进行说明 OBJECT是Table类型
CREATE OR REPLACE TYPE bill_stat_table AS TABLE OF Bill_Stat_Type

(3)
create or replace function Bill_Stat(P_starDate in VARCHAR2,
                                     P_endDate  in VARCHAR2)
  return bill_stat_table
is
  cursor Cur_sql is
select trade_code_b,GJ_COLLECTION_HEAD.APP_PERSON,Biz_type,num
 from  GJ_COLLECTION_HEAD;
  c1 Cur_sql%rowtype;
Result bill_stat_table := bill_stat_table();
BEGIN
     Result := bill_stat_table();
     open Cur_sql;
     loop
      fetch Cur_sql
       into c1;
     exit when Cur_sql%notfound; 
     Result.extend;
     Result(Result.count) := Bill_Stat_Type(NULL, NULL, NULL, NULL); 
     Result(Result.count).trade_code_b := c1.trade_code_b;
     Result(Result.count).trade_name := c1.trade_name; 
     Result(Result.count).Biz_type := c1.biz_type; 
     Result(Result.count).num := c1.num; 
     end loop;
    close Cur_sql;
    return(Result);
end Bill_Stat;
(4)Oracle调用如下：
 select *
 from table(cast(bill_stat('1999-01-01','2009-01-01') as bill_stat_table));