Q' 的使用

最近在项目经常看见使用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?