oracle 中使用like的問題

今天我想查詢開發人員建立主鍵時使用PK__開頭的索引，發現執行如下語句會查詢不是兩個PK__開頭的語句：

SELECT *
FROM dba_indexes
WHERE owner = 'ICARE' AND index_name LIKE 'PK__%'
ORDER BY 2

很明顯,_在like中表示任意字元，但是如何使用轉義呢？

google查詢了一下發現如下連結記錄下來：

http://www-eleves-isia.cma.fr/documentation/OracleDoc/LIKE-OPERATOR.html

但願＼在blog中正常，修改為如下，執行就正確了：

SELECT *
FROM dba_indexes
WHERE owner = &o AND index_name LIKE 'PK__%' ESCAPE ''
ORDER BY 2

LIKE-OPERATOR

    LIKE OPERATOR 
 
    The LIKE operator is used in character string comparisons with 
    pattern matching.  The syntax for a condition using the LIKE 
    operator is shown in this diagram: 
 
SYNTAX: 
 
char1 [NOT] LIKE char2 [ESCAPE 'c'] 
 
where: 
 
char1 
    is a value to be compared with a pattern.  This value can have 
    datatype CHAR or VARCHAR2. 
 
NOT 
    logically inverts the result of the condition, returning FALSE if 
    the condition evaluates to TRUE and TRUE if it evaluates to FALSE. 
 
char2 
    is the pattern to which char1 is compared.  The pattern is a value 
    of datatype CHAR or VARCHAR2 and can contain the special pattern 
    matching characters % and _. 
 
ESCAPE 
    identifies a single character as the escape character.  The escape 
    character can be used to cause Oracle to interpret % or _ literally, 
    rather than as a special character, in the pattern. 
    If you wish to search for strings containing an escape character, you 
    must specify this character twice.  For example, if the escape 
    character is '/', to search for the string 'client/server', you must 
    specify, 'client//server'. 
 
    While the equal (=) operator exactly matches one character value to 
    another, the LIKE operator matches a portion of one character value 
    to another by searching the first value for the pattern specified by 
    the second. 
 
    With the LIKE operator, you can compare a value to a pattern rather 
    than to a constant.  The pattern can only appear after the LIKE 
    keyword.  For example, you can issue the following query to find the 
    salaries of all employees with names beginning with 'SM': 
 
    SELECT sal 
        FROM emp 
        WHERE ename LIKE 'SM%' 
 
    The following query finds the salaries of all employees with the 
    name 'SM%', since the query uses the equality operator instead of 
    the LIKE operator: 
 
    SELECT sal 
        FROM emp 
        WHERE ename = 'SM%' 
 
    The following query finds the salaries of all employees with the 
    name 'SM%'.  Oracle interprets 'SM%' as a text literal, rather than 
    as a pattern, because it precedes the LIKE operator: 
 
    SELECT sal 
        FROM emp 
        WHERE 'SM%' LIKE ename 
 
    Patterns often use special characters that Oracle matches with 
    different characters in the value: 
 
    * An underscore (_) in the pattern matches exactly one character (as 
      opposed to one byte in a multi-byte character set) in the value. 
    * A percent sign (%) in the pattern can match zero or more 
      characters (as opposed to bytes in a multi-byte character set) in 
      the value.  Note that the pattern '%' cannot match a null. 
 
    Case is significant in all conditions comparing character 
    expressions including the LIKE and equality (=) operators.  You can 
    use the UPPER() function to perform a case insensitive match, as in 
    this condition: 
 
    UPPER(ename) LIKE 'SM%' 
 
    When LIKE is used to search an indexed column for a pattern, the 
    performance benefit associated with the index is lost if the first 
    character in the pattern is % or _.  If the leading character in the 
    pattern is not % or _, there is some performance benefit to the 
    index because Oracle can restrict the comparison to rows known to 
    begin with the specified first character. 
 
EXAMPLE I: 
    This condition is true for all ENAME values beginning with MA: 
 
    ename LIKE 'MA%' 
 
    All of these ENAME values make the condition TRUE: 
 
    MARTIN, MA, MARK, MARY 
 
    Since case is significant, ENAME values beginning with Ma, ma, and 
    mA make the condition FALSE. 
 
EXAMPLE II: 
    Consider this condition: 
 
    ename LIKE 'SMITH_' 
 
    This condition is true for these ENAME values: 
 
    SMITHE, SMITHY, SMITHS 
 
    This condition is false for 'SMITH', since the special character _ 
    must match exactly one character of the ENAME value. 
 
THE ESCAPE OPTION: 
    You can include the actual characters % or _ in the pattern by using 
    the ESCAPE option.  The ESCAPE option identifies the escape 
    character.  If the escape character appears in the pattern before 
    the character % or _, Oracle interprets this character literally in 
    the pattern, rather than as a special pattern matching character. 
 
EXAMPLE III: 
    To search for any employees with the character string 'A_B' in their 
    name: 
 
    SELECT ename 
        FROM emp 
        WHERE ename LIKE '%A_B%' ESCAPE '' 
 
    The ESCAPE option identifies the backslash () as the escape 
    character.  In the pattern, the escape character precedes the 
    underscore (_).  This causes Oracle to interpret the underscore 
    literally, rather than as a special pattern matching character.