Oracle自定义函数查询结果集

最新推荐文章于 2024-04-12 23:42:03 发布

weixin_34220834

最新推荐文章于 2024-04-12 23:42:03 发布

阅读量1.1k

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1766298/blog/1607782

版权

2019独角兽企业重金招聘Python工程师标准>>>

--定义行类型
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_KEY
FROM LOANAUDITUSER.ZHPH_INTEGERFACE_LOG WHERE CONTRACT_NO = contractNo
ORDER 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'));

转载于:https://my.oschina.net/u/1766298/blog/1607782