oracle 单行函数 指令

   单行函数的功能非常的简单就是完成某一个功能，例如：现在希望将字母变为大写，这样的功能就可以通过单行函数完成，在Oracle之中提供的单行函数非常的多，今天主要是看核心的几个。           

        单行函数分类为：字符串函数、数字函数、日期函数、转换函数、通用函数。所有的单行函数在进行记忆的时候都要求清楚的记下单行函数的名称、返回值类型、接收的参数数据。

4.1、字符串函数

        字符串函数的主要功能主要是进行字符串数据的处理，下面按照不同的类型来观察函数的使用。

        在Oracle之中为了方便用户进行函数的验证，专门提供了一个dual的虚拟表，而关于这张表的相关概念，随后再进行讲解。

1、  大小写转换函数：

        · 转大写：字符串 UPPER(字符串 | 数据列)；

        · 转小写：字符串 LOWER(字符串 | 数据列)。

范例：验证函数 —— 直接在字符串数据上使用

SELECT UPPER('Hello'),LOWER('Hello') FROM dual ;

范例：验证函数 —— 在列上使用（将所有雇员的姓名按照小写字母的方式显示）

SELECT LOWER(ename) FROM emp ;

        通过以上的验证可以清楚的发现，在使用UPPER()和LOWER()函数操作的时候是不关心提供的字符串数据是大写还是小写的，都会按照统一的规则转换，那么就可以实际之中利用这两个函数进行一些数据的处理。例如：某些系统上用户名是不区分大小写的，而这样的功能都是在数据保存的时候将保存的数据变为统一的大写或小写。

范例：要求用户输入一个雇员的姓名，而后查询出此雇员的详细信息

        · 那么此时首先要解决的问题就是如何实现用户的数据输入，在Oracle数据库里提供了一种替代变量的可输入操作。

SELECT * FROM &tablename ;  è要查询的数据表名称由用户输入

输入 tablename 的值:  emp

原值    1:SELECT * FROM &tablename

新值    1:SELECT * FROM emp

        · 那么下面就利用替代变量输入雇员姓名

SELECT * FROM emp WHERE ename=UPPER('&name') ;

工作之中如果有些系统不区分大小写，那么其主要的操作形式一定是按照统一的大写或小写保存数据，那么就要使用到了UPPER()、LOWER()两个函数。

2、  首字母大写，其余字母小写：字符串 initcap(字符串 |列)

范例：将每一位雇员姓名的首字母大写，其余字母小写

SELECT INITCAP(ename) FROM emp ;

3、  取得字符串长度：数字 LENGTH(字符串 |列)

范例：计算字符串的长度

SELECT LENGTH('hello world') FROM dual ;

范例：查询出所有雇员姓名以及每个雇员姓名的长度

SELECT ename,LENGTH(ename) FROM emp ;

范例：查询出所有雇员姓名的长度为5的全部雇员信息

        · 此时一定要增加WHERE子句来进行数据行的控制，同时还需要利用LENGTH()计算出名字长度

SELECT ename,LENGTH(ename)

FROM emp

WHERE LENGTH(ename)=5 ;

4、  替换指定字符串的内容：字符串 REPLACE(字符串 |列，要替换的内容，新的内容)

范例：替换字符串数据

SELECT REPLACE('hello world','l','_') FROM dual ;

范例：替换数据列

SELECT ename,REPLACE(ename,'A','_') FROM emp ;

5、  字符串截取操作：

        · 由指定位置截取到结尾：字符串 SUBSTR(字符串 | 数据列，截取开始索引)；

        · 指定截取的开始和结束位置：字符串 SUBSTR(字符串 | 数据列，截取开始索引，截取结束索引)；

范例：字符串截取 —— 由指定位置截取到结尾

SELECT SUBSTR('hello world',7) FROM dual ;

范例：字符串截取 —— 截取部分

SELECT SUBSTR('hello world',0,5) FROM dual ;

SELECT SUBSTR('hello world',1,5) FROM dual ;

        实际上在Oracle之中，字符串的索引下标是从1开始的（程序是从0开始的），但是为了考虑到程序人员的使用习惯，所以即使设置了0，那么也表示从1开始，这一点会由Oracle自己进行处理。

范例：截取每一位雇员姓名的前三位字母

SELECT ename,SUBSTR(ename,1,3) FROM emp ;

范例：截取每一位雇员姓名的后三位字母

        ·传统思路：对于后三位字母如果要想取得，那么必须确定截取的开始位置，由于每一位雇员姓名的长度是不一样的，所以截取的开始点也一定是不一样的，那么就只能通过计算长度后减去一定的数字才可以得到正确的开始位置。

SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ;

        · 改善思路：在Oracle设计的时候，SUBSTR()函数就已经考虑到了由后向前的截取

