Oracle Cursor with getting data from each row

Question

I am new in programming. I want to use procedure to get all the substr in address and put it in my new column 'info'. My procedure now only get the first row of string from address and place it all over the 'info' column. Am I need a loop or something to make my procedure get the right data from each row?

create or replace PROCEDURE update_data
IS

   data_1 VARCHAR2(13 CHAR);

   CURSOR c1 IS
     SELECT substr(t.address, 3, 4)
     FROM table_1 t
     WHERE t.age > 21

BEGIN

   OPEN c1;  

   fetch c1 INTO data_1;

   Update table_1 t
   SET t.info = data_1;

   CLOSE c1;

END;

Thanks for helping me out!!

Joel · Accepted Answer · 2016-05-19 21:19:03Z

One major problem is that you're not limiting your UPDATE statement. You're actually updating EVERY ROW in the entire table. Watch out for that whenever you're writing UPDATE and DELETE statements or you will cause major problems and your co-workers won't thank you. I would suggest you write all of these types of statements as SELECT statements for to ensure that you're properly limiting with the WHERE clause and then convert it to a more dangerous statement.

As for your specific question - yes - you need to loop through every record in the cursor and then update the SINGLE corresponding row in the table.

Here's an updated example:

CREATE OR REPLACE PROCEDURE update_data
IS
    CURSOR c1
    IS
        SELECT primary_key_column, /* SELECT THIS SO THAT YOU CAN IDENTIFY THE SINGLE ROW TO UPDATE */
               SUBSTR(t.address, 3, 4) AS info /*NAME THIS SO IT CAN BE REFERENCED IN THE LOOP */
        FROM   table_1 t
        WHERE  t.age > 21;
BEGIN
    FOR r /* NAME THE ROW */ IN c1 LOOP /* ORACLE AUTMOATICALLY OPENS THE CURSOR HERE */
        UPDATE table_1 t
        SET    t.info = r.info
        WHERE  t.primary_key_column = r.primary_key_column; /* LIMIT THE UPDATE TO THIS ROW ONLY */
    END LOOP; /* ORACLE AUTOMATICALLY CLOSES THE CURSOR HERE */
END;

You could use the OPEN, FETCH, CLOSE method you are currently using with a loop, but I personally think the FOR ... IN syntax is more readable, easier to write, and handles opening and closing cursors for you, so I've used it here.

The main thing to notice is that the UPDATE is now limited to one single record, so that every iteration through the loop will update the same record that you're running SUBSTR on - and NOT the entire table.

Alternatively, this could all be done with a single statement:

UPDATE table_1 t
SET    t.info = SUBSTR(t.address, 3, 4)
WHERE  t.age > 21;

Just make sure you're properly limiting your UPDATE.

Collectives™ on Stack Overflow

Oracle Cursor with getting data from each row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related