Oracle数据库-函数总结

最新推荐文章于 2022-03-31 13:47:52 发布
最新推荐文章于 2022-03-31 13:47:52 发布
阅读量733 收藏 3
点赞数 1
分类专栏： 数据库 文章标签： Oracle函数 Oracle数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/duguyuyun12345/article/details/88547258
版权

Oracle有很多内置函数：单行函数和集合函数

单行函数是指当查询表或视图时每行都能返回一个结果，可用于SELECT，WHERE，ORDER BY 等子句中，而集合函数是作用在多行记录上返回一个结果，可用于带GROUP BY或HAVING子句的查询中，单行函数数量比较多

注：dual是一个虚拟表，用来构成select的语法规则，Oracle里保证dual里面永远只有一条数据，所以可以使用dual来测试这些函数。部分操作：select user from dual;查看当前用户

                       select "TO_CHAR"(sysdate,'yyyy-mm-dd hh:MM:ss')from dual;–获得当前系统时间

                       select SYS_CONTEXT('USERENV','TERMINAL') from dual;     -获得主机名

                       select SYS_CONTEXT('USERENV','language') from dual;      -获得当前local

                       select dbms_random.random from dual;                  -获得一个随机数

1.数值型函数：

          ABS(n)函数：用于返回绝对值，输入参数为数值型，如果参数可以隐式转换为数值类型，也可以

          SELECT ABS(100),ABS(-100),ABS('100') FROM DUAL;

         MOD(n2,n1)函数，该函数表示返回n2除以n1的余数，参数为任意数值或可以隐式转换为数值的类型，如果n1为0，那么函数返回n2:

          SELECT MOD(5,2),MOD(8/3,5),MOD('10',5),MOD(-10,6),MOD(1,0) FROM DUAL;

         SING(n)函数，返回参数n的符号，正数返回1，0返回0，负数返回-1，但如果n为binary_float或者binary_double类型是，n>=0或者n=NaN函数会返回1：

         SELECT SIGN('9'),SIGN(-9),SIGN(0.00),SIGN(-2*'9') FROM DUAL;

         ceil()取整函数（ceil向上取整，floor向下取整）

         SELECT ceil(66.6) num1,floor(66.6) num2 from dual;

         返回固定小数位数（round(),四舍五入，trunc:直接截断）

         SELECT round(66,666,2) num1,trunc(66.666,2) from dual;

