Oracle自定义函数查询结果集

2018-01-17 10:36:49来源:oschina作者:城里的月光人点击

分享
第七城市th7cn

--定义行类型 CREATE TYPE ROW_TYPE AS OBJECT(CONTRACT_NO VARCHAR2(30),SEND_RECEIVE VARCHAR2(20),API_URL VARCHAR2(100),SEND_CONTENT CLOB,RECEIVE_CONTENT CLOB,LOG_INFO CLOB,CREATE_TIME DATE,ID_CARD VARCHAR2(30),SEARCH_KEY VARCHAR2(200) ); --定义结果集类型 CREATE TYPE TABLE_TYPE AS TABLE OF ROW_TYPE;


--根据合同号查询日志函数 CREATE OR REPLACE FUNCTION queryLogByContractNo(contractNo IN VARCHAR2) RETURN TABLE_TYPE PIPELINED AS V ROW_TYPE; BEGIN FOR MYROW IN (SELECT CONTRACT_NO,SEND_RECEIVE,API_URL,SEND_CONTENT,RECEIVE_CONTENT, LOG_INFO,CREATE_TIME,ID_CARD,SEARCH_KEYFROM LOANAUDITUSER.ZHPH_INTEGERFACE_LOG WHERE CONTRACT_NO = contractNoORDER BY CREATE_TIME DESC) LOOP


V := ROW_TYPE(MYROW.CONTRACT_NO, MYROW.SEND_RECEIVE,MYROW.API_URL,MYROW.SEND_CONTENT,MYROW.RECEIVE_CONTENT,MYROW.LOG_INFO,MYROW.CREATE_TIME,MYROW.ID_CARD,MYROW.SEARCH_KEY);pipe row (V); END LOOP; RETURN; END;


--使用方法


select * from table(queryLogByContractNo('91015160842729633983'));

第七城市th7cn

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台