oracle关键字作为列名

╰半橙微兮° 2021-06-24 13:59 631阅读 0赞

在创建表时如果使用了Oralce的某些保留字，将会收到系统提示的“无效名”等错误，那么Oracle都有哪些保留字需要回避呢？   
这个问题完全可以使用Oralce自带的V$RESERVED\_WORDS视图来回答，不同的版本中这个视图中记录的内容是有差别的，以实际环境查询内容为准。   
  
简单演示一下使用保留字创建表时报错的现象。当看到有类似“invalid ... name”等错误时，就要提高警惕了，不是你敲错了字母就是用到了某些被禁止使用的保留字。   
  
1.保留字作为列名时   
例如，我们打算使用“number”这个名字作为表的一个列名，尝试创建这个表是就会收到报错   
sec@ora10g> create table t (number int);   
create table t (number int)   
                \*   
ERROR at line 1:   
ORA-00904: : invalid identifier   
  
2.保留字作为表名时   
使用“number”作为表名也是一样会报错   
sys@ora10g> create table number ( x int);   
create table number ( x int)   
             \*   
ERROR at line 1:   
ORA-00903: invalid table name   
  
3.查询一下还有哪些保留字不可以作为标识符（以Oracle 10gR2环境为例）   
如果V$RESERVED\_WORDS视图的RESERVED字段内容是“Y”表示这个保留字不可以在标识符中使用的。   
sys@ora10g> select \* from v$reserved\_words where RESERVED = 'Y';   
  
KEYWORD                            LENGTH R R R R D   
\------------------------------ ---------- - - - - -   
SHARE                                   5 Y N N N N   
\+                                       1 Y N N N N   
PCTFREE                                 7 Y N N N N   
EXISTS                                  6 Y N N N N   
&                                       1 Y N N N N   
INSERT                                  6 Y N N N N   
DROP                                    4 Y N N N N   
BETWEEN                                 7 Y N N N N   
FROM                                    4 Y N N N N   
)                                       1 Y N N N N   
DESC                                    4 Y N N N N   
OPTION                                  6 Y N N N N   
TO                                      2 Y N N N N   
PRIOR                                   5 Y N N N N   
LONG                                    4 Y N N N N   
THEN                                    4 Y N N N N   
DEFAULT                                 7 Y N N N N   
IS                                      2 Y N N N N   
,                                       1 Y N N N N   
INTO                                    4 Y N N N N   
HAVING                                  6 Y N N N N   
MINUS                                   5 Y N N N N   
INTEGER                                 7 Y N N N Y   
UPDATE                                  6 Y N N N N   
GRANT                                   5 Y N N N N   
/                                       1 Y N N N N   
ALL                                     3 Y N N N N   
^                                       1 Y N N N N   
ORDER                                   5 Y N N N N   
EXCLUSIVE                               9 Y N N N N   
FLOAT                                   5 Y N N N N   
DATE                                    4 Y N N N N   
ON                                      2 Y N N N N   
NUMBER                                  6 Y N N N N   
UNION                                   5 Y N N N N   
RESOURCE                                8 Y N N N N   
PUBLIC                                  6 Y N N N N   
TABLE                                   5 Y N N N N   
VARCHAR2                                8 Y N N N N   
@                                       1 Y N N N N   
ELSE                                    4 Y N N N N   
VALUES                                  6 Y N N N N   
.                                       1 Y N N N N   
RENAME                                  6 Y N N N N   
AS                                      2 Y N N N N   
=                                       1 Y N N N N   
ALTER                                   5 Y N N N N   
INDEX                                   5 Y N N N N   
FOR                                     3 Y N N N N   
WHERE                                   5 Y N N N N   
CHECK                                   5 Y N N N N   
SMALLINT                                8 Y N N N Y   
WITH                                    4 Y N N N N   
DELETE                                  6 Y N N N N   
REVOKE                                  6 Y N N N N   
(                                       1 Y N N N N   
SIZE                                    4 Y N N N N   
NOCOMPRESS                             10 Y N N N N   
>                                       1 Y N N N N   
AND                                     3 Y N N N N   
|                                       1 Y N N N N   
:                                       1 Y N N N N   
NULL                                    4 Y N N N N   
GROUP                                   5 Y N N N N   
ASC                                     3 Y N N N N   
IN                                      2 Y N N N N   
VIEW                                    4 Y N N N N   
SET                                     3 Y N N N N   
COMPRESS                                8 Y N N N N   
\-                                       1 Y N N N N   
\[                                       1 Y N N N N   
<                                       1 Y N N N N   
NOT                                     3 Y N N N N   
LIKE                                    4 Y N N N N   
TRIGGER                                 7 Y N N N N   
SELECT                                  6 Y N N N N   
CLUSTER                                 7 Y N N N N   
LOCK                                    4 Y N N N N   
CREATE                                  6 Y N N N N   
INTERSECT                               9 Y N N N N   
\]                                       1 Y N N N N   
DISTINCT                                8 Y N N N N   
!                                       1 Y N N N N   
CONNECT                                 7 Y N N N N   
MODE                                    4 Y N N N N   
OF                                      2 Y N N N N   
RAW                                     3 Y N N N N   
\*                                       1 Y N N N N   
UNIQUE                                  6 Y N N N N   
SYNONYM                                 7 Y N N N N   
VARCHAR                                 7 Y N N N N   
ANY                                     3 Y N N N N   
DECIMAL                                 7 Y N N N Y   
IDENTIFIED                             10 Y N N N N   
OR                                      2 Y N N N N   
START                                   5 Y N N N N   
NOWAIT                                  6 Y N N N N   
BY                                      2 Y N N N N   
CHAR                                    4 Y N N N Y   
  
99 rows selected.   
  
4.强制使用保留字的方法   
如果要强制使用这些保留字，可以使用双引号将保留字括起来使用。不过如果使用了保留字，那么使用起时将非常的不便，而且极易出现混淆的问题，因此应该严格禁止这种情况的发生。   
1）以上面的例子为例演示一下使用保留字成功创建表的情况   
sys@ora10g> create table t ("number" int);   
  
Table created.   
  
sys@ora10g> create table "number" ( x int);   
  
Table created.   
  
sys@ora10g> desc t   
Name                      Null?    Type   
\------------------------- -------- ----------------   
number                             NUMBER(38)   
  
2）此时必须使用带引号的形式引用这个特殊的表名，操作不便，而且容易出现错误。   
sys@ora10g> desc "number"   
Name                      Null?    Type   
\------------------------- -------- ----------------   
X                                  NUMBER(38)   
  
5.V$RESERVED\_WORDS视图中的其他列的的含义请参考Oracle的官方文档   
http://download.oracle.com/docs/cd/B19306\_01/server.102/b14237/dynviews\_2048.htm\#REFRN30204   
  
V$RESERVED\_WORDSThis view gives a list of all SQL keywords. To determine whether a particular keyword is reserved in any way, check theRESERVED,RES\_TYPE,RES\_ATTR, andRES\_SEMIcolumns.   
  
Column Datatype Description   
KEYWORD VARCHAR2(30) Name of the keyword   
LENGTH NUMBER Length of the keyword   
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.   
  
  
  
6.小结   
很多问题Oracle都在内部给出了答案，很是便利。如本例中使用V$RESERVED\_WORDS视图快速得到保留字的功能就是一个很好的例子。