Oracle自定义函数无法在SELECT执行但可在存储过程中运行的问题

Oracle的BOOLEAN类型是PL/SQL专属类型，SQL引擎（即普通SELECT这类SQL语句）不支持将BOOLEAN作为查询结果列的类型。你的函数返回BOOLEAN，直接在SELECT中调用就会触发ORA-00902: invalid datatype错误。而存储过程能正常运行，是因为存储过程属于PL/SQL执行环境，PL/SQL本身完全支持BOOLEAN类型的变量和操作。

方法1：修改原函数，返回SQL兼容类型 #

将函数的返回类型改为SQL支持的NUMBER（0表示未投票，1表示已投票），这样就能直接在SELECT语句中调用：

create or replace FUNCTION previouslyVoted(election_code_in IN NUMBER, electorate_id_in IN NUMBER, voter_id_in IN NUMBER)
RETURN NUMBER IS
voter_id_temp NUMBER;
BEGIN
    SELECT COUNT(*) INTO voter_id_temp
    FROM ballot_issuance bi
    WHERE bi.electorate_id = electorate_id_in
    AND bi.election_serial_number = election_code_in
    AND bi.voter_id = voter_id_in;

RETURN CASE WHEN voter_id_temp > 0 THEN 1 ELSE 0 END;
END;

修改后，不管是在SQL Developer还是PHP中，都可以直接执行：

SELECT previouslyVoted(20190404,1,13321) "Result" FROM dual;

PHP调用时建议使用绑定变量（避免SQL注入风险），示例代码：

// 假设$conn是已建立的Oracle连接
$stmt = oci_parse($conn, 'SELECT previouslyVoted(:election, :electorate, :voter_id) AS "Result" FROM dual');
oci_bind_by_name($stmt, ':election', $ELECTION);
oci_bind_by_name($stmt, ':electorate', $ELECTORATE);
oci_bind_by_name($stmt, ':voter_id', $oracle_voter_id);
oci_execute($stmt);
$row = oci_fetch_assoc($stmt);
$hasVoted = $row['RESULT'] == 1;

方法2：创建包装函数（保留原函数） #

如果不想修改原BOOLEAN类型的函数，可以新建一个包装函数，将BOOLEAN结果转换为SQL兼容类型：

create or replace FUNCTION previouslyVoted_SQL(election_code_in IN NUMBER, electorate_id_in IN NUMBER, voter_id_in IN NUMBER)
RETURN NUMBER IS
voter_has_voted BOOLEAN;
BEGIN
    voter_has_voted := previouslyVoted(election_code_in, electorate_id_in, voter_id_in);
    RETURN CASE WHEN voter_has_voted THEN 1 ELSE 0 END;
END;

之后调用这个包装函数即可：

SELECT previouslyVoted_SQL(20190404,1,13321) "Result" FROM dual;

方法3：PHP中调用存储过程（不修改函数） #

如果坚持使用原BOOLEAN函数，可以通过调用存储过程获取结果，但步骤相对繁琐：

// 调用存储过程并获取输出
$stmt = oci_parse($conn, 'BEGIN runprevvoted; END;');
oci_execute($stmt);
// 若需要将结果返回给PHP，需修改存储过程添加输出参数
// 示例修改存储过程：
// create or replace PROCEDURE runprevvoted(p_voted OUT BOOLEAN)
// AS
// BEGIN
//    p_voted := previouslyVoted(20190404, 1, 13321);
// END;
// 然后PHP中绑定输出参数获取结果

内容的提问来源于stack exchange，提问作者Chriscolle