问题描述
我试图看看是否存在一些东西创建一个带分区逻辑的序列。
我需要一个依赖于其他主键的序列号：
I'm trying to see if exists something to create a sequence with partition logic.I need a sequence number that depend on other primary key ex:
id_person sequence id
1 | 1
1 | 2
2 | 1
3 | 1
1 | 3
因此序列必须依赖于 id_person 分区。在oracle上有这样的东西，或者我必须在应用程序层面上自己实现它。
so the sequence must depend on the id_person partition. Is there something like this on oracle or i must implement it by myself on the application level?
谢谢。
您已创建此PLSQL包一个函数和过程：
Hi have create this PLSQL package one function and procedure:
PROCEDURE INIT_SEQUENCE(NAME varchar2, pkColumnNameList PARTITIONED_SEQUENCE_PK_COLUMN);
FUNCTION GET_NEXT_SEQUENCE_VALUE(NAME varchar2, pkPartitionColValue PARTITIONED_SEQUENCE_COL_VALUE) RETURN NUMBER;
INIT_SEQUENCE - 输入要在序列处关联的名称和列名称列表固定的主键部分，用于输入序列Ex：'ID_PERSON'
INIT_SEQUENCE - get in input the name to associate at the sequence and a list of column name that are the fixed primary key part that vincolate the sequence Ex:'ID_PERSON'
，此过程的工作是创建将根据pkColumnNameList列管理序列增量的表。
the work of this procedure is to create the table that will manage the increment of sequence according to pkColumnNameList column.
GET_NEXT_SEQUENCE_VALUE-获取要递增的序列名称和pkColumnNameList主键的值，并进行下一步：
1）动态创建sql以工作
2）dbms_lock.allocate_unique（）;锁定表
3）检查表中是否存在一个记录，用于输入
中的pk值4）如果存在记录，则在序列列中用max + 1更新记录
5）如果不存在记录，则在序列栏中插入新的记录
6）return new id;
GET_NEXT_SEQUENCE_VALUE- get the name of sequence to increment and the value of pkColumnNameList primary key and make the next step:1) Create dynamically the sql to work2) dbms_lock.allocate_unique(); to lock the table3) check if is present a record in the table for pk value in input4) if a record is present update the record with max + 1 in the sequence column5) if a record is not present insert the new record with the 1 in the sequence column6) return new id;
我想接收评论关于这个感谢提前...
i would like to receive comment about this thanks in advance...
推荐答案
实际要求二级序列是无缺口的？如果是，你有一个巨大的序列化/可扩展性问题。
Is the actual requirement that the secondary sequence be gap free? If so, you've got a giant serialization/scalability issue.
如果你需要提供一个无缝序列供人类消费，你可以使用一个实际的序列
If you need to present a gap-free sequence for human consumption, you could use an actual sequence (or a timestamp, for that matter) as Nick Pierpont suggests and preserve scalability, you could use analytic functions.
数据集（t1）：
ID_PERSON SEQUENCE_ID
---------- -----------
1 1
2 2
3 3
1 4
1 5
1 6
2 7
3 8
1 9
SQL：
select *
from
(select id_person,
sequence_id as orig_sequence_id,
rank ()
over (partition by id_person
order by sequence_id)
as new_sequence_id
from t1
)
order by id_person, new_sequence_id;
结果：
ID_PERSON ORIG_SEQUENCE_ID NEW_SEQUENCE_ID
---------- ---------------- ---------------
1 1 1
1 4 2
1 5 3
1 6 4
1 9 5
2 2 1
2 7 2
3 3 1
3 8 2
这篇关于Oracle分区序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！