精通Oracle核心技术和项目实战之常用函数ppt课件

1、第9章 常用函数Oracle数据库提供了大量的系统函数。他们可以对数据库的值、对象和设置进展操作并前往相关信息。此外，Oracle数据库还提供了一些处置列值的聚合函数，对表中的数据进展统计分析。经过这些函数，可以进一步提供快速、简单的方法以完成特定的任务。在本章中，我们将重点学习聚合函数、数学函数、字符串函数、数据类型转换函数、日期函数等。.9.1 聚合函数在访问数据库时，经常需求对表中的某列数据进展统计分析，如求最大值、最小值、平均值等。一切针对这些表中一列或者多列数据的分析就叫做聚合分析。Oracle提供了一些聚合函数，如表所示。函数功能avg()求平均值，计算并返回表达式的平均值coun

2、t()统计数目，返回一个集合中的项数max()求最大值，返回表达式中的最大项min()求最小值，返回表达式中的最小项sum()求和，计算并返回表达式各项的和stddev()求标准偏差stddev_pop()求总体标准偏差.9.1 聚合函数在select语句中运用聚合函数如下图。.9.1.1 计数函数count()count()函数用来计算表中行的总数，计算内容由select语句执行。运用count()函数时，必需指定一个列的称号或者运用星号。【例如9-1】运用count()函数获取customersnew数据表中记录数和customersnew表中status列的记录数。【例如9-2】运用co

3、unt()函数获取customersnew数据表中status列为gold的记录数。.9.1.2 求和函数sum()sum()函数用于对数据求和，它分组计算指定列的和。假设不运用分组，那么该函数默许把整个表作为一组。sum()只能作用于数值类型的数据。其语法构造如以下图所示。【例如9-3】查询orders数据表中order_total总和。【例如9-4】orders数据表中，存储的是顾客的订单编号，每个顾客存在多个订单的情况。查询orders数据表中各个顾客的order_total总和。.9.1.3 均值函数avg()avg()函数用于计算结果集中一切数据的算术平均值，也可以求某组数据的平均值

4、。该函数作用于数值类型数据，前往数据类型值，其语法构造如下图。【例如9-5】查询orders数据表中order_total字段的一切数据的算数平均值。【例如9-6】查询orders数据表中order_total大于订单平均值的一切订单信息。.9.1.4 最大值max()和最小值min()利用max()函数和min()函数，可以获得结果集中记录数据的最大值和最小值。与前面引见的不同，这里的数据可以是数值、字符串或日期数据类型。其中，字符串是根据ASCII码的顺序来获取最大值和最小值的，根本的语法构造如下图。.9.1.4 最大值max()和最小值min()【例如9-7】假设想知道orders数据表

5、中最大order_total。【例如9-8】orders数据表中最小order_total。【例如9-9】假设想求得orders数据表中最大order_total，还可以换一种方式，经过order by语句按照order_total进展排序，然后运用rownum关键字，只选取结果集中的第1条记录。.9.2 数学函数数学函数操作数字数值，执行数学和算术运算。本小节主要讲解数值型的定义、数值型函数。.9.2.1 Oracle中的数值型Oracle中数值型是用来存放数的，包括整数和实数。在Oracle中一致运用number表示数值型。number (p,s)是一种格式化的数字，其中p是精度，s是刻度

6、范围。精度是数值中一切有效数字的个数，而刻度范围是小数点右边数字位的个数。精度和刻度范围都是可选的，但假设指定了刻度范围，那么也必需指定精度。.9.2.1 Oracle中的数值型Oracle提供了丰富的内置函数对数据进展处置，一切函数都有数值型参数并前往数值型值。需求留意的是一切三角函数的操作数和值都是弧度而不是角度，如下表所示。.9.2.1 Oracle中的数值型函数名称描述abs(n)用于返回n的绝对值acos(n)反余弦函数，用于返回-11之间的数，n表示弧度asin(n)反正弦函数，用于返回-11之间的数，n表示弧度atan(n )反正切函数.用于返回n的反正切值，n表示弧度ceil(

7、n)用于返回大于或等于n的最小整数cos(n )用于返回n的余弦值,n为弧度cosh(n)用于返回n的双曲余弦值，n为数字exp(n)用于返回e的n次幂，e=2.71828183floor(n )用于返回小于等于n的最大整数ln（n）用于返回n的自然对数，n必须大于0log(n1,n2)用于返回以n1为底n2的对数mod(n1,n2)用于返回n1除以n2的余数power(n1,n2 )用于返回n1的n2次方round(n1,n2)用于返回舍入小数点右边n2位的n1的值，n2的默认值为0，这会返回小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数sign(n)若n为负

