Oracle函数与表达式

Oracle函数与表达式Oracle中提供了大量的内置函数，以处理各种形式的运算。这些函数涵盖了字符串运算、数值运算、日期运算等方面。同样，Oracle允许使用数值运算、逻辑运算等基本的表达式运算，另外，提供了SQL标准所规定的特殊判式。Oracle中的字符串函数；Oracle中的数学函数；Oracle中的日期函数；第14章Oracle中的函数与表达式Oracle中的聚合函数；Oracle中的运算表达式；Oracle中的特殊判式；Oracle中的高级函数——分析函数与窗口函数。14.1Oracle中的字符串函数Oracle提供了丰富的字符串函数，本小节将通过实例讲述Oracle中各字符串函数的使用。14.1.1lpad()函数lpad()函数用于左补全字符串。在某些情况下，预期的字符串为固定长度，而且格式统一，此时可以考虑使用lpad()函数。例如，深市股票代码都以0开头，并且都为6位，可以利用lpad格式化股票代码，以保证股票代码的格式。selectlpad('21',6,'0')stock_codefromdual;需要注意的是，当原字符串的长度大于预期长度时，实际进行的是截取字符串操作。selectlpad('1234567',6,'0')stock_codefromdual;14.1.2rpad()函数与lpad()函数相反，rpad()函数从右端补齐字符串。selectrpad('abc',10,'*')fromdual;注意与说明：lpad()和rpad()都用于填充字符串，lpad()从左端进行填充，而rpad()从右端进行填充，但是，二者在最终截取字符串时，都是从左端开始截取。selectrpad('abcdefg',6,'*')fromdual;14.1.3lower()函数——返回小写字符串lower()函数用于返回字符串的小写形式。lower()函数在查询语句中经常扮演重要角色。例如，对于用户名和密码的校验来说，用户名一般并不区分大小写，用户无论输入了大写还是小写形式，都被认为是合法用户。因此，在数据库查询时，应该将数据库中用户名与用户输入的用户名进行统一。selectuser_id,user_namefromt_userswherelower(user_name)=lower('Alex');14.1.4upper()函数——返回大写字符串upper()函数用于返回字符串的大写形式。与lower()函数类似，upper()函数也可以用在查询语句中，以统一数据库和查询条件的一致性。selectuser_id,user_namefromt_userswhereupper(user_name)=upper('ALEX');注意与说明：upper()函数和lower()函数只针对英文字符其作用，因为只有英文字符才有大小写之分。14.1.5initcap()函数——单词首字母大写initcap()函数将单词的首字母大写。selectinitcap('big')fromdual;需要注意的是，initcap()函数不能自动识别单词selectinitcap('bigbigtiger')fromdual;initcap()函数会将参数中的非单词字符作为单词分隔符selectinitcap('big_big_tiger')fromdual;selectinitcap('big/big/tiger')fromdual;selectinitcap('bigbigtiger')fromdual;14.1.6length()函数——返回字符串长度length()函数用于返回字符串的长度。selectlength('abcd')fromdual;空字符串的长度不是0，而是null。因为空字符串被视作null，所以，length(null)返回的仍然是null。selectlength('')fromdual;对其其他数据类型，照样可以通过length()函数来获得其长度。length()函数会首先将参数转换为字符串，然后计算其长度。selectlength(12.51)fromdual;14.1.7substr()函数——截取字符串substr()函数用于截取字符串。该函数可以指定截取的起始位置，截取长度，可以实现灵活的截取操作，因此，成为字符串操作中最常用的函数之一。例如，对于字符串“1234567890”，现欲截取自第5位开始的4个字符。selectsubstr('1234567890',5,4)fromdual;需要注意的是，Oracle中字符位置从1开始，而不是像某些编程语言（如Java）那样从0开始。如果不指定长度，那么substr()函数将获取起始位置参数至字符串结尾处的所有字符。selectsubstr('1234567890',5)fromdual;14.1.8instr()函数——获得字符串出现的位置instr()函数用于获得子字符串在父字符串中出现的位置。selectinstr('bigbigtiger','big')fromdual;可以指定额外的参数，以命令该函数从指定位置开始搜索。selectinstr('bigbigtiger','big',2)fromdual;还可以指定出现次数参数，以指定是第几次搜索到子字符串。selectinstr('bigbigtiger','big',2,2)fromdual;14.1.9ltrim()函数——删除字符串首部空格ltrim()中的l代表left。该函数用于删除字符串左端的空白符。selectltrim('abc')fromdual;需要注意的是，空白符不仅仅包括了空格符，还包括TAB键、回车符和换行符。14.1.10rtrim()函数——删除字符串尾部空格rtrim()中的r代表right。该函数用于删除字符串右端空白符。删除字符串首尾空白符可以结合使用ltrm()和rtrim()函数。selectrtrim(ltrim('abc'))fromdual;14.1.11trim()函数——删除字符串首尾空格trim()函数可用于删除首尾空格，相当于ltrim()和rtrim()的组合。selecttrim('abc')fromdual;14.1.12to_char()函数——将其他类型转换为字符类型to_char()函数用于将其他数据类型的数据转换为字符型，这些类型主要包括数值型、日期型。1.将数值型转换为字符串selectto_char(120,'99999')resultfromdual;selectto_char(0.96,'9.99')resultfromdual;selectto_char(0.96,'0.00')resultfromdual;selectto_char(5897.098,'999,999,999.000')resultfromdual;selectto_char(5987.098,'$999,999,999.000')resultfromdual;2.将日期型转换为字符串selectto_char(sysdate,'yyyy-mm-dd')resultfromdual;selectto_char(sysdate,'YYYY-MON-DD')fromdual;14.1.13chr()函数——将ascii码转换为字符串chr()函数用于将ascii码转换为字符串。通过chr()函数，可以对不宜直接输入的字符进行操作。例如，将回车换行符插入到数据中。insertintotest_datavalues(6,'周林'||chr(13)||chr(10)|'梁军',20);select*fromtest_datawhereid=6;14.1.14translate()函数——替换字符translate()函数用于替换字符串。替换的规则类似于翻译的过程。selecttranslate('56338','1234567890','avlihemoqr')resultfromdual;需要注意的是，当字符不能被成功“翻译”，那么，Oracle将使用空字符替换它。利用此特性，可以使用translate()函数来删除一个含有数字和英文字母的字符串中的所有字母：selecttranslate('21343yuioioizf899dasiwpe58595oda0j098','#abcdefghijklmnopqrstuvwxyz','')reulstfromdual;14.2Oracle中的数学函数Oracle提供的数学函数可以处理日常使用到的大多数数学运算。本小节将讲述Oracle中常用的几种数学函数。14.2.1abs()函数——返回数字的绝对值abs()函数的参数只能是数值型，该参数用于返回参数的绝对值。selectabs(-2.1)fromdual;14.2.2round()函数——返回数字的“四舍五入”值round()函数用于返回某个数字的四舍五入值。为了使用该函数，除了提供原始值之外，还应提供精确到的位数。精确位数可以为正整数、0和负整数。selectround(2745.173,2)resultfromdual;如果不使用第二个参数，那么，相当于使用了参数0，即精确到整数。selectround(2745.173)resultfromdual;如果第二个参数为负数，那么，相当于将数值精确到小数点之前的位数。selectround(2745,-1)resultfromdual;14.2.3ceil()函数——向上取整ceil()函数只能有一个参数。该函数将参数向上取整，以获得大于等于该参数的最小整数。selectceil(21.897)resultfromdual;需要注意的是该函数针对负数的运算：selectceil(-21.897)resultfromdual;因为ceil()函数返回的是大于等于参数的最小整数，所以，该函数返回的并非-22，而是-21。14.2.4floor()函数——向下取整与ceil函数相反，floor()函数用于返回小于等于某个数值的最大整数。selectfloor(21.897)resultfromdual;selectfloor(-21.897)resultfromdual;14.2.5mod()函数——取模操作mod()函数有两个参数，第一个参数为被除数，第二个参数为除数。mod()函数的实际功能为获得两数相除之后的余数。selectmod(5,2)resultfromdual;14.2.6sign()函数——返回数字的正负性sign()函数只有一个参数。该函数将返回参数的正负性。若返回值为1，表示该参数大于0；若返回值为-1，表示该参数小于0；若返回值为0，表示该参数等于0。selectsign(8)resultfromdual;selectsign(-8)resultfromdual;selectsign(0)resultfromdual;sign()函数为判断两个数值的大小关系提供了方便。因为在oracle中，利用类似ifelse的结构来判断两个数值之间的大小关系，并不像编程语言中那样方便，而且极易造成代码的复杂化。14.2.7sqrt()函数——返回数字的平方根sqrt()函数也只有一个参数。该函数用于返回参数的平方根。可以利用round()函数和sqrt()函数返回某个数值的近似平方根。selectround(sqrt(2),3)resultfromdual;14.2.8power()函数——乘方运算power()函数有两个参数。该函数用于实现数值的乘方运算。selectpower(6,2)resultfromdual;14.2.9trunc()函数——截取数字trunc()函数用于截取部分数字。其工作机制非常类似于round()函数。与round()函数不同的是，该函数不对数值做四舍五入处理，而是直接截取。selecttrunc(2745.173,2)resultfro