Give me an example of recursive plsql query
- User
Give me an example of recursive plsql query
- 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 theemployee_hierarchyCTE 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 - User
What is your chat gpt version?
- Assistant
I am powered by OpenAI's GPT-3 (third generation of the Generative Pre-trained Transformer).