oracle 安位取值计算,Oracle数据库之单行函数

Oracel数据库总结：

SQL> –字符函数

SQL> select lower(‘Hello World’) 小写,upper(‘Hello World’) 大写,initcap(‘hello world’) 首字母大写

2 from dual;

小写 大写 首字母大写

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

hello world HELLO WORLD Hello World

SQL> –substr(a,b) 从a中，第b位开始取

SQL> select substr(‘Hello World’,3) from dual;

SUBSTR('H

---------llo World

SQL> –substr(a,b,c) 从a中，第b位开始取,取c位

SQL> select substr(‘Hello World’,3,4) from dual;

SUBS

----llo

SQL> –length 字符数 lengthb 字节数

SQL> select length(‘Hello World’) 字符,lengthb(‘Hello World’) 字节

2 from dual;

字符 字节

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

11 11

SQL> –instr(a,b) 在a中，查询b

SQL> select instr(‘Hello World’,’ll’) 位置 from dual;

位置

---------- 3

SQL> –lpad 左填充 rpad右填充

SQL> – abcd —> 10位

SQL> select lpad(‘abcd’,10,’‘) 左,rpad(‘abcd’,10,’‘) 右

2 from dual;

左 右

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

******abcd abcd******

SQL> –trim 去掉前后指定的字符

SQL> select trim(‘H’ from ‘Hello WorldH’) from dual;

TRIM('H'FR

----------

ello World

SQL> –replace 替换

SQL> select replace(‘Hello World’,’l’,’*’) from dual;

REPLACE('HE

-----------He**o Wor*d

SQL> –四舍五入

SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,

2 round(45.926,-1) 四,round(45.926,-2) 五

3 from dual;

一 二 三 四 五

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

45.93 45.9 46 50 0

SQL> –日期

SQL> –当前时间

SQL> select sysdate from dual;

SYSDATE

--------------10-3月 -15

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

TO_CHAR(SYSDATE,'YY

-------------------2015-03-10 14:57:11

SQL> –昨天 今天 明天

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

昨天 今天 明天

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

09-3月 -15 10-3月 -15 11-3月 -15

SQL> –计算员工的工龄：天 星期 月 年

SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,

2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年

3 from emp;

ENAME HIREDATE 天 星期 月 年

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

SMITH 17-12月-80 12501.6256 1785.94652 416.720855 34.2510292

ALLEN 20-2月 -81 12436.6256 1776.66081 414.554188 34.072947

WARD 22-2月 -81 12434.6256 1776.37509 414.487522 34.0674675

JONES 02-4月 -81 12395.6256 1770.80366 413.187522 33.9606182

MARTIN 28-9月 -81 12216.6256 1745.23224 407.220855 33.4702073

BLAKE 01-5月 -81 12366.6256 1766.66081 412.220855 33.8811662

CLARK 09-6月 -81 12327.6256 1761.08938 410.920855 33.7743168

SCOTT 19-4月 -87 10187.6256 1455.37509 339.587522 27.9113031

KING 17-11月-81 12166.6256 1738.08938 405.554188 33.333221

TURNER 08-9月 -81 12236.6256 1748.08938 407.887522 33.5250018

ADAMS 23-5月 -87 10153.6256 1450.51795 338.454188 27.8181525

ENAME HIREDATE 天 星期 月 年

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

JAMES 03-12月-81 12150.6256 1735.80366 405.020855 33.2893853

FORD 03-12月-81 12150.6256 1735.80366 405.020855 33.2893853

MILLER 23-1月 -82 12099.6256 1728.51795 403.320855 33.1496593

SQL> –months_between

SQL> select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二

2 from emp;

ENAME HIREDATE 一 二

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

SMITH 17-12月-80 416.720919 410.794437

ALLEN 20-2月 -81 414.554252 408.697663

WARD 22-2月 -81 414.487585 408.633147

JONES 02-4月 -81 413.187585 407.278308

MARTIN 28-9月 -81 407.220919 401.439599

BLAKE 01-5月 -81 412.220919 406.310566

CLARK 09-6月 -81 410.920919 405.052502

SCOTT 19-4月 -87 339.587585 334.729921

KING 17-11月-81 405.554252 399.794437

TURNER 08-9月 -81 407.887585 402.08476

ADAMS 23-5月 -87 338.454252 333.600889

ENAME HIREDATE 一 二

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

JAMES 03-12月-81 405.020919 399.24605

FORD 03-12月-81 405.020919 399.24605

MILLER 23-1月 -82 403.320919 397.600889

SQL> –78个月后-

SQL> –add_months

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

ADD_MONTHS(SYS

--------------10-9月 -21

SQL> –last_day 月的最后一天

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA

--------------31-3月 -15

SQL> –next_day

SQL> –下一个星期二

SQL> select next_day(sysdate,’星期二’) from dual;

NEXT_DAY(SYSDA

--------------17-3月 -15

SQL> select next_day(sysdate,’星期三’) from dual;

NEXT_DAY(SYSDA

--------------11-3月 -15

SQL> –对日期四舍五入

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

ROUND(SYSDATE, ROUND(SYSDATE,

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

01-3月 -15 01-1月 -15

SQL> –隐式转换的前提：被转换对象是可以转换的

SQL> –to_char

SQL> –2015-03-10 15:23:12今天是星期二

SQL> select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss”今天是”day’) from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI

----------------------------------2015-03-10 15:24:38今天是星期二

SQL> –查询员工薪水：两位小数 千位符 货币代码

SQL> select to_char(sal,’L9,999.99’) from emp;

TO_CHAR(SAL,'L9,999

------------------- ￥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,'L9,999

------------------- ￥950.00

￥3,000.00

￥1,300.00

SQL> – 通用函数

SQL> –nvl2(a,b,c) 当a=null时候，返回c，否则返回b

SQL> select sal*12+nvl2(comm,comm,0) from emp;

SAL*12+NVL2(COMM,COMM,0)

------------------------ 9600

19500

15500

35700

16400

34200

29400

36000

60000

18000

13200

SAL*12+NVL2(COMM,COMM,0)

------------------------ 11400

36000

15600

SQL> –nullif(a,b) 当a=b时候，返回null，否则返回a

SQL> select nullif(‘abc’,’abc’) 值 from dual;

值

---

SQL> select nullif(‘abc’,’abcd’) 值 from dual;

值

---abc

SQL> –coalesce 从左到右找到第一个不为null的值

SQL> select comm,sal,coalesce(comm,sal) “第一个不为null的值”

2 from emp;

COMM SAL 第一个不为null的值

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

800 800

300 1600 300

500 1250 500

2975 2975

1400 1250 1400

2850 2850

2450 2450

3000 3000

5000 5000

0 1500 0

1100 1100

COMM SAL 第一个不为null的值

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

950 950

3000 3000

1300 1300

SQL> –涨工资，总裁1000 经理800 其他400

SQL> select ename,job,sal 涨前,

2 case job when ‘PRESIDENT’ then sal+1000

3 when ‘MANAGER’ then sal+800

4 else sal+400

5 end 涨后

6 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 6000

TURNER SALESMAN 1500 1900

ADAMS CLERK 1100 1500

ENAME JOB 涨前 涨后

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

JAMES CLERK 950 1350

FORD ANALYST 3000 3400

MILLER CLERK 1300 1700

SQL> select ename,job,sal 涨前,

2 decode(job,’PRESIDENT’,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 6000

TURNER SALESMAN 1500 1900

ADAMS CLERK 1100 1500

ENAME JOB 涨前 涨后

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

JAMES CLERK 950 1350

FORD ANALYST 3000 3400

MILLER CLERK 1300 1700