使用 START WITH from Query 创建序列

发布于 2024-09-14 02:44:22 字数 146 浏览 13 评论 0原文

如何创建一个序列，其中我的 START WITH 值来自查询？

我正在尝试这样： <代码> 创建序列“Seq”增量 1 开始于 (SELECT MAX("ID") FROM "Table");

但是，我收到 ORA-01722 错误

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

想挽留 2024-09-21 02:44:22

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

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

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;
/

Check out these links.

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

回复收藏 0 原文

坦然微笑 2024-09-21 02:44:22

这里我有我的例子，效果很好：

declare
 ex number;
begin
  select MAX(MAX_FK_ID)  + 1 into ex from TABLE;
  If ex > 0 then
    begin
            execute immediate 'DROP SEQUENCE SQ_NAME';
      exception when others then
        null;
    end;
    execute immediate 'CREATE SEQUENCE SQ_NAME INCREMENT BY 1 START WITH ' || ex || ' NOCYCLE CACHE 20 NOORDER';
  end if;
end;

Here I have my example which works just fine:

declare
 ex number;
begin
  select MAX(MAX_FK_ID)  + 1 into ex from TABLE;
  If ex > 0 then
    begin
            execute immediate 'DROP SEQUENCE SQ_NAME';
      exception when others then
        null;
    end;
    execute immediate 'CREATE SEQUENCE SQ_NAME INCREMENT BY 1 START WITH ' || ex || ' NOCYCLE CACHE 20 NOORDER';
  end if;
end;

回复收藏 0 原文

~没有更多了~