实用！快速学习Oracle数据库函数使用技巧！ (oracle数据库函数怎么用)

Oracle数据库是目前市场上使用最广泛的数据库之一，也是许多企业级系统的首选数据库。在处理数据时，Oracle数据库中的函数是非常重要的一部分。函数可以提供一种简单的方法来处理数据，使得代码更清晰，操作更快捷。在使用Oracle数据库时，掌握一些函数使用技巧就显得尤为重要。

1. 常用函数

（1）NVL函数：该函数用于检查一个表达式是否为NULL，如果是NULL则返回一个特定的值。它的语法如下：

NVL ( expr1, expr2 )

其中，expr1用来检测是否为NULL，expr2用来指定表达式的值。

例如：假设我们在查询一个员工表中的工资，但有些员工的工资字段为NULL，则我们可以使用如下语句：

SELECT NVL(SALARY, 0) FROM EMPLOYEE;

其中的0即为特定的值。

（2）TO_CHAR函数：该函数用于将一个日期、数字等数据类型转换为字符型。其语法如下：

TO_CHAR ( value, format )

其中，value为要转换的值，format定义了转换的样式。

例如：假设我们要查询一个员工的工龄，但是数据库存储的是入职时间的日期型数据，我们可以使用如下语句：

SELECT TO_CHAR(MONTHS_BETWEEN(sysdate,EMPLOYEE.HIRE_DATE)/12,’FM999D99′) “年数” FROM EMPLOYEE;

其中，FM999D99用于定义年数的格式。

（3）SUM函数：该函数用于对数据库中某一列的值进行求和操作。其语法如下：

SUM ([DISTINCT|ALL] expression)

其中，expression为要求和的列。

例如：假设我们要求出员工表中所有员工的工资总和，则我们可以使用如下语句：

SELECT SUM(SALARY) FROM EMPLOYEE;

2. 高级函数

（1）LAG/LEAD函数：这两个函数用于访问同一行中的前一行和后一行数据。其语法如下：

LAG/LEAD (expr, [offset [,default]])

其中，expr为要访问的数据，offset表示访问的偏移量，默认为1，即访问前一行或后一行数据。default表示当无法访问到数据时返回的默认值。

例如：假设我们要查询员工表中每个员工的前一行工资情况，则我们可以使用如下语句：

SELECT EMPLOYEE.ID, EMPLOYEE.SALARY, LAG(SALARY, 1) OVER (ORDER BY ID) AS “前一行工资情况” FROM EMPLOYEE;

其中，OVER子句表示排序方式。

（2）CAST函数：该函数用于将一种数据类型转换为另一种数据类型。其语法如下：

CAST (expression AS data_type)

其中，expression为要转换的值，data_type表示转换后的数据类型。

例如：假设我们要将员工表中的字符串类型的工资字段转换为数字类型，我们可以使用如下语句：

SELECT CAST(SALARY AS NUMBER) FROM EMPLOYEE;

（3）DECODE函数：该函数用于根据条件返回不同的结果。其语法如下：

DECODE(expr,search,result,[search,result]……[default])

例如：假设我们要查询员工表中“性别”一列，但是该表存储“性别”的值为0/1，我们想要将它们转换为“男”/“女”，则可以使用如下语句：

SELECT NAME, DECODE(SEX, 0, ‘男’, 1, ‘女’) AS “性别” FROM EMPLOYEE;

其中，0和1表示检测到的值，‘男’和‘女’表示要返回的结果。

本文介绍了Oracle数据库中常用的一些函数及其使用技巧，这些函数在处理数据时是非常实用的，能够提高效率、节省时间。需要注意的是，在使用这些函数时需要根据具体的情况选择最适合的函数进行操作。熟练掌握这些函数的使用方法，能够更好地发挥Oracle数据库的优势，提高工作效率和数据处理能力。

相关问题拓展阅读：

• oracle中的over函数怎么用的，什么意思

oracle中的over函数怎么用的，什么意思

Oracle从8.1.6开始提供分析函数，分析函数汪卖用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

一、 over函数

over函数指定了分析函数工作的数据窗口的大小，这个数据窗口大小可能会随着行的变化而变化，例如：

over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数

over(partition by deptno) 按照部门分区

over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150的数据记录

