Oracle数学函数和SQL数学函数的区别

（一）数学函数

1绝对值

S:select abs(-1) value

O:select abs(-1) value from dual

2取整(大)

S:select ceiling(-1001) value

O:select ceil(-1001) value from dual

3取整（小）

S:select floor(-1001) value

O:select floor(-1001) value from dual

4取整（截取）

S:select cast(-1002 as int) value

O:select trunc(-1002) value from dual

5四舍五入

S:ROUND ( numeric_expression , length [ , function ] )

(当 length 为正数时，对小数点右边四舍五入，当 length 为负数且小于小数点前的数字个数时，则对小数点的左边四舍五入,否则返回0，返回与 numeric_expression 相同的类型)如：

select round(123456,4) value（返回值：123460）

O:select round(123456,4) value from dual（返回值：12346）

6e为底的幂

S:select Exp(1) value 27182818284590451

O:select Exp(1) value from dual 271828182

7取e为底的对数

S:select log(27182818284590451) value（返回值：1）

O:select ln(27182818284590451) value from dual（返回值：1）

8取10为底对数

S:select log10(10) value

O:select log(10,10) value from dual

9取平方

S:select SQUARE(4) value

O:select power(4,2) value from dual

10取平方根

组（多行）函数\x0d\ 与单行函数相比，oracle提供了丰富的基于组的，多行的函数。这些函数可以在select或select的having子句中使用，当用于select子串时常常都和GROUP BY一起使用。\x0d\ AVG([{DISYINCT|ALL}])\x0d\ 返回数值的平均值。缺省设置为ALL\x0d\SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM scottempAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)187794118 187794118 1916071413\x0d\ COUNT({|DISTINCT|ALL} )\x0d\ 返回查询中行的数目，缺省设置是ALL,表示返回所有的行。\x0d\ MAX([{DISTINCT|ALL}])\x0d\ 返回选择列表项目的最大值，如果x是字符串数据类型，他返回一个VARCHAR2数据类型，如果X是一个DATA数据类型，返回一个日期，如果X是numeric数据类型，返回一个数字。注意distinct和all不起作用，应为最大值与这两种设置是相同的。\x0d\ MIN([{DISTINCT|ALL}])\x0d\ 返回选择列表项目的最小值。\x0d\ STDDEV([{DISTINCT|ALL}])\x0d\ 返回选者的列表项目的标准差，所谓标准差是方差的平方根。\x0d\ SUM([{DISTINCT|ALL}])\x0d\ 返回选择列表项目的数值的总和。\x0d\ VARIANCE([{DISTINCT|ALL}])\x0d\ 返回选择列表项目的统计方差。\x0d\ 用GROUP BY给数据分组\x0d\ 正如题目暗示的那样组函数就是 *** 作那些已经分好组的数据，我们告诉数据库用GROUP BY怎样给数据分组或者分类，当我们在SELECT语句的SELECT子句中使用组函数时，我们必须把为分组或非常数列放置在GROUP BY子句中，如果没有用group by进行专门处理，那么缺省的分类是将整个结果设为一类。\x0d\select stat,counter() zip_count from zip_codes GROUP BY state;ST ZIP_COUNT-- ---------AK 360AL 1212AR 1309AZ 768CA 3982\x0d\ 在这个例子中，我们用state字段分类；如果我们要将结果按照zip_codes排序,可以用ORDER BY语句，ORDER BY子句可以使用列或组函数。\x0d\select stat,counter() zip_count from zip_codes GROUP BY state ORDER BY COUNT() DESC;ST COUNT()-- --------NY 4312PA 4297TX 4123CA 3982\x0d\ 用HAVING子句限制分组数据\x0d\ 现在你已经知道了在查询的SELECT语句和ORDER BY子句中使用主函数，组函数只能用于两个子串中，组函数不能用于WHERE子串中，例如下面的查询是错误的：\x0d\错误SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDEAND SUM(sale_amount)>10000 GROUP BY sales_clerk\x0d\ 这个语句中数据库不知道SUM()是什么，当我们需要指示数据库对行分组，然后限制分组后的行的输出时，正确的方法是使用HAVING语句：\x0d\SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDEGROUP BY sales_clerkHAVING SUM(sale_amount)>10000;

DBMS_RANDOM包提供了内置的随机数生成器。

