1.select * from EMP t where t.deptno = 10; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7782 CLARK MANAGER 7839 1981/6/9 2450.00 107839 KING PRESIDENT 1981/11/17 500
1. 查询本节点及本节点以下的所有节点: select * from table1 c start with c.p_id='0000000' connect by prior c.id=c.p_id and c.use_yn='Y' order by id ; 2. 查询节点中所有的层级关系 SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT"
存储过程或函数可以返回集合类型,方法很多,今天整理在一个包中,其它情况可照猫画虎. CREATE OR REPLACE PACKAGE PKG_COLLECTION_LHR AUTHID CURRENT_USER AS ----------------------------------------------------------------------------------- -- Created on 2013-05-24 14:37:41 by lhr --Changed on 20
查找员工编号为7369的领导: 1 SELECT LEVEL,E.* FROM EMP E CONNECT BY PRIOR E.MGR = E.EMPNO START WITH E.EMPNO = 78762 ORDER BY LEVEL DESC "start with" -- this identifies all LEVEL=1 nodes in the tree "connect by" -- describes how to walk from the
一.创建数据 1.1.建立表与插入数据 CREATE TABLE DISTRICT ( ID ) NOT NULL, PARENT_ID ), NAME BYTE) NOT NULL ); ALTER TABLE DISTRICT ADD ( CONSTRAINT DISTRICT_PK PRIMARY KEY (ID)); ALTER TABLE DISTRICT ADD ( CONSTRAINT DISTRICT_R01 FOREIGN KEY (PARENT_ID) REFERENCES
Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询.其语法是: 1 [ START WITH condition ]2 CONNECT BY [ NOCYCLE ] conditionThe start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (bos