oracle函数

 

单行函数

                 Function  name(column | expression,[arg0,arg1,arg2….])

 

 

单行函数的分类：

       字符函数：接受字符串的输入并返回字符串的值

       数值函数：接受数值的输入并返回数值

       日期函数：对日期数据进行操作

       转换函数：从一种数据类型转换成另一种类型

       通用函数：NVL,DECODE

 

 

字符函数：专门处理字符的，例如可以将大写字符改为小写，还可以求出字符的长度

UPPER        ------ 把字符串转换成大写

LOWER       ------ 把字符串转换成小写

例子：

       select UPPER(‘aaa’)  from DUAL

       如：我们要查询名字为smith的员工，不区分大小写

       select * from emp  where  LOWER(ename)=LOWER(‘SMITH’)

      

       INITCAP              -------  将单词的第一个字符转换成大写，其余的变成小写

       例：select  INITCAP(HELLO WORLD)  from DUAL  返回Hello World

       CONCAT      -------  字符串连接函数

       例：select  CONCAT (HELLO,WORLD)  from DUAL  返回HELLOWORLD

       这种可以连接字符串，但是肯定不如“||”好使

       substr()   字符串截取 ，三个参数，第一个为被截取的字符串，第二个指定开始截取的位置，第三个位截取的长度

       length()   求字符串长度

       replace()  字符串替换 

       例：select substr('hello',1,3),length('hello'),replace('hello','l','x')  from DUAL

      

       数值函数：

      ROUND（）      四舍五入

       TRUNC（）        截取小数位

       MOD（）            取余

       ROUND 示例：

取整 ：select round(’19.567’)  from dual

       保留两位小数：select round(’19.567’,2)  from dual

       对整数四舍五入：select round(’193478.567’,-2)  from dual 返回193500

      

       TRUNC：直接取整，不进行四舍五入

              select trunc(’19.567’)  from dual  结果为19

 

 

       MOD ：两个参数，第一个为被除数，第二个为除数，结果为余数

       例：MOD（10,3）

      

       日期函数：

在oracle存在很多日期函数，可以对日期进行操作，比如说加减日期等，但是在加减的时候存在一些规律

 日期+数字 = 日期 

 日期-数字  = 日期 

 日期-日期= 数字（天数）

例：求当前日期，用sysdate

 

select sysdate  from dual

      

例：现在要显示10部门员工入职的星期数

 

select ename  ,round((sysdate-hiredate)/7)  from emp;

 

常用的日期函数包括：

       MONTHS_BETWEEN                    求出给出日期范围的月数

ADD_ MONTHS                                      在日期上加上指定的月数，求出之后的日期

       NEXT_DAY                                             下一个星期几是什么日期

       LAST_DAY                                             求出给定日期当月最后一天日期

      

 

              MONTHS_BETWEEN 例：

                     select empno,ename,MONTHS_BETWEEN(hiredate,sysdate)  from emp;

             

              ADD_ MONTHS  例：

                     selectADD_ MONTHS(sysdate,4)  from dual;

             

              NEXT_DAY 例

                     selectNEXT_DAY (sysdate,1)  from dual; // 第二个参数为1-7的数字，1代表星期日，以此类推。

 

             

              LAST_DAY 例

                    

                     selectLAST_DAY (sysdate)  from dual;

             

             

 转换函数

           TO_CHAR       转换成字符串

           TO_NUMBER   转换成数字

           TO_DATE        转换成日期

           比如说现在需要拆分日期中的年月日，分别求出年月日得字符，拆分的时候必须使用通配符：

l  年：yyyy 

l  月：MM

l  日：DD

TO_CHAR示例：

      select ename,to_char(hiredate,'yyyy')  year,

                            to_char(hiredate,'mm')  month,

                            to_char(hiredate,'dd')  day

from emp;

比如打印中国喜欢的日期格式

select ename,to_char(hiredate,'yyyy-mm-dd')  hiredate   from emp;

在显示中如果出现的月份带有前导，可以用fm去掉前导，比如05月，那个0就是月份的前导

select ename,to_char(hiredate,'fmyyyy-mm-dd')  hiredate   from emp;

TO_CHAR既可以格式化日期，还可以格式数字

           中国人看到太长的数字喜欢加上‘，’进行分割，可以用to_char实现，9表示一位数字

比如：

      select to_char(sal,'99,999')  from emp;

如果希望数字可以明确的表示区域，可以使用以下两种符号：

      $:表示美元

      L：代表local的缩写，以本地语言为准

select to_char(sal,'$99,999')  from emp;

TO_NUMBER示例：

比如把字符串变成数字进行相加

      select  to_number(‘123’)+to_number(‘234’)  from dual

 

TO_DATE  示例，只要用于把字符串转换成日期

           如：

           select to_date(‘2009-10-10’,’yyyy-mm-dd’)  from dual;

通用函数：

              NVL :如果值为空，可以把一个null指定成一个固定值

                  如:select nvl(null,0)  from dual

           如果我们要查询员工的奖金

           select ename,nvl(comm,0)  from emp;

DECODE有点跟SQL中的case语句作用相似

            语法：

           decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

         要求，查询员工表的职位，以中文形式显示

         CLERK ：（办事员）

         SALESMAN：（财务）

         MANAGER ：（经理）

         ANALYST：（分析员）

组函数：

              常见的组函数有如下几个：

  COUNT(): 求记录条数

  MAX()：求最大值

  MIN()：求最小值

  AVG()：求平均值

  SUM()：求和

分析函数：FUNCTION_NAME(<argument>,<argument>...) OVER  (<Partition-Clause><Order-by-Clause><Windowing Clause>)

例:
sum(sal) over (partition by deptno order by ename) new_alias
sum就是函数名
(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)
over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数
partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区
order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的.