使用 START WITH 从查询创建序列

2021-12-24 00:00:00 sequence oracle plsql oracle9i ora-01722

如何创建 START WITH 值来自查询的序列?

How can I create a Sequence where my START WITH value comes from a query?

我正在尝试这种方式:<代码>CREATE SEQUENCE "Seq" INCREMENT BY 1 START WITH (SELECT MAX("ID") FROM "Table");

但是，我收到了 ORA-01722 错误

But, I get the ORA-01722 error

推荐答案

START WITH CLAUSE 接受一个整数.您可以动态地形成创建序列"语句，然后使用立即执行来执行它来实现此目的.

The START WITH CLAUSE accepts an integer. You can form the "Create sequence " statement dynamically and then execute it using execute immediate to achieve this.

declare
    l_new_seq INTEGER;
begin
   select max(id) + 1
   into   l_new_seq
   from   test_table;

    execute immediate 'Create sequence test_seq_2
                       start with ' || l_new_seq ||
                       ' increment by 1';
end;
/

查看这些链接.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_6014.htm
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm

相关文章