Oracle树查询及相关函数

　Oracle树查询的最重要的就是select start with connect by prior 语法了 依托于该语法 我们可以将一个表形结构的中以树的顺序列出来 在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树查询相关的Oracle特性函数等 在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等

　以我做过的一个项目中的表为例 表结构如下

　Sql代码

　CREATE TABLE FLFL

　(

　ID      NUMBER                                NOT NULL

　MC      NVARCHAR ( )

　FLJB    NUMBER

　SJFLID  NUMBER

　)

　FLJB是作为树的级别 在很多查询中可以加快SQL的查询效率 在下面演示的功能基本上不使用这个关键字

　SJFLID存储的是上级ID 如果是顶级父节点 该SJFLID为null（得补充一句 当初的确是这样设计的 不过现在知道 表中最好别有null记录 这会引起全文扫描 建议改成 代替)

　我们从最基本的 *** 作 逐步列出树查询中常见的 *** 作 所以查询出来的节点以家族中的辈份作比方

　 查找树中的所有顶级父节点（辈份最长的人） 假设这个树是个目录结构 那么第一个 *** 作总是找出所有的顶级节点 再根据该节点找到其下属节点

　Sql代码

　SELECT FROM flfl WHERE sjflid IS NULL;

　这是个引子 没用到树型查询

　 查找一个节点的直属子节点（所有儿子） 如果查找的是直属子类节点 也是不用用到树型查询的

　Sql代码

　SELECT FROM flfl WHERE sjflid = ;

　这个可以找到ID为 的直属子类节点

　 查找一个节点的所有 直属子节点（所有后代）

　Sql代码

　SELECT FROM flfl START WITH ID = CONNECT BY sjflid = PRIOR ID;

　这个查找的是ID为 的节点下的所有直属子类节点 包括子辈的和孙子辈的所有直属节点

　 查找一个节点的直属父节点（父亲） 如果查找的是节点的直属父节点 也是不用用到树型查询的

　Sql代码

　SELECT b FROM flfl a JOIN flfl b ON a sjflid = b ID WHERE a ID = ;

　这个找到的是ID为 的节点的直属父节点 要用到同一张表的关联了

　 查找一个节点的所有直属父节点（祖宗）

　Sql代码

　SELECT FROM flfl START WITH ID = CONNECT BY PRIOR sjflid = ID;

　这里查找的就是ID为 的所有直属父节点 打个比方就是找到一个人的父亲 祖父等 但是值得注意的是这个查询出来的结果的顺序是先列出子类节点再列出父类节点 姑且认为是个倒序吧

　上面列出两个树型查询方式 第 条语句和第 条语句 这两条语句之间的区别在于prior关键字的位置不同 所以决定了查询的方式不同 当sjflid = PRIOR ID时 数据库会根据当前的ID迭代出sjflid与该ID相同的记录 所以查询的结果是迭代出了所有的子类记录 而PRIOR ID = sjflid时 数据库会跟据当前的sjflid来迭代出与当前的sjflid相同的id的记录 所以查询出来的结果就是所有的父类结果

　以下是一系列针对树结构的更深层次的查询 这里的查询不一定是最优的查询方式 或许只是其中的一种实现而已

　 查询一个节点的兄弟节点（亲兄弟）

　Sql代码

　SELECT a

　FROM flfl a

　WHERE EXISTS (SELECT

　FROM flfl b

　WHERE a sjflid = b sjflid AND b ID = );

　这里查询的就是与ID为 的节点同属一个父节点的节点了 就好比亲兄弟了

　 查询与一个节点同级的节点（族兄弟） 如果在表中设置了级别的字段 上表中的FLJB 那么在做这类查询时会很轻松 同一级别的就是与那个节点同级的 在这里列出不使用该字段时的实现!

　Sql代码

　WITH tmp AS

　(SELECT     a LEVEL lev

　FROM flfl a

　START WITH a sjflid IS NULL

　CONNECT BY a sjflid = PRIOR a ID)

　SELECT

　FROM tmp

　WHERE lev = (SELECT lev

　FROM tmp

　WHERE ID = )

　这里使用两个技巧 一个是使用了LEVEL来标识每个节点在表中的级别 还有就是使用with语法模拟出了一张带有级别的临时表

　 查询一个节点的父节点的的兄弟节点（伯父与叔父）

　Sql代码

　WITH tmp AS

　(SELECT     flfl LEVEL lev

　FROM flfl

　START WITH sjflid IS NULL

　CONNECT BY sjflid = PRIOR ID)

　SELECT b

　FROM tmp b

　(SELECT

　FROM tmp

　WHERE ID = AND lev = ) a

　WHERE b lev =

　UNION ALL

　SELECT

　FROM tmp

　WHERE sjflid = (SELECT DISTINCT x ID

　FROM tmp x

　tmp y

　(SELECT

　FROM tmp

　WHERE ID = AND lev > ) z

　WHERE y ID = z sjflid AND x ID = y sjflid);

　这里查询分成以下几步 首先 将第 个一样 将全表都使用临时表加上级别 其次 根据级别来判断有几种类型 以上文中举的例子来说 有三种情况 （ ）当前节点为顶级节点 即查询出来的lev值为 那么它没有上级节点 不予考虑 （ ）当前节点为 级节点 查询出来的lev值为 那么就只要保证lev级别为 的就是其上级节点的兄弟节点 （ ）其它情况就是 以及以上级别 那么就要选查询出来其上级的上级节点（祖父） 再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点 最后 就是使用UNION将查询出来的结果进行结合起来 形成结果集

　 查询一个节点的父节点的同级节点（族叔）

　这个其实跟第 种情况是相同的

　Sql代码

　WITH tmp AS