over(order by salary rows between 50 perceding and 150 following)前50行，后150行

over（order by salary rows between unbounded preceding and unbounded following）所有行

over（order by salary range between unbounded preceding and unbounded following）所有行

二、 sum函数

功能描述：该函数计算组中表达式的累积和。

SAMPLE：下例计算同一经理下员工的薪水累积值

SELECT manager_id, last_name, salary,

SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary

RANGE UNBOUNDED PRECEDING) l_csum

FROM employees

WHERE manager_id in (101,103,108);

三、 应用实例

1， 测试环境设置

设有销售表t_sales (subcompany,branch,region,customer,sale_qty); 存储客户的销售明细，记录如下所示。

Subcompany Branch Region Customer Sale_qty

北京分公司 北京经营部 片区1 客户1 1

北京分公司 北京经营部 片区1 客户1 1

北京分公司 北京经营部 片区1 客户2 1

北京分公司 北京经营部 片区1 客户2 1

北京分公司 北京经营部 片区2 客户1 1

北京分公司 北京经营部 片区2 客户1 1

北京分公司 北京经营部 片区2 客户2 1

北京分公司 北京经营部 片区2 客户2 1

北京分公司 其他经营部 片区1 客户1 1

北京分公司 其他经营部 片区1 客户1 1

北京分公司 其他经营部 片区1 客户2 1

北京分公司 其他经营部 片区1 客户2 1

北京分公司 其他经营部 片区2 客户1 1

北京分公司 其他经营部 片区2 客户1 1

北京分公司 其他经营部 片区2 客户2 1

北京分公司 其他经营部 片区2 客户2 1

create table t_sales(

subcompany varchar2(40),

branch varchar2(40),

region varchar2(40),

customer varchar2(40),

sale_qty numeric(18,4)

);

comment on table t_sales is ‘销售表，分析函数测试’激悉;

comment on column t_sales.subcompany is ‘分公司’;

comment on column t_sales.branch is ‘经营部’;

comment on column t_sales.region is ‘片区’;

comment on column t_sales.customer is ‘客户’;

comment on column t_sales.sale_qty is ‘销售数量’;

2，问题提出

现在要求给出销售汇总报表，报表中需要提供的数据包括客户明陵乎汇总，和客户在其上级机构中的销售比例。

Subcompany Branch Region Customer Sale_qty Rate

北京分公司 北京经营部 片区1 客户1 2 50％

北京分公司 北京经营部 片区1 客户2 2 50％

北京分公司 北京经营部 片区1 小计 4 50％

北京分公司 北京经营部 片区2 客户1 2 50％

北京分公司 北京经营部 片区2 客户2 2 50％

北京分公司 北京经营部 片区2 小计 4 50％

北京分公司 北京经营部 小计 小计 8 50％

北京分公司 北京经营部 片区1 客户1 2 50％

北京分公司 北京经营部 片区1 客户2 2 50％

北京分公司 北京经营部 片区1 小计 4 50％

北京分公司 北京经营部 片区2 客户1 2 50％

北京分公司 北京经营部 片区2 客户2 2 50％

北京分公司 北京经营部 片区2 小计 4 50％

北京分公司 北京经营部 小计 小计 8 50％

北京分公司 小计 小计 小计％

3，解决方案（方案1）

首先我们可以使用oracle对group by 的扩展功能rollup得到如下的聚合汇总结果。

select

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by rollup(subcompany,branch,region,customer);

Subcompany Branch Region Customer Sale_qty

北京分公司 北京经营部 片区1 客户1 2

北京分公司 北京经营部 片区1 客户2 2

北京分公司 北京经营部 片区1 4

北京分公司 北京经营部 片区2 客户1 2

北京分公司 北京经营部 片区2 客户2 2

北京分公司 北京经营部 片区2 4

北京分公司 北京经营部 8

北京分公司 其他经营部 片区1 客户1 2

北京分公司 其他经营部 片区1 客户2 2

北京分公司 其他经营部 片区1 4

北京分公司 其他经营部 片区2 客户1 2

北京分公司 其他经营部 片区2 客户2 2

北京分公司 其他经营部 片区2 4

北京分公司 其他经营部 8

北京分公司 16

16

分析上面的临时结果，我们看到：

