PL / SQL游标for循环 [英] PL/SQL Cursor for loop

问题描述

我相信我需要一个游标for循环来从表test_data中通过street1列。我有一个程序需要测试表中的每一行。

这是我到目前为止：

 光标c1是
 street1 
从test_data 
 
开始
如果Instr（street1，'Cnr'，1）> ; = 1; 
 then 
 Newstreetname：= Substr（street1，Instr（street1，'Cnr'，1）+3）; 
否则如果
 Instr（street1，'PO Box'，1）> = 1; 
 then 
 Newstreetname：= Substr（street1，Instr（street1，'PO Box'，1））; 
否则如果
 REGEXP_ Instr（street1，[\d]，1）= 0; 
 then 
 Newstreetname：= street1; 
否则如果
 REGEXP_Instr（street1，[\ d]，1）> = 1; 
然后
 Newstreetnumber：= regexp_substr（street1，'\d +（\s | \ /）（\d +）？ -  \\（\d +）？ ）？ 
 Newstreetname：= regexp_substr（street1，'（\w + \s\w +）$'）; 
结束
  

解决方案

* 1。在游标定义

* 2中需要一个SELECT和一个分号。您可以在光标上添加FOR LOOP

例如：

  DECLARE 
 cursor c1 is 
 SELECT street1 
 from test_data; 
 r1 c1％ROWTYPE; 
 BEGIN 
 for r1 IN c1 LOOP 
 ...使用r1.street1 
执行您的操作END LOOP; 
 END;或者，您可以完全避免显式的游标定义，例如：
 
 
 b 
 
  FOR r1 IN（SELECT street1 FROM test_data）LOOP 
 ...使用r1.street1 
执行您的操作END LOOP; 
  
 * 3。您的IF语句不能包含分号 - 例如：
 如果
 Instr（r1.street1，'Cnr'，1 ）> = 1 
然后
  
 * 4。因此您要更新表格，列 newstreetnumber 和 newstreetname   - 在这种情况下，您可以做这：
  DECLARE 
光标c1是
 SELECT street1 
从test_data 
更新; 
 r1 c1％ROWTYPE; 
 BEGIN 
 for r1 IN c1 LOOP 
 ...使用r1.street1 
更新test_data 
 SET newstreetnumber = ... 
，newstreetname = ... 
当前c1; 
 END LOOP; 
 END; 
  
但是，请注意，这不适合大容量，它都在一个UPDATE语句中。
 
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

解决方案
*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.street1
END 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.

                        
这篇关于PL / SQL游标for循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！