Know Difference between Oracle Reserved Words and Keywords

与C和JAVA体系类似,Oracle中存在一些保留词(Reserved Words)或关键词(Keywords)以及少量的命名空间(Namespace)。

保留词(Reserved Words)是优先级最高的词汇,在Oracle中这些词都有着特殊的意义。所以这些词不允许被重定义 (redefine),换而言之就不是不允许用作对象的名字。以下为Oracle 10g中保留词的列表:

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

注意虽然我们不能直接将保留词用于对象名,但还是可以通过特殊手段来完成这种不推荐的定义目的,如:

如上表所列START为一个保留词
SQL> CREATE TABLE START (T1 INT);

CREATE TABLE START (T1 INT)

ORA-00903: invalid table name

利用双引号可以成功将保留词用作对象名,但是在管理时会很麻烦,极力不推荐!

SQL> CREATE TABLE "START" (T1 INT);

Table created

SQL> SELECT * FROM START;

SELECT * FROM START

ORA-00903: invalid table name

SQL> SELECT * FROM "START";

                                     T1
---------------------------------------

SQL> SELECT * FROM "start";

SELECT * FROM "start"

ORA-00942: table or view does not exist

SQL> select object_name from dba_objects where object_name='START';

OBJECT_NAME
--------------------------------------------------------------------------------
START

SQL> DROP TABLE "start";

DROP TABLE "start"

ORA-00942: table or view does not exist

SQL> DROP TABLE "START";

Table dropped

 

关键词同样是在Oracle中具有特殊意义的词汇,但是其可以用作重定义(redefine)。Oracle文档介绍这些关键词可能在今后的版本中变成保留词。以下为Oracle 10g中关键词的列表:

Oracle Keywords
ADMIN CURSOR FOUND MOUNT
AFTER CYCLE FUNCTION NEXT
ALLOCATE DATABASE GO NEW
ANALYZE DATAFILE GOTO NOARCHIVELOG
ARCHIVE DBA GROUPS NOCACHE
ARCHIVELOG DEC INCLUDING NOCYCLE
AUTHORIZATION DECLARE INDICATOR NOMAXVALUE
AVG DISABLE INITRANS NOMINVALUE
BACKUP DISMOUNT INSTANCE NONE
BEGIN DOUBLE INT NOORDER
BECOME DUMP KEY NORESETLOGS
BEFORE EACH LANGUAGE NORMAL
BLOCK ENABLE LAYER NOSORT
BODY END LINK NUMERIC
CACHE ESCAPE LISTS OFF
CANCEL EVENTS LOGFILE OLD
CASCADE EXCEPT MANAGE ONLY
CHANGE EXCEPTIONS MANUAL OPEN
CHARACTER EXEC MAX OPTIMAL
CHECKPOINT EXPLAIN MAXDATAFILES OWN
CLOSE EXECUTE MAXINSTANCES PACKAGE
COBOL EXTENT MAXLOGFILES PARALLEL
COMMIT EXTERNALLY MAXLOGHISTORY PCTINCREASE
COMPILE FETCH MAXLOGMEMBERS PCTUSED
CONSTRAINT FLUSH MAXTRANS PLAN
CONSTRAINTS FREELIST MAXVALUE PLI
CONTENTS FREELISTS MIN PRECISION
CONTINUE FORCE MINEXTENTS PRIMARY
CONTROLFILE FOREIGN MINVALUE PRIVATE
COUNT FORTRAN MODULE PROCEDURE
PROFILE SAVEPOINT SQLSTATE TRACING
QUOTA SCHEMA STATEMENT_ID TRANSACTION
READ SCN STATISTICS TRIGGERS
REAL SECTION STOP TRUNCATE
RECOVER SEGMENT STORAGE UNDER
REFERENCES SEQUENCE SUM UNLIMITED
REFERENCING SHARED SWITCH UNTIL
RESETLOGS SNAPSHOT SYSTEM USE
RESTRICTED SOME TABLES USING
REUSE SORT TABLESPACE WHEN
ROLE SQL TEMPORARY WRITE
ROLES SQLCODE THREAD WORK
ROLLBACK SQLERROR TIME

 

Pl/SQL中还有部分的保留词(PL/SQL Reserved Words),在嵌入SQL语句需要对这些词汇特殊处理。

PL/SQL Reserved Words
ABORT BETWEEN CRASH DIGITS
ACCEPT BINARY_INTEGER CREATE DISPOSE
ACCESS BODY CURRENT DISTINCT
ADD BOOLEAN CURRVAL DO
ALL BY CURSOR DROP
ALTER CASE DATABASE ELSE
AND CHAR DATA_BASE ELSIF
ANY CHAR_BASE DATE END
ARRAY CHECK DBA ENTRY
ARRAYLEN CLOSE DEBUGOFF EXCEPTION
AS CLUSTER DEBUGON EXCEPTION_INIT
ASC CLUSTERS DECLARE EXISTS
ASSERT COLAUTH DECIMAL EXIT
ASSIGN COLUMNS DEFAULT FALSE
AT COMMIT DEFINITION FETCH
AUTHORIZATION COMPRESS DELAY FLOAT
AVG CONNECT DELETE FOR
BASE_TABLE CONSTANT DELTA FORM
BEGIN COUNT DESC FROM
FUNCTION NEW RELEASE SUM
GENERIC NEXTVAL REMR TABAUTH
GOTO NOCOMPRESS RENAME TABLE
GRANT NOT RESOURCE TABLES
GROUP NULL RETURN TASK
HAVING NUMBER REVERSE TERMINATE
IDENTIFIED NUMBER_BASE REVOKE THEN
IF OF ROLLBACK TO
IN ON ROWID TRUE
INDEX OPEN ROWLABEL TYPE
INDEXES OPTION ROWNUM UNION
INDICATOR OR ROWTYPE UNIQUE
INSERT ORDER RUN UPDATE
INTEGER OTHERS SAVEPOINT USE
INTERSECT OUT SCHEMA VALUES
INTO PACKAGE SELECT VARCHAR
IS PARTITION SEPARATE VARCHAR2
LEVEL PCTFREE SET VARIANCE
LIKE POSITIVE SIZE VIEW
LIMITED PRAGMA SMALLINT VIEWS
LOOP PRIOR SPACE WHEN
MAX PRIVATE SQL WHERE
MIN PROCEDURE SQLCODE WHILE
MINUS PUBLIC SQLERRM WITH
MLSLABEL RAISE START WORK
MOD RANGE STATEMENT XOR
MODE REAL STDDEV
NATURAL RECORD SUBTYPE

 

除去保留词和关键词外,Oracle内部还存在一张命名空间(namespace)的列表,这些命名空间或为OCI函数或为Oracle内部函数作为函数的起始字符,如SQL*NET透明网络服务相关函数都以”NS”为函数名的开头,Oracle不推荐使用用户函数使用这些保留的命名空间为开头,以避免潜在的冲突可能。

Oracle Reserved Namespaces

Namespace Library
O OCI functions
S function names from SQLLIB and system-dependent libraries
XA external functions for XA applications only
GEN KP L NA NC ND NL NM NR NS NT NZ TTC UPI Internal functions

沪ICP备14014813号-2

沪公网安备 31010802001379号