Oracle学习笔记4--单行函数

Oracle学习笔记4--单行函数

　　使用函数可以完成一系列的操作。数据库之间的不同，在于对函数的支持上是不一样。

　　本次笔记学习函数。函数分为单行函数和多行函数，此处重点学习单行函数。

　　单行函数分类：

　　字符函数

　　数值函数

　　日期函数

　　转换函数

　　通用函数

　　字符函数：又分为大小写控制函数和字符控制函数

　　1)大小写空值函数：

　　LOWER()

　　UPPER()

　　INITCAP()

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5

SQL> select lower('SQL COURSE') , upper('sql course') , initcap('SQL course') from dual ; 　　  　　LOWER('SQLCOURSE') UPPER('SQLCOURSE') INITCAP('SQLCOURSE') 　　------------------ ------------------ -------------------- 　　sql course        SQL COURSE        Sql Course

　　通过上面的查询结果，看出：

　　Lower() 函数的作用是：将字符转换为小写。

　　Upper()函数的作用是：将字符转换为大写。

　　Initcap()函数的作用是：将字符的首字母转换为大写。

　　例：查询名字是king的雇员信息 ,由于不确定数据库中名字的大小写形式，我们引入lower函数和 upper()函数，来保证查询的有效性。

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11

SQL> select * from emp where lower(ename) = 'king' ; 　　  　　EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO 　　----- ---------- --------- ----- ----------- --------- --------- ------ 　　7839 KING      PRESIDENT      1981/11/17   5000.00              10 　　SQL> select * from emp where ename = upper('king' ); 　　  　　EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO 　　----- ---------- --------- ----- ----------- --------- --------- ------ 　　7839 KING      PRESIDENT      1981/11/17   5000.00              10

　　例：将查询结果字符的首字母转换为大写形式，使用initcap 函数。

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1	SQL> select initcap(ename) from emp where ename = 'KING';

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3

INITCAP(ENAME) 　　-------------- 　　King

　　字符控制函数：

　　concat()

　　substr()

　　length()

　　replace()

　　例：字符串除了使用‘||’连接之外，还可以使用concat() 函数进行连接 。

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11

SQL> select concat('Hello ' , 'world' ) from dual ; 　　  　　CONCAT('HELLO','WORLD') 　　----------------------- 　　Hello world 　　SQL> select substr('hello' , 1 , 2) ,length('world') , replace('sql' , 'q' , 'X') from dual ; 　　  　　SUBSTR('HELLO',1,2) LENGTH('WORLD') REPLACE('SQL','Q','X') 　　------------------- --------------- ---------------------- 　　    he                  5     sXl

　　上例中我们可以看到 substr()的作用是截取字符串，在字符串的后面跟两个参数，第一个参数表示的是要截取的

　　起始位置，第二个参数表示的是截取的个数，length()函数返回的是字符串的长度。replace() 字符串后面跟两

　　个参数，第一个参数表示要替换的字母，第二个参数表示将第一个字母换为第二个字母。

　　另外Oracle还支持倒着截取字符串的方式：

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7

SQL> select ename ,substr(ename ,-3 ) from emp where deptno = 10 ; 　　  　　ENAME     SUBSTR(ENAME,-3) 　　---------- ---------------- 　　CLARK     ARK 　　KING      ING 　　MILLER    LER

　　数值函数：

　　round()：四舍五入函数

　　trunc()：截断小数位函数

　　mod() ：取余函数

　　例：

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5

SQL> select round(234.12 , 2) , round(234.324) , round(234.25 , -2) from dual; 　　  　　ROUND(234.12,2) ROUND(234.324) ROUND(234.25,-2) 　　--------------- -------------- ---------------- 　　    234.12           234             200

　　后面的参数 2 表示保留小数点后两位，如果不写，默认为零，-2 表示对小数点前面的数进行四舍五入ROUND(234.25,-2) , 4 舍去，3 舍去得到200。

　　例：

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5

SQL> select trunc(234.12 , 2) , trunc(234.324) , trunc(248.25 , -2) from dual; 　　  　　TRUNC(234.12,2) TRUNC(234.324) TRUNC(248.25,-2) 　　--------------- -------------- ---------------- 　　         234.12           234             200

