在游标中调用函数进行循环 [英] Call a function in a cursor for loop

问题描述

我创建了一个过程，该过程查找一些缺少日期的记录，需要填充。
我编写了一个游标，该游标使用select语句查找这些记录，并打算使用for循环更新它们。

I have created a procedure which finds some records with a missing date which needs to be populated. I have written a cursor which uses a select statement to find these records and was going to use a for loop to update them.

应用程序中已有一个公共函数，如果我将记录ID传递给它，它将返回我需要的日期。我的问题是一种语法，实际上，如何在游标的for循环中调用此公共函数并传递所需的变量以获取日期，然后更新在游标select语句中找到的记录。

There is an existing public function in the application that will return the date I need if I pass the ID of the record to it . My question is a syntax one really, how do I call this public function in a cursor for loop and pass the variable I need to it to get the date back then update the records that I have found in the cursor select statement.

我编写的过程看起来像这样：

The procedure I have written looks a bit like this:

CREATE OR REPLACE PROCEDURE PRO_POPMISSINGDATE IS

CURSOR cur_FindMissingDate IS
 SELECT fieldID,
        field2,
        field3
 FROM table1
      table2
      table3
 WHERE CONDITION 1
       CONDITION 2
       CONDITION 3

BEGIN

FOR rec_cur_FindMissingDate IN cur_FindMissingDate
LOOP


BEGIN

        UPDATE TABLE2
        SET    missingdate = fnc_get_date(fieldID);

        WHERE  field2 = field4

        COMMIT;

    EXCEPTION
        WHEN OTHERS THEN

            dbms_output.put_line('Error updating record ' || SUBSTR(SQLERRM, 1, 250));
            ROLLBACK;
    END;
END LOOP;
END PRO_POPMISSINGDATE;

我在更新语句中收到ORA-00904无效标识符错误，因为它无法识别
fieldID。我显然在这里做了一些完全错误的事情，但是我遇到了一个障碍，任何建议将不胜感激

I am getting an ORA-00904 invalid identifier error in the update statement as it doesnt recognise fieldID. I have evidently done something totally wrong here but I have hit a mental block, any advice would be much appreciated

推荐答案

我认为您意思是：

SET    missingdate = fnc_get_date(rec_cur_FindMissingDate.fieldID);

这篇关于在游标中调用函数进行循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！