oracle函数语法（OracleDB组函数）

本文目录

• OracleDB组函数
• oracle列转行函数
• oracle在函数中能定义函数吗

OracleDB组函数

　　Oracle DB组函数

　　&# ; 组函数

　　– 类型和语法

　　– 使用AVG SUM MIN MAX COUNT

　　– 在组函数中使用DISTINCT关键字

　　– 组函数中的NULL值

　　何谓组函数

　　组函数会对行集进行计算 为每个组提供一个结果

　　与单行函数不同 组函数用于对行集进行计算 从而为每个组提供一个结果 这些集合可以包含整个表 也可以包含表分割成的组

　　组函数的类型

　　&# ; AVG

　　&# ; COUNT

　　&# ; MAX

　　&# ; MIN

　　&# ; STDDEV

　　&# ; SUM

　　&# ; VARIANCE

　　每个函数都接受一个参数 下表列出了在语法中可使用的选项

　　组函数 语法

　　SELECT group_function(column)

　　FROM table

　　;

　　组函数 语法

　　组函数应放在SELECT关键字之后 可以使用逗号分隔多个组函数

　　使用组函数的准则

　　&# ; DISTINCT使函数仅考虑非重复值;ALL使函数考虑每个值(包括重复值) 默认值为ALL 因此无需指定

　　&# ; 使用expr参数的函数的数据类型可以是CHAR VARCHAR NUMBER或DATE

　　&# ; 所有组函数都忽略空值 要用一个值替代空值 使用NVL NVL COALESCE CASE或DECODE函数

　　使用AVG和SUM函数

　　可以对数字数据使用AVG和SUM函数

　　hr@TEST 》 SELECT AVG(salary) MAX(salary) MIN(salary) SUM(salary) FROM employees WHERE job_id LIKE %REP% ;

　　AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)

　　可以对能够存储数字数据的列使用AVG SUM MIN和MAX函数 示例显示所有销售代表的月薪平均值 最高值 最低值与总和

　　使用MIN和MAX函数

　　可以对数字 字符和日期数据类型使用MIN和MAX函数

　　hr@TEST 》 SELECT MIN(hire_date) MAX(hire_date) FROM employees;

　　MIN(HIRE_DATE) MAX(HIRE_DATE)

　　 JAN APR

　　可以对数字 字符和日期数据类型使用MAX和MIN函数 示例显示任职时间最短和最长的雇员

　　下面的示例显示在包含所有雇员的列表中 按字母顺序排列姓氏时位于首位及位于末位的雇员姓氏

　　hr@TEST 》 SELECT MIN(last_name) MAX(last_name) FROM employees;

　　MIN(LAST_NAME) MAX(LAST_NAME)

　　Abel Zlotkey

　　注 AVG SUM VARIANCE和STDDEV函数仅可用于处理数字数据类型 MAX和MIN函数不能用于处理LOB或LONG数据类型

　　使用COUNT函数

　　COUNT(*)将返回表中的行数

　　hr@TEST 》 SELECT COUNT(*) FROM employees WHERE department_id = ;

　　COUNT(*)

　　示例显示部门 中雇员的数量

　　COUNT(expr)将返回expr为非空值的行的数量

　　hr@TEST 》 SELECT COUNT(mission_pct) FROM employees WHERE department_id = ;

　　COUNT(MISSION_PCT)

　　示例显示部门 中可以获得佣金的雇员的数量

　　COUNT函数有以下三种格式

　　&# ; COUNT(*)

　　&# ; COUNT(expr)

　　&# ; COUNT(DISTINCT expr)

　　COUNT(*)用于返回表中符合SELECT语句标准的行数 包括重复行和在任何列中含有空值的行 如果SELECT语句中包含WHERE子句 则COUNT(*)会返回符合WHERE子句中条件的行数 相反 COUNT(expr)返回由expr标识的列中非空值的数量 COUNT(DISTINCT expr)返回由expr标识的列中不同非空值的数量

　　使用DISTINCT关键字

　　&# ; COUNT(DISTINCT expr)将返回expr的不同非空值的数量

　　&# ; 要显示EMPLOYEES表中不同部门值的数量 可使用

　　hr@TEST 》 SELECT COUNT(DISTINCT department_id) FROM employees;

　　COUNT(DISTINCTDEPARTMENT_ID)

　　使用DISTINCT关键字可以避免对某一列中的任何重复值进行计数

　　示例显示EMPLOYEES表中不同部门值的数量

　　组函数和空值

　　组函数将忽略列中的空值

　　hr@TEST 》 SELECT AVG(mission_pct) FROM employees;

　　AVG(MISSION_PCT)

　　仅根据在表的MISSION_PCT列中存储了有效值的行计算平均值 平均值的计算方法是用付给所有雇员的佣金总和除以获得佣金的雇员数( )

　　NVL函数会强制组函数包括空值

　　hr@TEST 》 SELECT AVG(NVL(mission_pct )) FROM employees;

　　AVG(NVL(MISSION_PCT ))

　　根据表中的所有行计算平均值 不考虑MISSION_PCT列中是否存储空值 平均值的计算方法是用付给所有雇员的佣金总和除以公司中的雇员总数( )

oracle列转行函数

在oracle中，列转行的函数是“unpivot()”函数，该函数用于对表格数据进行列转行转换，语法为“unpivot(自定义列名 列的值 for 自定义列名 列名 in(列名))”。

oracle的函数有：1、字符串函数，包括ASCII()、CONCAT()等；2、数字函数，包括ABS()、COS()等；3、日期函数，包括EXTRACT()、ROUND()等；4、转换函数，包括TO_CHAR()、TO_DATE()等。

Oracle 数据库中主要使用两种类型的函数：

1、单行函数：对每一个函数应用在表的记录中时，只能输入一行中的列值作为输入参数(或常数)，并且返回一个结果。

例如1：MOD(X,Y) 是求余函数，返回的X除以Y的余数，其中X和Y可以是列值，也可以是常数。

例如2：TO_CHAR(X,’YYYYMMDD’)是时间类型转字符串的函数，其中X可以是行中某一时间类型（date）的列，也可以是一个时间类型的常数。

常用的单行函数大致以下几类：

字符串函数：对字符串进行操作，例如：TO_CHAR()、SUBSTR()、DECODE()等等。

数值函数：对数值进行计算或操作，返回一个数字。例如：ABS()、MOD()、ROUND()等等。

转换函数：将一种数据类型转换成另外一种类型：例如：TO_CHAR()、TO_NUMBER()、TO_DATE()等等。

日期函数：对时间和日期进行操作的函数。例如：TRUNC()、SYSDATE()、ADD_MONTHS()等等。

2、聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如 SUM(x)返回结果集中 x 列的总合。

oracle在函数中能定义函数吗

oralce支持自定义函数的。
语法如下: create or replace function function_name
　　(
　　argu1 datatype1,
　　argu2 datatype2, ........
　　)
　　return datatype
　　is
　　begin
　　end;
　　执行 var v1 varchar2(100)
　　exec :v1:=function_name