Oracle学习(三)----函数

1 函数可以没有参数，但必须要有返回值，函数分为单行函数和多行函数
1.1 单行函数
单行函数是指有单个参数输入，每行返回一个结果；
可以转换数据类型；
可以嵌套；
参数可以是一列或一个值。
单行函数类型可以是字符、数值、日期、转换、通用
1.1.1 字符函数
--大小写控制函数：LOWER；UPPER；INITCAP；


SQL> select upper('hello') 小写, lower('hello') 大写， initcap('hello word.') 首字母大写 from dual;


小写  大写  首字母大写
---- - ---- - ---------- -
HELLO hello Hello Word.


--字符控制函数：CONCAT(连接字符), SUBSTR,LENGTH/LENGTHB,INSTR,LPAD |RPAD, TRIM, REPLACE
(1):concat


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


CONCAT
----------
helloworld
(2):函数嵌套


SQL> select concat(concat('hello', 'world'), 'ddd') 函数嵌套 from dual;


函数嵌套
------------ -
helloworldddd
(3) :substr;
substr(a, b)----从a中，第b位开始取;
substr(a,b,c)---从a中，第b位开始取，取5个字符


SQL> select substr('abcdefghijklmnopq', 3) "第三个字符开始取", substr('abcdefghijklmnopq', 3, 5) "三个字符开始取5个" from dual;


第三个字符开始  三个
-------------- - ---- -
cdefghijklmnopq cdefg
(4):length是计算字符串的字符数目，lengthb是计算字节数目，其中一个中文两个字节


SQL> select length('中国abc') 字符数目， lengthb('中国abc') 字节 from dual;


字符数目       字节
---------- ----------
5          7
(5) : instr('母串'，'子串')，在母串中查找子串，返回子串在母串中的位置，找不到返回0;




SQL> select instr('abcdefg', 'efg') 查找 from dual;


查找
----------
5
(6) : lpad，rpad将未满的占位符填充符号;


SQL> select lpad('abc', 10, '*') lpad, rpad('def', 10, '$') rpad from dual;


LPAD       RPAD
---------- ----------
*******abc def$$$$$$$


(7):trim，去掉前后制定的字符，字符可以是空格，也可以不是空格,相当于在字符串删除掉该字符


SQL> select trim('A' from 'ABCdefg') trim from dual;


TRIM
------
BCdefg
(8)：replace替换; repalce('abcd', 'b', 'B');将字符串中的b替换成为‘B’
SQL> select replace('abcd', 'b', 'B') from dual;


REPL
----
aBcd
1.1.2 数字函数
(1) ROUND：四舍五入
2 > 0保留到小数点后两位，
0保留到个数
1 保留到十位;


SQL> select round(123.456, 2), round(123.456, 1), round(123.456, 0), round(123.456, 1) from dual;


ROUND(123.456, 2) ROUND(123.456, 1) ROUND(123.456, 0) ROUND(123.456, 1)
---------------- ---------------- ---------------- ----------------
123.46            123.5              123            123.5


SQL>


(2) TRUNC：截断
数字大于零时在小数点后边截断；


SQL> select trunc(123.456, 2), trunc(123.456, 0) from dual;


TRUNC(123.456, 2) TRUNC(123.456, 0)
---------------- ----------------
123.45              123


(3) MOD：求余


1.1.3 日期函数


oracle数据库中date包含日期和时间
mysql数据库中有三种数据类型date，time，timestamp

(1)系统自带的显示时间的格式：sysdate包含日期和时间


SQL> select sysdate from dual;


SYSDATE
--------------
25 - 3月 - 16


（2）将日期转化为想要的格式




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


时间
------------------ -
2016 - 03 - 25 15:16 : 01
（3）日期的运算：
1. 在日期上加上或减去一个数字结果仍为日期;
SQL> select sysdate - 1 昨天, sysdate 今天, sysdate 明天 from dual;


昨天           今天           明天
-------------- -------------- --------------
24 - 3月 - 16     25 - 3月 - 16     25 - 3月 - 16
2. 两个日期相减返回日期之间相差的天数;
按照周 月 年 显示(这样只是粗略的计算)


SQL> select(sysdate - hiredate) / 7 周, (sysdate - hiredate) / 30 月, (sysdate - hiredate) / 365 年 from emp;


周         月         年
---------- ---------- ----------


