oracle - 没有序列的序列 [英] oracle - sequences without a sequence

问题描述

我想用序列号填充一列，但单个序列是不够的.如果您愿意，此列的行为将有点像子 ID"；表中记录组的递增 ID.

计划是在使用触发器插入时获取序列中的下一个数字"，就像可以使用普通的序列一样.但是，它不仅是下一个数字"，还需要是给定结果集中的下一个数字".

考虑以下示例数据，其中 display_id 列是我需要帮助管理的序列，它取决于 group_name..

的记录值<前>身份证 |组名 |显示编号------------------------------5 |A组|34 |A组|23 |A组|12 |B组|21 |B组|1

我正在考虑像这样的查询来获取 GroupA 的下一个数字":

select max(record_id) + 1来自 my_records其中 group_name = 'GroupA'

对于 GroupA 返回 4，但是对于 GroupB 返回 3.

我们当然可以使用上面的查询，但会失去 sequence 的原子优势.有没有办法自信地管理这样的序列?

我们不担心可能会跳过数字(因为序列可能).

由于回滚等原因(与序列一样)，我们对错过一两个数字感到满意.但是，我们的要求仍然是 display_id 列保持多个序列.

解决方案

虽然我强烈建议反对它(更喜欢使用单个序列并且只接受会有比预期更大的差距)，但您可以构建自己的伪-序列表

创建表 my_sequences (序列名称 VARCHAR2(30) 主键，序列值 NUMBER);

插入几行

INSERT INTO my_sequences( sequence_name, sequence_val )值('组A'，1)；INSERT INTO my_sequences( sequence_name, sequence_val )值('GroupB', 1);

然后写一个函数来获取下一个序列值

CREATE FUNCTION get_nextval(p_sequence_name IN VARCHAR2)退货号码是l_val NUMBER;开始选择序列值INTO l_valFROM my_sequencesWHERE sequence_name = p_sequence_name更新；更新 my_sequencesSET 序列值 = 序列值 + 1WHERE sequence_name = p_sequence_name;返回 l_val;结尾;

这将锁定表中特定序列的行，直到检索下一行的事务提交或回滚.与使用 Oracle 序列相比，这会从根本上降低应用程序的可伸缩性，因为它确保一次只能有一个会话为特定的 group_name 插入一行——其他会话将阻塞等待序列.如果您的系统的并发用户数相对较少(或 group_name 值相对较多)，您可能可以接受.但总的来说，这是一种糟糕的做法.根据 Oracle 版本，您可能能够使用自治事务来增加并发性，但这只会给解决方案增加一点复杂性.当您真的担心可扩展性时，您真的想推迟整个设计并只使用 Oracle 序列.

I want to populate a column with a sequential number but a single sequence is not sufficient. This column will behave somewhat like a 'sub id' if you will; an incrementing id for groups of records in the table.

The plan is to get the 'next number in the sequence' when inserting using a trigger, much like a normal sequence may be used. However, rather than just the 'next number', it needs to be the 'next number' in a given result set.

Consider the following example data where the display_id column is the sequence I need help managing and it is dependent on the record's value for group_name..

 id | group_name | display_id
------------------------------
  5 |   GroupA   |     3
  4 |   GroupA   |     2
  3 |   GroupA   |     1
  2 |   GroupB   |     2
  1 |   GroupB   |     1

I'm thinking of a query like this query to get the 'next number' for GroupA:

select max(record_id) + 1
from my_records
where group_name = 'GroupA'

For GroupA it returns 4, but for GroupB it returns 3.

We could, of course, use the above query but would lose the atomic benefits of a sequence. Is there any way to manage such a sequence confidently?

We are not concerned about potentially skipping numbers (as sequences may).

Edit:
We are comfortable with a number or two being missed due to rollbacks and the like (as with sequences). However, our requirement is still that the display_id column maintain multiple sequences.

解决方案

Although I would strongly recommend against it (preferring to use a single sequence and just accept that there will be larger than expected gaps), you can build your own pseudo-sequence table

CREATE TABLE my_sequences (
  sequence_name VARCHAR2(30) PRIMARY KEY,
  sequence_val  NUMBER
);

insert a couple of rows

INSERT INTO my_sequences( sequence_name, sequence_val )
  VALUES( 'GroupA', 1 );
INSERT INTO my_sequences( sequence_name, sequence_val )
  VALUES( 'GroupB', 1 );

and then write a function to get the next sequence value

CREATE FUNCTION get_nextval( p_sequence_name IN VARCHAR2 )
  RETURN NUMBER
IS
  l_val NUMBER;
BEGIN
  SELECT sequence_val
    INTO l_val
    FROM my_sequences
   WHERE sequence_name = p_sequence_name
     FOR UPDATE;

  UPDATE my_sequences
     SET sequence_val = sequence_val + 1
   WHERE sequence_name = p_sequence_name;

  RETURN l_val;
END;

This will lock the row in the table for the particular sequence until the transaction that retrieved the next row either commits or rolls back. This will radically decrease the scalability of your application compared to using Oracle sequences by ensuring that only one session can be inserting a row for a particular group_name at a time-- the others will block waiting for the sequence. If you have a system with a relatively small number of concurrent users (or a relatively large number of group_name values), that may be acceptable to you. But in general it is a poor practice. Depending on the Oracle version, you may be able to use autonomous transactions to increase concurrency but that just adds one more bit of complexity to the solution. At the point that you're really worried about scalability, you'd really want to push back on the whole design and just use an Oracle sequence.

这篇关于oracle - 没有序列的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！