第14章Oracle中的函数与表达式ppt课件

1、第14章 Oracle中的函数与表达式Oracle中提供了大量的内置函数，以处置各种方式的运算。这些函数涵盖了字符串运算、数值运算、日期运算等方面。同样，Oracle允许运用数值运算、逻辑运算等根本的表达式运算，另外，提供了SQL规范所规定的特殊判式。Oracle中的字符串函数；Oracle中的数学函数；Oracle中的日期函数；第14章 Oracle中的函数与表达式Oracle中的聚合函数；Oracle中的运算表达式；Oracle中的特殊判式；Oracle中的高级函数分析函数与窗口函数。14.1 Oracle中的字符串函数Oracle提供了丰富的字符串函数，本小节将经过实例讲述Oracle中

2、各字符串函数的运用。14.1.1 lpad()函数lpad()函数用于左补全字符串。在某些情况下，预期的字符串为固定长度，而且格式一致，此时可以思索运用lpad()函数。例如，深市股票代码都以0开头，并且都为6位，可以利用lpad格式化股票代码，以保证股票代码的格式。select lpad(21,6,0) stock_code from dual;需求留意的是，当原字符串的长度大于预期长度时，实践进展的是截取字符串操作。select lpad(1234567,6,0) stock_code from dual;14.1.2 rpad()函数与lpad()函数相反，rpad()函数从右端补齐字符

3、串。select rpad(abc, 10, *) from dual; 留意与阐明：lpad()和rpad()都用于填充字符串，lpad()从左端进展填充，而rpad()从右端进展填充，但是，二者在最终截取字符串时，都是从左端开场截取。select rpad(abcdefg, 6, *) from dual;14.1.3 lower()函数前往小写字符串lower()函数用于前往字符串的小写方式。lower()函数在查询语句中经常扮演重要角色。例如，对于用户名和密码的校验来说，用户名普通并不区分大小写，用户无论输入了大写还是小写方式，都被以为是合法用户。因此，在数据库查询时，应该将数据库中用

4、户名与用户输入的用户名进展一致。select user_id, user_name from t_users where lower(user_name) = lower(Alex);14.1.4 upper()函数前往大写字符串upper()函数用于前往字符串的大写方式。与lower()函数类似，upper()函数也可以用在查询语句中，以一致数据库和查询条件的一致性。select user_id, user_name from t_users where upper(user_name) = upper(ALEX);留意与阐明：upper()函数和lower()函数只针对英文字符其作用，由于

5、只需英文字符才有大小写之分。14.1.5 initcap ()函数单词首字母大写initcap()函数将单词的首字母大写。select initcap(big) from dual;需求留意的是，initcap()函数不能自动识别单词 select initcap(bigbigtiger) from dual;initcap()函数会将参数中的非单词字符作为单词分隔符select initcap(big_big_tiger) from dual; select initcap(big/big/tiger) from dual; select initcap(big big tiger) fro

6、m dual; 14.1.6 length()函数前往字符串长度length()函数用于前往字符串的长度。select length(abcd ) from dual;空字符串的长度不是0，而是null。由于空字符串被视作null，所以，length(null)前往的依然是null。select length() from dual;对其其他数据类型，照样可以经过length()函数来获得其长度。length()函数会首先将参数转换为字符串，然后计算其长度。select length(12.51) from dual;14.1.7 substr()函数截取字符串substr()函数用于截取字符

7、串。该函数可以指定截取的起始位置，截取长度，可以实现灵敏的截取操作，因此，成为字符串操作中最常用的函数之一。例如，对于字符串“1234567890，现欲截取自第5位开场的4个字符。select substr(1234567890, 5, 4) from dual;需求留意的是，Oracle中字符位置从1开场，而不是像某些编程言语如Java那样从0开场。 假设不指定长度，那么substr()函数将获取起始位置参数至字符串结尾处的一切字符。 select substr(1234567890, 5) from dual; 14.1.8 instr()函数获得字符串出现的位置instr()函数用于获得

