使用一个IN和两个OUT参数创建函数

Question

我想创建一个Oracle函数，它由一个输入参数和两个输出参数组成。下面是我写的代码。

CREATE OR REPLACE FUNCTION GET_CMM_REPORT
(
p_span_link_id IN nvarchar2,
p_cmmapproved  OUT SYS_REFCURSOR,
p_cmm_tobe_approved  OUT SYS_REFCURSOR
) 
RETURN VARCHAR2 AS 
BEGIN
select ROUTE_APPROVED_BY_CMM AS "CMM APPROVED" from TBL_FIBER_INV_CMPAPPROVED_INFO where 
SPAN_LINK_ID= p_span_link_id
and ROUTE_APPROVED_BY_CMM > 0;
select NE_LENGTH
from TBL_FIBER_INV_CMPAPPROVED_INFO where SPAN_LINK_ID= p_span_link_id;
RETURN NULL;
END GET_CMM_REPORT;

我的功能还可以吗，因为我是新创建oracle功能的。请建议如何实施。

Answer 1

这应该是语法有效的

CREATE OR REPLACE FUNCTION GET_CMM_REPORT
(
p_span_link_id IN nvarchar2,
p_cmmapproved  OUT SYS_REFCURSOR,
p_cmm_tobe_approved  OUT SYS_REFCURSOR
) 
RETURN VARCHAR2 AS 
BEGIN
open p_cmmapproved for 
select ROUTE_APPROVED_BY_CMM AS "CMM APPROVED" 
from TBL_FIBER_INV_CMPAPPROVED_INFO 
where SPAN_LINK_ID= p_span_link_id
and ROUTE_APPROVED_BY_CMM > 0;
open p_cmm_tobe_approved for 
select NE_LENGTH
from TBL_FIBER_INV_CMPAPPROVED_INFO 
where SPAN_LINK_ID= p_span_link_id;
RETURN NULL;
END GET_CMM_REPORT;

不过，从逻辑上讲，一个有两个out参数并返回硬编码null的函数相当奇怪。你最好使用存储过程，而不是

CREATE OR REPLACE PROCEDURE GET_CMM_REPORT
(
p_span_link_id IN nvarchar2,
p_cmmapproved  OUT SYS_REFCURSOR,
p_cmm_tobe_approved  OUT SYS_REFCURSOR
) 
AS
BEGIN
open p_cmmapproved for 
select ROUTE_APPROVED_BY_CMM AS "CMM APPROVED" 
from TBL_FIBER_INV_CMPAPPROVED_INFO 
where SPAN_LINK_ID= p_span_link_id
and ROUTE_APPROVED_BY_CMM > 0;
open p_cmm_tobe_approved for 
select NE_LENGTH
from TBL_FIBER_INV_CMPAPPROVED_INFO 
where SPAN_LINK_ID= p_span_link_id;
END GET_CMM_REPORT;

或者创建两个单独的函数，每个函数返回一个光标。