Functions and procedures in the WITH Clause part#2

See Functions and procedures in the WITH Clause part#1 here

If required to return both values before and after update rows in a single SELECT statement, that could be achieved by an PL/SQL function in the WITH clause. The example below updates one line by adding a number (2)

SQL> CREATE TABLE a_test AS SELECT 1 x FROM DUAL;

Table created.

WITH FUNCTION with_function(p_val IN NUMBER) RETURN NUMBER IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  o_val number;
  BEGIN
   execute immediate 'update a_test set x = x + :numval
    where rownum = 1 returning x into :numval2'
    using in p_val, out o_val;
   commit;
    RETURN o_val;
  END;
SELECT with_function(2) new_value, x old_value FROM a_test;

 NEW_VALUE  OLD_VALUE
---------- ----------
	 3	    1

Further reading about using RETURNING INTO clause as a part of a DELETE, INSERT or UPDATE statement should be found in documentation here