Проблема сравнения строк в PL/SQL Oracle

Ответ 1

Как отметил Фил, пустая строка рассматривается как NULL, а NULL не равна или не соответствует чему-либо. Если вы ожидаете пустые строки или NULL, вам нужно будет обрабатывать те, у которых NVL():

 DECLARE
 str1  varchar2(4000);
 str2  varchar2(4000);
 BEGIN
   str1:='';
   str2:='sdd';
-- Provide an alternate null value that does not exist in your data:
   IF(NVL(str1,'X') != NVL(str2,'Y')) THEN
    dbms_output.put_line('The two strings are not equal');
   END IF;
 END;
 /

Что касается нулевых сравнений:

В соответствии с документацией Oracle 12c по NULLS нулевые сравнения с использованием IS NULL или IS NOT NULL позволяют оценить TRUE или FALSE. Однако все остальные сравнения оцениваются до UNKNOWN, а не FALSE. В документации далее говорится:

Условие, которое оценивает UNKNOWN, действует почти как FALSE. Например, оператор SELECT с условием в предложении WHERE, который вычисляет UNKNOWN, не возвращает строк. Однако условие, оценивающее значение UNKNOWN, отличается от FALSE тем, что дальнейшие операции по оценке состояния UNKNOWN будут оцениваться НЕИЗВЕСТНЫМ. Таким образом, NOT FALSE оценивает значение TRUE, но NOT UNKNOWN не оценивает UNKNOWN.

Справочная таблица предоставляется Oracle:

Condition       Value of A    Evaluation
----------------------------------------
a IS NULL       10            FALSE
a IS NOT NULL   10            TRUE        
a IS NULL       NULL          TRUE
a IS NOT NULL   NULL          FALSE
a = NULL        10            UNKNOWN
a != NULL       10            UNKNOWN
a = NULL        NULL          UNKNOWN
a != NULL       NULL          UNKNOWN
a = 10          NULL          UNKNOWN
a != 10         NULL          UNKNOWN

Я также узнал, что мы не должны писать PL/SQL, если пустые строки всегда будут оцениваться как NULL:

В Oracle Database в настоящее время обрабатывается значение символа с нулем как null. Однако в будущих выпусках это не может продолжаться, и Oracle рекомендует не обрабатывать пустые строки так же, как и нули.

Ответ 2

Позвольте заполнить пробелы в вашем коде, добавив другие ветки в логику и посмотреть, что происходит:

SQL> DECLARE
  2   str1  varchar2(4000);
  3   str2  varchar2(4000);
  4  BEGIN
  5     str1:='';
  6     str2:='sdd';
  7     IF(str1<>str2) THEN
  8      dbms_output.put_line('The two strings is not equal');
  9     ELSIF (str1=str2) THEN
 10      dbms_output.put_line('The two strings are the same');
 11     ELSE
 12      dbms_output.put_line('Who knows?');
 13     END IF;
 14   END;
 15  /
Who knows?

PL/SQL procedure successfully completed.

SQL>

Значит, две строки ни то, ни они не одинаковы? А?

Это сводится к этому. Oracle обрабатывает пустую строку как NULL. Если мы попытаемся сравнить NULL и другую строку, результат не будет TRUE и FALSE, это будет NULL. Это остается фактом, даже если другая строка также является NULL.

Ответ 3

Я сравниваю строки, используя =, а не <>. Я выяснил, что в этом контексте = работает более разумно, чем <>. Я указал, что две пустые (или NULL) строки равны. Реальная реализация возвращает PL/SQL boolean, но здесь я изменил это на pls_integer (0 - false, а 1 - true), чтобы легко продемонстрировать функцию.

create or replace function is_equal(a in varchar2, b in varchar2)
return pls_integer as
begin
  if a is null and b is null then
    return 1;
  end if;

  if a = b then
    return 1;
  end if;

  return 0;
end;
/
show errors

begin
  /* Prints 0 */
  dbms_output.put_line(is_equal('AAA', 'BBB'));
  dbms_output.put_line(is_equal('AAA', null));
  dbms_output.put_line(is_equal(null, 'BBB'));
  dbms_output.put_line(is_equal('AAA', ''));
  dbms_output.put_line(is_equal('', 'BBB'));

  /* Prints 1 */
  dbms_output.put_line(is_equal(null, null));
  dbms_output.put_line(is_equal(null, ''));
  dbms_output.put_line(is_equal('', ''));
  dbms_output.put_line(is_equal('AAA', 'AAA'));
end;
/

Ответ 4

Чтобы исправить основной вопрос, "как я должен обнаружить, что эти две переменные не имеют одинакового значения, когда один из них является нулевым?", мне не нравится подход nvl(my_column, 'some value that will never, ever, ever appear in the data and I can be absolutely sure of that'), потому что вы не можете всегда гарантируют, что значение не появится... особенно с помощью NUMBER.

Я использовал следующее:

if (str1 is null) <> (str2 is null) or str1 <> str2 then
  dbms_output.put_line('not equal');
end if;

Отказ от ответственности: я не волшебник Oracle, и я сам придумал это и не видел его в другом месте, поэтому может быть какая-то тонкая причина, почему это плохая идея. Но он избегает ловушки, упомянутой APC, что сравнение null с чем-то другим не дает ни TRUE, ни FALSE, кроме NULL. Поскольку предложения (str1 is null) всегда возвращают TRUE или FALSE, никогда не имеют значения null.

(Обратите внимание, что PL/SQL выполняет оценку короткого замыкания, как отмечено здесь.)

Ответ 5

Я создал сохраненную функцию для этой цели сравнения текста:

CREATE OR REPLACE FUNCTION TextCompare(vOperand1 IN VARCHAR2, vOperator IN VARCHAR2, vOperand2 IN VARCHAR2) RETURN NUMBER DETERMINISTIC AS
BEGIN
  IF vOperator = '=' THEN
    RETURN CASE WHEN vOperand1 = vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = '<>' THEN
    RETURN CASE WHEN vOperand1 <> vOperand2 OR (vOperand1 IS NULL) <> (vOperand2 IS NULL) THEN 1 ELSE 0 END;
  ELSIF vOperator = '<=' THEN
    RETURN CASE WHEN vOperand1 <= vOperand2 OR vOperand1 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = '>=' THEN
    RETURN CASE WHEN vOperand1 >= vOperand2 OR vOperand2 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = '<' THEN
    RETURN CASE WHEN vOperand1 < vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NOT NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = '>' THEN
    RETURN CASE WHEN vOperand1 > vOperand2 OR vOperand1 IS NOT NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = 'LIKE' THEN
    RETURN CASE WHEN vOperand1 LIKE vOperand2 OR vOperand1 IS NULL AND vOperand2 IS NULL THEN 1 ELSE 0 END;
  ELSIF vOperator = 'NOT LIKE' THEN
    RETURN CASE WHEN vOperand1 NOT LIKE vOperand2 OR (vOperand1 IS NULL) <> (vOperand2 IS NULL) THEN 1 ELSE 0 END;
  ELSE
    RAISE VALUE_ERROR;
  END IF;
END;

В примере:

SELECT * FROM MyTable WHERE TextCompare(MyTable.a, '>=', MyTable.b) = 1;

Ответ 6

К первому вопросу:

Возможно, сообщение не было распечатано, потому что вы отключили выход. Используйте эти команды, чтобы включить его:

set serveroutput on
exec dbms_output.enable(1000000);

По второму вопросу:

Мой PLSQL довольно ржавый, поэтому я не могу дать вам полный фрагмент, но вам нужно будет перебрать результирующий набор SQL-запроса и CONCAT со всеми строками.