If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > RETURNING in EXECUTE IMMEDIATE

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Moderator.
 
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.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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;
/




Quote:
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 !
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On