2.字符函数  

        获取字符串长度：

        LENGTH函数，该函数可以得到指定字符串的长度，返回类型是数字：

        SELECT "LENGTH"('ABCDE神FGHI') FROM DUAL;

       字符串连接：

       concat(char1,char2)函数，该函数连接两个参数并返回（只支持两个）。效果和连接符||相似

       SELECT "CONCAT"('战神', '刑天'),'战神'||'刑天' FROM DUAL;

       连接符||

   SELECT '张三'||'name' 姓名 from DUAL;

      字符串搜索函数：

      INSTR函数，该函数可以让我们在指定字符串中搜索是否存在另一个字符串

      INSTR|INSTRB|INSTRC|INSTR2|INSTR4 string,substring,position,occurrence

            INSTR:字符为单位 INSTRB字节为单位 INSTRC 以unicode字符为单位

            string：待搜索的字符串，substring:要搜索的字符串，position：搜索开始位置，默认为1，表示字符串左边第一个位置，负数从右边开始

            occurrence：substring第几次出现，默认为1；

      SELECT INSTR('THIS IS A 测试','测') FROM DUAL;

     替换字符串函数：

     语法结构：REPLACE(char,search_string[,replacement_string];

     SELECT "REPLACE"('THIS IS A TEST','TES','RESUL') FROM DUAL;

3.日期型函数

    

       Oracle中的日期类型只有date和TIMESTAMP,TIMESTAMP是比date更精确的类型。日期时间的函数用于处理日期时间类型的数据，Oracle以7位数字格式来存放日期数据，包括世纪，年，月，日，小时，分钟，秒，并且日期格式为DD-MON-YY。

       在Oracle中准确是从星期日开始到星期六结束，时间差以天数为单位

3.1 系统日期，时间函数

      SYSDATE函数，该函数没有参数，可以得到系统的当前日期

      SELECT SYSDATE FROM DUAL;

      SYSTIMESTAMP函数，该函数没有参数，返回系统时间，返回类型为带时区的TIMESTAMP类型

      SELECT SYSTIMESTAMP FROM DUAL;

      得到数据库时区函数：

      SELECT DBTIMEZONE FROM DUAL;

3.2 date操作

      与date操作的两个转换函数：

           to_date(char[fmt[,'nls_param’]]),to_char(date[,fmt,[,nls_param]]) 

           to_date(char[fmt[,'nls_param’]]):将字符类型按一定格式转换为日期类型：

       to_date(‘2019-01-23’，‘yyyy-mm-dd’):前者为字符串后者为转化格式，注意对应

       select to_date('2017-12-11','yyyy-mm-dd') from dual

      to_char(date,’yyyy-mm-dd hh24:mi:ss):将日期按一定格式转换为字符类型

       select to_char(sysdate,'yyyy:mm:dd hh24:mi:ss ') from dual

      返回多种日期格式：to_char(sysdate,’’);

select to_char(sysdate,'yyyy') 年,   to_char(sysdate,'mm')月,   to_char(sysdate,'DD') 日,       

          to_char(sysdate,'HH24') 时,  to_char(sysdate,'MI')分,     to_char(sysdate,'SS')秒,       

         to_char(sysdate,'DAY') 天,    to_char(sysdate,'Q') 第几季度,    to_char(sysdate,'W') 第几周 from dual

4.转换函数

       转换函数可以完成不同数据类型之间的转换，是平常使用比较多的函数类型之一  

      自动类型转换:select 1+'1' from dual; 

      数字转换成字符串：select 12,to_char(12) from dual;

                     select 123,to_char(1234,'999') from dual;

      将字符串转换为数字：select '123',to_number('3.14') from dual；

      CAST(expr as type_name)函数，该函数是数据类型转换，可以把expr参数转换成type_name类型，基本上用于数字与字符之间以及字符与日期类型之间的转换。

      SELECT CAST('123'AS INTEGER) AS VHR,CAST(123 AS VARCHAR2(8)) AS NUM,CAST(SYSDATE AS VARCHAR2(12))
AS DT FROM  DUAL;

     字符串转换成date类型：select add_months('1-7月-2017',2) from dual;

5.NULL函数

    返回表达式为NULL的函数

    COALESCE(expr)函数。返回列表中第一个不为null的表达式，如果都为null，返回null

         SELECT COALESCE(NULL,9-9,NULL) FROM DUAL

    排除指定条件函数

    LNNVL(condition)函数，该函数可以得到除了condition要求条件之外的数据，包括null的条件，通常用于WHERE条件中

         SELECT * FROM PRODUCTINFO WHERE LNNVL(QUANTITY>=70)

    替换NULL值函数

     NVL(expr1,expr2)函数，替换NULL值，如果expr1为NULL值，则返回expr2的值，否则返回expr1的值。要求两个参数类型一致，至少相互之间进行隐式转换。

         SELECT PRODUCTNAME,NVL(QUANTITY,0),CATEGORY FROM PRODUCTINFO;

    NVL2(expr1,expr2,expr3)函数，该函数通NVL类似，不同的是当expr1为NULL时，返回expr3的值，当expr1不为空时，返回expr2的值

6.集合函数

集合函数经常配合GROUP BY或HAVING子句使用，该类型函数中除了COUNT函数都会忽略列值为NULL的数据

6.1 求平均值函数

AVG([distinct[all] expr]函数，该函数可求取指定列的平均值，表示某组的平均值，返回数值类型，参数含义：

       distinct:去除重复的值  all:表示所有的值，包括重复的值，默认值  expr:表达式，只能是数值类型

       SELECT AVG(ALL age+20) FROM student;

       使用该函数式，WHERE条件子句中可以使用条件，与group by子句一起使用

       SELECT AVG(ALL PRODUCTPRICE) FROM PRODUVTINFO GROUP BY CATEGORY;

6.2 求记录数量函数

COUNT(*|[distinct][all]expr)函数，该函数用来计算记录的数量或某列的个数，函数必须指定列名，或使用*，参数含义：

       *：表示计算所有记录 distinct：表示去除重复记录 all:代表所有的，默认 expr：要计算的值，通常是表的列

       SELECT COUNT(*) FROM PRODUCTINFO;

6.3 返回最大，最小值函数

MAX([distinct|all]expr)函数，返回指定列的 最大值

MIN([distinct|all]expr)函数，返回指定列的最小值

SELECT * FROM PRODUCTINFO

WHERE PRODUCTPRICE = (SELECT MAX(PRODUCTPRICE) FROM PRODUCTINFO)

6.4 求和函数

SUM([distinct|all] expr)函数，分组计算指定列的和，如果不分组，则函数默认把整个表作为一组，参数代表含义：

例：计算不同类型产品的 数量和

SELECT SUM(ALL QUANTITY),CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY;

6.5 GROUPING函数

GROUPING函数可以接受一列，返回0或者1，如果列值为空，那么GROUPING()返回1，如果列值非空返回0，GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方返回某个值时，GROUPING()非常有用。

GROUPING函数既可以与CUBE操作一起使用，也可以与ROLLUP一起使用。GROUPING函数使用一个单独的列表示，在GROUPING函数中的expr必须匹配一个GROUP BY子句中的表达式，该函数返回值0或者1

select grouping(division_id),division_id,sum(salary) from employees group by rollup(division_id)

使用CASE函数：

select case grouping(division_id) when 1 then ‘all divisions’ else division_id end as div, sum(salary)

from employee group by rollup(division_id) order by division_id;

7 条件判断函数

7.1 case when end

结构：case when 条件值then  语句when 条件值 then 语句 else 否则语句 end

示例：select case when t.result=’1’ then‘成功’ when t.result=’2’ then‘失败’ else ‘不知道’

end  result  from user;

7.2 decode函数

多条件分支判断，最后一个参数表示默认值

select decode(字段名，‘条件1’，‘值1’,’条件2’,’值2’，默认值) from 表名

select decode(identity, 'student', '学生', 'teacher', '老师', 'nnn', '不知道'，‘神秘’) as identity from person

8 其他函数

8.1 ROW_NUMBER() OVER函数

row_number() over的用法  用于排序去重操作

 

 

 

 

独孤宇云12345
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle常用基本语句_函数部分
chenzhenshagua的博客
11-04 573
递归 select * from addr_cz ad connect by nocycle ad.id= prior ad.parentid start with ad.id=273782 【注意：prior 为start开始数据的字段】 递归查询： select * from rmgz.addr r where r.grade=5 start with id=641075320--401517548 connect by ...
Oracle常用函数
i1103的博客
12-29 1465
一、数据准备 CREATE TABLE stu( id INT PRIMARY KEY AUTO_INCREMENT, -- AUTO_INCREMENT 设置自增序列 stuname VARCHAR(10), sex VARCHAR(2), age INT, money VARCHAR(10) ); INSERT INTO stu(stuname,sex,age,money) VALUE('张三','男',18,'-18'); INSERT INTO stu(stuname,sex,age,mon
oracle函数 localtimestamp
weixin_30462049的博客
07-02 855
【功能】：返回会话中的日期和时间 【参数】：没有参数，没有括号 【返回】：日期 【示例】select localtimestamp from dual; 返回：14-11月-08 12.35.37.453000 上午 转载于:https://www.cnblogs.com/fanweisheng/p/11119898.html...
oracle内置函数大全
lennyf的专栏
09-12 475
ORACLE内置函数大全 SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;        A         A      ZERO     SPACE--------- --------- ---------
oracle使用大全
u011533553的博客
01-27 1936
第一章：从数据库表中检索信息 1. any、all 操作符 any操作符将一个值与某个列表中的任何值进行比较，此时必须在any之前添加一个=、、=操作符 例如：select * from customers where customer_id > any (2,3,4); all操作符将一个值与某个列表中的所有值进行比较，此时必须在any之前添加一个=、、=
oracle数据库函数总结.xmind
05-10
oracle函数库大全
Oracle数据库实验 -- 函数和表达式
01-15
trim( ) 表示字符串string2去除前面|后面|前后面（leading|trailing|both）的字符string1，默认去除方式为both
Oracle数据库--存储过程、函数、触发器和包.pptx
09-22
【Oracle数据库存储过程】 ...总的来说，存储过程、函数、触发器和包都是Oracle数据库中增强应用功能和数据管理能力的重要工具。熟练掌握它们的创建、使用和管理，能够提高数据库的性能和开发效率。
oracle负数转正数函数_12、INT取整和ROUND舍入函数
weixin_39821330的博客
12-03 4412
理论概念INT函数将数字向下舍入到最接近的整数。语法INT(number)其中number是需要进行向下舍入取整的实数ROUND函数就是返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果ROUND函数的语法是：ROUND(number,num_digits)，即：Round(数值，保留的小数位数)number：需要进行四舍五入的数字。num_digits：指定的位数，按此位数...
Mysql 查询负数显示为0
weixin_43752427的博客
03-31 3740
CASEWHEN
oracle除数为0时的解决方法（decode的使用）
热门推荐
tom和cat的博客
11-28 2万+
decode(除数，0，显示0%，不为零的正常表达式）代替原来那个有可能出0除报错的表达式 解决：select decode(b,0,0,a/b*100) per from aa; （1）decode的含义 含义解释：decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 该函数的含义如下： IF 条件=值1 THEN 　　　　RETURN(...
oracle负数,oracle取交集_oracle取负数_oracle case when
weixin_28689729的博客
04-07 363
oracle取交集oracle取交集select name from test1union [all]select name from test2;使用union时，默认将对结果进行排序，union all则不进行排序操作，所以会消耗更少的资源；然而，union all将不进行去重的操作~2.交集的运算select name from test1intersectselect name from ...
Oracle数据库-成绩管理系统.docx
12-29
本文主要介绍了学生成绩管理系统的Oracle数据库应用开发综合实验，涵盖了建表、表空间、触发器、索引、试图、函数等方面的内容。 第一章 引言 1.1 编写目的 本文的编写目的是为了介绍学生成绩管理系统的Oracle...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值