Выполнить немедленное изменение последовательности не работает

Question

Я застрял на этом довольно простом сценарии. Это не работает, как я ожидаю.

declare
 st VARCHAR(1024);
begin
  for x in (SELECT sequence_name FROM USER_SEQUENCES) loop
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1000';
      execute immediate st;
      st := 'select ' || x.sequence_name ||  '.nextval from dual';
      execute immediate st;
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1';
      execute immediate st;
  end loop;
end;
/

Когда я запускаю это, это, кажется, не работает вообще - все мои последовательности просто остаются, как они есть, и они не были увеличены на тысячу динамическими утверждениями. Если я проверю nextval до и после анонимного блока разница всего 1, а не 1001.

Если я заменю execute immediate с dbms_output.put_line и выполнить сгенерированные команды вручную, последовательности меняются, как я хочу.

Что мне не хватает?

Answer 1

И то и другое alter sequence заявления работают, это прирост между ними не происходит. nextval вызов в вашем цикле не оценивается, потому что оператор выбора никуда не отправляет свои выходные данные. Из документации, заметка, которая относится именно к тому, что вы делаете:

Замечания:
Если dynamic_sql_statement является SELECT оператора, и вы пропускаете в into_clause и bulk_collect_into_clause, затем execute_immediate_statement никогда не выполняется.
Например, этот оператор никогда не увеличивает последовательность:

EXECUTE IMMEDIATE 'SELECT S.NEXTVAL FROM DUAL'

Так что вам нужно выбрать это значение во что-то:

declare
 st VARCHAR(1024);
 val number;
begin
  for x in (SELECT sequence_name FROM USER_SEQUENCES) loop
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1000';
      execute immediate st;
      st := 'select ' || x.sequence_name ||  '.nextval from dual';
      execute immediate st into val;
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1';
      execute immediate st;
  end loop;
end;
/

Я добавил val переменная и into val пункт на второй выполнить немедленно.

Чтобы продемонстрировать, что это работает сейчас:

create sequence s42;

Sequence s42 created.

declare
 st VARCHAR(1024);
 n number;
begin
  for x in (SELECT sequence_name FROM USER_SEQUENCES) loop
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1000';
      execute immediate st;
      st := 'select ' || x.sequence_name ||  '.nextval from dual';
      execute immediate st into n;
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1';
      execute immediate st;
  end loop;
end;
/

anonymous block completed

select s42.nextval from dual;

   NEXTVAL
----------
      1001 

Без into пункт, это вернулось с 1, а не 1001, что вы видите.

Answer 2

restart start with Синтаксис в 12c может упростить шаги:

create sequence test_sequence;

declare
 st VARCHAR(1024);
begin
  for x in (SELECT sequence_name, last_number FROM USER_SEQUENCES) loop
      st := 'ALTER SEQUENCE ' || x.sequence_name
          || ' RESTART START WITH ' || to_char(x.last_number+1000);
      execute immediate st;
  end loop;
end;
/

select test_sequence.nextval from dual;

NEXTVAL
-------
   1001