cursor - 如何在 Firebird 中使用游标将行插入表中？

Question

同学们，我是Firebird的新手。我试图在游标循环中调用过程 (p_procedure) 并将结果插入表 (tmp_port)。

execute block
as
declare contr integer;
declare IN$DT date;
declare cur_list_of_cont cursor for (select first 100 contracts.doc from TABLE_1);

begin

delete from  tmp_port;
IN$DT = getdate()-2;

open cur_list_of_cont;
  while (ROW_COUNT > 0) do
  begin
    fetch cur_list_of_cont into contr;


     insert into tmp_port (
        DT,
        ....)

        select
            :IN$DT as DT,
            ...
        from p_procedure (0,  :contr , :IN$DT);


    if (ROW_COUNT = 0) then leave;
    suspend;
  end
  close cur_list_of_cont;

end;

问题是从 cur_list_of_cont 中只处理了第一行。为什么其他99行没有处理？

更新

FireBird 服务器版本为 2.5

更新

在这个实现中它工作正常:

begin

IN$DT = getdate()-2;

FOR select first 100 contracts.doc from TABLE_1
 INTO :contr 
 DO
  BEGIN
  insert into tmp_port (
        DT,
        ....)

        select
            :IN$DT as DT,
            ...
        from p_procedure (0,  :contr , :IN$DT);

  END
  SUSPEND;

end;

如果第一个例子也能用就更好了。怎么做？

Answer 1

问题是您错误地使用了 ROW_COUNT。如 Firebird 2.5 language reference 中所述:

Description: The ROW_COUNT context variable contains the number of rows affected by the most recent DML statement (INSERT, UPDATE, DELETE, SELECT or FETCH) in the current trigger, stored procedure or executable block.

...

• After a FETCH from a cursor, ROW_COUNT is 1 if a data row was retrieved and 0 otherwise. Fetching more records from the same cursor does not increment ROW_COUNT beyond 1.

如果没有删除任何内容，while (ROW_COUNT > 0) 可能为 false，如果您的过程没有返回任何行并因此没有插入任何行，您也可以退出循环。

如果您查看 example of using FETCH ，您可以将代码修改为:

open cur_list_of_cont;
fetch cur_list_of_cont into contr;
while (row_count > 0) do
begin
    insert into tmp_port (
          DT,
          ....)
      select
          :IN$DT as DT,
          ...
      from p_procedure (0,  :contr , :IN$DT);
    fetch cur_list_of_cont into contr;
end
close cur_list_of_cont;

但是，根据您的代码，您应该考虑使用 FOR SELECT，因为在大多数情况下它更容易。