我们知道，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;
以上操作完成后，便可以进行执行操作了。