　(SELECT     a LEVEL lev

　FROM flfl a

　START WITH a sjflid IS NULL

　CONNECT BY a sjflid = PRIOR a ID)

　SELECT

　FROM tmp

　WHERE lev = (SELECT lev

　FROM tmp

　WHERE ID = )

　只需要做个级别判断就成了

　基本上 常见的查询在里面了 不常见的也有部分了 其中 查询的内容都是节点的基本信息 都是数据表中的基本字段 但是在树查询中还有些特殊需求 是对查询数据进行了处理的 常见的包括列出树路径等

　补充一个概念 对于数据库来说 根节点并不一定是在数据库中设计的顶级节点 对于数据库来说 根节点就是start with开始的地方

　下面列出的是一些与树相关的特殊需求

　 名称要列出名称全部路径

　这里常见的有两种情况 一种是是从顶级列出 直到当前节点的名称（或者其它属性） 一种是从当前节点列出 直到顶级节点的名称（或其它属性） 举地址为例 国内的习惯是从省开始 到市 到县 到居委会的 而国外的习惯正好相反（老师说的 还没接过国外的邮件 谁能寄个瞅瞅 ）

　从顶部开始

　Sql代码

　SELECT     SYS_CONNECT_BY_PATH (mc / )

　FROM flfl

　WHERE ID =

　START WITH sjflid IS NULL

　CONNECT BY sjflid = PRIOR ID;

　从当前节点开始

　Sql代码

　SELECT     SYS_CONNECT_BY_PATH (mc / )

　FROM flfl

　START WITH ID =

　CONNECT BY PRIOR sjflid = ID;

　在这里我又不得不放个牢骚了 oracle只提供了一个sys_connect_by_path函数 却忘了字符串的连接的顺序 在上面的例子中 第一个SQL是从根节点开始遍历 而第二个SQL是直接找到当前节点 从效率上来说已经是千差万别 更关键的是第一个SQL只能选择一个节点 而第二个SQL却是遍历出了一颗树来 再次PS一下

　sys_connect_by_path函数就是从start with开始的地方开始遍历 并记下其遍历到的节点 start with开始的地方被视为根节点 将遍历到的路径根据函数中的分隔符 组成一个新的字符串 这个功能还是很强大的

　 列出当前节点的根节点

　在前面说过 根节点就是start with开始的地方

　Sql代码

　SELECT     CONNECT_BY_ROOT mc flfl

　FROM flfl

　START WITH ID =

　CONNECT BY PRIOR sjflid = ID;

　connect_by_root函数用来列的前面 记录的是当前节点的根节点的内容

　 列出当前节点是否为叶子

　这个比较常见 尤其在动态目录中 在查出的内容是否还有下级节点时 这个函数是很适用的

　Sql代码

　SELECT     CONNECT_BY_ISLEAF flfl

　FROM flfl

　START WITH sjflid IS NULL

　CONNECT BY sjflid = PRIOR ID;

　connect_by_isleaf函数用来判断当前节点是否包含下级节点 如果包含的话 说明不是叶子节点 这里返回 反之 如果不包含下级节点 这里返回

lishixinzhi/Article/program/Oracle/201311/11193

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

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

1、lower()：大写字符转化成小写的函数

使用举例：select lower(表中字段) from 表名

该sql实现将表里的字段信息中含有字母的全部转成小写。

2、upper()：小写字符转化成大写的函数

使用举例：select upper(表中字段) from 表名

该sql实现将 user表里的字段信息中含有字母的全部转成大写。

扩展资料

oracle常用函数介绍：

1、CONCAT(X,Y)：连接字符串X和Y；

2、LENGTH(X)：返回X的长度；

3、REPLACE(X,old,new)：在X中查找old，并替换成new；

4、SUBSTR(X,start[,length])：返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾；

5、TRUNC(X[,Y])：X在第Y位截断；

6、COUNT()：数据统计；

7、MIN()、MAX()：最小值、最大值。

这个要区分数据库的。如果是informix、mysql、sybase可以直接以字符串形式查询，如果是oracle那就需要加to_date函数。

不用写什么between

and

,直接写大于小于号即可。

写字符串的时候注意标准格式:2012-04-20

10:00:00

或者2012/04/20

10:00:00

oracle中的Function有两种，一种是系统内置的函数（直接拿来用就行），一种是自定义的(自己写的完成特定功能的)。function可以在DML语句中执行。如select upper('AaBbCcDd') '大写' from dual 其中upper就是系统自带的函数，是把参数中的字符串变成大写字符。

自定义函数的语句格式是：

create or replace function 函数名 return 返回值类型 is [声明部分]

begin 主体语句； return 结果; [exception 异常处理部分；] end;

调用：select 函数名 from dual;

或者像执行procedure一样调用也可以 begin 函数名; end;

你这个结构有问题，你这个循环不管几次，总是会在第一次就跳出去。return 应该是在loop结束之后。

去看看上面的查询能不能返回结果。

自己写的程序尽量有异常控制。方便调试。你这种简单的程序，调试一下就能看到问题在哪里了。

　Oracle DB组函数

　&# ; 组函数

　– 类型和语法

　– 使用AVG SUM MIN MAX COUNT

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

　– 组函数中的NULL值

　何谓组函数

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

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

　组函数的类型

　&# ; AVG

　&# ; COUNT

　&# ; MAX

　&# ; MIN

　&# ; STDDEV

　&# ; SUM

　&# ; VARIANCE

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

　组函数 语法

　SELECT group_function(column)

　FROM table

　[WHERE condition]

　[ORDER BY column];

　组函数 语法

　组函数应放在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列中是否存储空值 平均值的计算方法是用付给所有雇员的佣金总和除以公司中的雇员总数( )

lishixinzhi/Article/program/Oracle/201311/18770