Home » SQL & PL/SQL » SQL & PL/SQL » Returning updated rows with REF CURSOR?
Returning updated rows with REF CURSOR? [message #596929] Fri, 27 September 2013 10:10 Go to next message
nlefk83
Messages: 1
Registered: September 2013
Location: United Kingdom
Junior Member
Hi all. I am new to PL/SQL, trying to find my way around things. The following code is indicative of what I'd like to do (as in not correct at all Smile). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?

-- Incorrect indicative example 1.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;

-- Incorrect indicative example 2.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;

Unfortunately, I don't seem to get good results about this from google. Any help/indicators would be very very appreciated!
Re: Returning updated rows with REF CURSOR? [message #596931 is a reply to message #596929] Fri, 27 September 2013 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
Realize that we don't know exactly what you are trying to accomplish.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Returning updated rows with REF CURSOR? [message #596932 is a reply to message #596929] Fri, 27 September 2013 10:23 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

I think you are looking for the RETURNING INTO clause,
http://docs.oracle.com/cd/E16655_01/appdev.121/e17622/tuning.htm#sthref1054
but you need to learn the UIPDATE statement first, man.
icon5.gif  Re: Returning updated rows with REF CURSOR? [message #596933 is a reply to message #596929] Fri, 27 September 2013 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Any help/indicators would be very very appreciated!


To do what?

Re: Returning updated rows with REF CURSOR? [message #596974 is a reply to message #596929] Sat, 28 September 2013 07:15 Go to previous message
Solomon Yakobson
Messages: 1792
Registered: January 2010
Senior Member
You can't do this with cursor directly. What you can do is:

SQL> drop table emp1 purge
  2  /

Table dropped.

SQL> create table emp1
  2    as
  3      select  empno,
  4              ename,
  5              sal,
  6              deptno
  7        from  emp
  8  /

Table created.

SQL> set serveroutput on
SQL> declare
  2      v_empno_tbl sys.OdciNumberList;
  3      v_cur sys_refcursor;
  4      v_rec emp1%rowtype;
  5  begin
  6      update emp1
  7         set sal = sal + 1000
  8       where deptno = 10
  9       returning empno
 10         bulk collect
 11         into v_empno_tbl;
 12      open v_cur
 13        for
 14          select  *
 15            from  emp1
 16            where empno in (select * from table(v_empno_tbl));
 17      loop
 18        fetch v_cur
 19          into v_rec;
 20        exit when v_cur%notfound;
 21        dbms_output.put_line(v_rec.ename || ' new salary is ' || v_rec.sal);
 22      end loop;
 23      close v_cur;
 24  end;
 25  /
CLARK new salary is 3450
KING new salary is 6000
MILLER new salary is 2300

PL/SQL procedure successfully completed.

SQL>


Keep in mind, it isn't 100% equivalent to your "code". If returning clause would allow cursor, such cursor would be read consistent to "right after update" point-in-time, while in code I posted there is a gap. So if some other session committed changes to table emp1 updated rows (deptno = 10) within that gap, select in code I posted will see such changes.

SY.
Previous Topic: is user() a keyword in mysql
Next Topic: complex constraint
Goto Forum:
  


Current Time: Fri Apr 18 03:10:00 CDT 2014

Total time taken to generate the page: 0.09456 seconds