8、数，则返回-1。若n为正数，则返回1，若n=0，则返回0sin(n )用于返回n的正弦值，n为弧度sinh( n)用于返回n的双曲正弦值，n为弧度sqrt(n )度于返回n的平方根tan(n )用于返回n的正切值，n为弧度tanh( n)用于返回n的双曲正切值，n为弧度trunc(n1,n2)用于返回截尾到n2位小数的n1的值，n2默认设置为0，当n2为馱认设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上.9.2.2 绝对值、取余、判别数值正负本小节主要讲解数值类型函数中的取绝对值函数、取余函数、判别正负函数。1.前往数字的绝对值abs()函数绝对值表示在数轴上，一个数的

9、点到原点的间隔。非负数的绝对值是它本身，非正数的绝对值是它的相反数。Oracle提供abs()函数来计算数字的绝对值。该函数包含一个参数，通常参数类型为数字类型，或可以隐式转换成数字类型。【例如9-10】利用abs()前往数值的绝对值，要求测试正数、负数，以及自动转换成数字的字符串在绝对值函数中的运用。.9.2.2 绝对值、取余、判别数值正负2.取余操作mod()函数取余函数也叫取模，该函数表示一个除数除一个值并给出余数。该函数要求两个参数被除数和除数，参数类型是数值型，或者可以隐式转换为数值型的其他类型，其语法方式如下图。【例如9-11】演示mod()函数的运用方法。.9.2.2 绝对值、取

10、余、判别数值正负3.前往数字的正负性sign()函数sign()函数的功能是前往参数的符号正或负。当参数大于0时，sign()函数前往1；当参数小于0时，sign()函数前往-1；当参数等于0时，sign()函数将前往0。【例如9-12】演示sign()函数的运用。.9.2.3 近似值函数、截取、取整函数在Oracle中，近似值函数运用round()，ceil()函数、floor()函数用于取整。1.前往数字的“四舍五入值round()函数四舍五入表示在取小数近似数的时候，假设尾数的最高位数字是4或者比4小,就把尾数去掉。假设尾数的最高位数是5或者比5大,就把尾数舍去并且在它的前一位进“1，该

11、函数的运用语法如下图。【例如9-13】当小数位数为正数时，表示准确到小数点之后的位数。.9.2.3 近似值函数、截取、取整函数2.截取数字trunc(n1,n2)函数trunc(n1,n2)函数用于将数值n1根据n2进展截取。其任务机制非常类似于round()函数，但是trunc()函数并不进展四舍五入操作，而是直接舍去。该函数的调用语法如下图。【例如9-14】利用trunc()函数截取部分数字。当小数位数大于0时，表示截取至原数值的小数点之后的位数。.9.2.3 近似值函数、截取、取整函数2.向上取整ceil()函数向上取整表示将前往大于等于数值型参数的最小整数。Oracle中ceil()是

12、向上取整函数，该参数要求是数值类型或者可以隐式转换为数值的类型，可以是非整数。【例如9-15】演示ceil()函数的运用，将数据表orders中的order_total向上取整。.9.2.3 近似值函数、截取、取整函数3.向下取整floor()函数向下取整表示前往小于等于数值型参数的最大整数。与ceil()函数相反，floor()函数用于前往小于等于参数值的最大整数。【例如9-16】演示floor()函数的运用。.9.2.4 平方根、乘方运算函数在Oracle数据库中，sqrt()函数用于前往数字的平方根；power()函数用于计算一个值与给定正指数的乘方。1.前往数字的平方根sqrt()函数