大多数语言的随机函数生成器都需要提供一个种子，在Oracle中，选取种子的过程叫做初始化，再执行随机函数之前，可以不用显式地执行初始化，Oracle会自动使用日期、用户ID、进程ID来进行初始化。当然，也可以显式地初始化或者叫自定义初始化。

val参数表示种子数字

字符串长度最多可以为2000

RANDOM函数的返回值取值范围是[ -2 31 ,2 31 ]，因此如果我们若想得到[-1,1]范围内的伪随机结果：

如果是[0,1]，取绝对值即可

若想[m,n] (m<n)这种形式，例如[3,9]

[m,n] (m<n)这种形式的一般表达式为

select abs(dbms_randomrandom/power(2,32))(n-m)+m from dual;

在RANDOM函数的示例中，我们已经讨论了 [0,1] 区间和 [m,n]（m<n） 区间上的随机数的生成方法，而VALUE函数即可以生成 [0,1] 上的随机数，又可以生成 [m,n]（m<n） 上的随机数，

当没有参数时，返回 [0,1] 上的随机数。

low参数表示最小值，high参数表示最大值，返回 [low,high] 区间内的随机数

opt参数控制字符串的大小写格式，可选值及含义如下：

‘u’或'U'：表示大写字母

‘l’或‘L’：表示小写字母

‘a’或‘A’：表示大小写混合

‘x’或‘X’：表示大写字母和数字混合

‘p’或‘P’：表示任意可显示字符

len表示字符串长度

　注意 ）Oracle中的字符串是严格区分大小写的 日期和字符只能在单引号中出现

　 ）把列与列 列与字符连接在一起用 || 符号

　 ）列的别名 紧跟列名 也可以在列名和别名之间加入关键字 AS 以便在别名中包含空格或特殊的字符并区分大小写 使用双引号

　例子

　SELECT    last_name  ||  is a   ||  job_id   AS  Employee Details

　FROM    employees

　where    first_name  like  %s_ ;

　通用函数

　空值是无效的 未指定的 未知的或不可预知的值 空值不是空格或者 包含空值的数学表达式的值都为空值

　这些函数适用于任何数据类型 同时也适用于空值

　NVL (expr expr )

　NVL (expr expr expr )

　NULLIF (expr expr )

　COALESCE (expr expr exprn)

　（ ）NVL (expr expr ) >expr 为NULL 返回expr 不为NULL 返回expr 注意两者的类型要一致

　（ ）NVL (expr expr expr ) >expr 不为NULL 返回expr 为NULL 返回expr expr 和expr 类型不同的话 expr 会转换为expr 的类型

　（ ）NULLIF (expr expr ) >相等返回NULL 不等返回expr

　（ ）COALESCE(expr expr exprn) >列表中第一个非空的表达式是函数的返回值 如果所有的表达式都是空值 最终将返回一个空值

　条件表达式在SQL语句中使用IF THEN ELSE 逻辑 可以使用两种方法:–CASE表达式    CASE  expr  WHEN  parison_expr   THEN  return_expr                     [WHEN  parison_expr   THEN  return_expr                      WHEN  parison_exprn  THEN  return_exprn                     ELSE  else_expr]    END

　–DECODE函数    DECODE ( col | expression   search   result                 [ search result ]                [ default] )

　分组函数

　分组函数作用于一组数据 并对一组数据返回一个值

　组函数语法

　SELECT [column ] group_function(column)

　FROM table

　[WHERE condition]

　[GROUP BY column]

　[ORDER BY column];

　注意 在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中

　(所用包含于SELECT列表中 而未包含于组函数中的列都必须包含于GROUPBY子句中)

　但包含在GROUP BY 子句中的列不必包含在SELECT列表中

　且可以在在GROUP BY子句中包含多个列

　不能在WHERE子句中使用组函数 但可以在HAVING子句中使用组函数

　使用HAVING过滤分组

　 行已经被分组

　 使用了组函数

　 满足HAVING子句中条件的分组将被显示

　SELECT column group_function

　FROM table

　[WHERE condition]

　[GROUP BY group_by_expression]

　[HAVING group_condition]

　[ORDER BY column];

　子查询

　语法

　SELECT select_list

　FROM table

　WHERE expr operator

　(SELECT select_list

　FROM table);

　注意 子查询(内查询) 在主查询之前一次执行完成

　子查询的结果被主查询使用(外查询)

　子查询要包含在括号内

　将子查询放在比较条件的右侧

　除非进行Top N 分析 否则不要在子查询中使用ORDER BY子句

