Oracle和SQLServer分别使用函数和存储过程返回结果集

我们知道，SQLServer无论是函数还是存储过程，其返回结果集都是较为简单的，在这里我们就不多加说明了，下面我们将通过实例详细分析一下Oracle的函数和存储过程返回结果集的方法：

1、函数

首先创建某一个对象类型如下：

CREATE OR REPLACE TYPE "NS_FB_BUSINESS_SPECIALVAL_ROW"                                                                                                                                                                                                                                                                                                                                                                       AS OBJECT
(
  BID INTEGER,
  BIZ_TYPE VARCHAR2(4),
  CONTRACT_NO    VARCHAR2(32),
  DEBTOR INTEGER,
  CURRENCY_NO   VARCHAR2(4),
  START_DATE   DATE,
  MATURITY  DATE,
  STATE      INTEGER,
  SUB_STATE  INTEGER,
  SPEVAL NUMBER(15,2),
  special_list_id INTEGER,
  ledger_type VARCHAR2(4),
  loanTerm VARCHAR(64),
  LOANTERMNAME VARCHAR(256),
  CORPNO VARCHAR2(32),
  CORPNAME VARCHAR2(100),
  CRGRADE VARCHAR2(32),
  self_support INTEGER
)

然后，创建一表类型如下：

CREATE OR REPLACE TYPE "NS_FB_BUSINESS_SPECIALVAL_TAB" AS TABLE OF NS_FB_BUSINESS_SPECIALVAL_ROW

紧接着我们要创建一函数如下：

