CTE or the WITH Query

 A Common Table Expression (CTE) is the result set of a query which exists temporarily and for use only within the context of a larger query

So for the database to understand we define it using WITH clause before the starting of the CTE query statement. Here statement means SELECT, INSERT, UPDATE, DELETE.

In Oracle 12c and above we can use functions in with clause

WITH 
    FUNCTION get_number RETURN NUMBER IS
    BEGIN
        RETURN 12345;
    END;

SELECT employee_id, first_name, last_name, get_number()
FROM hr.employees ;

The most important point is that we can refer it in the scope of the execution of the WITH clause query. Once the execution is done of the query we cannot use the CTE in any other statements or query. So we can say that the scope of the CTE is still the execution of the statement.

A Recursive common table expression (RCTE) as the keyword recursive suggests references itself. It has a sub query that refers to its own name. It is mainly used when we are dealing with hierarchical data such as company hierarchy positions chart or table because the recursive CTE will continue executing until the entire hierarchy is returned in the result set.