　单行 *** 作符对应单行子查询 多行 *** 作符对应多行子查询

　单行 *** 作符operator                                                     多行 *** 作符operator

　=    等于                                                                            IN     等于列表中的任何一个

　>    大于                                                                            ANY  和子查询返回的任意一个值比较

　>=  大于等于                                                                    ALL   和子查询返回的所有值比较

　<    小于                                                                            EXISTS  功能等价于IN

　<=  小于等于

　<>  不等于

　DECODE和CASE SELECT中除GROUP BY 子句以外的所有子句中 都可以使用单行子查询

　在子查询中使用HAVING子句

　SELECT select_list

　FROM table

　[GROUP BY group_by_expression]

　[HAVING group_condition] expr operator

　(SELECT select_list

　FROM table);

　在FROM子句中使用子查询 例子

　SELECT a last_name a salary a department_id b salavg

　FROM employees a (SELECT department_id AVG(salary) salavg

　FROM employees

　GROUP BY department_id) b

　WHERE a department_id = b department_id

　AND   a salary > b salavg;

　有两个简单例子 以说明 exists 和 in 的效率问题

　 ) select from T where exists(select from T where T a=T a) ;

　T 数据量小而T 数据量非常大时 T <<T 时 ) 的查询效率高

　 ) select from T where T a in (select T a from T ) ;

　T 数据量非常大而T 数据量小时 T >>T 时 ) 的查询效率高

　exists 用法

　请注意 ）句中的有颜色字体的部分 理解其含义

　其中 select from T where T a=T a 相当于一个关联表查询 相当于

　 select from T T      where T a=T a

　但是 如果你当当执行 ） 句括号里的语句 是会报语法错误的 这也是使用exists需要注意的地方

　 exists（xxx） 就表示括号里的语句能不能查出记录 它要查的记录是否存在

　因此 select 这里的 其实是无关紧要的 换成 也没问题 它只在乎括号里的数据能不能查找出来 是否存在这样的记录 如果存在 这 ） 句的where 条件成立

　in 的用法

　继续引用上面的例子

　 ) select from T where T a in (select T a from T )

　这里的 in 后面括号里的语句搜索出来的字段的内容一定要相对应 一般来说 T 和T 这两个表的a字段表达的意义应该是一样的 否则这样查没什么意义

　打个比方 T T 表都有一个字段 表示工单号 但是T 表示工单号的字段名叫 ticketid T 则为 id 但是其表达的意义是一样的 而且数据格式也是一样的 这时 用 ）的写法就可以这样

　 select from T where T ticketid in (select T id from T )

　EXISTS *** 作符

　EXISTS *** 作符检查在子查询中是否存在满足条件的行

　 如果在子查询中存在满足条件的行

　– 不在子查询中继续查找

　– 条件返回TRUE

　 如果在子查询中不存在满足条件的行

　– 条件返回FALSE

　– 继续在子查询中查找

　相关子查询

　相关子查询按照一行接一行的顺序执行 主查询的每一行都执行一次子查询 子查询使用了主查询中的列

　SELECT  column column

　FROM  table outer

　WHERE columnk operator (SELECT  colum colum

　FROM  table

　WHERE expr= outer expr);

　相关更新

　使用相关子查询依据一个表中的数据更新另一个表的数据

　UPDATE  table alias

　SET  column = (SELECT  expression

　FROM  table   alias

　WHERE  lumn = lumn);

　相关删除

　使用相关子查询依据一个表中的数据删除另一个表的数据

　DELETE FROM  table alias

　WHERE  column operator (SELECT  expression

　FROM  table alias

　WHERE lumn = lumn);

　WITH子句

　 使用WITH子句 可以避免在SELECT语句中重复书写相同的语句块

　 WITH子句将该子句中的语句块执行一次 并存储到用户的临时表空间中

　 使用WITH子句可以提高查询效率

　我们可以使用WITH  Clause来定义一个query   block 然后在SELECT   statement的其它地方使用这个query   block 如果在一个很复杂的 Query 里 我们必须重复地使用某个 subquery 那么使用WITH   Clause可以降低Query的复杂度以及提高 performance 因为WITH   Clause 所读出的资料会暂存在User的temporary   tablespace中

　WITH子句应用举例

　WITH

　dept_costs AS (SELECT  d department_name SUM(e salary) AS dept_total

　FROM  employees e departments d

　WHERE  e department_id = d department_id

　GROUP BY  d department_name)