　　trunc(234.12 , 2)制定保留小数的位数; trunc(234.324)直接舍去小数 , trunc(248.25 , -2)，舍去小数点前面的两位。

　　小结：trunc() 和 round()函数的区别是trunc(),直接进行相应的操作，不会执行四舍五入。

　　例：

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5

SQL> select mod(10 , 3) from dual ; 　　  　　MOD(10,3) 　　---------- 　　         1

　　使用mod()函数进行求余的操作。

　　日期函数：

　　关于日期的一些操作：

　　日期-数字 = 日期

　　日期+数字 = 日期

　　日期-日期 = 天数

　　日期之间不可以做加法的运算。

　　例：估计员工入职的月数，sysdate 表示的是当前的时间。

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11 　　12 　　13 　　14 　　15 　　16 　　17 　　18 　　19 　　20

SQL> select empno , ename , round((sysdate - hiredate)/30) days from emp ; 　　  　　EMPNO ENAME           DAYS 　　----- ---------- ---------- 　　7369 SMITH            406 　　7499 ALLEN            403 　　7521 WARD             403 　　7566 JONES            402 　　7654 MARTIN           396 　　7698 BLAKE            401 　　7782 CLARK            400 　　7788 SCOTT            328 　　7839 KING             394 　　7844 TURNER           397 　　7876 ADAMS            327 　　7900 JAMES            394 　　7902 FORD             394 　　7934 MILLER           392 　　  　　14 rows selected

　　其他日期函数：

　　MONTHS_BETWEEN()：求出给定日期范围的月数。

　　ADD_MONTHS()：在制定日期上加上指定的月数，求出之后的日期。

　　NEXT_DAY()：下一个的今天是那一个日期。

　　LAST_DAY()：求出给定日期的最后一天日期。

　　例：查询连个日期之间相差的月数，sysdate 表示的当前的时间。

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11 　　12 　　13 　　14 　　15 　　16 　　17 　　18 　　19 　　20

SQL> select empno , ename , months_between(sysdate , hiredate) days from emp ; 　　  　　EMPNO ENAME           DAYS 　　----- ---------- ---------- 　　7369 SMITH     399.772141 　　7499 ALLEN     397.675367 　　7521 WARD      397.610850 　　7566 JONES     396.256012 　　7654 MARTIN    390.417302 　　7698 BLAKE     395.288270 　　7782 CLARK            394 　　7788 SCOTT     323.707625 　　7839 KING      388.772141 　　7844 TURNER    391.062463 　　7876 ADAMS     322.578592 　　7900 JAMES     388.223754 　　7902 FORD      388.223754 　　7934 MILLER    386.578592 　　  　　14 rows selected

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6

例：当前日期加上四个月之后的时间。 　　SQL> select add_months(sysdate , 4) from dual ; 　　  　　ADD_MONTHS(SYSDATE,4) 　　--------------------- 　　2014/8/9 22:29:57

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11 　　12 　　13

例：下一个星期三的日期。 　　SQL> select next_day(sysdate , '星期三') from dual ; 　　  　　NEXT_DAY(SYSDATE,'星期三') 　　-------------------------- 　　2014/4/16 22:31:23 　　例：本月的最后一天的日期。 　　SQL> select last_day(sysdate) from dual ; 　　  　　LAST_DAY(SYSDATE) 　　----------------- 　　2014/4/30 22:32:0

　　转换函数：

　　to_char()：转换为字符串

　　to_number():转换成数字

　　to_date():转换成日期

　　三者之间的相互转化关系：

　　<-- to_date --> to_number

　　date =================char ===================number

　　--> to_char <-- to_char

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11 　　12 　　13 　　14 　　15 　　16 　　17 　　18 　　19 　　20

SQL> select empno , ename ,to_char( hiredate , 'yyyy-mm-dd') from emp ; 　　  　　EMPNO ENAME     TO_CHAR(HIREDATE,'YYYY-MM-DD') 　　----- ---------- ------------------------------ 　　7369 SMITH     1980-12-17 　　7499 ALLEN     1981-02-20 　　7521 WARD      1981-02-22 　　7566 JONES     1981-04-02 　　7654 MARTIN    1981-09-28 　　7698 BLAKE     1981-05-01 　　7782 CLARK     1981-06-09 　　7788 SCOTT     1987-04-19 　　7839 KING      1981-11-17 　　7844 TURNER    1981-09-08 　　7876 ADAMS     1987-05-23 　　7900 JAMES     1981-12-03 　　7902 FORD      1981-12-03 　　7934 MILLER    1982-01-23 　　  　　14 rows selected

