oracle - 如何使用oracle在循环中设置变量？

Question

我有一个循环和一个变量 v_rownum我想为它设置值:

        FOR donvi_rows IN v_donvi
        LOOP
            DECLARE
               v_rownum number;
            SELECT r
            INTO v_rownum
            FROM
            (SELECT ROWNUM AS r, k.Id
            FROM don_vi k
            WHERE k.ParentId = 1 )
            WHERE Id = donvi_rows.Id;

        END LOOP;

但它抛出一个异常:

PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "begin" was substituted for "SELECT" to continue.

我目前正在学习甲骨文。我对它了解不多。请帮我解决我的问题。

Answer 1

使用 Oracle，您需要在块的顶部声明所有变量，在 BEGIN 之前。 :

DECLARE
    ... variable declarations
BEGIN
    ... program logic
END;

你的代码应该是这样的:

DECLARE 
    v_rownum number;
    CURSOR v_donvi IS
      SELECT * FROM whatever;
    ... any other declarations
BEGIN
    FOR donvi_rows IN v_donvi
    LOOP
        SELECT r
          INTO v_rownum
          FROM (
            SELECT ROWNUM AS r, k.Id
            FROM don_vi k
            WHERE k.ParentId = 1
          )
          WHERE Id = donvi_rows.Id;
    END LOOP;
END;