Oracle中的双引号与单引号

场景一数据库对象名称

创建对象时对象名称可以加双引号不能加单引号。加双引号表示区分大小写不加双引号表示默认大写。

Example 1创建表空间

SQL> create tablespace omf_tbs1;
Tablespace created.

SQL> create tablespace "omf_tbs2";
Tablespace created.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
OMF_TBS1
omf_tbs2

SQL> create tablespace 'omf_tbs3';
create tablespace 'omf_tbs3'
                  *
ERROR at line 1:
ORA-02216: tablespace name expected

Example 2创建用户及授权

SQL> create user miguel identified by "Xqc$689" default tablespace omf_tbs1;
User created.

SQL> create user "pablo" identified by Milf377 default tablespace omf_tbs2;
create user "pablo" identified by Milf377 default tablespace omf_tbs2
*
ERROR at line 1:
ORA-00959: tablespace 'OMF_TBS2' does not exist

SQL> create user "pablo" identified by Milf377 default tablespace "omf_tbs2";
User created.

SQL> select username from dba_users where username like 'MIGUEL';
USERNAME
--------------------------------------------------------------------------------
MIGUEL

SQL> select username from dba_users where username like 'pablo';
USERNAME
--------------------------------------------------------------------------------
pablo

SQL> create user 'Phoebe' identified by "Pwd3457";
create user 'Phoebe' identified by "Pwd3457"
            *
ERROR at line 1:
ORA-01935: missing user or role name

给用户授权的情况与上面类似大写的用户名可以加也可以不加双引号小写的用户名要加双引号。但是不能给用户名加单引号。

SQL> grant create session to miguel;
Grant succeeded.

SQL> grant resource,connect to "MIGUEL";
Grant succeeded.

SQL> grant create session to pablo;
grant create session to pablo
                        *
ERROR at line 1:
ORA-01917: user or role 'PABLO' does not exist

SQL> grant create session to "pablo";
Grant succeeded.

SQL> grant resource,connect to 'pablo';
grant resource,connect to 'pablo'
                          *
ERROR at line 1:
ORA-00987: missing or invalid username(s)

Example 3用户登录

对于小写的用户名登录时要加双引号。

SQL> conn miguel/Xqc$689
Connected.

SQL> conn MIGUEL/Xqc$689
Connected.

SQL> conn pablo/Milf377
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn "pablo"/Milf377
Connected.

使用SQLPlus在终端登录时注意对用户名和密码中的特殊字符进行转义比如引号、$等。

[oracle@oracledb ~]$ sqlplus miguel/Xqc$689

ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@oracledb ~]$ sqlplus miguel/Xqc\$689   -- 这里$前有一个转义符\
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@oracledb ~]$ sqlplus "pablo"/Milf377

ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@oracledb ~]$ sqlplus \"pablo\"/Milf377
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

场景二用户密码

创建用户时密码可以也可以不加双引号。不管加不加双引号密码都区分大小写。密码不能加单引号。

SQL> create user miguel identified by "Xqc$689" default tablespace omf_tbs1;
User created.

SQL> create user "pablo" identified by Milf377 default tablespace "omf_tbs2";
User created.

SQL> create user phoebe identified by 'Jojo666';
create user phoebe identified by 'Jojo666'
                                 *
ERROR at line 1:
ORA-00988: missing or invalid password(s)

场景三字段列名称

对于列名称不能加双引号如果加了单引号字段名称会被转化成纯字符串。

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
OMF_TBS1
omf_tbs2

7 rows selected.

SQL> select "TBALESPACE_NAME" from dba_tablespaces;
select "TBALESPACE_NAME" from dba_tablespaces
       *
ERROR at line 1:
ORA-00904: "TBALESPACE_NAME": invalid identifier

SQL> select 'TBALESAPCE_NAME' from dba_tablespaces;
'TBALESAPCE_NAME'
----------------
TBALESAPCE_NAME
TBALESAPCE_NAME
TBALESAPCE_NAME
TBALESAPCE_NAME
TBALESAPCE_NAME
TBALESAPCE_NAME
TBALESAPCE_NAME

7 rows selected.

SQL> select sysdate from dual;
SYSDATE
---------
12-JAN-23

SQL> select "SYSDATE" from dual;
ERROR:
ORA-01741: illegal zero-length identifier

SQL> select 'sysdate' from dual;
'SYSDATE'
---------
sysdate

场景四字段列的值

对于字段的值必须加单引号并且区分大小写。

示例1

SQL> select username from dba_users where username like "MIGUEL";
select username from dba_users where username like "MIGUEL"
                                                   *
ERROR at line 1:
ORA-00904: "MIGUEL": invalid identifier

SQL> select username from dba_users where username like MIGUEL;
select username from dba_users where username like MIGUEL
                                                   *
ERROR at line 1:
ORA-00904: "MIGUEL": invalid identifier

SQL> select username from dba_users where username like 'MIGUEL';
USERNAME
--------------------------------------------------------------------------------
MIGUEL

SQL> select username from dba_users where username like 'pablo';
USERNAME
--------------------------------------------------------------------------------
pablo

示例2

SQL> select username from dba_users where username="pablo";
select username from dba_users where username="pablo"
                                              *
ERROR at line 1:
ORA-00904: "pablo": invalid identifier

SQL> select username from dba_users where username=pablo;
select username from dba_users where username=pablo
                                              *
ERROR at line 1:
ORA-00904: "PABLO": invalid identifier

SQL> select username from dba_users where username='pablo';
USERNAME
--------------------------------------------------------------------------------
pablo

SQL> select username from dba_users where username='PABLO';
no rows selected

---

📖总的来说单双引号的使用大致满足以下规则

• 对于对象名称例如用户名、表空间名不能使用单引号因为会被转化为纯字符串可以使用双引号此时区分大小写。
• 对于用户密码不能使用单引号可以使用双引号不管加不加双引号都区分大小写。
• 对于字段列的名称不能使用双引号如果使用单引号则会被转化为纯字符串。
• 对于字段列的值必须加单引号并且区分大小写。