8、子字符串在父字符串中出现的位置。select instr(big big tiger, big) from dual;可以指定额外的参数，以命令该函数从指定位置开场搜索。 select instr(big big tiger, big, 2) from dual; 还可以指定出现次数参数，以指定是第几次搜索到子字符串。select instr(big big tiger, big, 2, 2) from dual;14.1.9 ltrim()函数删除字符串首部空格ltrim()中的l代表left。该函数用于删除字符串左端的空白符。select ltrim( abc) from dual; 需求

9、留意的是，空白符不仅仅包括了空格符，还包括TAB键、回车符和换行符。14.1.10 rtrim()函数删除字符串尾部空格rtrim()中的r代表right。该函数用于删除字符串右端空白符。删除字符串首尾空白符可以结合运用ltrm()和rtrim()函数。select rtrim(ltrim( abc ) from dual;14.1.11 trim()函数删除字符串首尾空格trim()函数可用于删除首尾空格，相当于ltrim()和rtrim()的组合。select trim( abc ) from dual;14.1.12 to_char()函数将其他类型转换为字符类型to_char()函数用

10、于将其他数据类型的数据转换为字符型，这些类型主要包括数值型、日期型。1. 将数值型转换为字符串select to_char(120, 99999) result from dual; select to_char(0.96, 9.99) result from dual; select to_char(0.96, 0.00) result from dual; select to_char(5897.098, 999,999,999.000) result from dual; select to_char(5987.098, $999,999,999.000) result from dua

11、l;2. 将日期型转换为字符串select to_char(sysdate, yyyy-mm-dd) result from dual; select to_char(sysdate, YYYY-MON-DD) from dual; 14.1.13 chr()函数将ascii码转换为字符串chr()函数用于将ascii码转换为字符串。经过chr()函数，可以对不宜直接输入的字符进展操作。例如，将回车换行符插入到数据中。insert into test_data values (6, 周林|chr(13)|chr(10)|梁军, 20); select * from test_data wher

12、e id = 6; 14.1.14 translate()函数交换字符translate()函数用于交换字符串。交换的规那么类似于翻译的过程。 select translate(56338, 1234567890, avlihemoqr) result from dual;需求留意的是，当字符不能被胜利“翻译，那么，Oracle将运用空字符交换它。利用此特性，可以运用translate()函数来删除一个含有数字和英文字母的字符串中的一切字母：select translate(21343yuioioizf899dasiwpe58595oda0j098, #abcdefghijklmnopqrst

13、uvwxyz, ) reulst from dual; 14.2 Oracle中的数学函数Oracle提供的数学函数可以处置日常运用到的大多数数学运算。本小节将讲述Oracle中常用的几种数学函数。14.2.1 abs ()函数前往数字的绝对值abs()函数的参数只能是数值型，该参数用于前往参数的绝对值。select abs(-2.1) from dual; 14.2.2 round ()函数前往数字的“四舍五入值round()函数用于前往某个数字的四舍五入值。为了运用该函数，除了提供原始值之外，还应提供准确到的位数。准确位数可以为正整数、0和负整数。select round(2745.173

14、, 2) result from dual; 假设不运用第二个参数，那么，相当于运用了参数0，即准确到整数。select round(2745.173) result from dual; 假设第二个参数为负数，那么，相当于将数值准确到小数点之前的位数。 select round(2745, -1) result from dual; 14.2.3 ceil()函数向上取整ceil()函数只能有一个参数。该函数将参数向上取整，以获得大于等于该参数的最小整数。select ceil(21.897) result from dual; 需求留意的是该函数针对负数的运算： select ceil(-

15、21.897) result from dual; 由于ceil()函数前往的是大于等于参数的最小整数，所以，该函数前往的并非-22，而是-21。14.2.4 floor()函数向下取整与ceil函数相反，floor()函数用于前往小于等于某个数值的最大整数。select floor(21.897) result from dual; select floor(-21.897) result from dual;14.2.5 mod ()函数取模操作mod()函数有两个参数，第一个参数为被除数，第二个参数为除数。mod()函数的实践功能为获得两数相除之后的余数。select mod(5,2)

16、result from dual; 14.2.6 sign()函数前往数字的正负性sign()函数只需一个参数。该函数将前往参数的正负性。假设前往值为1，表示该参数大于0；假设前往值为-1，表示该参数小于0；假设前往值为0，表示该参数等于0。select sign(8) result from dual; select sign(-8) result from dual; select sign(0) result from dual; sign()函数为判别两个数值的大小关系提供了方便。由于在oracle中，利用类似if else的构造来判别两个数值之间的大小关系，并不像编程言语中那样方便，

17、而且极易呵斥代码的复杂化。14.2.7 sqrt()函数前往数字的平方根sqrt()函数也只需一个参数。该函数用于前往参数的平方根。可以利用round()函数和sqrt()函数前往某个数值的近似平方根。select round(sqrt(2), 3) result from dual; 14.2.8 power()函数乘方运算power()函数有两个参数。该函数用于实现数值的乘方运算。select power(6, 2) result from dual; 14.2.9 trunc()函数截取数字trunc()函数用于截取部分数字。其任务机制非常类似于round()函数。与round()函数不

18、同的是，该函数不对数值做四舍五入处置，而是直接截取。select trunc(2745.173, 2) result from dual; 保管位数的值可以为0，当该参数的值为0时，将保管到整数。 select trunc(2745.173) result from dual; 当保管位数小于0时，表示保管到小数点之前的位数。 select trunc(2745.173, -1) result from dual; 14.2.10 vsize()函数前往数据的存储空间vsize()函数根据数据库的存储格式，来前往其所占用的存储空间的字节数。select vsize(abc123) from d

19、ual; 留意与阐明：vsize()函数在前往的是Oracle实践存储数据的字节数，在实践开发中运用的几率也较小。读者可以不用了解Oracle本身的存储机制。14.2.11 to_number()函数将字符串转换为数值类型to_number()函数可以将字符串转换为数值型。select to_number(257.90) result from dual; 需求留意的是，被转换的字符串必需符合数值类型格式。假设被转换的字符串不符合数值型格式，Oracle将抛出错误提示。select to_number(a) result from dual;14.3 Oracle中的日期函数Oracle提供了

20、丰富的日期函数。利用日期函数可以灵敏的对日期进展运算。14.3.1 to_date()函数将字符串转换为日期型to_date()函数用于将字符串转换为日期。被转换的字符串必需符合特定的日期格式。select to_date(12/02/09, mm/dd/yy) result from dual; 14.3.2 add_months()函数为日期加上特定月份add_months()函数将为日期添加特定月份，并获得新的日期。select to_char(add_months(sysdate, 2), yyyy-mm-dd) result from dual;14.3.3 last_day()函数

21、前往特定日期所在月的最后一天last_day()函数将接受一个日期参数。该函数首先获得日期参数所在月的信息，然后获得该月最后一天的日期。select to_char(last_day(sysdate), yyyy-mm-dd) result from dual;可以综合利用add_months()函数来获得假设干月之后的月份的最后一天。 select to_char(last_day(add_months(sysdate, 3), yyyy-mm-dd) result from dual;14.3.4 months_between ()函数前往两个日期所差的月数months_between()

22、函数用于获取两个日期所间隔的月数。该函数的前往值是一个实数。select months_between(sysdate, to_date(2021-02-08, yyyy-mm-dd) result from dual;当第一个日期早于第二个日期，那么前往值将是负值。select months_between(to_date(2021-02-08, yyyy-mm-dd), to_date(2021-03-08, yyyy-mm-dd) result from dual; 14.3.5 current_date()函数前往当前会话时区的当前日期current_date()函数用于前往当前会话时

23、区的当前日期。select sessiontimezone, to_char(current_date, yyyy-mm-dd hh:mi:ss) result from dual; 留意与阐明：current_date等无参数函数作为Oracle的关键字存在。在运用时，不能为其添加小括号。即select current_date() from dual是错误的SQL语句。14.3.6 current_timestamp()函数前往当前会话时区的当前时间戳current_timestamp()函数用于前往当前会话时的区时间戳。可以结合sessiontimezone来查看其用法。select s

24、essiontimezone, current_timestamp from dual;14.3.7 extract()函数前往日期的某个域日期由假设干域组成，例如年、月、日、小时等等。extract()函数可以前往这些域的详细值。为了运用该函数，除了要指定原日期外，还应该指定要前往的域名。select extract(year from sysdate) result from dual;需求留意的是，year、month、day域只能从日期如sysdate中获得，而hour、minute、second只能从时间型如systimestamp中获得。 14.4 Oracle中的聚合函数所谓聚合

25、函数是指针对多条记录的函数。Oracle最常用的聚合函数包括，max()、min()、avg()、sum()和count()函数。本节将讲述这些函数的用法。14.4.1 max()函数求最大值max()函数用于获得记录集在某列的最大值。例如，为了前往员工最高工资，可以利用max()函数。select max(salary) max_salary from t_salary; 需求留意的是，聚合函数往往是前往记录集的统计值，因此，不能与其中的单条记录同时出现。例如，不能将max(salary)与详细列一同查询。select employee_id, max(salary) max_salary

26、from t_salary; select distinct e.employee_name, s.salary from t_employees e, t_salary swhere e.employee_id = s.employee_id and s.salary = (select max(salary) from t_salary)14.4.2 min()函数求最小值min()函数可以用来获得记录集在某列上的最小值，其功能与max()函数相反。select distinct e.employee_name, s.salary from t_employees e, t_salary

27、swhere e.employee_id = s.employee_id and s.salary = (select min(salary) from t_salary)14.4.3 avg()函数求平均值avg()函数用于获得记录集在某列上的平均值。select e.employee_name, avg(salary)from t_employees e, t_salary swhere e.employee_id = s.employee_idgroup by e.employee_id, e.employee_name14.4.4 sum()函数求和sum()函数用于获得结果集上某列值

28、的和。select e.employee_name, sum(salary)from t_employees e, t_salary swhere e.employee_id = s.employee_idgroup by e.employee_id, e.employee_name14.4.5 count()函数获得记录数count()函数的作用对象同样为记录集。与其他聚合函数不同的是，count()函数可以有三种方式来进展计数：count(*)计算行数、count(column)计算某列和count(1)累加1。insert into t_employees values (16, nul

29、l, null,null);select count(*) from t_employees; select count(employee_id) from t_employees;select count(employee_name) from t_employees;select count(1) from t_employees; 普通来说，利用count(1)进展计数的速度最快，但是特别留意的是，预期的结果是针对整行数据，还是某列的数据。14.5 Oracle中的其他函数除了数值函数、字符串函数、日期函数和聚合函数外，Oracle还提供了其他功能性更强的函数。本节将引见decode()

30、、nvl()和cast()函数。14.5.1 decode()函数多值判别decode()函数用于多值判别。其执行过程类似于解码操作。该函数最常见的运用为，实现类似if else的功能。例如，可以利用decode()函数为员工工资添加标识，工资大于6000者为高收入，其他的为普通收入。select e.employee_id, e.employee_name, decode(sign(avg(s.salary) - 6000),1, 高收入, 普通收入) incommingfrom t_employees e, t_salary swhere e.employee_id = s.employe

31、e_idgroup by e.employee_id, e.employee_name 14.5.2 nvl()函数为空值重新赋值nvl()函数用于处置某列的值。该函数有两个参数，第一个参数为要处置的列。假设其值为空，那么前往第二个参数的值，否那么，将前往列值。select employee_id, nvl(employee_name, 未知) employee_name from t_employees;nvl()函数更常见的用途为判别数值能否为空。由于sum()等函数往往会前往null，例如，表示汇率的列一旦为null，那么最终的货币结算额度也为null，所以，必需对汇率列进展nvl()的

32、处置。在统计员工工资时，null同样是不受欢迎的结果，那么可以利用nvl()函数进展处置。select e.employee_id, nvl(e.employee_name, 未知) employee_name, nvl(sum(s.salary), 0) salaryfrom t_employees e, t_salary swhere e.employee_id = s.employee_id(+)group by e.employee_id, e.employee_name14.5.3 cast()函数强迫转换数据类型cast()函数用于强迫转换数据类型。Oracle会根据操作符来自动进

33、展数据类型的转换，例如：select 123 + 200 result from dual;Oracle会根据运算符“+将123转换为数值型123。 select 123 | 200 result from dual;Oracle会根据运算符“|将数字200转换为字符串200。 cast()函数最常用的场景是转换列的数据类型，以创建新表create table tmp_salary asselect cast(salary_id as varchar2(20) salary_id, cast(employee_id as varchar2(20) employee_id, cast(month

34、 as varchar2(20) month, cast(salary as varchar2(20) salaryfrom t_salarydesc tmp_salary; 14.6 Oracle中的运算表达式Oracle中的常用运算包括：数学运算、逻辑运算和按位运算。本节将经过范例着重讲述这三种运算的常用运算符和运算规那么。14.6.1 数学运算数学运算是最常用的运算方式，Oracle中的数学运算符包括：+、-、*、/，分别代表了加、减、乘除运算。在运用数学运算时，Oracle会自动将其他数据类型转换为数值型，然后再参与运算。select 5+3 result from dual;sele

35、ct 5-3 result from dual;select 5*2 result from dual;select 5/2 result from dual;需求留意的是，任何一种运算符与null的运算结果均为null。 select 5+null result from dual;select 5-null result from dual;select 5*null result from dual;select 5/null result from dual;14.6.2 逻辑运算Oracle中的逻辑运算包括：大于运算，可用于数值型、日期型和字符串类型；=：大于等于运算，可用于数值型、

36、日期型和字符串类型；：小于运算，可用于数值型、日期型和字符串类型；=：大于等于运算，可用于数值型、日期型和字符串类型；=：等于，可用于数值型、日期型和字符串类型；：不等于，可用于数值型、日期型和字符串类型；!=：与用法一样；NOT：取反操作；AND：布尔值的与操作；OR：布尔值的或操作。14.6.2 逻辑运算需求留意的是，Oracle中的逻辑运算符只能作为条件判别，并不前往值。为了查询工资在5000-7000之间的记录，可以利用逻辑运算符来组合查询条件。select * from t_salary where salary=5000 and salary=7000; 对于null值，需求特别留

37、意的是，无论运用哪种运算符，结果都会前往null。当比较的结果为null，并作为条件出现时，Oracle都会将其解释为false。select 1 result from dual where 1=null;select 1 result from dual where 1null; select 1 result from dual where null=null; select 1 result from dual where nullnull;14.6.3 位运算 从Oracle8i开场，系统曾经提供了位运算符。最常用的莫过于bitand运算符。select bitand(192, 10

38、0) result from dual; 14.7 Oracle中的特殊判式除了逻辑运算之外，Oracle提供了一些特殊判式。这些判式可以用来生成更加复杂和灵敏的查询条件。本节将着重引见以下几种判式。Between：取值范围。In：集合成员测试。Like：方式匹配。is null：空值判别。all，some，any：数量判别。exists：存在性判别。14.7.1 between范围测试between判式，用于判别某个值能否在另外两个值之间。这些值可以为数值型、字符串和日期型。运用betwwen判式来获得ID号在1-5之间的员工信息。select * from t_employees wher

39、e employee_id between 1 and 5; betwwen判式同样可以运用于字符串和日期型。字符串是按照字母表的顺序进展比较，而日期型是按照日期的先后顺序进展比较。select * from t_employees where b between b and c;select * from t_employees where b between bc and c; 留意与阐明：between判式与=、=的组合是等价关系。但是，效率上要比后者差。14.7.2 in集合成员测试in用于判别某个值能否一个集合的成员。select * from t_employees where s

40、tatus in(NEW, ACT); 值得留意的是，in判式中的集合的成员的数据类型可以不一致，例如，select * from t_employees where status in(NEW, ACT, sysdate, 1)中的数据类型包含了字符串、日期型和数值型。14.7.3 like方式匹配like判式的最大特点在于，可以运用通配符。其通常的运用场景为处置模糊查询。select * from t_employees where employee_name like 钟%; 假设要求字符串中含有原义字符“%，例如，含有百分比的字符串。那么，like判式应写作：like 钟% escap

41、e 。Oracle会首先解释escape关键字，并将其后的字符“解释为本义字符。那么在“钟%中的“%不再表示通配符，而是表示原义字符“%。“_下划线是可用于like判式的另一个通配符，该通配符表示一个恣意的字符。14.7.4 is null空值判别在逻辑判别中，对于列值为空的判别，不能运用=或者。oracle对与空值的判别提供了专门的判式is null。例如，为了获取表t_employees中员工信息不全的记录，可以利用如下所示的查询语句。select * from t_employees where employee_id is null or employee_name is null o

42、r work_years is null or status is null; 14.7.5 exists存在性判别in判式用于判别表的列值能否存在于列表集合中。而exists判式那么可用于判别查询结果集合能否为空。例如，为了查询出表t_employees所存储的员工信息中，哪些员工存在于工资表中，即可利用exists判式。select * from t_employees e where exists(select * from t_salary where employee_id = e.employee_id);14.7.6 all，some，any数量判别all，some和any判式的

43、作用对象为记录集合。all表示，记录集中的一切记录，some表示其中的一些记录，any判式那么表示其中的恣意记录。例如，在员工工资表t_salary中，为了查找高于id为4和5的工资信息，即可运用all判式。select * from t_salary where employee_id = 4 or employee_id = 5; select * from t_salary where salary all(select distinct salary from t_salary where employee_id = 4 or employee_id = 5); select * fr

44、om t_salary where salary some(select distinct salary from t_salary where employee_id = 4 or employee_id = 5); 此时的some判式实践相当于逻辑运算中的or运算，即salary6000 or salary7000。此时，运用any判式，将前往同样的结果。 14.8 Oracle高级函数分析函数与窗口函数Oracle中的分析函数具有非常强大的功能。分析函数往往与另一类函数窗口函数同时运用。窗口函数总是为查询过程中的当前记录提供一个相关记录集，而且随着当前记录的推移，相应的记录集也会随之改动

45、，这非常类似于“滑动窗的概念。分析函数的操作对象即为“滑动窗所指定的记录集合。本节将经过实例来讲述分析函数和窗口函数的运用。14.8.1 排名分析函数中的排名函数可以针对窗口中的记录生成排序序号。常用的排名函数有rank()、dense_rank()和row_number()。rank()函数用于前往当前记录在窗口函数所指定的记录集中的排名。rank()函数在排名过程中，具有腾跃的特点。select * from students; select student_name, rank() over(order by student_age) position from students;sel

46、ect student_name, dense_rank() over(order by student_age) position from students; select student_name, row_number() over(order by student_age) position from students; 14.8.2 分区窗口对于窗口函数，利用partition by关键字可以指定分区窗口。现欲统计各员工的工资在各自部门的高低情况，那么可以利用partition by进展分区，然后利用分析函数对分区内的记录进展统计 select t.*, dense_rank()

47、over(partition by department order by salary) position from salary t order by t.employee_id另外一种常见需求为，在获得员工工资的同时，也需求部门一切员工的工资总额 select t.*,sum(salary) over(partition by department) total_salary,round(avg(salary) over(partition by department) average_salary from salary t order by employee_id留意，avg(sal

48、ary) over(partition by department)是不可分割的一个整体。对于数据表salary中每条记录都会前往单个值，因此，当运用round()函数，函数的作用对象应为avg(salary) over(partition by department)这个整体，而不能运用诸如round(avg(salary) over(partition by department)等方式。另外，利用partition by进展分区之后，当前记录总是处于某个分区中，此时的窗口即为该分区。14.8.3 窗口子句对于每条记录，一旦运用了窗口函数，都会为其产生一个可操作的记录集合。而对于该记录集，

49、可以运用窗口子句，来进一步限制窗口范围。常用的窗口子句包括两类：利用rows子句的行方式进展限制；利用range子句的值方式进展限制。rows子句select employee_id, employee_name,sum(salary) over(order by employee_id rows between 1 preceding and 1 following) three_total from salaryrows子句由于和位置相关，因此，在窗口函数中必需含有排序子句order by。假设未运用order by子句，而直接运用rows子句，Oracle将抛出错误提示， 14.8.3 窗口子句留意，rows betwwen 1 preceding and 1 following不一定前往3条记录。例如，对于employee_id为1的记录，排序之后，该记录为第一条记录，不存在前一条记录，因此只前往两条记录，而求和操作前往的实践为employee_id为1和2的员工的工资总和10500。2. range子句range子句按照列值进展窗口的进一步限制。select employee_id, employe