13、从平方根的意义我们知道，该函数的参数不能小于0。【例如9-17】利用sqrt()函数前往参数的平方根。.9.2.4 平方根、乘方运算函数2.乘方运算power()函数该函数有两个参数，第一个参数为乘方运算的底数，第二个参数为乘方运算的指数。【例如9-18】利用power()函数进展乘方运算。.9.2.5 格式化数值to_char()函数to_char()函数是将数值型数据转换为字符串。第一个参数是待格式化的值，第二个是一个定义输出格式的模板。可以将数值进展格式化，并前往格式化后的字符串。其调用格式如下图。.9.2.5 格式化数值to_char()函数1. 格式字符“0“0代表一个数字位。当原数

14、值没有数字位与之匹配时，强迫添加0。【例如9-19】Oracle中格式字符“0的运用，将数据表order_items中unit_price用格式字符“0显示。.9.2.5 格式化数值to_char()函数2.格式字符“99，代表一个数字位。当原数值中的整数部分没有数字位与之匹配时，不填充任何字符。【例如9-20】Oracle中格式字符“9的运用，将数据表order_items中unit_price用格式字符“9显示。.9.2.5 格式化数值to_char()函数3.格式字符“,逗号,，分组符号。常见的运用为千位分隔符。【例如9-21】Oracle中格式字符“，的运用，将数据表order_ite

15、ms中unit_price用格式字符“，显示。.9.2.5 格式化数值to_char()函数4.格式字符“FMFormat Mask而对于格式字符“9来说，小数部分没有对应位时，依然运用“0进展填充。这违背了运用习惯。利用格式字符“FM可以处理这一问题。【例如9-22】Oracle中格式字符“FM的运用，将数据表order_items中unit_price用格式字符“FM显示。.9.2.5 格式化数值to_char()函数6. 格式字符“L美圆符号表示货币，但是货币标识往往具有本地化的颜色。例如，在我国，通常运用“￥而非“$来表示货币。在to_char()函数中，运用“L来指定本地化的货币标识

16、。【例如9-23】Oracle中格式字符“L的运用，将数据表order_items中unit_price用格式字符“L显示。.9.2.5 格式化数值to_char()函数7. 格式字符“C通常，货币标识只能表示能否为货币，而货币种类无法进展区分。在to_char()函数中，还提供了另外，一个格式字符“C。该字符将前往与数据库系统环境相关的货币种类符号。【例如9-24】Oracle中格式字符“C的运用，将数据表order_items中unit_price用格式字符“C显示。.9.2.5 格式化数值to_char()函数9.字符串与数值的比较在字符串与数值的比较运算中，字符型首先被转换为数值型，然

17、后进展比较。【例如9-25】给出字符串与数值的比较运算。.9.3 字符串函数字符型是用来存放字符和字符串的。字符串函数对字符类型的数据进展相关的处置，它们可以接纳字符或字符串类型的参数，前往字符串或数字类型的处置结果。用户可以在select语句的select和where子句以及表达式中运用字符串函数。本节详细讲解字符类型定义及字符串函数的运用。.9.3.1 字符型简介字符类型，即字母、数据、标点符号、数字和空格的混合方式，在Oracle中的字符型有varchar2(n)、nvarchar2(n)、char(n)、nchar2(n)、long等类型。char(n)，用于指定变量或列的数据类型为固

18、定长度的字符串。其中，n代表字符串的长度。varchar(n)，用于存储可变长度的字符串。varchar2(n)，同样是可变长度的字符串类型，普通在Oracle中，尽量运用varchar2(n)Oracle中的字符类型函数可以去掉字符串空格，截取子串，搜索字符串，衔接字符串，下面将详细讲解。.9.3.2 删除空格在很多时候对SQL语句进展处置时，需求将字符串中的空格去除。函数ltrim()去除字符串前面的一切空格；函数rtrim()去除一个字符串尾部的一切空格；函数trim()去除一个字符串两侧的一切空格。.9.3.2 删除空格1.ltrim()函数ltrim ()函数把字符串头部的空格去掉，

