Oracle PL/SQL string compare issue

问题:

1).Hello, I have the following Oracle PL/SQL codes that may be rusty from you guys perspective:

 DECLARE
 str1  varchar2(4000);
 str2  varchar2(4000);
 BEGIN
   str1:='';
   str2:='sdd';
   IF(str1<>str2) THEN
    dbms_output.put_line('The two strings is not equal');
   END IF;
 END;
 /

This is very obvious that two strings str1 and str2 are not equal, but why 'The two strings are not equal' was not printed out? Do Oracle have another common method to compare two string?

回答1:

As Phil noted, the empty string is treated as a NULL, and NULL is not equal or unequal to anything. If you expect empty strings or NULLs, you'll need to handle those with 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;
 /

---

Concerning null comparisons:

According to the Oracle 12c documentation on NULLS, null comparisons using IS NULL or IS NOT NULL do evaluate to TRUE or FALSE. However, all other comparisons evaluate to UNKNOWN, not FALSE. The documentation further states:

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

A reference table is provided by 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

I also learned that we should not write PL/SQL assuming empty strings will always evaluate as NULL:

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

回答2:

Let's fill in the gaps in your code, by adding the other branches in the logic, and see what happens:

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>

So the two strings are neither the same nor are they not the same? Huh?

It comes down to this. Oracle treats an empty string as a NULL. If we attempt to compare a NULL and another string the outcome is not TRUE nor FALSE, it is NULL. This remains the case even if the other string is also a NULL.

回答3:

I compare strings using = and not <>. I've found out that in this context = seems to work in more reasonable fashion than <>. I have specified that two empty (or NULL) strings are equal. The real implementation returns PL/SQL boolean, but here I changed that to pls_integer (0 is false and 1 is true) to be able easily demonstrate the function.

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:

To fix the core question, "how should I detect that these two variables don't have the same value when one of them is null?", I don't like the approach of nvl(my_column, 'some value that will never, ever, ever appear in the data and I can be absolutely sure of that') because you can't always guarantee that a value won't appear... especially with NUMBERs.

I have used the following:

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

Disclaimer: I am not an Oracle wizard and I came up with this one myself and have not seen it elsewhere, so there may be some subtle reason why it's a bad idea. But it does avoid the trap mentioned by APC, that comparing a null to something else gives neither TRUE nor FALSE but NULL. Because the clauses (str1 is null) will always return TRUE or FALSE, never null.

(Note that PL/SQL performs short-circuit evaluation, as noted here.)

回答5:

I've created a stored function for this text comparison purpose:

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;

In example:

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

回答6:

Only change the line str1:=''; to str1:=' ';

回答7:

To the first question:

Probably the message wasn't print out because you have the output turned off. Use these commands to turn it back on:

set serveroutput on
exec dbms_output.enable(1000000);

On the second question:

My PLSQL is quite rusty so I can't give you a full snippet, but you'll need to loop over the result set of the SQL query and CONCAT all the strings together.