明细到客户的汇总信息，其除数为当前的sum(sale_qty),被除数应该是到片区的小计信息。

明细到片区的汇总信息，其除数为片区的sum(sale_qty),被除数为聚合到经营部的汇总数据。

。。。

考虑到上述因素，我们可以使用oracle的开窗函数over，将数据定位到我们需要定位的记录。如下代码中，我们利用开窗函数over直接将数据定位到其上次的小计位置。

over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null)

经整理后的查询语句如下。

select subcompany,

decode(f_branch, 1,subcompany||'(С¼Æ)’, branch),

decode(f_region,1,branch||'(С¼Æ)’,region),

decode(f_customer,1,region||'(С¼Æ)’, customer),

sale_qty,

trim(to_char(round(sale_qty/

sum(sale_qty) over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null),2) *100,99990.99))

from (select grouping(branch) f_branch,

grouping(region) f_region,

grouping(customer) f_customer,

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by subcompany, rollup(branch, region, customer))

Subcompany Branch Region Customer Sale_qty Rate

北京分公司 北京经营部 片区1 客户1 2 50.00

北京分公司 北京经营部 片区1 客户2 2 50.00

北京分公司 北京经营部 片区2 客户1 2 50.00

北京分公司 北京经营部 片区2 客户2 2 50.00

北京分公司 北京经营部 片区1 片区1(小计) 4 50.00

北京分公司 北京经营部 片区2 片区2(小计) 4 50.00

北京分公司 其他经营部 片区1 客户1 2 50.00

北京分公司 其他经营部 片区1 客户2 2 50.00

北京分公司 其他经营部 片区2 客户1 2 50.00

北京分公司 其他经营部 片区2 客户2 2 50.00

北京分公司 其他经营部 片区1 片区1(小计) 4 50.00

北京分公司 其他经营部 片区2 片区2(小计) 4 50.00

北京分公司 北京经营部 北京经营部(小计) (小计) 8 50.00

北京分公司 其他经营部 其他经营部(小计) (小计) 8 50.00

北京分公司 北京分公司(小计) (小计) (小计).00

北京分公司 北京经营部 片区1 客户1 2 50.00

4，可能的另外一种解决方式（方案2）

select subcompany,

decode(f_branch, 1,subcompany||'(С¼Æ)’, branch),

decode(f_region,1,branch||'(С¼Æ)’,region),

decode(f_customer,1,region||'(С¼Æ)’, customer),

sale_qty,

/* trim(to_char(round(sale_qty/*/

decode(f_branch+f_region+f_customer,

,

(sum(sale_qty) over(partition by subcompany,branch,region))/2,

,

(sum(sale_qty) over(partition by subcompany,branch))/3,

,

(sum(sale_qty) over(partition by subcompany))/4 ,

sum(sale_qty) over()/4

)/*

,2) *100,99990.99))*/

from (select grouping(branch) f_branch,

grouping(region) f_region,

grouping(customer) f_customer,

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by subcompany, rollup(branch, region, customer))

在上面的解决方式中，更大的问题在于开窗函数过大。导致每次计算涉及到的行数过多，影响到执行的速度和效率。并且需要额外的计算处理清除多余叠加进去的数值 。

over函做拿并数是oracle中的分析函数，分析函数是对行集组进行聚合计算，但是不敏岁像普通聚合仗函数那样每组只返回一个值，分析函数可以为每组返回多个值。

使用方法为：over(partition by排 列名1

order by

列名2 )，括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数，如果是

聚合函数

，那么order by 不能一起使用。

扩展资料

在SQL语句中，很多查询语句需要进行

GROUP BY

分组汇总，但是一旦经过分组，SELECT返回的记录孢数就会减少。为了保留所有原始行记录，并且仍可以进行分组

数据分析

，分析函数应运而生。

oracle数据库函数，分析函数用于为行定义一个窗口，对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

RANK（）也为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如两个相同的行生成序号3，那么接下来会生成序号。

DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。也就是说如果两个相同的行生成序号，那么接下来生成的序号还是。

参考资纯迹料来源：

百度百科-分析函数

oracle数据库函数怎么用的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于oracle数据库函数怎么用,实用！快速学习Oracle数据库函数使用技巧！,oracle中的over函数怎么用的，什么意思的信息别忘了在本站进行查找喔。