oracle数据库学习---单行函数

单行函数

　　虽然各个数据库都是支持SQL语句的，但是每一个数据库也有每一个数据库自己所支持的操作函数，这些就是单行函数，而如果要想进行数据库开发的话，除了要会使用SQL之外，就是要多学习函数。单行函数主要分为以下五类：字符函数、数字函数、日期函数、转换函数、通用函数；Oracle 之中有一点比较麻烦，即使要验证字符串，也必须编写完整的SQL语句，所以在Oracle数据库之中为了用户的查询方便，所以专门提供了一个“dual”的虚拟表。

字符函数

字符函数的功能主要是进行字符串数据的操作，下面给出几个字符函数：

UPPER（字符串|列）：将输入的字符串变为大写返回；
LOWER（字符串|列）：将输入的字符串变为小写返回；
INITCAP（字符串|列）：开头首字母大写；
LENGTH（字符串|列）：求出字符串的长度；
REPLACE（字符串|列）：进行替换；
SUBSTR（字符串|列，开始点[结束点]）：字符串截取

范例：观察转大写的函数

select upper('hello') from dual

大写转换的用处：在一般的使用之中，用户输入数据的时候去关心数据本身存放的是大写还是小写吗？

SELECT*FROM emp WHERE ename='&str';

此时如果输入的是小写，则肯定无法查询出数据，所以这个时候不能要求用户这么多，所以这个时候只能由程序自己去适应，加入一个函数：

  SELECT*FROM emp WHERE ename==UPPER（'&str'）；

范例：观察转小写的操作，将所有的雇员姓名按照小写字母返回

select lower(ename) from emp;

范例：将每一个雇员姓名的开头首字母大写

SELECT INITCAP(ename) from emp

范例：查询出每个雇员姓名的长度

SELECT ename，LENGTH(ename) FROM emp；

范例：要求查询出姓名长度正好是5的雇员信息

SELECT ename,LENGTH（ename）FROM emp WHERE LENGTH（ename =5）;  

范例：使用字母“_”替换掉姓名中的所有字母“A”

SELECT REPLACE（ename,’A’,’_’）FROM emp;

字符串截取操作有两种语法；

语法一：SUBSTR（字符串|列，开始点），表示从开始点一直截取到结尾；

 SELECT ename,SUBSTE(ename,3)FROM emp;

语法二：SUBSTR（字符串|列，开始点，结束点），表示从开始点截取到结束点，截取部分内容；

 SELECT ename，SUBSTR(ename,0,3) FROM emp;
 SELECT ename，SUBSTR(ename,1,3) FROM emp；

范例：要求截取每个雇员姓名的后三个字母

正常思路：通过长度-2确定开始点

 SELECT ename, SUBSTR (ename，LENGTH(exarme)-2)FROM emp；

新思路：设置负数，表示从后指定截取位置；  

