oracle递归函数 connect by
在表NEW_TBS 空间下  创建1个适合锻炼 oracle递归函数的表和相关数据 并给出递归函数的具体用法 connect by 

以下是在NEW_TBS表空间下创建一个典型的层次结构表（员工-经理关系表）：
-- 创建员工表(包含自引用关系)
drop table TABLE employees;
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    job_title VARCHAR2(30),
    salary NUMBER(8,2),
    manager_id NUMBER(6),
    hire_date DATE,
    CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) 
    REFERENCES employees(employee_id)
) TABLESPACE NEW_TBS;

-- 创建索引优化递归查询
CREATE INDEX emp_manager_idx ON employees(manager_id) TABLESPACE NEW_TBS;

插入sql语句信息

--日期换成 01-12 格式变成 DD-MM-YYYY 格式请改写
INSERT INTO employees VALUES (100, 'Steven', 'King', 'CEO', 24000, NULL, TO_DATE('17-06-1987', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (101, 'Neena', 'Kochhar', 'VP', 17000, 100, TO_DATE('21-09-1989', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (102, 'Lex', 'De Haan', 'VP', 17000, 100, TO_DATE('13-01-1993', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (103, 'Alexander', 'Hunold', 'Manager', 9000, 102, TO_DATE('03-01-1990', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (104, 'Bruce', 'Ernst', 'Developer', 6000, 103, TO_DATE('21-05-1991', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (105, 'David', 'Austin', 'Developer', 4800, 103, TO_DATE('25-06-1997', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (106, 'Valli', 'Pataballa', 'Developer', 4800, 103, TO_DATE('05-02-1998', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (107, 'Diana', 'Lorentz', 'Developer', 4200, 103, TO_DATE('07-02-1999', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (108, 'Nancy', 'Greenberg', 'Manager', 12000, 101, TO_DATE('17-08-1994', 'DD-MM-YYYY'));
INSERT INTO employees VALUES (109, 'Daniel', 'Faviet', 'Accountant', 9000, 108, TO_DATE('16-08-1994', 'DD-MM-YYYY'));

COMMIT;

 3. CONNECTBY 递归查询用法详解
-- 3.1查询从CEO开始的整个组织架构
  

SELECT 
    LPAD(' ', 2*(LEVEL-1)) || first_name || ' ' || last_name AS employee,
    job_title,
    LEVEL as hierarchy_level,
    SYS_CONNECT_BY_PATH(last_name, '/') AS path
FROM 
    employees
START WITH 
    manager_id IS NULL  -- 从顶级(CEO)开始
CONNECT BY 
    PRIOR employee_id = manager_id  -- 父子关系定义
ORDER SIBLINGS BY last_name;

 结果

自底向上查询（查找某员工的所有上级）
-- 3.2查找ID为107员工的所有上级
 

-- 查找ID为107员工的所有上级
SELECT 
    LEVEL as level_from_bottom,
    employee_id,
    first_name || ' ' || last_name AS manager,
    job_title
FROM 
    employees
START WITH 
    employee_id = 107  -- 从指定员工开始
CONNECT BY 
    PRIOR manager_id = employee_id  -- 反向关系
ORDER BY LEVEL DESC;

 结果

3.3实用递归查询示例

SELECT 
    MAX(LEVEL) as max_depth
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR employee_id = manager_id;

结果

--3.4查找叶子节点（没有下属的员工）

--查找叶子节点（没有下属的员工）
SELECT 
    e.*
FROM 
    employees e
WHERE 
    NOT EXISTS (
        SELECT 1 FROM employees 
        WHERE manager_id = e.employee_id
    );

 结果

--3.5带条件的层次查询（筛选特定层级）
-- 只显示管理层级(LEVEL <= 3)

-- 只显示管理层级(LEVEL <= 3)
SELECT 
    employee_id,
    LPAD(' ', 2*(LEVEL-1)) || first_name || ' ' || last_name AS employee,
    job_title
FROM 
    employees
WHERE 
    LEVEL <= 3
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR employee_id = manager_id;

结果

--3.6使用CONNECT_BY_ISLEAF标识叶子节点

SELECT 
    first_name || ' ' || last_name AS employee,
    job_title,
    CASE CONNECT_BY_ISLEAF 
        WHEN 1 THEN 'Yes' 
        ELSE 'No' 
    END AS is_leaf_node
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR employee_id = manager_id;

结果

4. 递归WITH子句(Oracle 11g R2及以上)

WITH org_chart (employee_id, name, job_title, manager_id, lvl) AS (
    -- 基础查询(顶级节点)
    SELECT 
        employee_id, 
        first_name || ' ' || last_name,
        job_title,
        manager_id,
        1
    FROM 
        employees
    WHERE 
        manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询
    SELECT 
        e.employee_id,
        e.first_name || ' ' || e.last_name,
        e.job_title,
        e.manager_id,
        oc.lvl + 1
    FROM 
        employees e
    JOIN 
        org_chart oc ON e.manager_id = oc.employee_id
)
SELECT 
    LPAD(' ', 2*(lvl-1)) || name AS employee,
    job_title,
    lvl as hierarchy_level
FROM 
    org_chart
ORDER BY lvl, name;

 结果

5. 性能优化建议
确保manager_id列有索引
对于大型层次结构，考虑使用NOCYCLE防止循环引用
复杂查询可使用CONNECT_BY_ROOT获取根节点信息
在Oracle 11g及以上，可使用CONNECT_BY_ISCYCLE检测循环

-- 带循环检测的查询
SELECT 
    employee_id,
    first_name,
    last_name,
    CONNECT_BY_ISCYCLE AS is_cycle
FROM 
    employees
START WITH 
    employee_id = 100
CONNECT BY NOCYCLE 
    PRIOR employee_id = manager_id;

 结果