Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· OpenAIAi2 WildChat-4.8M (AI2)

Give me an example of recursive plsql query

  1. User

    Give me an example of recursive plsql query

  2. Assistant

    A recursive PL/SQL query is a query that calls itself repeatedly until a certain condition is met. Recursive queries are typically used for hierarchical data structures such as organization charts or bill-of-materials. Here, we demonstrate an example of a recursive PL/SQL query using the Common Table Expressions (CTEs) feature in Oracle.

    Consider a simple organization hierarchy table named 'employees':

    CREATE TABLE employees (
      id       NUMBER PRIMARY KEY,
      name     VARCHAR2(100) NOT NULL,
      manager_id  NUMBER
    );
    

    Now, let's insert some sample data into the 'employees' table:

    INSERT INTO employees (id, name, manager_id) VALUES (1, 'CEO', NULL);
    INSERT INTO employees (id, name, manager_id) VALUES (2, 'VP A', 1);
    INSERT INTO employees (id, name, manager_id) VALUES (3, 'VP B', 1);
    INSERT INTO employees (id, name, manager_id) VALUES (4, 'Manager A-1', 2);
    INSERT INTO employees (id, name, manager_id) VALUES (5, 'Manager A-2', 2);
    INSERT INTO employees (id, name, manager_id) VALUES (6, 'Manager B-1', 3);
    INSERT INTO employees (id, name, manager_id) VALUES (7, 'Manager B-2', 3);
    

    Now, we will fetch the hierarchy of employees using a recursive PL/SQL query with CTE:

    WITH RECURSIVE employee_hierarchy AS (
      -- Base case: Top-level employees
      SELECT id, name, manager_id, 1 as level
      FROM employees
      WHERE manager_id IS NULL
    
      UNION ALL
    
      -- Recursive case: Fetch the employees under each manager
      SELECT e.id, e.name, e.manager_id, h.level+1
      FROM employees e
      JOIN employee_hierarchy h
      ON e.manager_id = h.id
    )
    SELECT * FROM employee_hierarchy
    ORDER BY level, id;
    

    This query defines a recursive CTE named employee_hierarchy. The CTE has two parts: the base case and the recursive case. The base case retrieves the top-level employees with no manager (manager_id IS NULL). The recursive case retrieves the employees under each manager and increments the hierarchy level. Finally, the query selects all the records from the employee_hierarchy CTE and orders them by their hierarchy level and ID.

    Output:

    ID  NAME        MANAGER_ID  LEVEL
    --  ----------  ----------  -----
     1  CEO                   -  1
     2  VP A                  1  2
     3  VP B                  1  2
     4  Manager A-1           2  3
     5  Manager A-2           2  3
     6  Manager B-1           3  3
     7  Manager B-2           3  3
    
  3. User

    What is your chat gpt version?

  4. Assistant

    I am powered by OpenAI's GPT-3 (third generation of the Generative Pre-trained Transformer).