SELECT enarme，SUBSTR (ename，-3）FROM emp；　

面试题：请问SUBSTR（）函数截取的时候下标是从0还是从1开始？

在Oracle 教据库之中，SUBSTR）函教从0或1开始都是一样的；

数字函数

数字函数一共有三个：ROUND（数字|列[保留小数的位数）：四舍五入的操作；TRUNC（数字|列[保留小数的位]）：舍弃指定位置的内容；MOD（数字1，数字2）：取模，取余数；

范例：验证ROUND（）函数

SELECT ROUND(903.53567)FROM dual;
SELECT ROUND(-903.53567) from dual;
SELECT ROUND(903.53567,2) from dual;
SELECT ROUND(903.53567,-1) from dual;

范例：验证 TRUNC()函数

SELECT
	TRUNC( 903.53567 ),
	TRUNC( - 903.53567 ),
	TRUNC( 903.53567, 2 ),
	TRUNC( 903.53567,- 1 ) 
FROM
	dual;

范例：取模操作

SELECT
	MOD( 10, 3 ) 
FROM
	dual;

日期函数

如果现在要想进行日期的操作，则首先有一个必须要解决的问题，就是如何取得当前的日期，这个当前日期可以使用“SYSDATE”取得，代码如下：

SELECT SYSDATE FROM dual;

除了以上的当前日期之外，在日期中也可以进行若干计算：

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

SELECT SYSDATE +3,SYSDATE +300 FROM dual;　

日期-数字=日期，表示若干天前的日期；

SELECT SYSDATE-3,SYSDATE-300 FROM dual;

日期-日期=数字，表示的是两个日期间的天数，但是肯定是大日期-小日期；+

范例：求出每个雇员到今天为止的雇佣天数

SEL EC T ename，hiredate，SYSDATE-hiredate FROM emp.；

而且很多的编程语言之中，也都会提出一种概念，日期可以通过数字表示出来。除了以上的三个公式之外，也提供了如下的四个操作函数：

LAST_DAY（日期）：求出指定日期的最后一天；

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

SELECT LAST_DAY(SYSDATE) FROM dual;

NEXT_DAY（日期，星期数）：求出下一个指定星期x的日期；

范例：求出下一个周一

//在oracle能用

SELECT NEXT_DAY（SYSDATE，’星期一’）FROM dual；

// 在navicat中能用，具体看字符集

SELECT NEXT_DAY(SYSDATE, 'mon') from dual;

ADD MONTHS（日期，数字）：求出若千月之后的日期；范例：求出你们学习的最晚毕业时间（即：找到工作的最晚时间）//4个月后

SELECT ADD_MONTHS(SYSDATE, 4) FROM dual;

范例：求出每个雇员到今天为止的雇佣月份

SELECT ename,HIREDATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) FROM emp;

在所有的开发之中，如果是日期的操作，建议使用以上的函数，因为这些函数可以避免闰年的问题。

转换函数

现在已经接触到了Oracle数据库之中的三种数据：数字（NUMBER）、字符串（VARCHAR2）、日期（DATE），转换函数的主要功能是完成这几种数据间的互相转换的操作，一共有三种转换函数：

TO_CHAR（字符串|列，格式字符串）：将日期或者是数字变为字符串显示；
TO_DATE（字符串，格式字符串）：将字符串变为DATE数据显示；
TO_NUMBER（字符串）：将字符串变为数字显示；

在之前查询过当前的系统日期时间：

SELECT SYSDATE from dual;

这个时候是按照“日-月-年”的格式显示，很明显这种显示格式不符合于正常的思路，正常是“年-月-日”，所以这种情况下可以使用TO_CHARO函数，但是使用此函数的话需要一些格式字符串：年（yyy），月（mm），日(dd)

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd')FROM dual;

SELECT
  TO_CHAR( SYSDATE, 'yyyy-mm-dd' ) ,
  TO_CHAR( SYSDATE, 'yyyy' ) year,
  TO_CHAR( SYSDATE, 'mm' ) mounth,
  TO_CHAR( SYSDATE, 'dd' ) day
FROM
  dual;

但是这个时候的显示数据之中可以发现会存在前导0，如果要想消除掉这个0的话，可以加入一个“fm”。

SELECT
  TO_CHAR( SYSDATE, 'fmyyyy-mm-dd' ) ,
  TO_CHAR( SYSDATE, 'yyyy' ) year,
  TO_CHAR( SYSDATE, 'fmmm' ) mounth,
  TO_CHAR( SYSDATE, 'fmdd' ) day
FROM
  dual;

正常人都加0，所以这个标记知道就行了，可是在oracle之中，DATE里面是包含了时间的，但是之前的代码没有显示出时间，要想显示时间则需要增加标记：

SELECT
  TO_CHAR( SYSDATE, 'yyyy-mm-dd hh:mi:ss' ) day
FROM
  dual;
发现一个问题
SELECT
  TO_CHAR( SYSDATE, 'yyyy-mm-dd hh:mm:ss' ) day
FROM
  dual;
下面这个比上面少了45分钟

一定要注意，使用TO_CHAR）函数之后，所有的内容都是字符串，不再是之前的DATE型数据，TO_CHAR）函数也可以用于数字的格式化上，这个时候每一个’9’.表示一位数字的概念，而不是数字9的概念。

SELECT TO_CHAR(89078907890,'L999,999,999,999,999') from dual;

其中的字母“L”，表示的是“Locale”的含义，即：当前的所在的语言环境下的货币符号。

TO_DATEO函数

此函数的主要功能是将一个字符串变为DATE型数据。

SELECT TO_DATE('1989:09-12','yyyy-mm-dd')FROM dual;

一般此函数在更新数据库的时候使用较多；TO_NUMBERO函数：基本不用TO NUMBERO函数一看就知道是将字符串变数字的：

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

但是在Oracle 之中真的很智能，所以以上的功能不使用TO_NUMBERO也可以完成：        

SELECT '1'+'2' FROM dual;

所以现在的TO_NUMBER）函数基本上已经是不考虑了，重点的函数在TO_CHARO上，其次是TO_DATE;

通用函数

主要是有两个：NVL()、DECODE()，这两个函数算是Oracle自己的特色函数了；

NVL()函数，处理null范例：要求查询出每个雇员的全部年薪

SELECT ENAME,sal,COMM,(SAL+COMM)*12 from emp;
SELECT ENAME,sal,COMM,((sal*12) +NVL((COMM), 0)*12)as gz from emp;

DECODE()函数：多数值判斯;DECODE()函数非常类似于程序中的if else语句，唯一不同的是DECODE()函数判断的是数值，而不是逻错条件了。

例如，现在要求显示全部雇员的职位，但是这些职位要求替换为中文显示：

CLERK：办事员；

SALESMAN：销售；

MANAGER：经理；

ANALYST：分析员；

PRESIDENT：总载：

这种判断管定是逐行进行判断，所以这个时候就必须采用DECODEO，而此函数的语法如下：

DECODE（数值|列，判断值1，显示值1，判断值2，显示值2，判断值3，显示值3.…）

SELECT EMPNO,ENAME,JOB,
DECODE(job,'CLERK','办事员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁')FROM EMP;

欢迎批评指正！！！