CODEWITHSUNDEEP
sqloracle-databaseplsqlcursor
user3357925
user3357925

Reputation: 29

Oracle PL/SQL cursor update

I'm using oracle. My SQL skills are very bad, I want to update information from a query that I have obtained through the use of a cursor, I've read about using the WHERE CURRENT OF statement, but I don't see how that can fit into my current code. Does anyone mind lending a helping hand? I want to allow a calling program to update a row in the cursor (I want to update the race location) returned by the query in my current code. Here's my code so far:

DECLARE 
l_race_rec race%rowtype;

CURSOR Query1
IS
  SELECT * 
    FROM RACE 
   WHERE Race_Time='22-SEP-14 12.00.00.000000000'; 
BEGIN
OPEN Query1;
LOOP
FETCH query1 INTO l_race_rec;
EXIT WHEN query1%notfound;

dbms_output.put_line( l_race_rec.raceid || ', ' || l_race_rec.race_location || ', ' || 
l_race_rec.race_type || ', ' || l_race_rec.race_time || ', ' || l_race_rec.sex || ', ' || 
l_race_rec.minage || ', ' || l_race_rec.maxage );
END LOOP;
CLOSE Query1;    
END;

Upvotes: 0

Views: 18221

Answers (2)

wieseman
wieseman

Reputation: 176

Why don't you use a cursor for loop.

...
for row in query1
loop
dbms_output.put_line(row.raceid || ', ' ||
                     row.race_location || ', ' || 
                     row.race_type || ', ' ||
                     row.race_time || ', ' ||
                     row.sex || ', ' || 
                     row.minage || ', ' ||
                     row.maxage );

UPDATE RACE
  SET SOME_FIELD = nSome_value
  WHERE CURRENT OF QUERY1;

end loop;
...

In this way there no need to open and to close a cursor.

Keep in mind that a cursor for loop works better for a cursor with more than 1 row as result.

Good luck.

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Here's an example to get you going:

DECLARE 
  l_race_rec race%rowtype;

  CURSOR Query1 IS
    SELECT * 
      FROM RACE 
      WHERE Race_Time = '22-SEP-14 12.00.00.000000000'; 

  nSome_value  NUMBER := 42;
BEGIN
  OPEN Query1;

  LOOP
    FETCH query1 INTO l_race_rec;
    EXIT WHEN query1%notfound;

    dbms_output.put_line(l_race_rec.raceid || ', ' ||
                         l_race_rec.race_location || ', ' || 
                         l_race_rec.race_type || ', ' ||
                         l_race_rec.race_time || ', ' ||
                         l_race_rec.sex || ', ' || 
                         l_race_rec.minage || ', ' ||
                         l_race_rec.maxage );

    UPDATE RACE
      SET SOME_FIELD = nSome_value
      WHERE CURRENT OF QUERY1;
  END LOOP;

  CLOSE Query1;    
END;

Share and enjoy.

Upvotes: 3

Related Questions