Oracle笔记（2）：KeyWords关键字和保留字

Oracle关键字

通过PD建模，生成SQL语句导入Oracle中执行，生成表都没有问题。但是在删除，添加记录的过程中老是抛出异常，比如“表或视图不存在”；

这是生成的PL\SQL语句：

View Code

 1 /*==============================================================*/
 2 /* Table: "Branch"                                              */
 3 /*==============================================================*/
 4 create table "Branch" 
 5 (
 6    "Branch_id"          INTEGER              not null,
 7    "Branch_name"        NVARCHAR2(32),
 8    "Type"               NVARCHAR2(32),
 9    Y                    BINARY_DOUBLE,
10    X                    BINARY_DOUBLE,
11    "Mark"               NVARCHAR2(256),
12    constraint PK_BRANCH primary key ("Branch_id")
13 );
14 
15 /*==============================================================*/
16 /* Table: "CurrentBranchStatistic"                              */
17 /*==============================================================*/
18 create table "CurrentBranchStatistic" 
19 (
20    "Branch_id"          INTEGER,
21    "Number"             INTEGER
22 );

可以看到表名和字段名都被加了双引号，而且有些字段还用了关键字，如Number，Type

通过v$reserved_words视图可以查看关键字信息。

以system用户登录：

select * from v$reserved_words ;

可以查询到1700多条记录

字段类型及解释：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.

　　Y表示该关键字是保留字，不能用作标示符
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. 值为Y表示关键字在特定情况下不允许作为标示符，比如DML（数据库操纵语言）
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.

1.Oracle有许多保留字（Reserved Words)和关键字（Keywords），其区别是保留字不可以用来作为标识符，关键字可以用来作为标识符，但不建议使用。

2.一般保留字不能用做对象名。如果一定要用可以加双引号。

查询：select * from v$reserved_words where RESERVED='Y';结果如下

	KEYWORD	LENGTH	RESERVED	RES_TYPE	RES_ATTR	RES_SEMI	DUPLICATE
