精通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语句的se