　avg_cost  AS (SELECT  SUM(dept_total)/COUNT() AS dept_avg

　FROM  dept_costs)

　SELECT 

　FROM  dept_costs

　WHERE  dept_total > (SELECT  dept_avg

　FROM  avg_cost)

　ORDER BY  department_name;

　GROUP BY 扩展

　带有ROLLUP和CUBE *** 作的GROUP BY子句

　 使用带有ROLLUP和CUBE *** 作的GROUP BY子句产生多种分组结果

　 ROLLUP产生n + 种分组结果 其是对GROUP BY子句的扩展

　 CUBE产生 的n次方种分组结果 其是对GROUP BY子句的扩展

　注 其中的n指的是group_by_expression的数目

　ROLLUP *** 作符 ROLLUP产生n + 种分组结果 顺序是从右向左

　SELECT  [column ] group_function(column)

　FROM  table

　[WHERE condition]

　[GROUP BY [ROLLUP] group_by_expression]

　[HAVING having_expression]

　[ORDER BY column];

　CUBE *** 作符 CUBE会产生类似于笛卡尔集的分组结果

　SELECT  [column ] group_function(column)

　FROM  table

　[WHERE condition]

　[GROUP BY [CUBE] group_by_expression]

　[HAVING having_expression]

　[ORDER BY column];

　GROUPING函数 能够实现更加直观的分组结果显示提示

　SELECT  [column ] group_function(column) [GROUPING(group_by_expression)]

　FROM  table

　[WHERE condition]

　[GROUP BY  [ROLLUP] [CUBE] group_by_expression]

　[HAVING having_expression]

　[ORDER BY column];

　 GROUPING函数可以和CUBE或ROLLUP结合使用

　 使用GROUPING函数 可以找到哪些列在该行中参加了分组

　 使用GROUPING函数 可以区分空值产生的原因

　 GROUPING函数返回 或

　GROUPING SETS

　 GROUPING SETS是对GROUP BY子句的进一步扩充

　 使用GROUPING SETS可以实现在同一个查询中定义多个分组集

　 Oracle 对GROUPING SETS子句指定的分组集进行分组后用UNION ALL *** 作将各分组结果结合起来

　 Grouping set 的优点

　– 只进行一次分组即可

　– 不必书写复杂的UNION语句

　– GROUPING SETS中包含的分组项越多性能越好

　以下例子实现了对department_id job_id分组 对job_id manager_id分组 最终形成两个分组

　SELECT  department_id job_id manager_id avg(salary)

　FROM  employees

　GROUP BY GROUPING SETS ((department_id job_id) (job_id manager_id));

　复合列

　 复合列是被作为整体处理的一组列的集合

　 使用括号将若干列组成复合列在ROLLUP或CUBE中作为整体进行 *** 作

　 在ROLLUP或CUBE中 复合列可以避免产生不必要的分组结果

　例如 GROUP BY ROLLUP( department_id (job_id manager_id)); //小括号实现复合列

　连接分组集

　 连接分组集可以产生有用的对分组项的结合

　 将各分组集 ROLLUP 和CUBE用逗号连接 Oracle自动在GROUP BY子句中将各分组集进行连接

　 连接的结果是对各分组生成笛卡尔集

　例如 GROUP BY GROUPING SETS(a b) GROUPING SETS(c d);

lishixinzhi/Article/program/Oracle/201311/18505

在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 列的总合。

1、在SQL语句中直接调用，如： select sysdate from dual; 2、在PL/SQL代码中赋值给某个变量，如： declare dt date :=null; begin dt := sysdate; end; / 3、以上说的是Oracle内置函数，如果是自定义函数，则根据入口参数、出口参数的不同，调用方式不尽相同。

在ORACLE中,函数的调用方式有位置表示法调用函数、命名表示法调用函数、混合使用位置表示法和命名表示法调用函数、排除表示法、ql调用表示法 --混合表示法。

甲骨文股份有限公司（Oracle）是全球大型数据库软件公司，总部位于美国加州红木城的红木岸。在2008年，甲骨文股份有限公司是继Microsoft及IBM后，全球收入第三多的软件公司。Oracle数据库产品为财富排行榜上的前1000家公司所采用，许多大型网站也选用了Oracle系统。甲骨文股份有限公司于1989年正式进入中国，在北京、上海、广州和成都均设立了分支机构。