关于IN的奇怪现象

SQL> SELECT 'SQL IN trimmed trailing blanks!'

  2  FROM DUAL

  3  WHERE 'CA ' IN ( 'CA', 'US' )

  4  /

 

'SQLINTRIMMEDTRAILINGBLANKS!'

-------------------------------

SQL IN trimmed trailing blanks!

       但是文档中并没有提到使用in时会自动截去空格。

SQL> SELECT 'SQL IN trimmed trailing blanks!'

  2  FROM DUAL

  3  WHERE ' CA' IN ( 'CA', 'US' )

  4  /

 

'SQLINTRIMMEDTRAILINGBLANKS!'

-------------------------------

       而执行这个查询却没有返回行。

 

       最重要的一点是：不管在PL/SQL块中或者PL/SQL IN上执行相同的SQL时不要去掉任何东西，包括空格等。

SQL> DECLARE

  2     PROCEDURE exec_in ( message_in IN VARCHAR2, value_in IN VARCHAR2 )

  3     IS

  4        l_dummy VARCHAR2 ( 32767 );

  5     BEGIN

  6        BEGIN

  7           SELECT message_in

  8           INTO l_dummy

  9           FROM DUAL

 10            WHERE value_in IN ( 'CA', 'US' );

 11  

 12           DBMS_OUTPUT.put_line ( message_in );

 13        EXCEPTION

 14            WHEN OTHERS

 15            THEN

 16            DBMS_OUTPUT.put_line ( message_in || ' did not happen.' );

 17        END;

 18 

 19        IF value_in IN ( 'CA', 'US' )

 20        THEN

 21           DBMS_OUTPUT.put_line ( '"PL/SQL IN ' || SUBSTR ( message_in, 9 ));

 22        ELSE

 23            DBMS_OUTPUT.put_line ( '"PL/SQL IN '

 24                  || SUBSTR ( message_in, 9 )

 25                  || ' did not happen.'

 26                  );

 27        END IF;

 28     END;

 29  BEGIN

 30      exec_in ( '"SQL IN trimmed trailing blanks."', 'CA ' );

 31      exec_in ( '"SQL IN trimmed leading blanks."', ' CA' );

 32      exec_in ( '"SQL IN trimmed trailing tab."', 'CA ' );

 33  END;

 34  /

 

PL/SQL procedure successfully completed

 

SQL> SET SERVEROUTPUT ON

SQL> /

 

"SQL IN trimmed trailing blanks." did not happen.

"PL/SQL IN trimmed trailing blanks." did not happen.

"SQL IN trimmed leading blanks." did not happen.

"PL/SQL IN trimmed leading blanks." did not happen.

"SQL IN trimmed trailing tab." did not happen.

"PL/SQL IN trimmed trailing tab." did not happen.