Oracle PL/SQL - PL SQL SQL Statement RETURNING clause

Introduction

The following code uses RETURNING clause to get value.
Demo

SQL>
SQL> drop table dept;

Table dropped.-- from w w  w  .j a  va 2  s  . c  o  m

SQL> create table dept(
  2    deptno number(2,0),
  3    dname  varchar2(14),
  4    loc    varchar2(13),
  5    constraint pk_dept primary key (deptno)
  6  );

Table created.
SQL>
SQL> insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
SQL> insert into dept values(20, 'RESEARCH', 'DALLAS');
SQL> insert into dept values(30, 'SALES', 'CHICAGO');

SQL> insert into dept values(40, 'OPERATIONS', 'BOSTON');
SQL>
SQL> create or replace function f_appendDept_tx
  2        (i_deptNo NUMBER, i_column_tx VARCHAR2,
  3         i_append_tx VARCHAR2)
  4         return VARCHAR2
  5  is
  6        v_out_tx VARCHAR2(256);
  7        v_sql_tx VARCHAR2(2000);
  8  begin
  9        v_sql_tx:='update dept set '||i_column_tx||'='||
 10                 i_column_tx||'||:1 where deptNo=:2'||chr(10)||
 11                 ' returning '||i_column_tx||' into :3';
 12        DBMS_OUTPUT.put_line(v_sql_tx);
 13        execute immediate v_sql_tx
 14             using i_append_tx, i_deptNo, out v_out_tx;
 15        return v_out_tx;
 16  end f_appendDept_tx;
 17  /

Function created.

SQL>
SQL>
SQL> begin
  2        DBMS_OUTPUT.put_line(f_appendDept_tx(30,'dname','+'));
  3  end;
  4  /
update dept set dname=dname||:1 where deptNo=:2
 returning dname into :3
SALES+

PL/SQL procedure successfully completed.

SQL>
SQL>