19、其语法如下图。【例如9-26】运用函数ltrim()去除customersnew表中cust_first_name字段前面的空格，详细实现如下图。.9.3.3 求字符串长度函数利用length()函数可以得到指定字符串的长度，前往数值型。假设字符串为null，那么将前往null值。【例如9-27】利用length()函数前往字符长度，查询customersnew表中cust_first_name的长度，以及去掉两侧空格之后的长度。.9.3.4 字符转换函数在Oracle中，提供了一系列函数以完成字符与ASCII码转换、字符与数值之间的转换、大小写字母转换等转换任务。1ASCII()函数ASCI

20、I()函数前往字符表达式最第一个字符的ASCII码值，ASCII函数语法如下图。【例如9-30】运用ASCII()函数求customersnew表中cust_first_name字段的ASCII码值。.9.3.4 字符转换函数2chr(n)函数该函数是用于求n对应的ASCII字符，其中n是一个数字。【例如9-31】chr(n)函数例如。.9.3.4 字符转换函数3lower()函数lower()函数把字符串全部转换为小写，其语法如下图。【例如9-32】运用lower()函数将customersnew表中cust_first_name字段转换为小写。.9.3.4 字符转换函数4upper()函数

21、upper()函数把字符串全部转换为大写，其语法如下图。【例如9-33】运用upper()函数将customersnew表中nls_language字段转换为大写。.9.3.4 字符转换函数5单词首字符大写initcap()函数initcap()函数用于将字符串中的每个单词首字符大写、其它字符小写的方式。单词由空格、控制字符、标点符号限制。【例如9-34】运用initcap()函数将customersnew表中nls_language字段首字母大写。.9.3.5 衔接字符串衔接字符串可以运用concat()函数，也可以运用操作符“|。1.concat()函数concat()函数把提供的两个参数

22、衔接起来，前往衔接后的字符串。【例如9-35】运用concat()函数将customersnew表中顾客的姓名cust_first_name和姓cust_last_name衔接起来。.9.3.5 衔接字符串2.|运算符【例如9-36】运用|运算符将customersnew表中顾客的姓名cust_first_name和姓cust_last_name衔接起来。.9.3.6 检索字符串、取子串函数在很多实践运用中，我们需求获得字符串的某一部分内容来完成查询。在SQL中，经过取子串函数完成这一目的。1.获得字符串出现的位置instr()函数该函数可以查看在指定字符串中能否存在另一个字符串，语法构造如下

23、图。【例如9-37】利用instr()函数获得categories数据表的category_name字段中software出现的位置，如下图。.9.3.5 衔接字符串2.截取字符串substr()函数运用substr()函数可以对提供的参数进展截取，前往截取后的字符串，语法构造如下图。【例如9-38】利用instr()函数截取categories数据表的category_name字段中前三位。.9.3.5 衔接字符串3.交换字符串replace()replace()函数可以用指定的字符串替代需求交换的字符串，语法构造如下图。【例如9-39】利用replace()函数截取categories数据

24、表的category_name字段中software交换为“软件。.9.3.5 衔接字符串4.向左补全字符串lpad()函数lpad()函数和rpad()函数用于对字符串进展填充，详细可以在字符串的左边或右边衔接空格或其他字符。lpad()函数用于向左补全字符串，语法如下图。【例如9-40】categories数据表中category_id原来是2位，如今希望被格式化为4位数字，缺乏部分运用“0进展填充。如category_id为11，应被格式化为“0011，那么此时可以利用lpad()函数。.9.3.5 衔接字符串5.向右补全字符串rpad()函数该函数与lpad()函数的运用方法类似。该函

