函数 PL/SQL 不返回任何值

Question

我有下一个PL/SQL代码：

create or replace FUNCTION NUMBER_PLATES (name VARCHAR2)
RETURN INT
IS
    num_plates INT;
BEGIN
    SELECT count(*) INTO num_plates
    FROM plate p, detail_ped dt
    WHERE dt.plate = p.cod_plate AND p.name = name;
    RETURN (num_plates);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('ERROR');
END NUMBER_PLATES;

接下来是将函数执行到 SQL 命令中：

DECLARE a INT;
BEGIN
    a := NUMBER_PLATES('chicken');
    DBMS_OUTPUT.PUT_LINE(a);
END;

但是当实际为 3 时，该函数返回我 0。

我做错了什么？

如果我执行我的SQL句子返回 3：

    SELECT count(*)
    FROM plate p, detail_ped dt
    WHERE dt.plate = p.cod_plate AND p.name = 'chicken';

Answer 1

应重命名参数名称以避免名称冲突：

create or replace FUNCTION NUMBER_PLATES (p_name VARCHAR2)
RETURN INT
IS
    num_plates INT;
BEGIN
    SELECT count(*) INTO num_plates
    FROM plate p
    JOIN detail_ped dt                 -- proper JOIN syntax
      ON dt.plate = p.cod_plate
    WHERE p.name = p_name;
    RETURN (num_plates);
-- exception is dead code, COUNT(*) will return 0 if no records found
-- EXCEPTION
--    WHEN NO_DATA_FOUND THEN
--    DBMS_OUTPUT.PUT_LINE('ERROR');
END NUMBER_PLATES;
/

数据库<>小提琴演示