07-Oracle入门之单行函数

基本概念

sql 函数

注意:函数可以没有参数，但必须要有返回值

常见的两种sql 函数
所谓单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以转换数据类型
- 可以嵌套
- 参数可以是一列或一个值
种类

字符函数

大小写控制函数

这类函数改变字符的大小写

函数	结果
LOWER(‘sql Course’)	sql course
UPPER(‘sql Course’)	sql COURSE
INITCAP(‘sql Course’)	sql Course

显示员工 Higgins的信息（员工名字是大写）：

SELECT employee_id,last_name,department_id FROM employees WHERE last_name = 'higgins';
no rows selected

修改为

SELECT employee_id,department_id FROM employees WHERE LOWER(last_name) = 'higgins';

字符控制函数

函数	结果
CONCAT(‘Hello’,‘World’)	HelloWorld
SUBSTR(‘HelloWorld’,1,5)	Hello
LENGTH(‘HelloWorld’)	10
INSTR(‘HelloWorld’,‘W’)	6
LPAD(salary,10,’*’)	*****24000
RPAD(salary,‘*’)	24000*****
TRIM(‘H’ FROM ‘HelloWorld’)	elloWorld
replace(‘abcd’,‘b’,‘m’)	amcd

示例

SELECT employee_id,CONCAT(first_name,last_name) NAME,job_id,LENGTH (last_name),INSTR(last_name,'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id,4) = 'REP';

数字函数

ROUND: 四舍五入

SELECT ROUND(45.923,2),ROUND(45.923,0),-1)
FROM   DUAL;

DUAL 是一个‘伪表’，可以用来测试函数和表达式

select round(45.926,2) AA,round(45.926,1) BB,0) CC,round(45.926) DD,2  round(44.926,-1) EE,-2) FF from dual;

    AA     BB         CC     DD     EE         FF
---------- ---------- ---------- ---------- ---------- ----------
     45.93   45.9         46     46     40      0

TRUNC: 截断

