SQL 1: not see changes in do block
execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
begin
  delete from TABLE_BBB b where 0 = 0;
  for
    select
      A.ID,
      case
        when A.ID > 2 then (select sum(B.DBL) from TABLE_BBB B where B.AAA_ID < A.ID)
        else A.ID * 10
      end DBL
    from
      TABLE_AAA A
    order by
      A.ID
    into
      :AAA_ID, :DBL
  do
  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);
  end

end


SQL2: Using VIEW - see changes made in DO block
execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
begin
  delete from TABLE_BBB b where 0 = 0;
  for
    select
      A.ID, A.DBL
    from
      TABLE_AAA_VIEW A
    order by
      A.ID
    into
      :AAA_ID, :DBL
  do
  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);
  end

end

SQL 3: execute statement - see changes made in DO block

execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
begin
  delete from TABLE_BBB b where 0 = 0;
  for
    execute statement 'select
      A.ID,
      case
        when A.ID > 2 then (select sum(B.DBL) from TABLE_BBB B where B.AAA_ID < A.ID)
        else A.ID * 10
      end DBL
    from
      TABLE_AAA A
    order by
      A.ID'
    into
      :AAA_ID, :DBL
  do
  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);
  end

end

SQL 4: using cursor - see changes made in DO block

execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
declare variable RC smallint;

declare A_CURSOR cursor for
  (select
      A.ID,
      case
        when A.ID > 2 then (select sum(B.DBL) from TABLE_BBB B where B.AAA_ID < A.ID)
        else A.ID * 10
      end DBL
    from
      TABLE_AAA A
    order by
      A.ID);
begin
  delete from TABLE_BBB b where 0 = 0;

  open A_CURSOR;
  fetch A_CURSOR
  into :AAA_ID, :DBL;
  if (row_count = 0) then
    RC = 0;
  else
    RC = 1;
  while (RC = 1) do
  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);
    fetch A_CURSOR
    into :AAA_ID, :DBL;
    if (row_count = 0) then
      RC = 0;
    else
      RC = 1;
  end

end


SQL 5: select from procedure - do not see changes made in DO block

execute block
as
declare variable AAA_ID integer;
declare variable DBL double precision;
begin
  delete from TABLE_BBB b where 0 = 0;
  for
    execute statement 'select
      A.ID,
      case
        when A.ID > 2 then (select sum(B.DBL) from TABLE_BBB B where B.AAA_ID < A.ID)
        else A.ID * 10
      end DBL
    from
      TABLE_AAA A
    order by
      A.ID'
    into
      :AAA_ID, :DBL
  do
  begin
    insert into TABLE_BBB (AAA_ID, DBL) values (:AAA_ID, :DBL);
  end

end