oracle - 为什么Oracle 的v$reserved_words View 中有两个 "null"关键字？

Question

我注意到我们的 Oracle 11.2 数据库中的 v$reserved_words View 有两行关键字为 null(值为 null，而不是单词 null)。

所以这个查询:

select * from v$reserved_words where keyword is null;

返回两行，除了一行保留 = Y 和另一行保留 = N 之外相同。知道这样做的目的是什么或如何使用吗？

Answer 1

您需要了解如何解释 View v$reserved _words .要确定特定关键字是否以任何方式保留，请检查 保留 , RES_TYPE , RES_ATTR , 和 RES_SEMI 列。

SQL> column keyword format A10;
SQL> select * from v$reserved_words where keyword is null;

KEYWORD        LENGTH R R R R D     CON_ID
---------- ---------- - - - - - ----------
                    0 Y N N N N          0
                    0 N N N N N          0

这两行的所有列都没有“Y”。是的，其中一行的 RESERVED 为“Y”，但长度为 0。此外，没有任何属性为“Y”。

从文档来看，

RESERVED    VARCHAR2(1) A value of Y means that the keyword cannot be used as an identifier. A value of N means that it is not reserved.
RES_TYPE    VARCHAR2(1) A value of Y means that the keyword cannot be used as a type name. A value of N means that it is not reserved.
RES_ATTR    VARCHAR2(1) A value of Y means that the keyword cannot be used as an attribute name. A value of N means that it is not reserved.
RES_SEMI    VARCHAR2(1) A value of Y means that the keyword is not allowed as an identifier in certain situations, such as in DML. A value of N means that it is not reserved.
DUPLICATE   VARCHAR2(1) A value of Y means that the keyword is a duplicate of another keyword. A value of N means that it is not a duplicate.

这说明了原因。

除了 v$reserved_words，您还可以通过以下方式检查 SQL*Plus 中的 SQL 和 PL/SQL 保留字:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 5 13:05:15 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Sep 04 2014 15:01:52 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> help reserve

 RESERVED WORDS (PL/SQL)
 -----------------------

 PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
 for identifier names (unless enclosed in "quotes").

 An asterisk (*) indicates words are also SQL Reserved Words.

 ALL*            DESC*           JAVA            PACKAGE         SUBTYPE
 ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL*
 AND*            DO              LIKE*           PCTFREE*        SUM
 ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*
 ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*
 AS*             ELSIF           LONG*           POSITIVEN       TABLE*
 ASC*            END             LOOP            PRAGMA          THEN*
 AT              EXCEPTION       MAX             PRIOR*          TIME
 AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP
 AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR
 BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR
 BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE
 BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION
 BODY            EXTRACT         MODE*           RAW*            TO*
 BOOLEAN         FALSE           MONTH           REAL            TRIGGER*
 BULK            FETCH           NATURAL         RECORD          TRUE
 BY*             FLOAT*          NATURALN        REF             TYPE
 CHAR*           FOR*            NEW             RELEASE         UI
 CHAR_BASE       FORALL          NEXTVAL         RETURN          UNION*
 CHECK*          FROM*           NOCOPY          REVERSE         UNIQUE*
 CLOSE           FUNCTION        NOT*            ROLLBACK        UPDATE*
 CLUSTER*        GOTO            NOWAIT*         ROW*            USE
 COALESCE        GROUP*          NULL*           ROWID*          USER*
 COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*
 COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*
 COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*
 COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*
 CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE
 CONSTANT        IN*             ON*             SEPERATE        VIEW*
 CREATE*         INDEX*          OPAQUE          SET*            WHEN
 CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*
 CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*
 CURSOR          INTEGER*        OPTION*         SPACE           WHILE
 DATE*           INTERFACE       OR*             SQL             WITH*
 DAY             INTERSECT*      ORDER*          SQLCODE         WORK
 DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE
 DECLARE         INTO*           OTHERS          START*          YEAR
 DEFAULT*        IS*             OUT             STDDEV          ZONE
 DELETE*         ISOLATION


 RESERVED WORDS (SQL)
 --------------------

 SQL Reserved Words have special meaning in SQL, and may not be used for
 identifier names unless enclosed in "quotes".

 An asterisk (*) indicates words are also ANSI Reserved Words.

 Oracle prefixes implicitly generated schema object and subobject names
 with "SYS_". To avoid name resolution conflict, Oracle discourages you
 from prefixing your schema object and subobject names with "SYS_".

 ACCESS          DEFAULT*         INTEGER*        ONLINE          START
 ADD*            DELETE*          INTERSECT*      OPTION*         SUCCESSFUL
 ALL*            DESC*            INTO*           OR*             SYNONYM
 ALTER*          DISTINCT*        IS*             ORDER*          SYSDATE
 AND*            DROP*            LEVEL*          PCTFREE         TABLE*
 ANY*            ELSE*            LIKE*           PRIOR*          THEN*
 AS*             EXCLUSIVE        LOCK            PRIVILEGES*     TO*
 ASC*            EXISTS           LONG            PUBLIC*         TRIGGER
 AUDIT           FILE             MAXEXTENTS      RAW             UID
 BETWEEN*        FLOAT*           MINUS           RENAME          UNION*
 BY*             FOR*             MLSLABEL        RESOURCE        UNIQUE*
 CHAR*           FROM*            MODE            REVOKE*         UPDATE*
 CHECK*          GRANT*           MODIFY          ROW             USER*
 CLUSTER         GROUP*           NOAUDIT         ROWID           VALIDATE
 COLUMN          HAVING*          NOCOMPRESS      ROWNUM          VALUES*
 COMMENT         IDENTIFIED       NOT*            ROWS*           VARCHAR*
 COMPRESS        IMMEDIATE*       NOWAIT          SELECT*         VARCHAR2
 CONNECT*        IN*              NULL*           SESSION*        VIEW*
 CREATE*         INCREMENT        NUMBER          SET*            WHENEVER*
 CURRENT*        INDEX            OF*             SHARE           WHERE
 DATE*           INITIAL          OFFLINE         SIZE*           WITH*
 DECIMAL*        INSERT*          ON*             SMALLINT*


