PL/SQL Cursor for loop

database-cursor, for-loop, oracle, plsql

Solution

You need a SELECT and a semicolon in the cursor definition

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.street1
END LOOP;

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

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

[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.

Problem

I believe I need a cursor for loop to go through the street1 column from table test_data. I have a program which needs to test each row from the table. This is what I have so far: ``` cursor c1 is street1 from test_data Begin If Instr(street1, ‘Cnr’, 1) >= 1; Then Newstreetname := Substr(street1, Instr(street1, ‘Cnr’, 1)+3); Else if Instr(street1, ‘PO Box’, 1) >= 1; Then Newstreetname:= Substr(street1, Instr(street1, ‘PO Box’, 1)); Else if REGEXP_ Instr (street1, [\d], 1) = 0; Then Newstreetname:= street1; Else if REGEXP_ Instr (street1, [\d], 1) >= 1; Then Newstreetnumber:= regexp_substr(street1, '\d+(\s|\/)(\d+)?-?(\d+)?(\w {1})?'); Newstreetname:= regexp_substr(street1, '(\w+\s\w+)$'); End ```

Original source