执行立即更改序列不起作用 [英] execute immediate alter sequence not working

问题描述

我被困在这个非常简单的脚本上.它没有按我预期的那样工作.

I'm stuck on this pretty simple script. It isn't working like I expect it to.

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.

When I run this it doesn't appear to work at all - all of my sequences just stay as they are, and they have not been incremented by a thousand by the dynamic statements. If I check nextval before and after the anonymous block, the difference is only 1, not 1001.

如果我将execute immediate替换为dbms_output.put_line并手动执行生成的命令，则会根据需要更改顺序.

If I replace execute immediate with dbms_output.put_line and execute the generated commands manually the sequences are altered as I want.

我想念什么?

推荐答案

两个alter sequence语句均有效，只是两者之间的增量没有发生.您的循环中的nextval调用未得到评估，因为select语句未将其输出发送到任何地方.在文档中，一条注释恰好指的是您的所用内容.在做:

Both alter sequence statements are working, it's the increment in between that isn't happening. The nextval call in your loop is not being evaluated because the select statement isn't sending its output anywhere. From the documentation, a note that happens to refer to exactly what you are doing:

注意:
如果 dynamic_sql_statement 是SELECT语句，并且您同时省略了 into_clause 和 bulk_collect_into_clause ，则 execute_immediate_statement 永远不会执行.
例如，此语句从不增加序列:

Note:
If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes.
For example, this statement never increments the sequence:

EXECUTE IMMEDIATE 'SELECT S.NEXTVAL FROM DUAL'

因此，您需要将该值选择为某种值:

So you need to select that value into something:

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子句.

I've added a val variable, and an into val clause on the second execute immediate.

为了证明它现在可以工作:

To demonstrate that it works now:

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，这就是您所看到的.

Without the into clause, this came back with 1 rather than 1001, which is what you are seeing.

这篇关于执行立即更改序列不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！