PL/SQL Cursor for loop

*1. You need a SELECT and a semicolon in the cursor definition

*2. You can add a FOR LOOP over the cursor

For example:

    DECLARE      cursor c1 is        SELECT street1        from test_data;      r1 c1%ROWTYPE;    BEGIN      FOR r1 IN c1 LOOP         ... do your stuff with r1.street1      END LOOP;    END;

You can, alternatively, avoid the explicit cursor definition entirely, e.g.:

FOR r1 IN (SELECT street1 FROM test_data) LOOP   ... do your stuff with r1.street1END LOOP;

*3. Your IF statements cannot include a semicolon - e.g.:

    If    Instr(r1.street1, 'Cnr', 1) >= 1    Then

*4. [edit] so you want to update your table, columns newstreetnumber and newstreetname - in which case you could do something like this:

    DECLARE      cursor c1 is        SELECT street1        from test_data        FOR UPDATE;      r1 c1%ROWTYPE;    BEGIN      FOR r1 IN c1 LOOP         ... do your stuff with r1.street1         UPDATE test_data         SET newstreetnumber = ...            ,newstreetname = ...         WHERE CURRENT OF c1;      END LOOP;    END;

Note, however, that this will not perform well for large volumes, and I'd prefer to do it all in one UPDATE statement.

As Jeffrey Kemp said this can be done in one update statemant:

UPDATE test_data   SET newstreetname = CASE WHEN Instr(street1, ‘Cnr’, 1) >= 1                              THEN Substr(street1, Instr(street1, ‘Cnr’, 1)+3)                            WHEN Instr(street1, ‘PO Box’, 1) >= 1                              THEN Substr(street1, Instr(street1, ‘PO Box’, 1))                            WHEN REGEXP_Instr (street1, '[\d]', 1) = 0                              THEN street1                            WHEN REGEXP_Instr (street1, '[\d]', 1) >= 1                              THEN regexp_substr(street1, '(\w+\s\w+)$')                       END,       newstreetnumber = CASE WHEN .....                       END;