oracle心得2--单行函数理论讲解与案例分析.doc
Sql有两种函数，单行函数和多行函数
1.单行函数
单行函数:操作数据对象、接受参数返回一个结果、只对一行进行变换、每行返回一个结果、可以转换数据类型、可以嵌套、参数可以是一列或一个值
DUAL是一个‘伪表’，可以用来测试函数和表达式
2.字符函数
大小写控制函数：这类函数改变字符的大小写。
例子：
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 29757条件表达式
在 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
