PL/SQL quickie returning results with RETURNING clause

Oh how I love a (good) pun.

In depth blog posts are great, but sometimes little quick examples are all we feel like consuming.

Those out there new(ish) to PL/SQL, familiarise yourselves with the RETURNING clause, it can provide a useful efficiency. See this example here which shows the potential - grab extra information while updating a table instead of requiring another SELECT. You can also use it in conjunction to BULK COLLECT.

CREATE TABLE sw_temp (a NUMBER ,b NUMBER);

 

DECLARE

  l_a  sw_temp.a%TYPE;

  l_b  sw_temp.b%TYPE;

BEGIN

  INSERT INTO sw_temp VALUES (1, 1);

 

  UPDATE sw_temp

  SET a = 2

  RETURNING a, b

  INTO l_a, l_b;

   

  dbms_output.put_line('a:'||l_a);

  dbms_output.put_line('b:'||l_b);

END quickie;

/

 

a:2

b:1

 

PL/SQL procedure successfully completed.

 

DROP TABLE sw_temp;

Source: http://www.grassroots-oracle.com/2011/02/plsql-quickie-returning-results.html