Oracle 12c WITH clause examples

You can define any number of functions as PLSQL inline and can be called in SQL statements

Procedure can be defined but cant be invoked from SQL for results

However procedure results can be retrieved through functions and same function should be called from SQL statements for results

Procedures and functions defined in WITH clause  can't be iterated through for loop cursor

However dynamic sql allows you to work around this restriction

The whole reason for defining the PL/SQL code inline is to improve performance than the regular function

Example

WITH
  FUNCTION get_name(p_name IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Hello! '||p_name||'..'||' Oracle 12c database welcomes you :-)';
  END;

  FUNCTION add_numbers(p_n1 IN NUMBER, p_n2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN NVL(p_n1,0) + NVL(p_n2,0);
  END;
 
  PROCEDURE calcuations (p_mode IN VARCHAR2, p_n1 IN NUMBER, p_n2 IN NUMBER,p_out OUT NUMBER) IS
  l_calc NUMBER;
  BEGIN
   IF p_mode = '+' THEN
             l_calc := p_n1 + p_n2;
    ELSIF p_mode = '-' THEN
          l_calc := p_n1 - p_n2;
   END IF;
    p_out:= l_calc;
  END;
 
  FUNCTION wrap_calculations (p_mode IN VARCHAR2, p_n1 IN NUMBER, p_n2 IN NUMBER) RETURN NUMBER IS
  l_out NUMBER;
  BEGIN
      calcuations (p_mode,p_n1,p_n2,l_out);
      RETURN l_out;
  END;
SELECT get_name(:name), add_numbers (:n1,:n2), wrap_calculations('-',:n1,:n2)
FROM   dual;