Oracle别名使用要点小结

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

Error:
ORA-00972: identifier is too long
Cause:
You tried to reference a table, cluster, view, index, synonym, tablespace, or username with a value that was longer than 30 characters.
Action:
The options to resolve this Oracle error are:
Names for tables, clusters, views, indexes, synonyms, tablespaces, and usernames must be 30 characters or less. You must shorten the name to no more than 30 characters for these objects.

以上内容是我分析和解决我的SQL语句中，查询时列的别名过长问题的过程，也是我分享这篇小博文的引子！

下面是我针对别名的一个小结（注意：我的测试数据库是ORACLE 11.2.0.4.0 字符集是AMERICAN_AMERICA.AL32UTF8）

一：使用别名的好处

1）简化SQL语句的书写，特别是多表关联查询且表名较长时

2）增强SQL语句的可读性，特别是当查询出来的列字段较为复杂时效果更为明显

二：测试小结

1）测试时使用的简单表结构

CREATE TABLE SALES.STUDENT
(
  ID        NUMBER,
  NAME      VARCHAR2(20 BYTE),
  SEX       CHAR(1 BYTE),
  BIRTHDAY  DATE,
  HOBBY     VARCHAR2(20 CHAR)
)

2）测试使用的若干条测试数据

3）列名的最大长度——30个字节

SELECT ID ABCDEABCDEABCDEABCDEABCDEABCDE--30个英文大写字符，正常执行
FROM STUDENT

SELECT ID ABCDEABCDEABCDEABCDEABCDEABCDE1--多加另一个1就会报ORA-00972:identifier is to long
FROM STUDENT

SELECT ID 我们学校的好学生学号--10个中文汉字字符，正常执行
FROM STUDENT

SELECT ID 我们学校的好学生学号1--多加另一个1就会报ORA-00972:identifier is to long
FROM STUDENT

4）是否可以使用AS关键字——可以使用

SELECT ID AS 学号--使用AS的情况，正常执行
FROM STUDENT

SELECT ID 学号--不适用AS也可以正常执行，而且代码更简洁，建议使用方式
FROM STUDENT

5）是否可以以数字开头——可以以数字开头但必须使用双引号括起来

SELECT ID 123学号--执行时报"ORA-00923: FROM keyword not found where expected"
FROM STUDENT

SELECT ID "123学号"--正常执行

6）对于中文别名双引号、单引号、不使用引号的区别（注意：全是英文的单引或双引符号）——使用单引符号时报错

SELECT ID 学号--正常执行
FROM STUDENT

SELECT ID "学号"--正常执行
FROM STUDENT

SELECT ID '学号'--执行时报"ORA-00923: FROM keyword not found where expected"
FROM STUDENT

SELECT ID StudentId--正常执行
FROM STUDENT

SELECT ID "StudentId"--正常执行
FROM STUDENT

SELECT ID 'StudentId'--执行时报"ORA-00923: FROM keyword not found where expected"
FROM STUDENT

7）是否可以使用其他的特殊符号比如：小括号——可以使用但必须使用双引号括起来（注意：其他的特殊符号可能类似，这里以小括号为例做了测试，其他情况如果感兴趣可以自己动手试试）

SELECT ID AS (学号)--英文小括号且使用AS的情形，执行时报"ORA-00923: FROM keyword not found where expected"
FROM STUDENT

SELECT ID (学号)--英文小括号，执行时报"ORA-00904: "ID": invalid identifier"
FROM STUDENT

SELECT ID AS "(学号)"--正常执行
FROM STUDENT

SELECT ID AS（学号）--中文小括号且使用AS的情形，执行时报"ORA-00923: FROM keyword not found where expected"
FROM STUDENT

SELECT ID（学号）--中文小括号，执行时报"ORA-00904: "ID": invalid identifier"
FROM STUDENT

SELECT ID "（学号）"--正常执行
FROM STUDENT

8）是否可以保留别名中的空格——可以，但必须使用双引号括起来

SELECT ID 学 号--执行时报"ORA-00923: FROM keyword not found where expected"
FROM STUDENT

SELECT ID "学 号"--正常执行
FROM STUDENT

