Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Cool RETURNING in EXECUTE IMMEDIATE

    hi,

    just read the following code,

    sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
    RETURNING sal INTO :2';

    EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

    here what does 'RETURNING' do?

    thanx for all answers.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: RETURNING in EXECUTE IMMEDIATE

    RETURNING does what its name suggests: returns the specified value(s) into the specified variable(s). After the update, variable SALARY contains the new value of emp.sal that was set by the update statement, i.e. 2000. Of course, it is more usual to use RETURNING in a situation where the value is not known in advance, as it is here - e.g. if a column is maintained via a trigger.

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: RETURNING in EXECUTE IMMEDIATE

    One classy use of returning I found is:

    A table in which a column value is sequence.nextval.

    This column is the Primary key of the table, in other words, in concurrent user environment, I could never get the primary key value of record that I just inserted.
    (I could not do sequence.curr val as this is concurrent user environment. I could not do a 'select' from table as no other column is unique)

    Here I made use of 'returning' as in:

    create or replace procedure proc_insert(
    vFIRST_NAME IN VARCHAR2,
    vLAST_NAME IN VARCHAR2,
    nID out number) as
    begin

    insert into myTABLE (ID, FIRST_NAME, LAST_NAME)
    values(seq.nextval, vFIRST_NAME, vLAST_NAME) returning ID into nID;
    commit;
    end;
    /




    Originally posted by andrewst
    RETURNING does what its name suggests: returns the specified value(s) into the specified variable(s). After the update, variable SALARY contains the new value of emp.sal that was set by the update statement, i.e. 2000. Of course, it is more usual to use RETURNING in a situation where the value is not known in advance, as it is here - e.g. if a column is maintained via a trigger.
    Oracle can do wonders !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •