自动设置Oracle的序列起始值 [英] Automatically setting Oracle's sequence start value

问题描述

我有许多现有的表，每个表都有一个名为"id"的列.该列的整数值从1开始.因此，例如，表MY_TABLE包含3条ID为1、2和3(超级基本)的记录.

I have many existing tables, each with a column called 'id'. This column has an integer value starting at 1. So for example, the table MY_TABLE contains 3 records with ids 1, 2 and 3 (super basic).

我想为我拥有的每个表创建一个序列，并使用表的最大ID设置其起始值.在我的示例中，我将需要以下内容:

I want to create a sequence for each table I have and set its start value with the maximun id of the table. In my example, I would need something like this:

CREATE SEQUENCE MY_TABLE_SEQ START WITH 3 INCREMENT BY 1;

我尝试了类似的方法，但是没有用:

I tried something like this, but it didn't work:

CREATE SEQUENCE MY_TABLE_SEQ START WITH (SELECT NVL(MAX(id),1) FROM MY_TABLE) INCREMENT BY 1;

知道我可能会做什么吗?

Any idea what I might be able to do?

谢谢

推荐答案

DECLARE
    MAXVAL MY_TABLE.ID%TYPE;
BEGIN
    SELECT NVL(MAX(id),1) INTO MAXVAL FROM MY_TABLE;
    EXECUTE IMMEDIATE 'CREATE SEQUENCE MY_TABLE_SEQ START WITH ' || MAXVAL || ' INCREMENT BY 1';
END
/

创建序列后，您还可以更改序列.

You could also ALTER the sequences once they are created.

有关此主题的一些阅读材料: http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::::P11_QUESTION_ID:951269671592

Some readings about the subject: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:951269671592

这篇关于自动设置Oracle的序列起始值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！