oracle连接符使用说明: 把列与列,列与字符连接到一起.用||表示,可以用来合成列. 下面是一张测试表 里面有2条数据:

SQL> select id,name from test2.test2;

        ID NAME
---------- --------
         1 zhang02
         2 lisi02

演示示例1:

SQL> select id || name from test2.test2;

ID||NAME
------------------------------------------------
1zhang02
2lisi02

演示示例二：

SQL> select id ||'-'|| name from test2.test2;

ID||'-'||NAME
-------------------------------------------------
1-zhang02
2-lisi02

SQL> select id ||'+'|| name from test2.test2;

ID||'+'||NAME
-------------------------------------------------
1+zhang02
2+lisi02

SQL> select id ||'@'|| name from test2.test2;

ID||'@'||NAME
-------------------------------------------------
1@zhang02
2@lisi02

SQL> select id ||' %s job is '|| name from test2.test2;

ID||'%SJOBIS'||NAME
-----------------------------------------------------------
1 %s job is zhang02
2 %s job is lisi02

SQL> 
SQL> select id ||' $s job is '|| name from test2.test2;

ID||'$SJOBIS'||NAME
-----------------------------------------------------------
1 $s job is zhang02
2 $s job is lisi02

演示示例三: 特殊情况：需要使用单引号时，通常在字符串中应该出现的单引号前面再加一个单引号进行转义

SQL> select id ||'''s job is '|| name from test2.test2;

ID||'''SJOBIS'||NAME
----------------------------------------------------------
1's job is zhang02
2's job is lisi02

演示示例四： 引用用算符:{} [] \ <> 等符号内部的字符作为普通字符正常显示

SQL>  select id ||'{@xiaohua}'|| name from test2.test2;

ID||'{@XIAOHUA}'||NAME
----------------------------------------------------------
1{@xiaohua}zhang02
2{@xiaohua}lisi02

SQL>  select id ||'{@xiaohua}'|| name from test2.test2;

ID||'{@XIAOHUA}'||NAME
----------------------------------------------------------
1{@xiaohua}zhang02
2{@xiaohua}lisi02

SQL>  select id ||'<@xiaohua)>'|| name from test2.test2;

ID||'<@XIAOHUA)>'||NAME
-----------------------------------------------------------
1<@xiaohua)>zhang02
2<@xiaohua)>lisi02
		 
SQL>  select id ||'[@xiaohua]'|| name from test2.test2;

ID||'[@XIAOHUA]'||NAME
----------------------------------------------------------
1[@xiaohua]zhang02
2[@xiaohua]lisi02

演示示例五:

假设字段内容是带有<> 括号的，查询结果想去掉<> 这个括号，解决办法就是<> 符号前面添加大写Q或者小写q

SQL> select id ||'<@xiaohua>'|| name from test2.test2;

ID||'<@XIAOHUA>'||NAME
----------------------------------------------------------
1<@xiaohua>zhang02
2<@xiaohua>lisi02

SQL>   select id ||q'<@xiaohua>'|| name from test2.test2;

ID||Q'<@XIAOHUA>'||NAME
--------------------------------------------------------
1@xiaohuazhang02
2@xiaohualisi02

SQL> 
SQL>   select id ||Q'<@xiaohua>'|| name from test2.test2;

ID||Q'<@XIAOHUA>'||NAME
--------------------------------------------------------
1@xiaohuazhang02
2@xiaohualisi02

演示示例六: 批量生成查看特定表结构的拼接sql

SQL> select 'desc ' ||table_name from dba_tables where  table_name LIKE 'TEST%';

'DESC'||TABLE_NAME
--------------------------------------------------------------------------------
desc TEST002
desc TEST001
desc TEST1
desc TEST2

关于oracle拼接符的简单介绍演示完成,希望可以帮助有需要的朋友。