Functions in the WITH Clause (for 12c release 1)

 範例 : 

WITH

 FUNCTION getProduct(pInteger IN NUMBER) RETURN NUMBER IS 

 BEGIN 

    RETURN pInteger * pInteger;

 END getProduct;

SELECT LEVEL , getProduct (LEVEL)  from dual

CONNECT BY LEVEL <= 10 

--Using dynamic SQL 

DECLARE

  vSql     VARCHAR2(32767);

  vCursor  SYS_REFCURSOR;

  vMyValue NUMBER;

BEGIN

  vSql := 'WITH

           FUNCTION getProduct(pInteger IN NUMBER) RETURN NUMBER IS 

           BEGIN 

              RETURN pInteger * pInteger;

           END getProduct;

          SELECT  getProduct (LEVEL) myValue  from dual

          CONNECT BY LEVEL <= 10';

  OPEN vCursor FOR vSql;

  LOOP

    FETCH vCursor

      INTO vMyValue;

    EXIT WHEN vCursor%NOTFOUND;

    dbms_output.put_line('My Value => ' || vMyValue);

  END LOOP;

  CLOSE vCursor;

END;