SQL>

添加更多信息以阐明这两行是不同的类型。

中的支持说明我的 Oracle 支持 这里 https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=csshtn48w_9&_afrLoop=2343138470228212#990809说最好在 View 中检查类型: X$KWDDEF

SQL> select indx, keyword, length, type from X$KWDDEF where keyword is NULL;

      INDX KEYWORD        LENGTH       TYPE
---------- ---------- ---------- ----------
      2087                     0          2
      2088                     0          1

现在如何从 X$KWDDEF 形成 V$RESERVED_WORDS View ？这是基础查询:

SELECT inst_id, keyword, LENGTH,
   DECODE (MOD (TRUNC (TYPE / 2), 2), 0, 'N', 1, 'Y', '?') reserved,
   DECODE (MOD (TRUNC (TYPE / 4), 2), 0, 'N', 1, 'Y', '?') res_type,
   DECODE (MOD (TRUNC (TYPE / 8), 2), 0, 'N', 1, 'Y', '?') res_attr,
   DECODE (MOD (TRUNC (TYPE / 16), 2), 0, 'N', 1, 'Y', '?') res_semi,
   DECODE (MOD (TRUNC (TYPE / 32), 2), 0, 'N', 1, 'Y', '?') duplicate
  FROM x$kwddef;

那么TYPE究竟是什么呢？ TYPE 列用作对单词进行分组的桶。

SQL> select type, count(*)
  2      from x$kwddef
  3      group by type
  4      order by 1
  5  /

      TYPE   COUNT(*)
---------- ----------
         1       1939
         2         96
         9          2
        16         28
        33         20
        34          4

6 rows selected.

因此，基于 TYPE(如 Oracle 建议的)，这两行是不一样的。它们属于不同的类型。

不应将关键字“NULL”与这些行中的 NULL VALUE 错误解释。 NULL 关键字完全不同，LENGTH = 4。

SQL> select indx, keyword, length, type from X$KWDDEF where keyword = 'NULL';

      INDX KEYWORD        LENGTH       TYPE
---------- ---------- ---------- ----------
       338 NULL                4          2

由于 'X$KWDDEF' 有一个 'NULL' 关键字的条目为 TYPE 2，所以可以安全地忽略这两行。我猜 X$KWDDEF 的意思是 内核词定义 ，只是猜测!