PL/SQL Cursor for loop

Question

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

General Grievance · Accepted Answer · 2022-02-09 18:30:12Z

10

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.

edited Feb 9, 2022 at 18:30

General Grievance

5,12039 gold badges40 silver badges60 bronze badges

answered Apr 17, 2012 at 3:56

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1332821 Over a year ago

Yes table have newstreetnumber and newstreetname

A.B.Cade · Accepted Answer · 2012-04-17 07:43:32Z

2

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;

answered Apr 17, 2012 at 7:43

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

1 Comment

Rob van Wijk Over a year ago

+1 This is the way. Although I'd rewrite the "instr(street1,'Cnr',1) >= 1" as "street1 like '%Cnr%'"

Collectives™ on Stack Overflow

PL/SQL Cursor for loop

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related