PL/SQL quickie returning results

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;

ScottWe