CREATE OR REPLACE FUNCTION NS_FB_BUSINESS_SPECIAL_VAL(pDate_ IN DATE DEFAULT NULL)
  RETURN NS_FB_BUSINESS_SPECIALVAL_TAB PIPELINED
  IS
  SepcialVal NS_FB_BUSINESS_SPECIALVAL_ROW :=
    NS_FB_BUSINESS_SPECIALVAL_ROW(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
  for rec in(
  select fb.*,
       sval.speval,                 --- 卖断余额
       sval.special_list_id,        --- 卖断明细id
       sval.ledger_type,            --- 卖断台帐类型
       fb_ndqx_func.fb_calLoanTerm(fb.start_date, fb.maturity) as loanTerm,
       --贷款期限 ：1：三年以上； 2：三年； 3：一年；4：6个月；5：三个月；6：一个月；
       fb_ndqx_func.fb_calLoanTermName(fb_ndqx_func.fb_calLoanTerm(fb.start_date,
                                                                   fb.maturity)) as loanTermName,
       b.corpno,         --客户编号
       b.corpname,       --客户名称
       b.crgrade,        --信用等级
       fc.self_support
  From fb_business fb
  inner join fb_contracts fc on fb.bid=fc.bid
  inner join (select scl.bid,
                    scl.special_list_id,
                    sl.ledger_type,
                    sum(sl.amount * (case sl.dir_flag
                          when '+' then
                           1
                          else
                           -1
                        end)) as speval
               From fb_special_contract sc
              inner join fb_special_contract_list scl on sc.special_id =
                                                         scl.special_id
              inner join fb_special_ledger sl on sc.special_id =
                                                 sl.special_id
              where sc.biz_type = '114' and sl.ACT_DATE<=pDate_
              group by scl.special_list_id, scl.bid, sl.ledger_type) sval on sval.bid =
                                                                             fb.bid
   join corporations b on fb.debtor = b.corpid
  )
  LOOP
   SepcialVal.BID                   := rec.bid ;
   SepcialVal.BIZ_TYPE              := rec.BIZ_TYPE ;
   SepcialVal.CONTRACT_NO           := rec.CONTRACT_NO ;
   SepcialVal.DEBTOR                := rec.DEBTOR ;
   SepcialVal.CURRENCY_NO           := rec.CURRENCY_NO ;
   SepcialVal.START_DATE            := rec.START_DATE ;
   SepcialVal.MATURITY              := rec.MATURITY ;
   SepcialVal.STATE                 := rec.STATE ;
   SepcialVal.SUB_STATE             := rec.SUB_STATE ;
   SepcialVal.SPEVAL                := rec.SPEVAL ;
   SepcialVal.special_list_id       := rec.special_list_id ;
   SepcialVal.ledger_type           := rec.ledger_type ;
   SepcialVal.loanTerm              := rec.loanTerm ;
   SepcialVal.LOANTERMNAME          := rec.LOANTERMNAME ;
   SepcialVal.CORPNO                := rec.CORPNO ;
   SepcialVal.CORPNAME              := rec.CORPNAME ;
   SepcialVal.CRGRADE               := rec.CRGRADE ;
   SepcialVal.self_support          := rec.self_support;
   PIPE ROW(SepcialVal);
  END LOOP;
  RETURN;
END;

以上操作完成后，我们便可以做下面的查询操作了，

select * from table(NS_FB_BUSINESS_SPECIAL_VAL(temp_Date)) bs

 

2、存储过程

早期版本中可以使用package和ref cursor来返回，10g后可以直接使用SYS_REFCURSOR进行代替。

过程如下：

首先创建一package，

CREATE OR REPLACE PACKAGE GLOBALPKG
AS
    TYPE RCT1 IS REF CURSOR;
    TRANCOUNT INTEGER := 0;
    IDENTITY INTEGER;
  DBLINK  VARCHAR2(2) := 'FN';
END;

然后，创建一存储过程，

CREATE OR REPLACE PROCEDURE NsWsGetCltClassList
(
 Uid_ in int,
GroupNo in  varchar2,
ClassNo varchar2,
RCT1                        IN  OUT     GLOBALPKG.RCT1
)
/** 签名结果 **/
AS
        CCId int;
        CGNo varchar2 (2);
        CCNo varchar2 (18)  ;
        CCName varchar2 (60)  ;
        TreeNo varchar2(18)  ;
        Level_ smallint ;
        FullName varchar2(255)  ;
        Memo varchar (255);
        UserId int;
        ASSIGNMENTVARIABLE0 int;
        CURSOR  cur
         IS
 select a.CCId,a.CGNo,a.CCNo,a.CCName,a.TreeNo,a."LEVEL",a.FullName,a.Memo
         from NsCltClass a
        where a.cgNo = NsWsGetCltClassList.GroupNo
        and a.CCNo like NsWsGetCltClassList.ClassNo || '%';

BEGIN
       EXECUTE IMMEDIATE 'TRUNCATE TABLE NsWsGetCltClassList_TBL';
       OPEN cur;
       while 1=1 loop
             fetch  cur into CCId,CGNo,CCNo,CCName,TreeNo,Level_,FullName,Memo ;
             if cur%NOTFOUND then
                exit;
             end if;
         BEGIN
              select count(*)  into ASSIGNMENTVARIABLE0  from WSClientACL
            where ccid in  ( select ccid from
                      NsCltClass
                      where CgNo = NsWsGetCltClassList.GroupNo
                      and Uid = NsWsGetCltClassList.Uid_
                      and NsWsGetCltClassList.TreeNo  like TreeNo || '%' )       ; 
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                ASSIGNMENTVARIABLE0 := 0;
        END;
           if ASSIGNMENTVARIABLE0 >0 then
               UserId := NsWsGetCltClassList.Uid_;
            else
               UserId :=0;
            end if;
            insert into NsWsGetCltClassList_TBL(CCId,CGNo,CCNo,CCName,TreeNo,Level_,FullName,Memo,UId_)
          values(NsWsGetCltClassList.CCId,NsWsGetCltClassList.CGNo,NsWsGetCltClassList.CCNo,NsWsGetCltClassList.CCName,NsWsGetCltClassList.TreeNo,NsWsGetCltClassList.Level_,NsWsGetCltClassList.FullName,NsWsGetCltClassList.Memo,NsWsGetCltClassList.UserId);
       end loop;
       open RCT1 for
         select * from NsWsGetCltClassList_TBL
    order by TreeNo, Level_;
END;

以上操作完成后，便可以进行执行操作了。