1840.37754 429.421427 35.2949118
1831.09183  427.25476 35.1168296
1830.80612 427.188094 35.1113502
1825.23469 425.888094 35.0045009
1799.66326 419.921427 34.5140899
1821.09183 424.921427 34.9250488
1815.5204 423.621427 34.8181995
1509.80612 352.288094 28.9551858
1792.5204  418.25476 34.3771036
1802.5204 420.588094 34.5688844
1504.94897  351.15476 28.8620351
1790.23469 417.721427  34.333268
1790.23469 417.721427  34.333268
1782.94897 416.021427  34.193542


已选择15行。


可以用数字除24来向日期中加上或减去小时;


精确的计算，MONTHS_BEWTEEN(t1,t2)：


SQL> select ename, MONTHS_BETWEEN(sysdate, hiredate) 精算, (sysdate - hiredate) / 30 粗略估计 from emp;


ENAME            精算   粗略估计
---------- ---------- ----------
tom_abc
SMITH      423.278948 429.421579
ALLEN      421.182173 427.254912
WARD       421.117657 427.188246
JONES      419.762818 425.888246
MARTIN     413.924109 419.921579
BLAKE      418.795077 424.921579
CLARK      417.537012 423.621579
SCOTT      347.214431 352.288246
KING       412.278948 418.254912
TURNER      414.56927 420.588246
ADAMS      346.085399 351.154912
JAMES       411.73056 417.721579
FORD        411.73056 417.721579
MILLER     410.085399 416.021579




1. MONTHS_BEWTEEN：两个日期相差的月数；


SQL> select months_between('01-1月-2016', '03-3月-2015') 测试 from dual;


 测试
----------
9.93548387


