频道栏目
首页 > 数据库 > Oracle > 正文
oracle心得2--单行函数理论讲解与案例分析
2013-04-15 11:27:15      个评论      
收藏   我要投稿

oracle心得2--单行函数理论讲解与案例分析
 

Sql有两种函数,单行函数和多行函数
1.单行函数
单行函数:操作数据对象、接受参数返回一个结果、只对一行进行变换、每行返回一个结果、可以转换数据类型、可以嵌套、参数可以是一列或一个值

DUAL是一个‘伪表’,可以用来测试函数和表达式
2.字符函数

大小写控制函数:这类函数改变字符的大小写。

例子:

[sql]

select lower(ename) from emp;   LOWER(ENAM 
---------- 
smith 
allen 
ward   select upper(ename) from emp;   UPPER(ENAM 
---------- 
SMITH 
ALLEN 
WARD   select initcap(ename) from emp;   INITCAP(EN 
---------- 
Smith 
Allen 
Ward  


字符控制函数:

select concat('hello','word')from dual; CONCAT('H --------- helloword select substr('helloword',1,3) from dual; SUB --- hel select length('helloword') from dual; LENGTH('HELLOWORD') ------------------- 9 select instr('helloworld','w') from dual; INSTR('HELLOWORLD','W') ----------------------- 6 select lpad('hello',10,'*')from dual; LPAD('HELL ---------- *****hello select rpad('hello',10,'#')from dual; RPAD('HELL ---------- hello##### select trim(' hello ') from dual; TRIM( ----- hello

3.数字函数
ROUND:四舍五入
TRUNC: 截断
MOD:求余
Round 函数 :语法为ROUND(number,num_digits);其中Number是需要进行四舍五入的数字;Num_digits为指定的位数,按此位数进行四舍五入,如果 num_digits 大于 0,则四舍五入到指定的小数位; Num_digits值为多少就到相应的小数点位置四舍五入,如果 num_digits等于 0,则四舍五入到最接近的整数,如果 num_digits 小于 0,则在小数点左侧进行四舍五入;Num_digits值多少就到小数点左侧的整数相应的位置四舍五入。
例如:
ROUND(2.149, 0) 将 2.149 四舍五入到一个整数结果为2。
ROUND(2.15, 1) 将 2.15 四舍五入到一个小数位,结果为2.2。
ROUND(2.149, 1) 将 2.149 四舍五入到一个小数位结果为2.1。
ROUND(-1.475, 2) 将 -1.475 四舍五入到两小数位结果为-1.48)。
ROUND(21.5, -1) 将 21.5 四舍五入到小数点左侧一位结果为20。
例子:
SQL> select round(45.926,2) from dual; ROUND(45.926,2) --------------- 45.93 SQL> select round(45.926,-2) from dual; ROUND(45.926,-2) ---------------- 0 SQL> select round(55.926,-2) from dual; ROUND(55.926,-2) ---------------- 100 SQL> select round(50.926,-2) from dual; ROUND(50.926,-2) ---------------- 100 SQL> select round(150.926,-2) from dual; ROUND(150.926,-2) ----------------- 200 SQL> select round(50.326,-2) from dual; ROUND(50.326,-2) ---------------- 100 SQL> select round(550.326,-2) from dual; ROUND(550.326,-2) ----------------- 600 SQL> select trunc(45.926,2) from dual; TRUNC(45.926,2) --------------- 45.92 SQL> select trunc(45.926,-2) from dual; TRUNC(45.926,-2) ---------------- 0 SQL> select trunc(55.926,-2) from dual; TRUNC(55.926,-2) ---------------- 0 SQL> select trunc(155.926,-2) from dual; TRUNC(155.926,-2) ----------------- 100 SQL> select mod(1600,300) from dual; MOD(1600,300) ------------- 100 SQL> select mod(13,3) from dual; MOD(13,3) ---------- 1 SQL> select round(45.926,-1) from dual; ROUND(45.926,-1) ---------------- 50

4.日期
Oracle中的日期型数据实际含有两个值: 日期和时间。
默认的日期格式是 DD-MON-RR.函数SYSDATE 返回:日期、时间
在日期上加上或减去一个数字结果仍为日期。两个日期相减返回日期之间相差的天数。可以用数字除24来向日期中加上或减去小时。
日期函数

注:日期转换格式不支持转换中文格式的日期
例子:
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; TO_CHAR(SY ---------- 2013-04-04 SQL> select to_char(sysdate,'yyyy/mm/dd') from dual; TO_CHAR(SY ---------- 2013/04/04 select to_char(sysdate,'YEAR-MONTH-DAY') from dual; TO_CHAR(SYSDATE,'YEAR-MONTH-DAY') ----------------------------------------------------------- TWENTY THIRTEEN-4月 -星期四 select to_date('1212-12-12','yyyy/mm/dd') from dual; TO_DATE('1212- -------------- 12-12月-12 SQL> select to_date('1212-12-12','yyyy-mm-dd') from dual; TO_DATE('1212- -------------- 12-12月-12 SQL> select to_char(sysdate,'dd month year') from dual; TO_CHAR(SYSDATE,'DDMONTHYEAR') ---------------------------------------------------- 04 4月 twenty thirteen SQL> select to_char(sysdate,'dd month yyyy') from dual; TO_CHAR(SYSDAT -------------- 04 4月 2013

5.转换函数

隐式数据类型转换:Oracle自动完成下列转换:



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





例子:
select ename,to_char(sal,'$999,999.00') from emp; ENAME TO_CHAR(SAL, ---------- ------------ SMITH $800.00 ALLEN $1,600.00 WARD $1,250.00 select ename,to_char(sal,'l999,999.00') from emp; ENAME TO_CHAR(SAL,'L999,999 ---------- --------------------- SMITH ¥800.00 ALLEN ¥1,600.00 WARD ¥1,250.00
6. 通用函数
这些函数适用于任何数据类型,同时也适用于空值:
NVL (expr1, expr2):将空值转换成一个已知的值:可以使用的数据类型有日期、字符、数字。
函数的一般形式:
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
NVL2 (expr1, expr2, expr3) : expr1不为NULL,返回expr2;为NULL,返回expr3。相当于java中的三目运算符
NULLIF (expr1, expr2) : 相等返回NULL,不等返回expr1
COALESCE (expr1, expr2, ..., exprn):COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式,对其他的参数进行COALESCE 。
例子:

SQL> select ename,nvl(comm,0) from emp;   ENAME  NVL(COMM,0) 
---------- ----------- 
SMITH  0   ALLEN  300   WARD  500   错误写法,条件comm与0位置混乱   SQL> select ename,nvl2(comm,0,comm) from emp;   ENAME  NVL2(COMM,0,COMM) 
---------- ----------------- 
SMITH   ALLEN  0   WARD  0   JONES   正确写法:   SQL> select ename,nvl2(comm,comm,0) from emp;   ENAME  NVL2(COMM,COMM,0)   ---------- -----------------   SMITH  0   ALLEN  300   WARD  500   SQL> select nullif(2,2) from dual;   NULLIF(2,2) 
----------- 
SQL> select nullif(2,1) from dual;   NULLIF(2,1)   -----------     2 
SQL> select nullif(1,2) from dual;   NULLIF(1,2)   -----------     1   SQL> select ename,     2  coalesce(sal,comm) from emp; 
 
  ENAME  COALESCE(SAL,COMM)   ---------- ------------------   SMITH  800   ALLEN  1600   WARD  1250 
 
  COALESCE(COMM,SAL)   ------------------     800     300     500     2975    

7条件表达式
在 SQL 语句中使用IF-THEN-ELSE 逻辑;使用两种方法:
CASE 表达式
DECODE 函数
例子:

SQL> select ename,job,sal,case     2  job when 'SALESMAN' then sal*1.2     3  when 'MANAGER' then sal*1.8     4  else sal end  "revised_sal" from emp;   ENAME  JOB  SAL revised_sal   ---------- --------- ----- -----------   SMITH  CLERK  800  800   ALLEN  SALESMAN  1600  1920   WARD  SALESMAN  1250  1500   JONES  MANAGER  2975  5355   SQL> select ename,job,sal,decode(     2  job,'SALESMAN',1.2*sal,     3  'MANAGER',1.8*sal,sal)     4  revised_sal from emp;   ENAME  JOB  SAL REVISED_SAL 
---------- --------- ----- ----------- 
SMITH  CLERK  800  800   ALLEN  SALESMAN  1600  1920   WARD  SALESMAN  1250  1500  


8.嵌套函数
单行函数可以嵌套。嵌套函数的执行顺序是由内到外。
例子:
SQL> select ename,job,nvl2(to_char(job),'manager','NO MANAGER') from emp; ENAME JOB NVL2(TO_CH ---------- --------- ---------- SMITH CLERK manager ALLEN SALESMAN manager WARD SALESMAN manager

点击复制链接 与好友分享!回本站首页
上一篇:oracle心得1--oracle简介@基本sql语句@条件查询@排序数据理论与案例
下一篇:oracle心得3--多表查询@分组查询@子查询讲解与案例分析@经典练习题
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站