DBA-oracle随笔-拼接sql语句（substr,instr和 ||）用法

一.先介绍一下最简单的拼接写法(用 ||连接字符串)

老板有一个需求给出每一个职员所在的部门编号，部门名称，员工编号，员工姓名，薪水和职称。
先看一下不拼接的查询结果

SQL> select a.EMPNO,a.ENAME,a.JOB,a.SAL,a.DEPTNO,b.DNAME from emp a,dept b where a.DEPTNO=b.DEPTNO;

     EMPNO ENAME      JOB	          SAL      DEPTNO DNAME
---------- ---------- --------- ---------- ---------- --------------
      7369 SMITH      CLERK	          1040	   20 RESEARCH
      7499 ALLEN      SALESMAN	      1600	   30 SALES
      7521 WARD       SALESMAN	      1250	   30 SALES
      7566 JONES      MANAGER	      2975	   20 RESEARCH
      7654 MARTIN     SALESMAN	      1250	   30 SALES
      7698 BLAKE      MANAGER	      2850	   30 SALES
      7782 CLARK      MANAGER	      2450	   10 ACCOUNTING
      7788 SCOTT      ANALYST	      3000	   20 RESEARCH
      7839 KING       PRESIDENT       5000	   10 ACCOUNTING
      7844 TURNER     SALESMAN	      1500	   30 SALES
      7876 ADAMS      CLERK	          1100	   20 RESEARCH

     EMPNO ENAME      JOB	          SAL     DEPTNO DNAME
---------- ---------- --------- ---------- ---------- --------------
      7902 FORD       ANALYST	      3000	   20 RESEARCH
      7934 MILLER     CLERK	          1300	   10 ACCOUNTING

13 rows selected.

再来看一下使用 || 将字符串拼接起来

SQL> select '编号:' || a.EMPNO || ' 姓名:' || a.ENAME || ' 职位:' || a.JOB || ' 薪水:' ||
  2         a.SAL || ' 所属部门:' || b.DNAME || ' 部门编号:' || a.DEPTNO
  3    from emp a, dept b
  4   where a.DEPTNO = b.DEPTNO;
'编号:'||A.EMPNO||'姓名:'|
--------------------------------------------------------------------------------
编号:7369 姓名:SMITH 职位:CLERK 薪水:1040 所属部门:RESEARCH 部门编号:20
编号:7499 姓名:ALLEN 职位:SALESMAN 薪水:1600 所属部门:SALES 部门编号:30
编号:7521 姓名:WARD 职位:SALESMAN 薪水:1250 所属部门:SALES 部门编号:30
编号:7566 姓名:JONES 职位:MANAGER 薪水:2975 所属部门:RESEARCH 部门编号:20
编号:7654 姓名:MARTIN 职位:SALESMAN 薪水:1250 所属部门:SALES 部门编号:30
编号:7698 姓名:BLAKE 职位:MANAGER 薪水:2850 所属部门:SALES 部门编号:30
编号:7782 姓名:CLARK 职位:MANAGER 薪水:2450 所属部门:ACCOUNTING 部门编号:10
编号:7788 姓名:SCOTT 职位:ANALYST 薪水:3000 所属部门:RESEARCH 部门编号:20
编号:7839 姓名:KING 职位:PRESIDENT 薪水:5000 所属部门:ACCOUNTING 部门编号:10
编号:7844 姓名:TURNER 职位:SALESMAN 薪水:1500 所属部门:SALES 部门编号:30
编号:7876 姓名:ADAMS 职位:CLERK 薪水:1100 所属部门:RESEARCH 部门编号:20
编号:7902 姓名:FORD 职位:ANALYST 薪水:3000 所属部门:RESEARCH 部门编号:20
编号:7934 姓名:MILLER 职位:CLERK 薪水:1300 所属部门:ACCOUNTING 部门编号:10
13 rows selected

二.学会了||之后再来看看两个很有用的函数substr和instr

substr 用法
格式1（substr(字符串,字符串起始位置,字符串末尾位置））
格式2（substr(字符串,截取位置)）

#不使用substr
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/orcl/system01.dbf
/oracle/app/oradata/orcl/sysaux01.dbf
/oracle/app/oradata/orcl/undotbs01.dbf
/oracle/app/oradata/orcl/users01.dbf
#格式1使用substr，从起始字符串第一个/截取到第12字符串(0和1都是从第一个字符串开始)
SQL> select substr(name,0,12) from v$datafile;

SUBSTR(NAME,0,12)
------------------------
/oracle/app/
/oracle/app/
/oracle/app/
/oracle/app/
SQL> select substr(name,1,12) from v$datafile;

SUBSTR(NAME,1,12)
------------------------
/oracle/app/
/oracle/app/
/oracle/app/
/oracle/app/
#格式1substr超出范围也能正常使用，即截取全部字符串
SQL> select substr(name,1,999) from v$datafile;

SUBSTR(NAME,1,999)
--------------------------------------------------------------------------------
/oracle/app/oradata/orcl/system01.dbf
/oracle/app/oradata/orcl/sysaux01.dbf
/oracle/app/oradata/orcl/undotbs01.dbf
/oracle/app/oradata/orcl/users01.dbf
#格式1substr也可以倒着截取 从倒数第三个字符截取3个字符
SQL> select substr(name,-3,3) from v$datafile;

SUBSTR
------
dbf
dbf
dbf
dbf
#格式2 substr截取第8字符串往后字符串
SQL> select substr(name,8) from v$datafile;

SUBSTR(NAME,8)
--------------------------------------------------------------------------------
/app/oradata/orcl/system01.dbf
/app/oradata/orcl/sysaux01.dbf
/app/oradata/orcl/undotbs01.dbf
/app/oradata/orcl/users01.dbf

instr 用法
格式1(instr(源字符串，目标字符串)
格式2(instr(源字符串，目标字符串，起始位置，匹配序号）

###格式1 instr抓取name第一个'app'出现的位置号
SQL> select instr(name,'app') from v$datafile;

INSTR(NAME,'APP')
-----------------
		9
		9
		9
		9
#格式2  instr抓取name从第3个字符串开始第二次出现/的位置
SQL> select instr(name,'/',3,2) from v$datafile;

INSTR(NAME,'/',3,2)
-------------------
		 12
		 12
		 12
		 12

三.高级用法 substr和instr合用

# substr和instr合用 先用instr找到最后一个/的位置 +1就是/后面一个位置 最后substr截取数据文件名
SQL> select '目标端数据文件:' || '+DATA/test/datafile/' || substr(name,instr(name,'/',-1,1)+1) from v$datafile;
'目标端数据文件:'||'+DA
--------------------------------------------------------------------------------
目标端数据文件:+DATA/test/datafile/system01.dbf
目标端数据文件:+DATA/test/datafile/sysaux01.dbf
目标端数据文件:+DATA/test/datafile/undotbs01.dbf
目标端数据文件:+DATA/test/datafile/users01.dbf