最近在项目经常看见使用q', 于是决定研究一下。 q'是oracle10g才有的新特性。 主要用于把字符串包含起来进行赋值。 特别在dynamic sql中,如果被包含的sql很复杂, 而且有单引号的情况下, 可以简单的表达,不用去拆分sql,和判断单双引号, 给程序的编写和阅读带来很大方便。q'可以支持多种配对分隔符。如#,{},[],/等等。
note that: 不管是q',还是原先得single quote, 都支持换行书写同一个字符串。
字符串不支持双引号引用字符串, 而且在字符串中,用双引号代替单引号的方法好像也不好用。
SQL> select 'i am your boyfriend' MESSAGE from dual;
MESSAGE
--------------------------------------
i am your boyfriend
SQL>
SQL> select 'i am "your" boyfriend' MESSAGE from dual;
MESSAGE
------------------------------------------
i am "your" boyfriend
SQL>
SQL>
SQL> select "i am 'your' boyfriend" MESSAGE from dual;
select "i am 'your' boyfriend" MESSAGE from dual
*
第 1 行出现错误:
ORA-00904: "i am 'your' boyfriend": invalid identifier
SQL>
SQL> select 'i am your
2 boyfriend' MESSAGE from dual;
MESSAGE
----------------------------------------
i am your
boyfriend
SQL>
SQL> select q'{what the fuck ' are you doing?}' MESSAGE from dual;
MESSAGE
------------------------------------------------------------
what the fuck ' are you doing?
SQL>
SQL>
SQL> select q'/what the fuck ' are you doing?/' MESSAGE from dual;
MESSAGE
------------------------------------------------------------
what the fuck ' are you doing?
SQL>
SQL> select q'{what the fuck
2 ' are you doing?}' MESSAGE from dual;
MESSAGE
--------------------------------------------------------------
what the fuck
' are you doing?