发布日期:2015-11-05 11:19 来源: 标签: 数据库 oracle教程 oracle函数 oracle甲骨文连接
本章我们主要学习oracle数据库实际应用中常用甲骨文连接函数有哪些?下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。
通过[ nocycle ] <条件>开始<条件>连接语法1
通过[ nocycle ] <条件>连接语法2开始<条件>
1、CONNECT BY PRIOR
确定父行和子行之间的关系的一个条件
CONNECT BY <child_value> = <parent_value> 
eg:
conn hr/hr
SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id; 

2、START WITH
指定一个条件,该条件将该行标识为一个分层查询的根(即)
START WITH (column_name) = <value> 
eg:
conn hr/hr
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;
set pagesize 0
col last_name format a30
SELECT LEVEL, LPAD(' ', LEVEL*3) || LAST_NAME AS LAST_NAME
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;
set pagesize 20  

3、ORDER SIBLINGS BY
兄弟姐妹保留任何订购在分级查询子句指定并应用order_by_clause到层次结构的兄弟姐妹
ORDER SIBLINGS BY (column_name) 
eg:
conn hr/hr
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;  

4、CONNECT_BY_ROOT
connect_by_root是一元运算符,只有层次查询是有效的。当您使用这个操作符限定列时,将使用来自根行的数据返回列值。不能指定与条件的开始或连接。
下面的示例返回110个部门中的每一位员工的最后一个名字,每一个经理都在层次结构中,管理者和员工之间的层次,以及两者之间的路径:
conn hr/hr
set linesize 121
col emp format a20
col mgr format a20 
SELECT "Name", SUM(salary) "Total_Salary"
FROM (
  SELECT CONNECT_BY_ROOT last_name "Name", salary
  FROM employees
  WHERE department_id = 110
  CONNECT BY PRIOR employee_id = manager_id)
GROUP BY "Name";
-- Thanks Colin for the correction 

5、CONNECT_BY_ISCYCLE Pseudocolumn
它的connect_by_iscycle返回1,如果当前行有一个孩子,也就是它的祖先。否则,返回0
conn hr/hr
UPDATE employees SET manager_id = 145
WHERE employee_id = 100;
set linesize 121
col path format a50
SELECT last_name, LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
ERROR:
ORA-01436: CONNECT BY loop in user data
SELECT last_name, CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
AND LEVEL <= 4;
ROLLBACK;

6、CONNECT_BY_ISLEAF Pseudocolumn
《城市_连接_虚列isleaf归来1如果冰流行的一片叶子的树定义城市的连接状态。否则它返回0。这个信息是否一个给定的indicates行可以进一步扩大两个更多的系列节目。
conn hr/hr
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE level <= 3
AND department_id = 80
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
AND LEVEL <= 4; 

7、LEVEL Pseudocolumn

对于每一行由一个分层查询返回,它返回1一根排水平,2一根的孩子,等等
conn hr/hr

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id; 
SELECT LPAD(' ',2*(LEVEL-1)) || last_name ORG_CHART,
employee_id, manager_id, job_id
FROM employees
START WITH job_id = 'AD_VP'
CONNECT BY PRIOR employee_id = manager_id;

8、SYS_CONNECT_BY_PATH
返回从根节点到节点的列值的路径,用以将返回的每一行返回的列值分隔为一个列值
SYS_CONNECT_BY_PATH(<column>, <char>)  
eg:
conn scott/tiger
col empname format a20
col cbp format a30
SELECT LPAD(' ', 2*LEVEL, ' ' ) || ename empName, dname, job,
sys_connect_by_path( ename, '/' ) cbp
FROM emp e, dept d
WHERE e.deptno = d.deptno
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY job;
SELECT LPAD(' ', 2*LEVEL, ' ' ) || ename empName, dname, job,
sys_connect_by_path(empno, '.') cbp
FROM scott.emp emp, scott.dept dept
WHERE emp.deptno = dept.deptno
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;  

9、Function Demo
使用函数接收当前节点和当前节点的父母
CREATE OR REPLACE FUNCTION permissions_sub_tree_root (
the_id IN NUMBER,
the_level IN NUMBER)
RETURN NUMBER IS
sub_tree_root NUMBER(10);
BEGIN
  SELECT id
  INTO sub_tree_root
  FROM hierarchy
  WHERE level = the_level
  -- Connect 'upwards', i.e. find the parent
  CONNECT BY PRIOR PARENT = id
  START WITH ID = the_id;
  RETURN sub_tree_root;
END permissions_sub_tree_root;
/
SELECT id, name, username
FROM (
  SELECT ID, PARENT, NAME,
  permissions_sub_tree_root(id, LEVEL) ROOT
  FROM hierarchy
  CONNECT BY PRIOR id = PARENT) HIERARCHY, permissions
WHERE ROOT = hierarchy_id;

10、GROUP BY Demo
组与connect_by_root和演示connect_by_prior
conn hr/hr
SELECT name, SUM(salary) "Total_Salary"
FROM (
  SELECT CONNECT_BY_ROOT last_name name, salary
  FROM employees
  WHERE department_id = 110
  CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name;

11、Demos
1)缩进
conn hr/hr
col lname format a30
SELECT LPAD(' ', level*2, ' ') || last_name LNAME, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
START WITH employee_id = 100
CONNECT BY PRIOR e.employee_id = e.manager_id;
2)分层查询
在WHERE子句中的条件[不],如果右手边是一个查询条件,你不能对条件的左边使用水平。然而,你可以指定一个查询的FROM子句来实现相同的结果。例如,下面的语句是无效的:
SELECT employee_id, last_name FROM employees
WHERE (employee_id, LEVEL)
IN (SELECT employee_id, 2 FROM employees)
START WITH employee_id = 2
CONNECT BY PRIOR employee_id = manager_id;
但下面的说法是正确的因为它封装在FROM子句中包含信息的查询:
SELECT v.employee_id, v.last_name, v.lev
FROM (
  SELECT employee_id, last_name, LEVEL lev
  FROM employees v
  START WITH employee_id = 100
  CONNECT BY PRIOR employee_id = manager_id) v
WHERE (v.employee_id, v.lev) IN (
  SELECT employee_id, 2 FROM employees);

相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。