用于PL/SQL中的循环和列

I am new in PL/SQL. I have a problem with loop in this language. I' d like to make loop like this:

我是PL/SQL新手。我对这种语言中的循环有问题。我想这样循环:

FOR nr IN 1..102 
LOOP
  DBMS_OUTPUT.PUT_LINE(nr);
  IF rec.column_||nr IS NULL
    THEN
    DBMS_OUTPUT.PUT_LINE('test');
  END IF;
END LOOP;

I have created a cursor. As you can see I' d like to check all column with names column from column_1 to column_102. Unfortunately || operator does not work for this situation. Do you know some solution to my problem?

我创建了一个游标。如您所见，我想检查从column_1到column_102的所有列。不幸的是||操作符不适用于这种情况。你知道解决我问题的办法吗?

1 个解决方案

#1

You can do this with dynamic PL/SQL. Use an EXECUTE IMMEDIATE statement to execute a string argument as PL/SQL, which you can make up with || as it was intended in the question.

您可以使用动态PL/SQL来实现这一点。使用EXECUTE IMMEDIATE语句执行一个作为PL/SQL的字符串参数，您可以按照问题中的意图使用||进行补偿。

Example:

例子:

BEGIN 
    FOR nr IN 1..102 
    LOOP
        DBMS_OUTPUT.PUT_LINE(nr);
        EXECUTE IMMEDIATE 
            'BEGIN ' || 
            'IF rec.column.' || nr ||' is null THEN ' ||
                'DBMS_OUTPUT.PUT_LINE(''test''); ' ||
            'END IF; ' || 
            'END; ';
    END LOOP;
END;

Or you could also assign rec.column.' || nr ||' is null to a variable and make the PUT_LINE outside the EXECUTE IMMEDIATE part:

或者你也可以分配rc .column。' || nr ||'对变量为null，使执行直接部分外的PUT_LINE为:

UPDATE: It seems it is not possible to bind BOOLEAN variables, so I've modified the example to use a NUMBER.

更新:看起来绑定布尔变量是不可能的，所以我修改了示例以使用一个数字。

UPDATE 2: There is a possible efficiency improvement, altough maybe not suitable in this case. Use a constant VARCHAR for the dynamic SQL, and pass in nr with a binded variable. This is even more efficient than using native SQL if in a large loop. I don't think 'rec.column.:arg is null would execute as 'rec.column.1 is null, though.

更新2:有可能提高效率，但是在这种情况下可能不合适。动态SQL使用常量VARCHAR，并使用绑定的变量传入nr。这甚至比在大循环中使用本机SQL更有效。我不认为rec.column。:arg为null将执行为“rec.column”。1等于零。

 DECLARE
    isnull NUMBER;
 BEGIN 
    FOR nr IN 1..102 
    LOOP
        DBMS_OUTPUT.PUT_LINE(nr);
        EXECUTE IMMEDIATE 
            'BEGIN ' || 
                'IF rec.column.' || nr ||' IS NULL THEN ' || 
                    ':x:=1; ' || 
                'ELSE ' ||
                    ':x:=0; ' ||
                'END IF; ' ||
            'END; ' 
            USING OUT isnull;
        IF isnull = 1 THEN 
            DBMS_OUTPUT.PUT_LINE('test');
        END IF;
    END LOOP;
END;

UPDATE 3: Seeing that:

更新3:看到:

It is not possible to access rec inside the dynamic SQL statement because it is undefined (out of scope),

不可能在动态SQL语句中访问rec，因为它没有定义(超出范围)，
It seems not possible to pass a non-sql type as an argument to the dynamic statement (record, cursor)

似乎不可能将非sql类型作为参数传递给动态语句(记录、游标)

A possible workaround is to bind some id columns (SQL Type) to the dynamic statement, and use a select clause to find out if the current column is null:

一种可能的解决方案是将一些id列(SQL类型)绑定到动态语句，并使用select子句查找当前列是否为null:

DECLARE
        isnull NUMBER;
        rec_id NUMBER; -- Identifier of the fetched record
     BEGIN 
        rec_id := rec.id;
        FOR nr IN 1..102 
        LOOP
            DBMS_OUTPUT.PUT_LINE(nr);
            EXECUTE IMMEDIATE 
                'SELECT 1 FROM my_table WHERE id = :idarg ' ||
                   ' AND column_' || nr || ' IS NULL'
              INTO isnull USING rec_id;
            IF isnull = 1 THEN 
                DBMS_OUTPUT.PUT_LINE('test');
            END IF;
        END LOOP;
    END;

#1