SELECT ename,SUBSTR(ename,-3) FROM emp ;

面试题：请问Oracle之中的SUBSTR()函数进行截取的时候下标是从0还是从1开始？

        ·实际上从0还是从1开始没有任何的区别，Oracle之中的字符串是从1开始的，但是即使设置了0，也表示从1开始，同时SUBSTR()函数还可以设置为负数，表示由后的指定位置开始。

6、  去掉左右空格函数：字符串 TRIM(字符串 |列)

        使用TRIM()函数可以去掉的只是左右空格，但是中间的空格是无法取消的。

范例：验证TRIM()函数

SELECT '   helloworld   ',TRIM('   helloworld    ') FROMdual ;

        这类的操作在进行数据接收的时候非常有用处。

4.2、数字函数

        数字函数主要是针对于数字进行的一些小处理。

1、  四舍五入函数：数字 ROUND(数字 | 列[，保留小数位])

范例：验证ROUND()函数

SELECT   ROUND(9615.7652) ,       è    9616

        ROUND(9615.7652,2) ,              è    9615.77

        ROUND(9615.7652,-2) ,            è    9600

        ROUND(-15.5)                                     è    -16

FROM dual ;

        如果没有编写保留小数位数，那么会将小数点之后的数据直接进行进位，不保留小数位。

2、  截取小数函数：数字 TRUNC(数字 | 列[，保留小数位])

范例：验证TRUNC()函数

SELECT   TRUNC(9615.7652) ,

        TRUNC(9615.7652,2) ,

        TRUNC(9615.7652,-2) ,

        TRUNC(-15.5)

FROM dual ;

3、   求模：数字 MOD(数字1 |列1，数字2 | 列2)

范例：求余数

SELECT MOD(10,3) FROM dual ;

        以后在工作之中只要见到了ROUND这个单词一定要清楚它的作用就是四舍五入。

4.3、日期函数

        如果要想操作日期函数实际上有一个首要的问题需要解决。那么必须要首先取得当前的系统日期时间，为此在Oracle里面提供有两个伪列（是不存在表中的列，但是却可以直接使用的列）：SYSDATE、SYSTIMESTAMP。

范例：取得当前的系统日期

SELECT SYSDATE FROM dual ;

SELECT SYSTIMESTAMP FROM dual ;

        当有了系统日期之后就可以通过以下的三个公式来进行一些简单的计算：

                  · 日期 + 数字 =日期，表示若干天之后的日期；

                  · 日期 – 数字 =日期，表示若干天之前的日期；

                  · 日期 – 日期 =数字，表示两个日期之间的间隔天数。

范例：计算若干天之后的日期

SELECT SYSDATE + 3 , SYSDATE + 120 FROM dual ;

范例：计算若干天之前的日期

SELECT SYSDATE - 120 FROM dual ;

        计算的时候是按照自然日的天数进行的计算，是不会去考虑那个月有30天，有28天之类的。

范例：统计出每一位雇员到今天为止雇佣的天数

SELECT ename, SYSDATE - hiredate FROM emp ;

        但是如果在一个很长的时间之内通过天数计算年是绝对不准确的。所以如果要想准确的计算出天数，那么前后的差距不要超过30天。

        如果要想准确的进行日期时间的操作，那么就必须利用提供的日期函数。

1、  计算两个日期之间所经历的月数：数字 MONTHS_BETWEEN(日期1 |列1，日期2 |列2)

范例：计算每一位雇员到今天为止雇佣的年数

        · 年数的计算最准确的一定是通过月完成的，一年12个月是不会被改变的。

SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate) ,

        MONTHS_BETWEEN(SYSDATE,hiredate)/12

FROM emp ;

SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate) ,

        TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)

FROM emp ;

范例：查询出所有已经被雇佣了33年以上的雇员

SELECT * FROM emp

WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12>33 ;

2、  加上指定月之后的日期：日期 ADD_MONTHS(日期 | 列，月数)

范例：计算若干月之后的日期

SELECT SYSDATE+120,ADD_MONTHS(SYSDATE,4),ADD_MONTHS(SYSDATE,500)FROM dual ;

        在进行计算的时候，数字的大小是不受限制的。

思考题：要求计算出每一位雇员到今天为止雇佣的年限（理解）

        A、 例如：今天是2014年08月12日，CLARK的雇佣日期是：1981年06月09日

        B、 到今天CLARK已经在公司服务了：33年、02月、03天

第一步：计算出每一位雇员到今天为止的雇佣年份，直接利用月数除12即可

SELECT ename,hiredate,    TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year

FROM emp ;

第二步：计算月，在进行年计算的时候发现存在有小数，这些小数就包含了月

SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year ,

        TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months

FROM emp ;

第三步：计算天数，对于天数的计算实际上只学会了一个公式“日期1 – 日期2 = 天数”，于是分析日期：

        · 日期1：应该是当前日期“SYSDATE”；

        · 日期2：应该是已经剔除掉了经过月之后的日期。

SELECT ename,hiredate,

        TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year ,

        TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months ,

        TRUNC(SYSDATE -ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day

FROM emp ;

        只有日期函数可以准确的计算出日期来，其它的直接操作都无法进行。

3、  求出指定的下一个一周时间数的日期：日期 NEXT_DAY(日期 |列，一周时间数)

范例：计算下一个星期二

SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual ;

4、  求出指定日期所在月的最后一天日期：日期 LAST_DAY(日期 |列)

范例：求出本月的最后一天日期

SELECT LAST_DAY(SYSDATE) FROM dual ;

范例：查询出在每个雇员雇佣所在月倒数第二天雇佣的全部雇员信息

        · 每一个雇员雇佣日期是不同的，那么雇佣所在月也一定是不同的；

        · 倒数第二天应该使用：“雇佣所在月最后一天-2”，可以通过LAST_DAY()函数计算。

SELECT ename,hiredate,LAST_DAY(hiredate)-2

FROM emp

WHERE LAST_DAY(hiredate)-2=hiredate ;

        所有的日期函数实际上在进行一些统计操作的时候还是有用处的。

4.4、转换函数

        那么到现在为止已经接触到了Oracle中提供的三类数据：字符串、数字、日期，那么这三种数据之间是可以通过一些转换函数实现类型转换操作的。

1、  转字符串数据：字符串 TO_CHAR(数字 | 日期| 列，转换格式)

        在进行转换格式设置的时候要根据不同的数据类型进行格式标记的定义：

                  ·日期：年（yyyy）、月（mm）、日（dd）、时（HH、HH24）、分（mi）、秒（ss）；

                  ·数字：一位任意数字（9）、本地货币（L）。

范例：将日期格式化为字符串

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual ;         è    2014-08-12

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') FROM dual ;     è    2014-8-12

范例：在Oracle的SYSDATE里面是包含有时间数据的，也可以继续显示时间

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual ;

        那么实际上用户也可以利用TO_CHAR()函数进行年、月、日数据的拆分。

范例：拆分日期数据

SELECT TO_CHAR(SYSDATE,'yyyy') year ,

        TO_CHAR(SYSDATE,'mm') months ,

        TO_CHAR(SYSDATE,'dd') day

FROM dual ;

范例：查询出所有在2月份雇佣的雇员

SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')='02' ;

        使用TO_CHAR()还可以格式化数字显示。

范例：格式化数字显示

SELECT TO_CHAR(234789234798,'L999,999,999,999,999') FROM dual;

2、  转日期数据：日期 TO_DATE(字符串，转换格式)

范例：将字符串变为日期

SELECT '1981-09-15',TO_DATE('1981-09-15','yyyy-mm-dd') FROM dual;

        但是在Oracle之中提供有自动的转换方式，如果字符串按照日期的格式编写，那么可以自动由字符串变为日期。

3、  转数字类型：数字 TO_NUMBER(字符串，转换格式)

范例：将字符串变为数字

SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual ;

SELECT '1' + '2' FROM dual ;

        在Oracle里面这些自动的数据类型转换功能是非常方便的，包括日期和字符串、字符串和数字。

4.5、通用函数

        通用函数主要是Oracle的特色函数，有两个：NVL()、DECODE()。

1、  处理null数据：数字 NVL(列 |数据，默认值)

范例：要求计算每一位雇员的年薪

SELECT ename,sal,comm,(sal+comm)*12 FROM emp ;

        这个时候发现有一部分人是没有年薪的，而这部分人都是没有佣金的，因为在数据库之中，null与任何的数据进行数学计算其结果都是null，那么如果遇到了null应该按照数字0来处理，所以就可以使用NVL()函数。

SELECT ename,sal,comm,(sal+NVL(comm,0))*12 FROM emp ;

2、   多数据判断：数据 DECODE(字段 |数据，判断数据1，显示数据1，判断数据2，显示数据2，… [默认显示])

范例：将所有雇员的职位数据变为中文

SELECT ename,job ,

        DECODE(job,'CLERK','办事员','SALESMAN','销售','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁','未处理')

FROM emp ;

        非常类似于IF..ELSE IF语句。

本文转载自微博用户：MLDN李兴华的微博 地址：http://weibo.com/p/1035052004834713/wenzhang?cfs=600&Pl_Core_ArticleList__66_filter=&Pl_Core_ArticleList__66_page=11#Pl_Core_ArticleList__66