编写 PL/SQL 序列的验证步骤

发布于 2024-12-06 19:54:51 字数 509 浏览 0 评论 0原文

我正在为我的一个代码编写启动计划。其中，对于每个步骤，我都必须编写一个验证步骤，该步骤将告诉您部署/更改是否已正确进行。就像如果有一个 alter table 命令来添加新列然后作为验证一样，我将使用 select column_name from table 作为验证步骤。

我正在为我的序列脚本寻找验证步骤。我的序列脚本正在删除旧序列并重新创建它，并更改初始值。假设我的最后一个序列位于 10071 - 新序列将从 100710 开始。

我编写了以下查询

SELECT LAST_NUMBER 
  FROM all_sequences 
 WHERE sequence_name = 'SEQNAME';

现在，我的问题是，这会给出新序列还是旧序列的最后一个数字吗？

附：我不能使用序列 NextValue - 它会导致系统丢失 1 个数字，并会搞乱整个系统。不过，我愿意接受可以自动使用我的 *next_value* 的选项。

原文

I am writing the launch plan for one of my code. In which, for each step i have to write a verification step which will tell if the deployment/change has been made properly. Like if there is an alter table command to add a new column then as a verification, I would be using select column_name from table as verification step.

I am looking for a verification step for my sequence script. My sequence scripts is dropping the old sequence and re-creating it with initial value changed. lets say if my last sequece was on 10071 - the new sequece would start from 100710.

I wrote the following query

SELECT LAST_NUMBER 
  FROM all_sequences 
 WHERE sequence_name = 'SEQNAME';

Now, my question is, would that give the last number of my new sequence or the old sequence?

P.S:
I can't use sequence NextValue - it would cause the system to miss 1 number and will mess up the whole system. However, I am open for the options in which my *next_value* can be utlizied, automatically.

分享到QQ

分享到微博

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

发布评论

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

亽野灬性zι浪 2024-12-13 19:54:51

现在，我的问题是，这会给出新序列还是旧序列的最后一个数字？

呃...为什么不按照兰迪在评论中的建议尝试一下呢？

SQL> !cat /tmp/sql.sql

create sequence foo start with 10;

select min_value, max_value, increment_by, last_number
from user_sequences
where sequence_name = 'FOO';

drop sequence foo;

create sequence foo start with 20;

select min_value, max_value, increment_by, last_number 
from user_sequences 
where sequence_name = 'FOO';

drop sequence foo;

SQL> @/tmp/sql

Sequence created.


 MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
---------- ---------- ------------ -----------
         1 1.0000E+28            1          10


Sequence dropped.


Sequence created.


 MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
---------- ---------- ------------ -----------
         1 1.0000E+28            1          20


Sequence dropped.

SQL>

Now, my question is, would that give the last number of my new sequence or the old sequence?

Err ... Why not just try it as suggested by Randy in the comments ?

SQL> !cat /tmp/sql.sql

create sequence foo start with 10;

select min_value, max_value, increment_by, last_number
from user_sequences
where sequence_name = 'FOO';

drop sequence foo;

create sequence foo start with 20;

select min_value, max_value, increment_by, last_number 
from user_sequences 
where sequence_name = 'FOO';

drop sequence foo;

SQL> @/tmp/sql

Sequence created.


 MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
---------- ---------- ------------ -----------
         1 1.0000E+28            1          10


Sequence dropped.


Sequence created.


 MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
---------- ---------- ------------ -----------
         1 1.0000E+28            1          20


Sequence dropped.

SQL>

回复收藏 0 原文

终止放荡 2024-12-13 19:54:51

如果您的序列使用缓存（默认），LAST_NUMBER 将不准确

SQL> CREATE SEQUENCE seq_1;

Sequence created.

SQL>
SQL> SELECT seq_1.nextval FROM dual;

   NEXTVAL
----------
         1

SQL>
SQL> SELECT sequence_name,
  2         min_value,
  3         last_number
  4  FROM user_sequences
  5  WHERE sequence_name = 'SEQ_1';

SEQUENCE_NAME                   MIN_VALUE LAST_NUMBER
------------------------------ ---------- -----------
SEQ_1                                   1          21

SQL>
SQL> SELECT seq_1.nextval FROM dual;

   NEXTVAL
----------
         2

SQL>
SQL> SELECT sequence_name,
  2         min_value,
  3         last_number
  4  FROM user_sequences
  5  WHERE sequence_name = 'SEQ_1';

SEQUENCE_NAME                   MIN_VALUE LAST_NUMBER
------------------------------ ---------- -----------
SEQ_1                                   1          21

SQL>

LAST_NUMBER will not be accurate if your sequence uses a cache (which is the default)

SQL> CREATE SEQUENCE seq_1;

Sequence created.

SQL>
SQL> SELECT seq_1.nextval FROM dual;

   NEXTVAL
----------
         1

SQL>
SQL> SELECT sequence_name,
  2         min_value,
  3         last_number
  4  FROM user_sequences
  5  WHERE sequence_name = 'SEQ_1';

SEQUENCE_NAME                   MIN_VALUE LAST_NUMBER
------------------------------ ---------- -----------
SEQ_1                                   1          21

SQL>
SQL> SELECT seq_1.nextval FROM dual;

   NEXTVAL
----------
         2

SQL>
SQL> SELECT sequence_name,
  2         min_value,
  3         last_number
  4  FROM user_sequences
  5  WHERE sequence_name = 'SEQ_1';

SEQUENCE_NAME                   MIN_VALUE LAST_NUMBER
------------------------------ ---------- -----------
SEQ_1                                   1          21

SQL>

回复收藏 0 原文

~没有更多了~