Всегда возвращает значение Null: использование оператора Select при создании функции в Oracle SQL Developer

Значение KPI_TYPE, которое вы передаете функции, не существует в таблице TARGETS_TABLE, и функция вызывает исключение NO DATA FOUND, когда пытается использовать SELECT ... INTO ..., а выбранное значение соответствует нулю.

SQL-запрос получает исключение NO DATA FOUND и понимает, что возвращаемое значение должно быть NULL, молча потребляет исключение и заменяет NULL на его место.

Например, если у вас есть данные:

CREATE TABLE targets_table (KPI, target_50, target_100, target_125, target_150) AS
SELECT 'KPI A',  5, 10, 15, 20 FROM DUAL UNION ALL
SELECT 'KPI B', 10, 30, 40, 50 FROM DUAL;

Затем:

SELECT KPI(100, 'KPI C') FROM DUAL;

Выходы:

Но выполнение того же вызова функции в PL/SQL (а не в SQL):

DECLARE
  v_kpi NUMBER;
BEGIN
  v_kpi := KPI(100, 'KPI C');
END;
/

Показывает основное исключение:

ORA-01403: no data found
ORA-06512: at "FIDDLE_PTZQEOEAQSCSHGUJATEJ.KPI", line 9
ORA-06512: at line 4

Решение состоит в том, чтобы передать значение KPI_TYPE функции, которая действительно существует в таблице.

Однако, если вы создали таблицу, используя тип данных CHAR для столбцов KPI:

CREATE TABLE targets_table (
  KPI        CHAR(10),
  target_50  NUMBER,
  target_100 NUMBER,
  target_125 NUMBER,
  target_150 NUMBER
);
INSERT INTO targets_table (KPI, target_50, target_100, target_125, target_150)
SELECT 'KPI A',  5, 10, 15, 20 FROM DUAL UNION ALL
SELECT 'KPI B', 10, 30, 40, 50 FROM DUAL;

Затем:

SELECT KPI(100, 'KPI B') FROM DUAL;

Выведет:

Но:

SELECT KPI(100, 'KPI B     ') FROM DUAL;

Выходы:

Потому что вы дополнили KPI до правильной длины столбца.

В этом случае лучшим решением было бы преобразовать столбец KPI из CHAR(10) в VARCHAR2(10), поскольку вы используете строки переменной, а не фиксированной длины. Если вы не можете этого сделать, задайте для аргумента KPI_TYPE тот же тип, что и у столбца (что будет неявно дополнять более короткие строки до нужной длины). Однако предпочтительнее использовать столбец VARCHAR2 (хотя вы можете использовать оба):

CREATE OR REPLACE FUNCTION KPI(
  RAW_SCORE IN TARGETS_TABLE.TARGET_50%TYPE,
  KPI_TYPE  IN TARGETS_TABLE.KPI%TYPE
)
RETURN NUMBER
AS
  ACTUAL_SCORE NUMBER;
BEGIN
  select case when RAW_SCORE >= target_150 then 1.50
              when RAW_SCORE >= target_125 then 1.25
              when RAW_SCORE >= target_100 then 1.00
              when RAW_SCORE >= target_50 then 0.50
         else 0
         end
  into   ACTUAL_SCORE
  from   TARGETS_TABLE
  where  TARGETS_TABLE.KPI = KPI_TYPE;

  RETURN ACTUAL_SCORE;
END;
/

В качестве альтернативы, если вам нужна точно такая же функциональность в запросе (без функции - даже для репликации поведения для исключений NO DATA FOUND и TOO MANY ROWS, если KPI_TYPE соответствует нулю или нескольким строкам соответственно), вы можете использовать коррелированный подзапрос:

-- sample test data
WITH data (raw_score, kpi_type) AS (
  SELECT 10, 'KPI A' FROM DUAL UNION ALL
  SELECT 10, 'KPI B' FROM DUAL UNION ALL
  SELECT 10, 'KPI C' FROM DUAL
)
-- query starts here
SELECT raw_score,
       kpi_type,
       (
         select case
                when d.RAW_SCORE >= t.target_150 then 1.50
                when d.RAW_SCORE >= t.target_125 then 1.25
                when d.RAW_SCORE >= t.target_100 then 1.00
                when d.RAW_SCORE >= t.target_50  then 0.50
                else 0
                end
         from   TARGETS_TABLE t
         where  t.KPI = d.KPI_TYPE
       ) AS kpi
FROM   data d

рабочий пример

Возможно, вам вообще не нужна эта функция. Предполагая, что есть некоторые данные о тестировании оценок и что функция предназначена для вызова как часть некоторого списка выбора, возможно, вы могли бы рассмотреть возможность сделать это без функции...

WITH   --  S a m p l e    D a t a :
    targets_table (KPI, TARGET_50, TARGET_100, TARGET_150) AS
        ( Select 'KPI A',  5, 10, 20 From Dual UNion All
          Select 'KPI B', 10, 30, 50 From Dual
        ),

... ниже приведен cte (test_scores), содержащий ROW_SCORE и KPI_TYPE, представляющие возможный интерес. Если вы планируете вызывать функцию (передавать ей параметры) с помощью sql - рассмотрите код ниже этого cte как возможное решение...

--  dummy data as a sample
    test_scores (ID, RAW_SCORE, KPI_TYPE) AS
        ( Select 1,   6, 'KPI A' From Dual Union All 
          Select 2,  10, 'KPI 1' From Dual Union All 
          Select 3,  10, 'KPI A' From Dual Union All 
          Select 4,   9, 'KPI B' From Dual Union All
          Select 5, 100, 'KPI B' From Dual
        )

... код для получения баллов (1 или более)

--      S Q L :
Select    ts.ID, ts.RAW_SCORE, ts.KPI_TYPE,
          Case When tt.KPI Is Null Then Null
               When ts.RAW_SCORE >= TARGET_150 Then 1.50
               When ts.RAW_SCORE >= TARGET_100 Then 1.00
               When ts.RAW_SCORE >= TARGET_50  Then 0.50
          Else 0
          End "ACTUAL_SCORE",
          Case When tt.KPI Is Null Then 'No such KPI_TYPE in targets_table' End "NOTE"
From      test_scores ts  
Left Join targets_table tt ON(tt.KPI = ts.KPI_TYPE)
--Where tt.KPI = 'some KPI'    <<<---- when you need to filter the resultset
Order By  ts.ID

/*      R e s u l t :
ID  RAW_SCORE  KPI_TYPE  ACTUAL_SCORE  NOTE
--  ---------  --------  ------------  -----------------------------------
 1          6  KPI A              0.5   
 2         10  KPI 1                   No such KPI_TYPE in targets_table
 3         10  KPI A                1   
 4          9  KPI B                0   
 5        100  KPI B              1.5                                       */