　　转换日期的格式。年：y ，年是四位数字表示 yyyy ， 月：m 两位数字 表示 mm ， 日： d 两位数字表示 dd 。

　　数字转换为字符。使用一些符号对数字进行分割，增加可读性。可以使用‘$’和‘L’表示出区域。‘$’表示美元 ，L ： local表示以本地的语言进行金额的显示。

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11 　　12 　　13 　　14 　　15 　　16 　　17 　　18 　　19 　　20

SQL> select sal , to_char(sal , '$99,999') from emp ; 　　  　　      SAL TO_CHAR(SAL,'$99,999') 　　--------- ---------------------- 　　   800.00    $800 　　  1600.00  $1,600 　　  1250.00  $1,250 　　  2975.00  $2,975 　　  1250.00  $1,250 　　  2850.00  $2,850 　　  2450.00  $2,450 　　  3000.00  $3,000 　　  5000.00  $5,000 　　  1500.00  $1,500 　　  1100.00  $1,100 　　   950.00    $950 　　  3000.00  $3,000 　　  1300.00  $1,300 　　  　　14 rows selected

　　例：to_number() 字符串变为数字的函数

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4

SQL> select to_number('34')+46 from dual; 　　TO_NUMBER('34')+46 　　------------------ 　　                80

　　将字符串变为数字，然后进行数字的运算。

　　例：to_date() 将一个字符串变为date型的数据。

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4

SQL> select to_date('2014-4-9' ,'yyyy/mm/dd') dates from dual ; 　　DATES 　　----------- 　　2014/4/9

　　通用函数：

　　NVL() :将一个指定的null值变为指定的内容。空值是无法参与运算的所以把空值替换为 0 。

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11 　　12 　　13 　　14 　　15 　　16 　　17 　　18 　　19 　　20

SQL> select empno ,(nvl(comm , 0 )+sal)*12 annual from emp ; 　　  　　EMPNO    ANNUAL 　　----- ---------- 　　7369      9600 　　7499     22800 　　7521     21000 　　7566     35700 　　7654     31800 　　7698     34200 　　7782     29400 　　7788     36000 　　7839     60000 　　7844     18000 　　7876     13200 　　7900     11400 　　7902     36000 　　7934     15600 　　  　　14 rows selected

　　decode() 函数，类似与java中的if… else if … else

　　例：

　　?

　　

　　

　　

　　

　　

　　

　　

　　

1 　　2 　　3 　　4 　　5 　　6 　　7 　　8 　　9 　　10 　　11 　　12 　　13 　　14 　　15 　　16 　　17

SQL> select decode(1 , 1 ,'数值1' , 2 ,'数值2' , 3 , '数值3') from dual ; 　　  　　DECODE(1,1,'数值1',2,'数值2',3 　　------------------------------ 　　数值1 　　  　　SQL> select decode(2 , 1 ,'数值1' , 2 ,'数值2' , 3 , '数值3') from dual ; 　　  　　DECODE(2,1,'数值1',2,'数值2',3 　　------------------------------ 　　数值2 　　  　　SQL> select decode(3 , 1 ,'数值1' , 2 ,'数值2' , 3 , '数值3') from dual ; 　　  　　DECODE(3,1,'数值1',2,'数值2',3 　　------------------------------ 　　数值3

　　通过上面三个查询的结果，我们不难发现，decode()函数的作用和用法。第一个参数为待匹配的值，之后参数两两一组，一组中第一个字符与带匹配字符比较，相同则输出第二个字符。

　　case()的用法与decode()基本相同：

　　语法格式为：

　　selectename ,caseename

　　when'KING'then'Hello'

　　when'FORD'then'Word'

　　when'MILLER'then'HELLOWORD'

　　elseename

　　end name

　　fromemp

　　wheredeptno =10

　　查询结果为：

　　ENAME NAME

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

　　CLARK CLARK

　　KING Hello

　　MILLER HELLOWORD