单行函数

为了方便使用oracle数据库，sqlplus提供了大量的函数。

单行函数只对表中的一行进行操作，并且对每一行的输出只产生一个输出结果。

可用在 select，where，order by子句里，而且单行函数可以嵌套使用。

这是其返回类型与大致功能对象

首先介绍一下 dual表

首先是字符函数：

大小写转换：LOWER  UPPER INITCAP

字符处理：CONCAT SUBSTR LENGTH INSTR LPAD RPAD TRIM

----------------

将字符转换为小写：lower

大写：upper

将每个单词的头转化为大写，其余小写

SQL> select lower('SQL:Structural Query Language') from dual;

LOWER('SQL:STRUCTURALQUERYLAN
-----------------------------
sql:structural query language

SQL>
SQL> select upper('sql is used exclusively in rdbmses') from dual;

UPPER('SQLISUSEDEXCLUSIVELYINRDBMS
----------------------------------
SQL IS USED EXCLUSIVELY IN RDBMSES

SQL>
SQL> select upper("sql is used exclusively in rdbmses") from dual;
select upper("sql is used exclusively in rdbmses") from dual
             *
第 1 行出现错误:
ORA-00972: 标识符过长


SQL> select upper("sql is used exclusively in rdbmses") from dual;
select upper("sql is used exclusively in rdbmses") from dual
             *
第 1 行出现错误:
ORA-00972: 标识符过长


SQL> //不可用“”
select upper("sql is used exclusively in rdbmses") from dual
             *
第 1 行出现错误:
ORA-00972: 标识符过长


SQL> select initcap('SQL is an ENGLISH LIKE language') from dual;

INITCAP('SQLISANENGLISHLIKELANG
-------------------------------
Sql Is An English Like Language

concat(列名|表达式,列名|表达式)

链接两个字符串

substr(列名|表达式，m,[n])

其中n可选，返回一个字符串，从第m个字符开始，其长度为n。

SQL> select initcap('SQL is an ENGLISH LIKE language') from dual;

INITCAP('SQLISANENGLISHLIKELANG
-------------------------------
Sql Is An English Like Language

SQL>
SQL>
SQL> select concat('good','boy') from dual;

CONCAT(
-------
goodboy

SQL> select substr('ok,let we go to the city',3) from dual;

SUBSTR('OK,LETWEGOTOTH
----------------------
,let we go to the city

SQL>

length(列名|表达式)

返回长度。

instr(列名|表达式,'字符串',[m],[n])

返回所给字符串的数字位置，m表示从第m个字符开始搜索，n表示第几次出现字符。m,n默认都是1

SQL> select length('we are good')from dual;

LENGTH('WEAREGOOD')
-------------------
                 11

SQL> select length("we are good")from dual;
select length("we are good")from dual
              *
第 1 行出现错误:
ORA-00904: "we are good": 标识符无效


SQL> select instr('i am good man iam niubi','i') from dual;

INSTR('IAMGOODMANIAMNIUBI','I')
-------------------------------
                              1

SQL> select instr('i am good man iam niubi','i',n=2) from dual;
select instr('i am good man iam niubi','i',n=2) from dual
                                            *
第 1 行出现错误:
ORA-00907: 缺失右括号


SQL> select instr('i am good man iam niubi','i',1,2) from dual;

INSTR('IAMGOODMANIAMNIUBI','I',1,2)
-----------------------------------
                                 15

trim([leading|trailing|both]from 源字符串）

默认是 both

leading头

trailing尾

也就是在头或者尾去掉一些东西

replace(正文表达式，要搜寻的字符串，用这个替换）

找到就替换

SQL> l
  1  select trim('?' from '?sql*plus is the sql implementation
  2  used in an oracle rdbms or ordbms.')
  3* from dual
SQL> /

TRIM('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDB
---------------------------------------------------------------------
sql*plus is the sql implementation
used in an oracle rdbms or ordbms.


SQL> select trim(trailing '?' from '?ok?') from dual;

TRI
---
?ok

SQL> select replace('ok i will go','wi','fuck') from dual;

REPLACE('OKIWI
--------------
ok i fuckll go

小小的应用一下：

SQL> select empno,ename ,job
  2  from emp
  3  where JOB='salesman';

未选定行

SQL>
SQL> 3
  3* where JOB='salesman'
SQL> 3 where lower(job)='salesman'
SQL> l
  1  select empno,ename ,job
  2  from emp
  3* where lower(job)='salesman'
SQL> /

     EMPNO ENAME      JOB
---------- ---------- ---------
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7654 MARTIN     SALESMAN
      7844 TURNER     SALESMAN

SQL> 3
  3* where lower(job)='salesman'
SQL> 3 where lower('job')='salesman'
SQL> l
  1  select empno,ename ,job
  2  from emp
  3* where lower('job')='salesman'
SQL> /

未选定行//因为是个列，所以不加‘’

=============

数字型函数：

SQL> select round(168.888,1),trunc(168.888,1) from dual;

ROUND(168.888,1) TRUNC(168.888,1)
---------------- ----------------
           168.9            168.8

round(x,0)接收一个浮点数，0，1，2，3指多少位小数点  -1，-2，-3.。。。是个位，十位，百位等

trunc（，）是截取

SQL> select round(168.333,2),trunc(168.333,2) from dual;

ROUND(168.333,2) TRUNC(168.333,2)
---------------- ----------------
          168.33           168.33

SQL> #
SQL> select round(168.888,0),trunc(168.888,0) from dual;

ROUND(168.888,0) TRUNC(168.888,0)
---------------- ----------------
             169              168

SQL> select round(168.888),trunc(168.888) from fual;
select round(168.888),trunc(168.888) from fual
                                          *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> select round(168.888),trunc(168.888) from dual;

ROUND(168.888) TRUNC(168.888)
-------------- --------------
           169            168

SQL> select round(168.888,-1),trunc(168.888,-1) from dual;

ROUND(168.888,-1) TRUNC(168.888,-1)
----------------- -----------------
              170               160

SQL> select mod(1900,400) from dual;

MOD(1900,400)
-------------
          300

SQL> select mod(2000,400) from dual
  2  ;

MOD(2000,400)
-------------
            0

SQL> select mod(300,400) from dual;

MOD(300,400)
------------
         300

-----------------------

日期函数：

日期型数据的处理：

Oracle数据库内的日期存储格式为：  世纪，年，月，日，时，分，秒从9i开始，日期型数据的输出和和输入默认是 DD-MON-RR

首先，将数据库日期型设置为美国英语：

>alter session set NLS_DATE_LANGUAGE='AMERICAN'

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'
  2  ;

会话已更改。

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------
01-APR-18

 日期型与数字相加减，

两个日期型相减，不允许相加

一个小时数   23/24

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------
01-APR-18

SQL> select sysdate-10 from dual;

SYSDATE-10
------------
22-MAR-18

SQL> select sysdate+10 from dual;

SYSDATE+10
------------
11-APR-18

SQL> select to_date()
  2  select to_date()
  3  ;
select to_date()
*
第 2 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字


SQL> select to_date('12-2-18')+sysdate from dual;
select to_date('12-2-18')+sysdate from dual
                         *
第 1 行出现错误:
ORA-00975: 不允许日期 + 日期


SQL> select to_date('12-2-18')-sysdate from dual;
select to_date('12-2-18')-sysdate from dual
               *
第 1 行出现错误:
ORA-01843: 无效的月份


SQL> select to_date('12-JUL-18')-sysdate from dual;

TO_DATE('12-JUL-18')-SYSDATE
----------------------------
                  101.359144

SQL> select to_date('12-JUL-18')+sysdate from dual;
select to_date('12-JUL-18')+sysdate from dual
                           *
第 1 行出现错误:
ORA-00975: 不允许日期 + 日期

SQL> select sysdate-22/24 from dual;

SYSDATE-22/2
------------
31-MAR-18

SQL> select sysdate+22/24 from dual;

SYSDATE+22/2
------------
02-APR-18

SQL> select empno,ename,job,sal,(sysdate-hiredate)/365 as "Year"
  2  from dual;
select empno,ename,job,sal,(sysdate-hiredate)/365 as "Year"
                                    *
第 1 行出现错误:
ORA-00904: "HIREDATE": 标识符无效


SQL> select empno,ename,job,sal,(sysdate-hiredate)/365 as "Year"
  2  from emp
  3  where job LIKE 'SAL%';

     EMPNO ENAME      JOB              SAL       Year
---------- ---------- --------- ---------- ----------
      7499 ALLEN      SALESMAN        1600  37.136009
      7521 WARD       SALESMAN        1250 37.1305296
      7654 MARTIN     SALESMAN        1250 36.5332693
      7844 TURNER     SALESMAN        1500 36.5880638

SQL> #工龄
SP2-0042: 未知命令 "工龄" - 其余行忽略。
SQL>

months_between(日期1，日期2）

1-2 的月数  

add_months(日期，n）

日期加上n个月

next_day(日期，字符串）

字符串里是星期几

last_day(日期）

返回该日期所在月的最后一天

SQL> select months_between('01-JUL-12','03-FEB-11') from dual;

MONTHS_BETWEEN('01-JUL-12','03-FEB-11')
---------------------------------------
                             16.9354839

SQL> select add_months('15-OCT-11',8) from dual;

ADD_MONTHS('
------------
15-JUN-12

SQL> select next_date(sysdate,'MONDAY'） from dual;
select next_date(sysdate,'MONDAY'） from dual
       *
第 1 行出现错误:
ORA-00904: "NEXT_DATE": 标识符无效


SQL> select next_day(sysdate,'MONDAY'） from dual;

NEXT_DAY(SYS
------------
02-APR-18

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYS
------------
30-APR-18

SQL>
SQL> select ename,hiredate,LAST_DAY(hiredate),NEXT_DAY(hiredate,'
  2  SUNDAY'),
  3  MONTHS_BETWEEN(SYSDATE,hiredate) "Months",
  4  ADD_MONTHS(hiredate,3) "Review"
  5  FROM emp;
select ename,hiredate,LAST_DAY(hiredate),NEXT_DAY(hiredate,'
                                                           *
第 1 行出现错误:
ORA-01846: 周中的日无效


SQL> l
  1  select ename,hiredate,LAST_DAY(hiredate),NEXT_DAY(hiredate,'
  2  SUNDAY'),
  3  MONTHS_BETWEEN(SYSDATE,hiredate) "Months",
  4  ADD_MONTHS(hiredate,3) "Review"
  5* FROM emp
SQL> 1 Change /'
SQL> l
  1  Change /'
  2  SUNDAY'),
  3  MONTHS_BETWEEN(SYSDATE,hiredate) "Months",
  4  ADD_MONTHS(hiredate,3) "Review"
  5* FROM emp
SQL> select hiredate from emp;

HIREDATE
------------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87

HIREDATE
------------
03-DEC-81
03-DEC-81
23-JAN-82

已选择14行。

SQL> select ename,hiredate,LAST_DAY(hiredate),
  2  next_day(hiredate,'SUNDAY'),
  3  months_between(sysdate,hiredate)"months",
  4  add_months(hiredate,3) "review"
  5  from emp;

ENAME      HIREDATE     LAST_DAY(HIR NEXT_DAY(HIR     months review
---------- ------------ ------------ ------------ ---------- ------------
SMITH      17-DEC-80    31-DEC-80    21-DEC-80    447.504965 17-MAR-81
ALLEN      20-FEB-81    28-FEB-81    22-FEB-81     445.40819 20-MAY-81
WARD       22-FEB-81    28-FEB-81    01-MAR-81    445.343674 22-MAY-81
JONES      02-APR-81    30-APR-81    05-APR-81    443.988835 02-JUL-81
MARTIN     28-SEP-81    30-SEP-81    04-OCT-81    438.150126 28-DEC-81
BLAKE      01-MAY-81    31-MAY-81    03-MAY-81           443 01-AUG-81
CLARK      09-JUN-81    30-JUN-81    14-JUN-81    441.763029 09-SEP-81
SCOTT      19-APR-87    30-APR-87    26-APR-87    371.440448 19-JUL-87
KING       17-NOV-81    30-NOV-81    22-NOV-81    436.504965 17-FEB-82
TURNER     08-SEP-81    30-SEP-81    13-SEP-81    438.795287 08-DEC-81
ADAMS      23-MAY-87    31-MAY-87    24-MAY-87    370.311416 23-AUG-87

ENAME      HIREDATE     LAST_DAY(HIR NEXT_DAY(HIR     months review
---------- ------------ ------------ ------------ ---------- ------------
JAMES      03-DEC-81    31-DEC-81    06-DEC-81    435.956577 03-MAR-82
FORD       03-DEC-81    31-DEC-81    06-DEC-81    435.956577 03-MAR-82
MILLER     23-JAN-82    31-JAN-82    24-JAN-82    434.311416 23-APR-82

已选择14行。

SQL>

round 和trunc 用于日期型数据

round('20-OTC-11','MONTH')  这样是错的，没有自动转换，当作了字符串了，而round是不能处理字符串的。

反正round与trunc的区别是有无进位：

SQL> select round('28-OCT-11','MONTH') FROM dual;
select round('28-OCT-11','MONTH') FROM dual
             *
第 1 行出现错误:
ORA-01722: 无效数字


SQL> select round(to_date('28-OCT-11'),'MONTH') FROM dual;

ROUND(TO_DAT
------------
01-NOV-11

SQL> select round(to_date('28-OCT-11'),'YEAR') FROM dual;

ROUND(TO_DAT
------------
01-JAN-12

SQL> select TRUNC(to_date('28-OCT-11'),'MONTH') FROM dual;

TRUNC(TO_DAT
------------
01-OCT-11

SQL> select TRUNC(to_date('28-OCT-11'),'YEAR') FROM dual;

TRUNC(TO_DAT
------------
01-JAN-11

----------------

数据类型的转换，不管怎么说，大致分三类，数字，字符，日期

数字，与日期不可直接转换，必须通过字符这个桥梁来达到目的。

隐式转换我们就不说了   详见  单行函数 图

共有三个转换函数：

to_char(date,'fmt')

将日期转换为字符串格式。

SQL> select ename,to_char(hiredate,'DD/MM/YY') FROM dual;
select ename,to_char(hiredate,'DD/MM/YY') FROM dual
                     *
第 1 行出现错误:
ORA-00904: "HIREDATE": 标识符无效


SQL> select ename,to_char(hiredate,'DD/MM/YY') FROM emp;

ENAME      TO_CHAR(
---------- --------
SMITH      17/12/80
ALLEN      20/02/81
WARD       22/02/81
JONES      02/04/81
MARTIN     28/09/81
BLAKE      01/05/81
CLARK      09/06/81
SCOTT      19/04/87
KING       17/11/81
TURNER     08/09/81
ADAMS      23/05/87

ENAME      TO_CHAR(
---------- --------
JAMES      03/12/81
FORD       03/12/81
MILLER     23/01/82

已选择14行。

SQL> select ename,to_char(hiredate,'yy') FROM emp;

ENAME      TO
---------- --
SMITH      80
ALLEN      81
WARD       81
JONES      81
MARTIN     81
BLAKE      81
CLARK      81
SCOTT      87
KING       81
TURNER     81
ADAMS      87

ENAME      TO
---------- --
JAMES      81
FORD       81
MILLER     82

已选择14行。

SQL>

是的，大小写都行。

-----------

fm用于压缩空格与去掉前导0

SQL> select to_char(SYSDATE,'fmDD MONTH YEAR') FROM dual;

TO_CHAR(SYSDATE,'FMDDMONTHYEAR')
----------------------------------------------------
2 4月 TWENTY EIGHTEEN

SQL> select to_char(SYSDATE,'DD MONTH YEAR') FROM dual;

TO_CHAR(SYSDATE,'DDMONTHYEAR')
----------------------------------------------------
02 4月  TWENTY EIGHTEEN

fm:(format_model)日期格式化模式。

col hiredate for a60:将hiredate的输出宽度加长为60个字符。

SQL> help col

 COLUMN
 ------

 Specifies display attributes for a given column, such as:
     - text for the column heading
     - alignment for the column heading
     - format for NUMBER data
     - wrapping of column data
 Also lists the current display attributes for a single column
 or all columns.

 COL[UMN] [{column | expr} [option ...] ]

 where option represents one of the following clauses:
     ALI[AS] alias
     CLE[AR]
     ENTMAP {ON|OFF}
     FOLD_A[FTER]
     FOLD_B[EFORE]
     FOR[MAT] format
     HEA[DING] text
     JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
     LIKE {expr | alias}
     NEWL[INE]
     NEW_V[ALUE] variable
     NOPRI[NT] | PRI[NT]
     NUL[L] text
     OLD_V[ALUE] variable
     ON|OFF
     WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]


SQL>

SQL> col hiredate for a60
SQL> select  ename "Name",dal "Salary",
  2  to_char(hiredate,'fmDdspth "of" Month Year fmHH:MI:SS AM') HIREDATE
  3  FROM emp;
select  ename "Name",dal "Salary",
                     *
第 1 行出现错误:
ORA-00904: "DAL": 标识符无效


SQL> 1
  1* select  ename "Name",dal "Salary",
SQL> change /dal/sal
  1* select  ename "Name",sal "Salary",
SQL> /

Name           Salary
---------- ----------
HIREDATE
------------------------------------------------------------
SMITH             800
Seventeenth of 12月 Nineteen Eighty 12:00:00 上午

ALLEN            1600
Twentieth of 2月 Nineteen Eighty-One 12:00:00 上午

WARD             1250
Twenty-Second of 2月 Nineteen Eighty-One 12:00:00 上午


Name           Salary
---------- ----------
HIREDATE
------------------------------------------------------------
JONES            2975
Second of 4月 Nineteen Eighty-One 12:00:00 上午

MARTIN           1250
Twenty-Eighth of 9月 Nineteen Eighty-One 12:00:00 上午

BLAKE            2850
First of 5月 Nineteen Eighty-One 12:00:00 上午


Name           Salary
---------- ----------
HIREDATE
------------------------------------------------------------
CLARK            2450
Ninth of 6月 Nineteen Eighty-One 12:00:00 上午

SCOTT            3000
Nineteenth of 4月 Nineteen Eighty-Seven 12:00:00 上午

KING             5000
Seventeenth of 11月 Nineteen Eighty-One 12:00:00 上午


Name           Salary
---------- ----------
HIREDATE
------------------------------------------------------------
TURNER           1500
Eighth of 9月 Nineteen Eighty-One 12:00:00 上午

ADAMS            1100
Twenty-Third of 5月 Nineteen Eighty-Seven 12:00:00 上午

JAMES             950
Third of 12月 Nineteen Eighty-One 12:00:00 上午


Name           Salary
---------- ----------
HIREDATE
------------------------------------------------------------
FORD             3000
Third of 12月 Nineteen Eighty-One 12:00:00 上午

MILLER           1300
Twenty-Third of 1月 Nineteen Eighty-Two 12:00:00 上午


已选择14行。

===========

to_char(number,'fmt')

https://blog.csdn.net/qq_34907701/article/details/79786811

常用的模式：

9：不显示前导0的一位数字

0：显示前导0的一位数字

$:显示美元符

L：本地货币

.:显示小数点

，：显示千位符

MI：在数的右边显示减号

PR：把负数用尖括号括起来

SQL> select ename "Name",to_char(sal*12,'$99,999.00') "annual salary" from emp;

Name       annual sala
---------- -----------
SMITH        $9,600.00
ALLEN       $19,200.00
WARD        $15,000.00
JONES       $35,700.00
MARTIN      $15,000.00
BLAKE       $34,200.00
CLARK       $29,400.00
SCOTT       $36,000.00
KING        $60,000.00
TURNER      $18,000.00
ADAMS       $13,200.00

Name       annual sala
---------- -----------
JAMES       $11,400.00
FORD        $36,000.00
MILLER      $15,600.00

已选择14行。

SQL> select ename "Name",to_char(sal*12,'$999.00') "annual salary" from emp;

Name       annual s
---------- --------
SMITH      ########
ALLEN      ########
WARD       ########
JONES      ########
MARTIN     ########
BLAKE      ########
CLARK      ########
SCOTT      ########
KING       ########
TURNER     ########
ADAMS      ########

Name       annual s
---------- --------
JAMES      ########
FORD       ########
MILLER     ########

已选择14行。

SQL>

注意位数要留够。否则就会显示为####了。

SQL> select ename "Name",to_char(sal*12,'L99,999.00') "annual salary" from emp;

Name       annual salary
---------- --------------------
SMITH                ￥9,600.00
ALLEN               ￥19,200.00
WARD                ￥15,000.00
JONES               ￥35,700.00
MARTIN              ￥15,000.00
BLAKE               ￥34,200.00
CLARK               ￥29,400.00
SCOTT               ￥36,000.00
KING                ￥60,000.00
TURNER              ￥18,000.00
ADAMS               ￥13,200.00

Name       annual salary
---------- --------------------
JAMES               ￥11,400.00
FORD                ￥36,000.00
MILLER              ￥15,600.00

已选择14行。

to_number(str [,fmt]')

to_date(str [,fmt]')