Oracle常用单行函数

最新推荐文章于 2021-10-17 00:15:25 发布

Mr_PH

最新推荐文章于 2021-10-17 00:15:25 发布

阅读量1.1k

点赞数 3

分类专栏： Oracle sql 文章标签： sql Oracle 函数

本文链接：https://blog.csdn.net/Mr_PH/article/details/78537103

版权

sql 同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

Oracle

7 篇文章 0 订阅

订阅专栏

单行函数：每处理一条记录返回一条结果.

字符函数

LOWER : 转小写

SQL> select lower(ename) from emp;

LOWER(ENAME)
--------------------
smith
allen
ward

UPPER : 转大写

SQL> select upper(ename) from emp;

UPPER(ENAME)
--------------------
SMITH
ALLEN
WARD

INITCAP ：首字母大写

SQL> select initcap(ename) from emp;

INITCAP(ENAME)
--------------------
Smith
Allen
Ward

SUBSTR(a,b) : 从b开始截取字符串a

SQL> select substr('hello world',4) from dual;

SUBSTR('HELLOWOR
----------------
lo worle

SUBSTR(a,b,c) : 从a字符串的第b位开始截取c长度的字符串
```
SQL> select substr('hello world',4,5) from dual;

SUBSTR('HE
----------
lo wo
```

LENGTH(string) : 字符长度

SQL> select length('hello world') from dual;

LENGTH('HELLOWORLD')
--------------------
                  11

LENGTHB(string) : 字节长度

SQL> select lengthb('你好') from dual;

LENGTHB('你好')
---------------
              6

SQL> select length('你好') from dual;

LENGTH('你好')
--------------
             2

INSTR(a,b) : 在a中查找b 没有找到返回0

SQL> select instr('hello world','d') from dual;

INSTR('HELLOWORLD','D')
-----------------------
                     11

SQL> select instr('hello world','a') from dual;

INSTR('HELLOWORLD','A')
-----------------------
                      0

LPAD(a,b,c) : 左填充(给a的左边填充c知道满足b长度)

SQL> select lpad('hello world',20,'*') from dual;

LPAD('HELLOWORLD',20,'*')
----------------------------------------
*********hello world

RPAD(a,b,c) : 右填充

SQL> select rpad('hello world',20,'*') from dual;

RPAD('HELLOWORLD',20,'*')
----------------------------------------
hello world*********

TRIM(a,from,b) : 去除指定字符（从b中去除a）

SQL> select trim('h'from'hello world') from dual;

TRIM('H'FROM'HELLOWO
--------------------
ello world

REPLACE(a,b,c) : 替换指定字符（将字符串中a中的b替换为c）

SQL> select replace('hello world','l','*') from dual;

REPLACE('HELLOWORLD','
----------------------
he**o wor*d

数值函数

ROUND(a,b) : 四舍五入（保留a的b位小数）

SQL> select round(4523.1354,2) 保留两位小数,round(4523.1354,0) 保留整数,round(4523.1354,-2) 保留百位  from dual;

保留两位小数   保留整数   保留百位
------------ ---------- ----------
     4523.14       4523       4500

TRUNC(a,b) : 截取

SQL> select trunc(4523.1354,2)截取保留两位小数,trunc(4523.1354,0) 截取整数,trunc(4523.1354,-2) 截取百位  from dual;

截取保留两位小数   截取整数   截取百位
---------------- ---------- ----------
         4523.13       4523       4500

MOD(a,b) : 求余（a%b）

SQL> select mod(1520,30) from dual;

MOD(1520,30)
------------
          20

日期函数

Oracle中的日期

SQL> select sysdate from dual;

SYSDATE
--------------
15-11月-17

SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;

TO_CHAR(SYSDATE,'YYY
--------------------
2017-11-15

SQL> select to_char(sysdate,'yyyy-mm-dd-hh24-mi-ss') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DD-HH24-MI-SS
--------------------------------------
2017-11-15-09-48-47

时间的加减（两个时间类型可以相减但是不能相加）

SQL> select (sysdate-1) 昨天，sysdate 今天 ,(sysdate+1) 明天 from dual;

昨天           今天           明天
-------------- -------------- --------------
14-11月-17     15-11月-17     16-11月-17

MONTHS_BETWEEN(a,b) : 两个日期相差的月数(a-b)

SQL> select months_between(sysdate,hiredate) from emp;

MONTHS_BETWEEN(SYSDATE,HIREDATE)
--------------------------------
                      442.948817
                      440.852043
                      440.787527
                      439.432688
                      433.593978

ADD_MONTHS(a,b) : 给日期a加b月

SQL> select add_months(sysdate,3) from dual;

ADD_MONTHS(SYS
--------------
15-2月 -18

SQL> select add_months(sysdate,-3) from dual;

ADD_MONTHS(SYS
--------------
15-8月 -17

LAST_DAY(day) : 某个日期当前月的最后一天

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA
--------------
30-11月-17

NEXT_DAY(a,b) : a日期的下一个星期b

SQL> select next_day(sysdate,'星期一') from dual;

NEXT_DAY(SYSDA
--------------
20-11月-17

SQL> select next_day(sysdate,'星期五') from dual;

NEXT_DAY(SYSDA
--------------
17-11月-17

ROUND(a,b) : a日期保留b,四舍五入，TRUNC同理

SQL> select round(sysdate,'month') from dual;

ROUND(SYSDATE,
--------------
01-11月-17

SQL> select round(sysdate,'year') from dual;

ROUND(SYSDATE,
--------------
01-1月 -18

SQL> select round(sysdate,'day') from dual;

ROUND(SYSDATE,
--------------
12-11月-17

转换函数

TO_CHAR(data,'format_model')

SQL> select to_char(sysdate,'dy') 星期几  from dual;

星期几
------------------------
星期三

SQL> select to_char(sysdate,'day') 第几天  from dual;

第几天
------------------------
星期三

SQL> select to_char(sysdate,'month') 第几月  from dual;

第几月
----------------
11月

SQL> select to_char(sysdate,'yyyy') 第几年  from dual;

第几年
--------
2017

SQL> select to_char(sysdate,'year') 第几年  from dual;

第几年
--------------------------------------------------------------------------------
twenty seventeen

SQL> select to_char(sysdate,'mm') 第几月  from dual;

第几
----
11

SQL> select sal from emp;

       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100

       SAL
----------
       950
      3000
      1300

已选择14行。

SQL> select to_char(sal,'L99,999.99') from emp;

TO_CHAR(SAL,'L99,999.99')
----------------------------------------
           ￥800.00
         ￥1,600.00
         ￥1,250.00
         ￥2,975.00
         ￥1,250.00
         ￥2,850.00
         ￥2,450.00
         ￥3,000.00
         ￥5,000.00
         ￥1,500.00
         ￥1,100.00

TO_CHAR(SAL,'L99,999.99')
----------------------------------------
           ￥950.00
         ￥3,000.00
         ￥1,300.00

已选择14行。

注：L为本地货币符号，“，”为千位符，“.”为小数，“9”为精度

TO_NUMBER(data,'format_model')

SQL> select to_number(sal,'L99,999.99') from (select to_char(sal,'L99,999.99') sal from emp s);

TO_NUMBER(SAL,'L99,999.99')
---------------------------
                        800
                       1600
                       1250
                       2975
                       1250
                       2850
                       2450
                       3000
                       5000
                       1500
                       1100

TO_NUMBER(SAL,'L99,999.99')
---------------------------
                        950
                       3000
                       1300

通用函数

NVL(a,b) : 滤空（当a为null时返回b）

SQL> select nvl(comm,0) from emp;

NVL(COMM,0)
-----------
          0
        300
        500
          0
       1400

NVL2(a,b,c) : 类型VAL(a,b) 当a为空时返回c否则返回b

SQL> select nvl2(comm,comm,0) from emp;

NVL2(COMM,COMM,0)
-----------------
                0
              300
              500
                0

NULLIF(a,b) : 当a=b时返回null 否则返回a

SQL> select nullif(1500,1500) from dual;

NULLIF(1500,1500)
-----------------


SQL> select nullif(1500,160) from dual;

NULLIF(1500,160)
----------------
            1500

COALESCE(expr1,expr2,....) : 从左到右找到第一个不为空的值否则返回空

SQL> select coalesce(null,1,null,2) from dual;

COALESCE(NULL,1,NULL,2)
-----------------------
                      1

SQL> select coalesce(null,null,null,2) from dual;

COALESCE(NULL,NULL,NULL,2)
--------------------------
                         2

SQL> select coalesce(null,null,null,null) from dual;

C
-

条件表达式

CASE表达式（SQL99语法，较为繁琐）

SQL> select ename,job,sal 涨前 ,
  2  case job when 'PERSIDENT' then sal+1000
  3  when 'MANAGER' then sal+800
  4  else sal+500
  5  end 涨后
  6  from emp;

ENAME                JOB                      涨前       涨后
-------------------- ------------------ ---------- ----------
SMITH                CLERK                     800       1300
ALLEN                SALESMAN                 1600       2100
WARD                 SALESMAN                 1250       1750
JONES                MANAGER                  2975       3775
MARTIN               SALESMAN                 1250       1750
BLAKE                MANAGER                  2850       3650
CLARK                MANAGER                  2450       3250
SCOTT                ANALYST                  3000       3500
KING                 PRESIDENT                5000       5500
TURNER               SALESMAN                 1500       2000
ADAMS                CLERK                    1100       1600

ENAME                JOB                      涨前       涨后
-------------------- ------------------ ---------- ----------
JAMES                CLERK                     950       1450
FORD                 ANALYST                  3000       3500
MILLER               CLERK                    1300       1800

已选择14行。

DECODE函数（Oracle语法，较为简洁）

SQL> select ename,job,sal 涨前,
  2  decode(job,'PERSIDENT',sal+1000,
  3  'MANAGER',sal+800,
  4  sal+400) 涨后
  5  from emp;

ENAME                JOB                      涨前       涨后
-------------------- ------------------ ---------- ----------
SMITH                CLERK                     800       1200
ALLEN                SALESMAN                 1600       2000
WARD                 SALESMAN                 1250       1650
JONES                MANAGER                  2975       3775
MARTIN               SALESMAN                 1250       1650
BLAKE                MANAGER                  2850       3650
CLARK                MANAGER                  2450       3250
SCOTT                ANALYST                  3000       3400
KING                 PRESIDENT                5000       5400
TURNER               SALESMAN                 1500       1900
ADAMS                CLERK                    1100       1500

ENAME                JOB                      涨前       涨后
-------------------- ------------------ ---------- ----------
JAMES                CLERK                     950       1350
FORD                 ANALYST                  3000       3400
MILLER               CLERK                    1300       1700

已选择14行。

Mr_PH

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle常用单行函数

单行函数：每处理一条记录返回一条结果.字符函数LOWER : 转小写SQL> select lower(ename) from emp;LOWER(ENAME)--------------------smithallenwardUPPER : 转大写SQL> select upper(ename) from emp;UPPER(ENAM
复制链接

扫一扫