2. ADD_MONTHS：向指定日期中加上若干月数；
SQL> select add_months('01-1月-2016', 5) from dual;


 ADD_MONTHS('01
 --------------
 01 - 6月 - 16
3. NEXT_DAY：指定日期的下一个日期




SQL> select NEXT_DAY('01-1月-2016', '星期五') from dual;


NEXT_DAY('01-1
 --------------
 08 - 1月 - 16
4. LAST_DAY：本月的最后一天
SQL> select LAST_DAY('01-1月-2016') from dual;


LAST_DAY('01-1
--------------
31 - 1月 - 16
ROUND：日期四舍五入
TRUNC：日期截断


1.1.4类型转化


oracle能够自动将字符串转化为数字，将字符串转化为日期，将日期转化为字符串，将数字转化为字符串;
to_char, to_date, to_number


插图2.1
（1）隐式转换
SQL> select * from emp where hiredate > '01-1月 -81';


EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- -------- - ---------- -------------- ---------- ---------- ----------
7499 ALLEN      SALESMAN        7698 20 - 2月 - 81           1600        300         30
7521 WARD       SALESMAN        7698 22 - 2月 - 81           1250        500         30
7566 JONES      MANAGER         7839 02 - 4月 - 81           2975                    20
7654 MARTIN     SALESMAN        7698 28 - 9月 - 81           1250       1400         30
7698 BLAKE      MANAGER         7839 01 - 5月 - 81           2850                    30
7782 CLARK      MANAGER         7839 09 - 6月 - 81           2450                    10
7788 SCOTT      ANALYST         7566 19 - 4月 - 87           3000                    20
7839 KING       PRESIDENT            17 - 11月 - 81           5000                    10
7844 TURNER     SALESMAN        7698 08 - 9月 - 81           1500          0         30
7876 ADAMS      CLERK           7788 23 - 5月 - 87           1100                    20
7900 JAMES      CLERK           7698 03 - 12月 - 81            950                    30
7902 FORD       ANALYST         7566 03 - 12月 - 81           3000                    20
7934 MILLER     CLERK           7782 23 - 1月 - 82           1300                    10
(2)显示转换

SQL> select * from emp
2  where hiredate > to_date('1981-01-01 02:03:00', 'yyyy-mm-dd hh24:mi:ss');


EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- -------- - ---------- -------------- ---------- ---------- ----------
7499 ALLEN      SALESMAN        7698 20 - 2月 - 81           1600        300         30
7521 WARD       SALESMAN        7698 22 - 2月 - 81           1250        500         30
7566 JONES      MANAGER         7839 02 - 4月 - 81           2975                    20
7654 MARTIN     SALESMAN        7698 28 - 9月 - 81           1250       1400         30
7698 BLAKE      MANAGER         7839 01 - 5月 - 81           2850                    30
7782 CLARK      MANAGER         7839 09 - 6月 - 81           2450                    10
7788 SCOTT      ANALYST         7566 19 - 4月 - 87           3000                    20
7839 KING       PRESIDENT            17 - 11月 - 81           5000                    10
7844 TURNER     SALESMAN        7698 08 - 9月 - 81           1500          0         30
7876 ADAMS      CLERK           7788 23 - 5月 - 87           1100                     20
7900 JAMES      CLERK           7698 03 - 12月 - 81            950                    30
7902 FORD       ANALYST         7566 03 - 12月 - 81           3000                    20
7934 MILLER     CLERK           7782 23 - 1月 - 82           1300                    10


已选择13行。


(3) 显示转换2


查询员工的薪水：两位小数 本地货币代码 千位符
SQL> select ename, job, to_char(sal, 'L9,999.00') from emp;


ENAME      JOB       TO_CHAR(SAL, 'L9,999
---------- -------- - ------------------ -
tom_abc                       ￥8, 000.00
SMITH      CLERK                ￥800.00
ALLEN      SALESMAN           ￥1, 600.00
WARD       SALESMAN           ￥1, 250.00
JONES      MANAGER            ￥2, 975.00
MARTIN     SALESMAN           ￥1, 250.00
BLAKE      MANAGER            ￥2, 850.00
CLARK      MANAGER            ￥2, 450.00
SCOTT      ANALYST            ￥3, 000.00
KING       PRESIDENT          ￥5, 000.00
TURNER     SALESMAN           ￥1, 500.00
ADAMS      CLERK              ￥1, 100.00
JAMES      CLERK                ￥950.00
FORD       ANALYST            ￥3, 000.00
MILLER     CLERK              ￥1, 300.00






SQL> select to_number('￥8,000.00', 'L9,999.00') 格式转化 from dual;


格式转化
----------
8000






SQL> select * from emp
2  where to_char(hiredate, 'yyyy-mm-dd') > '1980-01-01'
3;


EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- -------- - ---------- -------------- ---------- ---------- ----------
7369 SMITH      CLERK           7902 17 - 12月 - 80            800                    20
7499 ALLEN      SALESMAN        7698 20 - 2月 - 81           1600        300         30
7521 WARD       SALESMAN        7698 22 - 2月 - 81           1250        500         30
7566 JONES      MANAGER         7839 02 - 4月 - 81           2975                    20
7654 MARTIN     SALESMAN        7698 28 - 9月 - 81           1250       1400         30
7698 BLAKE      MANAGER         7839 01 - 5月 - 81           2850                    30
7782 CLARK      MANAGER         7839 09 - 6月 - 81           2450                    10
7788 SCOTT      ANALYST         7566 19 - 4月 - 87           3000                    20
7839 KING       PRESIDENT            17 - 11月 - 81           5000                    10
7844 TURNER     SALESMAN        7698 08 - 9月 - 81           1500          0         30
7876 ADAMS      CLERK           7788 23 - 5月 - 87           1100                    20
7900 JAMES      CLERK           7698 03 - 12月 - 81            950                    30
7902 FORD       ANALYST         7566 03 - 12月 - 81           3000                    20
7934 MILLER     CLERK           7782 23 - 1月 - 82           1300                    10




1.1.5 通用函数
nvl(a, b)----当a是null的时候返回b；
nvl2(a,b,c)----当a是null的时候返回c，否则返回b；
nullif(a,b)----当a=b时，返回null， 否则返回a


多行函数是指有多行参数输入，单行输出；


SQL>  select count(*) from emp;


COUNT(*)
----------
15


2 插入的数据如果没有提交的话可以用rollback回退
SQL> rollback;


回退已完成。




案例：给员工涨工资：总裁1000 经理：800 其他张500
前后工资列出来


if (job = 'PRESIDENT')
sal+1000
else if (job = 'MANAGER')
sal+800
else
sal+500


case 表达式：
CASE expr(通常列名) WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END






1  select ename, job, sal 涨前,
2  (case job when 'PRESIDENT' then sal + 1000
3            when 'MANAGER' then sal + 800
4       else sal + 500 END) 涨后
5 * from emp
SQL> /


ENAME      JOB             涨前       涨后
---------- -------- - ---------- ----------
tom_abc                    8000       8500
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       6000
TURNER     SALESMAN        1500       2000
ADAMS      CLERK           1100       1600
JAMES      CLERK            950       1450
FORD       ANALYST         3000       3500
MILLER     CLERK           1300       1800


已选择15行。


decode表达式


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




已写入 file afiedt.buf


1  select ename, job, sal 涨前,
2  (
3  decode(job, 'PRESEIDENT', sal + 1000, 'MANAGER', sal + 800, sal + 500)
4) 涨后
5 * from emp
SQL> /


ENAME      JOB             涨前       涨后
---------- -------- - ---------- ----------
tom_abc                    8000       8500
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
JAMES      CLERK            950       1450
FORD       ANALYST         3000       3500
MILLER     CLERK           1300       1800


已选择15行。