PL/SQL Cursor for loop
34,980
Solution 1
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
andnewstreetname
- 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.
Solution 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;
Author by
user1332821
Updated on July 31, 2022Comments
-
user1332821 almost 2 years
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
-
user1332821 about 12 yearsYes table have newstreetnumber and newstreetname
-
Rob van Wijk about 12 years+1 This is the way. Although I'd rewrite the "instr(street1,'Cnr',1) >= 1" as "street1 like '%Cnr%'"