Oracle PL/SQL With Connect By Example
With Connect By Example
CREATE TABLE employees ( name VARCHAR2(25), department NUMBER(2)); INSERT INTO employees VALUES ('ANDY',4); INSERT INTO employees VALUES ('KATE',2); INSERT INTO employees VALUES ('ANNA',1); INSERT INTO employees VALUES ('GERARD',2); INSERT INTO employees VALUES ('AUDRINA',3); COMMIT; SELECT * FROM employees ; CREATE TABLE top_employees AS SELECT name FROM employees WHERE 1=2; SELECT * FROM top_employees; INSERT INTO top_employee WITH test AS ( SELECT ROWNUM test FROM dual CONNECT BY LEVEL <= (SELECT MAX(department) FROM employee)) SELECT name FROM employees, test WHERE test <= department ORDER BY name; SELECT * FROM top_employees;