SELECT ID XUE HAO--执行时报"ORA-00923: FROM keyword not found where expected"
FROM STUDENT

SELECT ID "XUE HAO"--正常执行
FROM STUDENT

9）使用别名时因SQL语句各子句的执行顺序引起的问题

ORACLE中的SQL语句，

1.WHERE/GROUP BY/HAVING子句中只能直接使用栏位或者常量，而不能使用栏位的别名，除非这个别名来自子查询之中，如：SELECT .... FROM (SELECT COLUMN_NAME C FROM TABLE_NAME) WHERE C > 1
2.而ORDER BY 则可以直接使用别名，如SELECT COLUMN_NAME C FROM TABLE_NAME ORDER BY C

这和SQL的执行顺序是有关的，SQL语句的执行顺序大致如下所示：

1. FROM语句

2. WHERE语句(结合条件)

3. START WITH语句

4. CONNECT BY语句

5. WHERE语句

6. GROUP BY语句

7. HAVING语句

8. MODEL语句

9. SELECT语句

10. UNION、MINUS、INTERSECT等集合演算演算

11. ORDER BY语句

我们可以看到SELECT子句是在WHERE子句执行后才执行的，当SELECT子句执行时查询列的别名才生成，所以在 WHERE子句中根本看不到列的别名，当然，自然也就无法引用列的别名了。所以字段、表达式的别名在WHERE子句和GROUP BY子句都是不能使用的，而在ORDER BY中不仅可以使用别名，甚至可以直接使用栏位的下标来进行排序，如：ORDER BY ID或者ORDER BY 1

SELECT ID ID_ 
FROM STUDENT 
WHERE ID_=11--执行时报"ORA-00904: "ID_": invalid identifier"

SELECT ID ID_ 
FROM STUDENT 
WHERE ID=11--正常执行

SELECT ID ID_ 
FROM STUDENT 
WHERE ID='11'--正常执行，注意：ID是NUMBER类型的数据

SELECT ID ID_ 
FROM STUDENT 
WHERE ID="11"--执行时报"ORA-00904: "11": invalid identifier"，注意：ID是NUMBER类型的数据


SELECT ID,COUNT(*) C
FROM STUDENT
GROUP BY ID
      HAVING C>0--执行时报"ORA-00904: "C": invalid identifier"
      
SELECT ID,COUNT(*) C
FROM STUDENT
GROUP BY ID
      HAVING Count(*)>0--正常执行   

SELECT ID,COUNT(*)C
FROM STUDENT
GROUP BY ID
      HAVING COUNT(*)>0
ORDER BY C--正常执行


SELECT ID ID_
FROM STUDENT
ORDER BY ID_--正常执行

SELECT ID ID_
FROM STUDENT
ORDER BY 1--正常执行

10）别名是否区分大小写——区分大小写（注意：当不用双引号括起来的时候英文字符大小写不敏感）

SELECT XUEHAO
FROM (SELECT ID XUEHAO FROM STUDENT) 
WHERE XUEHAO > 1--正常执行

SELECT XUEHAO
FROM (SELECT ID xuehao FROM STUDENT) 
WHERE XUEHAO > 1--正常执行

SELECT XUEHAO
FROM (SELECT ID "XUEHAO" FROM STUDENT) 
WHERE XUEHAO > 1--正常执行

SELECT XUEHAO
FROM (SELECT ID "xuehao" FROM STUDENT) 
WHERE XUEHAO > 1--执行时报"ORA-00904: "XUEHAO": invalid identifier"

SELECT XUEHAO
FROM (SELECT ID "xuehao" FROM STUDENT) 
WHERE xuehao > 1--执行时报"ORA-00904: "XUEHAO": invalid identifier"

SELECT XUEHAO
FROM (SELECT ID "xuehao" FROM STUDENT) 
WHERE "xuehao" > 1--执行时报"ORA-00904: "XUEHAO": invalid identifier"
SELECT "xuehao"
FROM (SELECT ID "xuehao" FROM STUDENT) 
WHERE "xuehao" > 1--正常执行

这个小例子也可以看到SQL语句是先执行WHERE子句然后才执行SELECT子句的！