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

第9章常用函数Oracle数据库提供了大量的系统函数。他们可以对数据库的值、对象和设置进行操作并返回相关信息。此外，Oracle数据库还提供了一些处理列值的聚合函数，对表中的数据进行统计分析。通过这些函数，能够进一步提供快速、简单的方法以完成特定的工作。在本章中，我们将重点学习聚合函数、数学函数、字符串函数、数据类型转换函数、日期函数等。9.1聚合函数数在访问数数据库时时，经常常需要对对表中的的某列数数据进行行统计分分析，如如求最大大值、最最小值、、平均值值等。所所有针对对这些表表中一列列或者多多列数据据的分析析就叫做做聚合分分析。Oracle提供了一一些聚合合函数，，如表所所示。函数功能avg()求平均值，计算并返回表达式的平均值count()统计数目，返回一个集合中的项数max()求最大值，返回表达式中的最大项min()求最小值，返回表达式中的最小项sum()求和，计算并返回表达式各项的和stddev()求标准偏差stddev_pop()求总体标准偏差9.1聚合函数数在select语句中使使用聚合合函数如如图所示示。9.1.1计数函数数count())count())函数用来来计算表表中行的的总数，，计算内内容由select语句执行行。使用用count())函数时，，必须指指定一个个列的名名称或者者使用星星号。【示例9-1】】使用count())函数获取取customersnew数据表中中记录数数和customersnew表中status列的记录录数。【示例9-2】】使用count())函数获取取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(()函数用于于计算结结果集中中所有数数据的算算术平均均值，也也可以求求某组数数据的平平均值。。该函数数作用于于数值类类型数据据，返回回数据类类型值，，其语法法结构如如图所示示。【示例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数据表中中最大order_total。【示例9-8】】orders数据表中中最小order_total。【示例9-9】】如果想求求得orders数据表中中最大order_total，还可以以换一种种方式，，通过orderby语句按照照order_total进行排序序，然后后使用rownum关键字，，只选取取结果集集中的第第1条记录。。9.2数学函数数数学函数数操作数数字数值值，执行行数学和和算术运运算。本本小节主主要讲解解数值型型的定义义、数值值型函数数。9.2.1Oracle中的数值值型Oracle中数值型型是用来来存放数数的，包包括整数数和实数数。在Oracle中统一使使用number表示数值值型。number(p,s)是一种格格式化的的数字，，其中p是精度，，s是刻度范范围。精精度是数数值中所所有有效效数字的的个数，，而刻度度范围是是小数点点右边数数字位的的个数。。精度和和刻度范范围都是是可选的的，但如如果指定定了刻度度范围，，那么也也必须指指定精度度。9.2..1Oracle中的数值值型Oracle提供了丰丰富的内内置函数数对数据据进行处处理，所所有函数数都有数数值型参参数并返返回数值值型值。。需要注注意的是是所有三三角函数数的操作作数和值值都是弧弧度而不不是角度度，如下表所示。。9.2..1Oracle中的数值值型函数名称描述abs(n)用于返回n的绝对值acos(n)反余弦函数，用于返回-1~1之间的数，n表示弧度asin(n)反正弦函数，用于返回-1~1之间的数，n表示弧度atan(n)反正切函数.用于返回n的反正切值，n表示弧度ceil(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为负数，则返回-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())函数绝对值表表示在数数轴上，，一个数数的点到到原点的的距离。。非负数数的绝对对值是它它本身，，非正数数的绝对对值是它它的相反反数。Oracle提供abs(()函数来计计算数字字的绝对对值。该该函数包包含一个个参数，，通常参参数类型型为数字字类型，，或可以以隐式转转换成数数字类型型。【示例9-10】利用abs(()返回数值值的绝对对值，要要求测试试正数、、负数，，以及自自动转换换成数字字的字符符串在绝绝对值函函数中的的使用。。9.2..2绝对值、、取余、、判断数数值正负负2.取余操作作——mod())函数取余函数数也叫取取模，该该函数表表示一个个除数除除一个值值并给出出余数。。该函数数要求两两个参数数——被除数和和除数，，参数类类型是数数值型，，或者可可以隐式式转换为为数值型型的其他他类型，，其语法法形式如如图所示示。【示例9-11】演示mod(()函数的使使用方法法。9.2..2绝对值、、取余、、判断数数值正负负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”，该函函数的使使用语法法如图所所示。【示例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()是向上取取整函数数，该参参数要求求是数值值类型或或者可以以隐式转转换为数数值的类类型，可可以是非非整数。。【示例9-15】演示ceil()函数的使使用，将将数据表表orders中的order_total向上取整整。9.2..3近似值函函数、截截取、取取整函数数3.向下取整整——floor()函数向下取整整表示返返回小于于等于数数值型参参数的最最大整数数。与ceil()函数相反反，floor())函数用于于返回小小于等于于参数值值的最大大整数。。【示例9-16】演示floor())函数的使使用。9.2..4平方根、、乘方运运算函数数在Oracle数据库中中，sqrt()函数用于于返回数数字的平平方根；；power())函数用于于计算一一个值与与给定正正指数的的乘方。。1.返回数字字的平方方根——sqrt(()函数从平方根根的意义义我们知知道，该该函数的的参数不不能小于于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”代表一一个数字字位。当当原数值值没有数数字位与与之匹配配时，强强制添加加0。【示例9-19】Oracle中格式字字符“0”的应用用，将数数据表order_items中unit_price用格式字字符“0”显示。。9.2..5格式化数数值——to_char(()函数2.格式字符符“9”9，代表一一个数字字位。当当原数值值中的整整数部分分没有数数字位与与之匹配配时，不不填充任任何字符符。【示例9-20】Oracle中格式字字符“9”的应用用，将数数据表order_items中unit_price用格式字字符“9”显示。。9.2..5格式化数数值——to_char(()函数3.格式字符符“,”逗号（,），分组组符号。。常见的的应用为为千位分分隔符。。【示例9-21】Oracle中格式字字符“，，”的应应用，将将数据表表order_items中unit_price用格式字字符“，，”显示示。9.2..5格式化数数值——to_char(()函数4.格式字符符“FM”——FormatMask而对于格格式字符符“9”来说，，小数部部分没有有对应位位时，仍仍然使用用“0”进行填填充。这这违背了了使用习习惯。利利用格式式字符““FM”可以解解决这一一问题。。【示例9-22】Oracle中格式字字符“FM”的应用用，将数数据表order_items中unit_price用格式字字符“FM”显示。。9.2..5格式化数数值——to_char(()函数6.格式字符符“L”美元符号号表示货货币，但但是货币币标识往往往具有有本地化化的色彩彩。例如如，在我我国，通通常使用用“￥””而非““$”来表示示货币。。在to_char()函数中，，使用““L”来指定定本地化化的货币币标识。。【示例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.字符串与与数值的的比较在字符串串与数值值的比较较运算中中，字符符型首先先被转换换为数值值型，然然后进行行比较。。【示例9-25】给出字符符串与数数值的比比较运算算。9.3字符串函函数字符型是是用来存存放字符符和字符符串的。。字符串串函数对对字符类类型的数数据进行行相关的的处理，，它们可可以接收收字符或或字符串串类型的的参数，，返回字字符串或或数字类类型的处处理结果果。用户户可以在在select语句的select和where子句以及及表达式式中使用用字符串串函数。。本节详详细讲解解字符类类型定义义及字符符串函数数的使用用。9.3..1字符型简简介字符类型型，即字字母、数数据、标标点符号号、数字字和空格格的混合合形式，，在Oracle中的字符符型有varchar2(n)、nvarchar2((n)、char(n))、nchar2((n)、long等类型。。char(n))，用于指指定变量量或列的的数据类类型为固固定长度度的字符符串。其其中，n代表字符符串的长长度。varchar(n))，用于存存储可变变长度的的字符串串。varchar2(n)，同样是是可变长长度的字字符串类类型，一一般在Oracle中，尽量量使用varchar2(n)Oracle中的字符符类型函函数可以以去掉字字符串空空格，截截取子串串，搜索索字符串串，连接接字符串串，下面面将具体体讲解。。9.3.2删除空格格在很多时时候对SQL语句进行行处理时时，需要要将字符符串中的的空格去去除。函函数ltrim())去除字符符串前面面的所有有空格；；函数rtrim())去除一个个字符串串尾部的的所有空空格；函函数trim()去除一个个字符串串两侧的的所有空空格。9.3..2删除空格格1.ltrim())函数ltrim()函数把字字符串头头部的空空格去掉掉，其语语法如图图所示。。【示例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码转换、、字符与与数值之之间的转转换、大大小写字字母转换换等转换换工作。。1．ASCII())函数ASCII())函数返回回字符表表达式最最第一个个字符的的ASCII码值，ASCII函数语法法如图所所示。【示例9-30】使用ASCII())函数求customersnew表中cust_first_name字段的ASCII码值。9.3..4字符转换换函数2．chr((n)函数该函数是是用于求求n对应的ASCII字符，其其中n是一个数数字。【示例9-31】chr(n)函数示例例。9.3..4字符转换换函数3．lower())函数lower())函数把字字符串全全部转换换为小写写，其语语法如图图所示。。【示例9-32】使用lower())函数将customersnew表中cust_first_name字段转换换为小写写。9.3..4字符转换换函数4．upper())函数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(()函数把提提供的两两个参数数连接起起来，返返回连接接后的字字符串。。【示例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()函数该函数可可以查看看在指定定字符串串中是否否存在另另一个字字符串，，语法结结构如图图所示。。【示例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数据表的的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()函数的使使用方法法类似。。该函数数的功能能是在字字符串c1的右边用用字符串串c2填充，直直到整个个字符串串长度为为n。【示例9-41】当原始字字符串长长度大于于预期长长度时，，rplad())函数也是是自左端端截取字字符串。。9.4日期函数数所谓日期期处理函函数就是是用来操操作与日日期、时时间相关关的函数数。利用用日期函函数可以以得到当当前的系系统时间间，以及及计算日日期之间间的秒、、分钟、、小时、、天、月月、年等等。9.4.1获取日期期和时间间的函数数Oracle中的日期期类型用用date表示，它它可以用用来放置置日期和和时间。。date类型实际际包含了了以下信信息：Century：世纪信信息；Year：年份信信息；Month：月份信信息；Day：天数信信息；Hour：小时信信息；Minute：分钟信信息；Second：秒数信信息。Oracle中也存在在timestamp数据类型型，它包包括了所所有date数据类型型的年月月日时分分秒的信信息，而而且包括括了小数数秒的信信息。9.4..1获取日期期和时间间的函数数1获取系统统日期函函数——sysdate该函数没没有参数数，返回回当前数数据库系系统日期期，是很很常用的的函数。。【示例9-42】使用sysdate函数返回回当前系系统日期期。9.4..1获取日期期和时间间的函数数2获取系统统时间函函数——systimestampsystimestamp函数与sysdate函数类似似，也没没有参数数。该函函数返回回的是系系统时间间，时间间精确到到微秒。。【示例9-43】使用systimestamp函数返回回当前系系统时间间。9.4..1获取日期期和时间间的函数数3返回会话话所在时时区当前前日期函函数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获得年份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,orYISO年（一年53周，第54周的星期一为新年的开始）IYYY,IYY,IY,orI季度Q月份MONTH,MON,MM,orRM本周内与当年第一天具有相同周内天数的日期WW本周内与ISO年第一天具有相同周内天数的日期IW本周内与当月第一天具有相同周内天数的日期W天DDD,DD,orJ本周内的第一天DAY,DY,orD小时HH,HH12,HH24分钟MI9.4.2日期和时时间差的的函数在实际中中，我们们往往想想知道在在特定的的两个日日期或者者时间内内有多少少天数、、小时等等，或者者从某个个时间开开始，经经过某个个时间段段，系统统需要对对某个数数据进行行操作。。这时需需要使用用一些日日期和时时间差的的函数。。9.4..2日期和时时间差的的函数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()函数该函数返返回指定定日期后后的一周周的对应应日期，，其语法法结构如如图所示示。【示例9-51】获取系统统当前时时间紧随随其后的的第一个个星期一一（当前前时间是是2012-12-10，周一））。9.5转换函数数转换函数数很重要要，