SELECT内部某个函数的布尔返回值

Question

我正在PL/SQL中开发一个包。

这是我的规格：

TYPE outrec IS RECORD(
tw_m_id                         NUMBER,
tw_m_dealer_id                  NUMBER,
tw_number                       NUMBER,
check_uid                       NUMBER);
TYPE outrecset IS TABLE OF outrec;
FUNCTION report
(
p_watermark           IN NUMBER,
p_param               IN NUMBER,
p_index               IN NUMBER
) RETURN outrecset
PIPELINED;

这是我的身体：

FUNCTION func
(
p_watermark           => p_watermark,
p_param               => p_param,
p_index               => p_index
)

RETURN outrecset
PIPELINED IS
temp outrec;

BEGIN
before_report(p_watermark           => p_watermark,
p_param               => p_param,
p_index               => p_index);

FOR c_rec IN (SELECT tw_m_id,
tw_m_dealer_id,
tw_number,
package_name.somefunction(tw_number) AS check_uid
FROM table1
JOIN table2 rk ON id1 = rk.id2
WHERE 1 = 1
AND id1 = rk.id2
AND id1 = p_param)

LOOP
temp.tw_m_tw_rechnungskopf_id       := c_rec.tw_m_tw_rechnungskopf_id;
temp.tw_m_haendler_id_rechnung      := c_rec.tw_m_haendler_id_rechnung;
temp.check_uid                      := c_rec.check_uid;
PIPE ROW(temp);
END LOOP;
END;

我正在尝试从package_name.somefunction(tw_number(AS check_uid中获取值。问题是somefunction返回BOOLEAN值。当我将check_uid设置为BOOLEAN时，我得到错误：PLS-00382：表达式的类型错误，因为SQL当然不支持BOOLEAN。我试过了：

CASE 
WHEN package_name.somefunction(tw_number) THEN true 
else false
END as check_uid

在SELECT中，我得到错误：PL/SQL:ORA-00920：无效的关系运算符。

有人能告诉我如何做到这一点吗？PL/SQL不是我最强的一面：(

• EDIT：我无法更改某个函数以返回一个示例varchar2，它需要保持原样

Answer 1

编写一个简单的包装函数，将PL/SQLBOOLEAN转换为在SQL中有效且记录类型所需的NUMBER数据类型，然后可以将函数调用移到SQL之外。

CREATE FUNCTION MAP_BOOLEAN( truthy IN BOOLEAN ) RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN CASE truthy
WHEN TRUE  THEN 1
WHEN FALSE THEN 0
ELSE NULL
END;
END map_boolean;
/

所以你的规格是：

CREATE PACKAGE package_name IS
TYPE outrec IS RECORD(
tw_m_id        NUMBER,
tw_m_dealer_id NUMBER,
tw_number      NUMBER,
check_uid      NUMBER
);
TYPE outrecset IS TABLE OF outrec;
-- Note: This may be in another package but is here for convenience.
FUNCTION somefunction(value IN NUMBER) RETURN BOOLEAN;
FUNCTION report
(
p_watermark IN NUMBER,
p_param     IN NUMBER,
p_index     IN NUMBER
) RETURN outrecset PIPELINED;
END;
/

相应的主体为：

CREATE PACKAGE BODY package_name IS
FUNCTION somefunction(value IN NUMBER) RETURN BOOLEAN
IS
BEGIN
RETURN TRUE;
END;
PROCEDURE before_report(
p_watermark IN NUMBER,
p_param     IN NUMBER,
p_index     IN NUMBER
)
IS
BEGIN
NULL;
END;

FUNCTION report(
p_watermark IN NUMBER,
p_param     IN NUMBER,
p_index     IN NUMBER
) RETURN outrecset PIPELINED
IS
temp outrec;
BEGIN
before_report(
p_watermark => p_watermark,
p_param     => p_param,
p_index     => p_index
);
FOR c_rec IN (
SELECT tw_m_id,
tw_m_dealer_id,
tw_number
FROM   table1
JOIN table2 rk ON id1 = rk.id2
WHERE  id1 = p_param
)
LOOP
temp.tw_m_id        := c_rec.tw_m_id;
temp.tw_m_dealer_id := c_rec.tw_m_dealer_id;
temp.check_uid      := MAP_BOOLEAN(
PACKAGE_NAME.SOMEFUNCTION( c_rec.tw_number )
);
PIPE ROW(temp);
END LOOP;
END;
END;
/

(注意：您还需要更新光标循环，因为您选择的值与记录的字段不匹配。(

db<gt；小提琴这里

Answer 2

通常，您会在包中创建一个重载函数，返回1/0或Y/N。但是，由于您无法访问包，因此可以在sql查询中使用内联函数为您执行此操作。

create or replace function func (parameter_i VARCHAR2) RETURN BOOLEAN
AS
BEGIN
return true;
END;
/
WITH
FUNCTION func_yn(parameter_i VARCHAR2)
RETURN NUMBER
IS
l_return_value BOOLEAN;
BEGIN
l_return_value :=func(parameter_i => parameter_i);
RETURN CASE l_return_value WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;
END func_yn;
SELECT 
func_yn('test')
FROM dual;