25、数的功能是在字符串c1的右边用字符串c2填充，直到整个字符串长度为n。【例如9-41】当原始字符串长度大于预期长度时，rplad()函数也是自左端截取字符串。.9.4 日期函数所谓日期处置函数就是用来操作与日期、时间相关的函数。利用日期函数可以得到当前的系统时间，以及计算日期之间的秒、分钟、小时、天、月、年等。.9.4.1 获取日期和时间的函数Oracle中的日期类型用date表示，它可以用来放置日期和时间。date类型实践包含了以下信息：Century：世纪信息；Year：年份信息；Month：月份信息；Day：天数信息；Hour：小时信息；Minute：分钟信息；Second：秒数信息。O

26、racle中也存在timestamp数据类型，它包括了一切date数据类型的年月日时分秒的信息，而且包括了小数秒的信息。.9.4.1 获取日期和时间的函数1 获取系统日期函数sysdate该函数没有参数，前往当前数据库系统日期，是很常用的函数。【例如9-42】运用sysdate函数前往当前系统日期。.9.4.1 获取日期和时间的函数2 获取系统时间函数systimestampsystimestamp函数与sysdate函数类似，也没有参数。该函数前往的是系统时间，时间准确到微秒。【例如9-43】运用systimestamp函数前往当前系统时间。.9.4.1 获取日期和时间的函数3 前往会话所在

27、时区当前日期函数current_date()current_date()函数得到会话时区下的当前日期。【例如9-44】结合当前时区来查看current_date函数的运用。.9.4.1 获取日期和时间的函数4前往日期的某个域extract()函数该函数可以从指定的时间当中提取到指定的日期部分，如日期中的年、月、日、时、分、秒等，其运用语法如下图。【例如9-45】结合数据表orders，从order_date中分解出月份。【例如9-46】结合数据表orders，从order_date中分解出小时信息。.9.4.1 获取日期和时间的函数extact()函数的各种域的列表如表所示。域代码说明year

28、获得年份month获得月份day获得天数hour获得小时数minute获得分钟数second获得秒数timezone_hour获得当前时区的小时数.9.4.1 获取日期和时间的函数5截取日期trunc()函数trunc()函数可以截取日期，运用方法与截取数字非常类似，其运用语法如下图。【例如9-47】结合数据表orders，将order_date分别截取到天和月。.9.4.1 获取日期和时间的函数trunc()函数的作用为将日期截取到某一种格式，截取格式分类如表所示。分类可用格式代码世纪CC、SSC年SYYY, YYYY, YEAR, SYEAR, YYY, YY, or YISO年（一年53

29、周，第54周的星期一为新年的开始）IYYY, IYY, IY, or I季度Q月份MONTH, MON, MM, or RM本周内与当年第一天具有相同周内天数的日期WW本周内与ISO年第一天具有相同周内天数的日期IW本周内与当月第一天具有相同周内天数的日期W天DDD, DD, or J本周内的第一天DAY, DY, or D小时HH, HH12, HH24分钟MI.9.4.2 日期和时间差的函数在实践中，我们往往想知道在特定的两个日期或者时间内有多少天数、小时等，或者从某个时间开场，经过某个时间段，系统需求对某个数据进展操作。这时需求运用一些日期和时间差的函数。.9.4.2 日期和时间差的函数

30、1.前往特定日期所在月的最后一天last_day()函数last_day()函数用于前往给定日期所在月份的最后一天，它有一个参数。【例如9-48】结合数据表orders，查询order_date对应的月份的最后一天。.9.4.2 日期和时间差的函数2.月份添加add_months()函数add_months()函数可以实现对日期和月份的计算，它可以在给定的日期上添加月份数，然后重新计算日期。【例如9-49】将数据表orders中order_date日期推后一个月。.9.4.2 日期和时间差的函数3.两个日期之间的月份months_between()函数months_between()函数有两个参数，可以获取两个参数之间的月份数，其语法构造如下图。【例如9-50】获取系统当前日期与数据表orders中order_date日期的月份差。.9.4.2 日期和时间差的函数4. 特定日期之后的一周之内的日期next_day()函数该函数前往指定日期