1	FLOAT	5	Y	N	N	N	N
2	TRIGGER	7	Y	N	N	N	N
3	CHECK	5	Y	N	N	N	N
4	TABLE	5	Y	N	N	N	N
5	CONNECT	7	Y	N	N	N	N
6	SYNONYM	7	Y	N	N	N	N
7	UNIQUE	6	Y	N	N	N	N
8	DROP	4	Y	N	N	N	N
9	ELSE	4	Y	N	N	N	N
10	SIZE	4	Y	N	N	N	N
11	IN	2	Y	N	N	N	N
12	RENAME	6	Y	N	N	N	N
13	WHERE	5	Y	N	N	N	N
14	HAVING	6	Y	N	N	N	N
15	SHARE	5	Y	N	N	N	N
16	UNION	5	Y	N	N	N	N
17	/	1	Y	N	N	N	N
18	SET	3	Y	N	N	N	N
19	(	1	Y	N	N	N	N
20	NOCOMPRESS	10	Y	N	N	N	N
21	VALUES	6	Y	N	N	N	N
22	\|	1	Y	N	N	N	N
23	REVOKE	6	Y	N	N	N	N
24	WITH	4	Y	N	N	N	N
25	NUMBER	6	Y	N	N	N	N
26	PRIOR	5	Y	N	N	N	N
27	FROM	4	Y	N	N	N	N
28	SELECT	6	Y	N	N	N	N
29	BY	2	Y	N	N	N	N
30	SMALLINT	8	Y	N	N	N	Y
31	MINUS	5	Y	N	N	N	N
32	THEN	4	Y	N	N	N	N
33	VARCHAR2	8	Y	N	N	N	N
34	INTEGER	7	Y	N	N	N	Y
35	ALL	3	Y	N	N	N	N
36	<	1	Y	N	N	N	N
37	DELETE	6	Y	N	N	N	N
38	AS	2	Y	N	N	N	N
39	PCTFREE	7	Y	N	N	N	N
40	TO	2	Y	N	N	N	N
41	ASC	3	Y	N	N	N	N
42	OPTION	6	Y	N	N	N	N
43	INTO	4	Y	N	N	N	N
44	DESC	4	Y	N	N	N	N
45	CLUSTER	7	Y	N	N	N	N
46	[	1	Y	N	N	N	N
47	DEFAULT	7	Y	N	N	N	N
48	CHAR	4	Y	N	N	N	Y
49	ORDER	5	Y	N	N	N	N
50	ALTER	5	Y	N	N	N	N
51	PUBLIC	6	Y	N	N	N	N
52	NOT	3	Y	N	N	N	N
53	BETWEEN	7	Y	N	N	N	N
54	IDENTIFIED	10	Y	N	N	N	N
55	VARCHAR	7	Y	N	N	N	N
56	OF	2	Y	N	N	N	N
57	FOR	3	Y	N	N	N	N
58	@	1	Y	N	N	N	N
59	ANY	3	Y	N	N	N	N
60	INSERT	6	Y	N	N	N	N
61	^	1	Y	N	N	N	N
62	!	1	Y	N	N	N	N
63	-	1	Y	N	N	N	N
64	DECIMAL	7	Y	N	N	N	Y
65	START	5	Y	N	N	N	N
66	EXCLUSIVE	9	Y	N	N	N	N
67	UPDATE	6	Y	N	N	N	N
68	=	1	Y	N	N	N	N
69	NOWAIT	6	Y	N	N	N	N
70	VIEW	4	Y	N	N	N	N
71	RESOURCE	8	Y	N	N	N	N
72	MODE	4	Y	N	N	N	N
73	]	1	Y	N	N	N	N
74	DATE	4	Y	N	N	N	N
75	NULL	4	Y	N	N	N	N
76	>	1	Y	N	N	N	N
77	&	1	Y	N	N	N	N
78	LOCK	4	Y	N	N	N	N
79	RAW	3	Y	N	N	N	N
80	LIKE	4	Y	N	N	N	N
81	.	1	Y	N	N	N	N
82	OR	2	Y	N	N	N	N
83	,	1	Y	N	N	N	N
84	GRANT	5	Y	N	N	N	N
85	*	1	Y	N	N	N	N
86	INTERSECT	9	Y	N	N	N	N
87	ON	2	Y	N	N	N	N
88	EXISTS	6	Y	N	N	N	N
89	LONG	4	Y	N	N	N	N
90	+	1	Y	N	N	N	N
91	COMPRESS	8	Y	N	N	N	N
92	INDEX	5	Y	N	N	N	N
93	IS	2	Y	N	N	N	N
94	CREATE	6	Y	N	N	N	N
95	DISTINCT	8	Y	N	N	N	N
96	AND	3	Y	N	N	N	N
97	)	1	Y	N	N	N	N
98	GROUP	5	Y	N	N	N	N
99	:	1	Y	N	N	N	N
100		0	Y	N	N	N	N

查询：select * from v$reserved_words where RES_SEMI='Y';结果如下：

	KEYWORD	LENGTH	RESERVED	RES_TYPE	RES_ATTR	RES_SEMI	DUPLICATE
1	INCREMENT	9	N	N	N	Y	N
2	SYSDATE	7	N	N	N	Y	N
3	ONLINE	6	N	N	N	Y	N
4	VALIDATE	8	N	N	N	Y	N
5	MODIFY	6	N	N	N	Y	N
6	MAXEXTENTS	10	N	N	N	Y	N
7	UID	3	N	N	N	Y	N
8	USER	4	N	N	N	Y	N
9	ROW	3	N	N	N	Y	N
10	ROWS	4	N	N	N	Y	N
11	SUCCESSFUL	10	N	N	N	Y	N
12	IMMEDIATE	9	N	N	N	Y	N
13	MLSLABEL	8	N	N	N	Y	N
14	COLUMN	6	N	N	N	Y	N
15	INITIAL	7	N	N	N	Y	N
16	ROWNUM	6	N	N	N	Y	N
17	OFFLINE	7	N	N	N	Y	N
18	WHENEVER	8	N	N	N	Y	N
19	CURRENT	7	N	N	N	Y	N
20	SESSION	7	N	N	N	Y	N
21	PRIVILEGES	10	N	N	N	Y	N
22	NOAUDIT	7	N	N	N	Y	N
23	LEVEL	5	N	N	N	Y	N
24	ACCESS	6	N	N	N	Y	N
25	AUDIT	5	N	N	N	Y	N
26	ADD	3	N	N	N	Y	N
27	FILE	4	N	N	N	Y	N
28	COMMENT	7	N	N	N	Y	N
29	ROWID	5	N	N	N	Y	N