SELECT TRUNC(45.923,TRUNC(45.923),TRUNC(45.923,-2) FROM DUAL;

MOD: 求余

SELECT last_name,salary,MOD(salary,5000) FROM employees WHERE job_id = 'SA_REP';

日期函数

Oracle 中的日期型数据实际含有两个值: 日期和时间。
默认的日期格式是 DD-MON-RR.
函数SYSDATE 返回:
- 日期
- 时间

日期的数学运算

在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
可以用数字除24来向日期中加上或减去小时。

SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;

种类

相关运算示例

函数	结果
MONTHS_BETWEEN (‘01-SEP-95’,’11-JAN-94’)	19.6774194
ADD_MONTHS (‘11-JAN-94’,6)	‘11-JUL-94’
NEXT_DAY (‘01-SEP-95’,’FRIDAY’)	‘08-SEP-95’
LAST_DAY(‘01-FEB-95’)	‘28-FEB-95’

假设SYSDATE = ‘25-JUL-95’:

函数	结果
ROUND(SYSDATE,’MONTH’)	01-AUG-95
ROUND(SYSDATE,’YEAR’)	01-JAN-96
TRUNC(SYSDATE,’MONTH’)	01-JUL-95
TRUNC(SYSDATE,’YEAR’)	01-JAN-95

日期的四舍五入

sql>  select round(sysdate,'MONTH') from dual;

ROUND(SYS ---------
01-JAN-17

to_char(‘日期/数字’,format)

sql> ed
Wrote file afiedt.buf

  1  select to_char(sysdate-1,'yyyy-mm-dd hh24:mi:ss') "昨天",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "今天",2* to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') "明天"  from dual
sql> /

昨天          今天      明天
------------------- ------------------- -------------------
2017-01-06 20:51:02 2017-01-07 20:51:02 2017-01-08 20:51:02

查询员工的入职时间,按照天周月年方式显示

sql> ed
Wrote file afiedt.buf

  1  select ename,hiredate-sysdate "天",(hiredate-sysdate)/7 "周",( hiredate-sysdate)/30 "月",2*  (hiredate-sysdate)/365 "年" from emp
sql> /

ENAME         天      周  月      年
---------- ---------- ---------- ---------- ----------
tom_abc
SMITH       -13170.87 -1881.5529   -439.029 -36.084575
ALLEN       -13105.87 -1872.2671 -436.86233 -35.906493
WARD        -13103.87 -1871.9814 -436.79567 -35.901014
JONES       -13064.87   -1866.41 -435.49567 -35.794164
MARTIN      -12885.87 -1840.8386   -429.529 -35.303753
BLAKE       -13035.87 -1862.2671   -434.529 -35.714712
CLARK       -12996.87 -1856.6957   -433.229 -35.607863
SCOTT       -10856.87 -1550.9814 -361.89567 -29.744849
KING        -12835.87 -1833.6957 -427.86233 -35.166767
TURNER      -12905.87 -1843.6957 -430.19567 -35.358548
ADAMS       -10822.87 -1546.1243 -360.76233 -29.651699
JAMES       -12819.87   -1831.41   -427.329 -35.122931
FORD        -12819.87   -1831.41   -427.329 -35.122931
MILLER      -12768.87 -1824.1243   -425.629 -34.983205

15 rows selected.

精确计算员工的入职月数

sql> ed
Wrote file afiedt.buf

  1* select ename,( sysdate-hiredate)/30 "月1",MONTHS_BETWEEN(sysdate,hiredate)  "月2:" from emp
sql> /

ENAME        月1    月2:
---------- ---------- ----------
tom_abc
SMITH      439.029019 432.705502
ALLEN      436.862352 430.608728
WARD       436.795685 430.544211
JONES      435.495685 429.189373
MARTIN     429.529019 423.350663
BLAKE      434.529019 428.221631
CLARK      433.229019 426.963566
SCOTT      361.895685 356.640986
KING       427.862352 421.705502
TURNER     430.195685 423.995824
ADAMS      360.762352 355.511953
JAMES      427.329019 421.157115
FORD       427.329019 421.157115
MILLER     425.629019 419.511953

15 rows selected.

显示当前日期的下一个周一

QL> ed
Wrote file afiedt.buf

 1* select next_day(sysdate,'MONDAY') from dual
sql> /

NEXT_DAY( ---------
09-JAN-17

转换函数

隐式数据类型转换

Oracle 自动完成下列转换

比这个日期都要大的08-9月 -81 所有员工信息

selct * from emp
 where hiredate > '08-9月 -81' --隐式类型转换

oracle自动转换
char===>number
number====>char

sql> select 3+2 || 'aaaa' from dual;

3+2|| -----
5aaaa

显式数据类型转换

TO_CHAR 函数对日期的转换

TO_CHAR(date,'format_model')

格式:
* 必须包含在单引号中而且大小写敏感。
* 可以包含任意的有效的日期格式。
* 日期之间用逗号隔开。

日期格式的元素

SELECT last_name,TO_CHAR(hire_date,'DD Month YYYY') AS HIREDATE FROM employees;

TO_CHAR 函数对数字的转换

TO_CHAR(number,'format_model')
TO_CHAR 函数中经常使用的几种格式:

SELECT TO_CHAR(salary,'$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst';

结果是：￥6,000.00

select to_char(sysdate,'yyyy-mm-dd') from dual --把日期转成字符串

　TO_NUMBER 和 TO_DATE 函数

＊　使用 TO_NUMBER 函数将字符转换成数字:

TO_NUMBER(char[,'format_model'])

使用 TO_DATE 函数将字符转换成日期:

TO_DATE(char[,'format_model'])

select * from emp where hiredate > to_date('1981-02-02','yyyy-mm-dd')  --把字符串转成日期

通用函数

这些函数适用于任何数据类型，同时也适用于空值：

NVL (expr1,expr2)
NVL2 (expr1,expr2,expr3)
NULLIF (expr1,expr2)
COALESCE (expr1,...,exprn)

NVL 函数

将空值转换成一个已知的值：

可以使用的数据类型有日期、字符、数字。
函数的一般形式:
- NVL(commission_pct,0)
- NVL(hire_date,’01-JAN-97’)
- NVL(job_id,’No Job Yet’)

SELECT last_name,NVL(commission_pct,(salary*12) + (salary*12*NVL(commission_pct,0)) AN_SAL FROM employees;

使用 NVL2 函数

NVL2 (expr1,expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

SELECT last_name,commission_pct,NVL2(commission_pct,'SAL+COMM','SAL') income FROM employees WHERE department_id IN (50,80);

NULLIF 函数

NULLIF (expr1,expr2) : 相等返回NULL，不等返回expr1

SELECT first_name,LENGTH(first_name) "expr1",LENGTH(last_name) "expr2",NULLIF(LENGTH(first_name),LENGTH(last_name)) result FROM employees;

COALESCE 函数

COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
即：找第一个不为空的值。

SELECT last_name,COALESCE(commission_pct,10) comm FROM employees ORDER BY commission_pct;

条件表达式

在 sql 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
- CASE 表达式：sql99的语法，类似Basic，比较繁琐
- DECODE 函数：Oracle自己的语法，类似Java，比较简介

CASE 表达式

在需要使用 IF-THEN-ELSE 逻辑时

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

CASE expr(通常列名) WHEN comparison_expr1（列的值） THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

应用：

SELECT last_name,CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;

DECODE 函数

在需要使用 IF-THEN-ELSE 逻辑时:

DECODE(col|expression,search1,result1 
                   [,search2,result2,]
                   [,default])
一个函数，参数可扩展。。。
Printf(“%s,%s,%s”,a,b,c)

SELECT last_name,DECODE(job_id,'IT_PROG',1.10*salary,'ST_CLERK',1.15*salary,'SA_REP',1.20*salary,salary) REVISED_SALARY FROM employees;

使用decode函数的一个例子：根据80号部门员工的工资，显示税率

SELECT last_name,DECODE (TRUNC(salary/2000,0,0.00,1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45) TAX_RATE FROM employees WHERE department_id = 80;

嵌套函数

单行函数可以嵌套。
嵌套函数的执行顺序是由内到外。

SELECT last_name,NVL(TO_CHAR(manager_id),'No Manager') FROM employees WHERE manager_id IS NULL;

07-Oracle入门之单行函数

基本概念

字符函数

大小写控制函数

字符控制函数

数字函数

ROUND: 四舍五入

TRUNC: 截断

MOD: 求余

日期函数

转换函数

隐式数据类型转换

显式数据类型转换

TO_CHAR 函数对日期的转换

TO_CHAR 函数对数字的转换

TO_NUMBER 和 TO_DATE 函数

通用函数

NVL 函数

使用 NVL2 函数

NULLIF 函数

COALESCE 函数

条件表达式

CASE 表达式

DECODE 函数

嵌套函数

猜你在找的Oracle相关文章

　TO_NUMBER 和 TO_DATE 函数