oracle PL/SQL 游标循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10183061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL Cursor for loop
提问by user1332821
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.
我相信我需要一个游标循环来遍历表 test_data 中的 street1 列。我有一个程序需要测试表中的每一行。
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
回答by Jeffrey Kemp
*1. You need a SELECT and a semicolon in the cursor definition
*1. 您需要在游标定义中使用 SELECT 和分号
*2. You can add a FOR LOOP over the cursor
*2. 您可以在光标上添加 FOR LOOP
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;
*3. Your IF statements cannot include a semicolon - e.g.:
*3. 您的 IF 语句不能包含分号 - 例如：
If
Instr(r1.street1, 'Cnr', 1) >= 1
Then
*4. [edit] so you want to update your table, columns newstreetnumberand newstreetname- in which case you could do something like this:
*4. [编辑] 所以你想更新你的表、列newstreetnumber和newstreetname- 在这种情况下你可以做这样的事情：
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.
但是，请注意，这对于大容量而言效果不佳，我更愿意在一个 UPDATE 语句中完成所有操作。
回答by A.B.Cade
As Jeffrey Kemp said this can be done in one update statemant:
正如 Jeffrey Kemp 所说，这可以在一个更新声明中完成：
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;
