Oracle-seqence（序列 ），手动序列

2018年11月22日 14:57 · 阅读(569) ·

[目录]

参考

ORACLE SEQUENCE 具体解释

Oracle 序列

什么是序列

数据库对象的一种。通过序列生成连续的数字以此来实现主键字段的自己主动、唯一增长，而且一个序列可为多列、多表同一时候使用。

创建一个简单序列

例子：

  1. --创建序列
  2. create sequence seq_test --创建一个序列
  3. minvalue 1               --最小值
  4. maxvalue 100000          --最大值 
  5. start with 1             --指定序列起始数
  6. increment by 1           --指定序列增长步长
  7. cache 20                 --指定数据库内存中预分配的序列值个数，以便高速获取。最小cache值为2

测试序列

  1. --测试序列
  2. select seq_test.nextval as 下一个值 from dual;
  3. select seq_test.currval as 当前值 from dual

创建语法说明

increment by:指定序列增长步长。能够为正（升序）、负整数（降序）。但不能为0。最高精度28。

start with： 指定序列起始数。默觉得序列最小值。

maxvalue ：指定序列最大值。最大28位。必须大于等于起始值且大于等于序列最小值。

nomaxvalue 无最大值（实际为10^27或-1）。默认。

minvalue ：指定序列最小值。

nominvalue ：无最小值（实际为1或-10^26）。默认。

cycle ：指定序列达到最大值或最小值后继续从头开始生成。带有cycle条件序列当达到最大值后，下一个值从最小值 minvalue 开始循环！

例子：

  1. create sequence seq_test --创建一个序列
  2. minvalue 1               --最小值
  3. maxvalue 100000          --最大值 
  4. start with 1             --指定序列起始数
  5. increment by 1           --指定序列增长步长
  6. cycle                    --指定序列达到最大值或最小值后继续从头开始生成
  7. cache 20                 --指定数据库内存中预分配的序列值个数，以便高速获取。最小cache值为2

nocycle ：不循环生成。默认。

cache ：指定数据库内存中预分配的序列值个数，以便高速获取。最小 cache 值为2。假设系统发生问题。全部缓存的没有被 DML 语句使用并提交的序列值将丢失。潜在丢失值数量等于 cache 的数量。cache 参数最大值为：(cell(maxvalue - minvalue)) / abs(increment)

nocache ：不指定缓存数，默认缓存20。

order ：指定 order 条件保证序列按请求顺序生成。

此条件适用于 rac 环境。

noorder ：不保证序列按请求顺序生成。

授权序列

  1. grant select on seq_test to luoma with grant option;
  2. grant select on seq_test to luoma;

修改语法说明

语法说明：

1.想以不同的数字又一次开始序列，必须删除重建

例如上面创建的例子中的序列修改为 55：

  1. alter sequence seq_test start with 55 --修改序列起始数

执行提示：

  1. ORA-02293:无法变更启动序列号

2.改动的 maxvalue 必须大于序列当前值

例如：

  1. alter sequence seq_test maxvalue 1 --修改序列最大值

执行提示：

  1. ORA-04004:MINVALUE 必须小于 MAXVALUE

删除语法说明

例子：

  1. drop sequence seq_test--删除序列

手动序列

案例：客户化系统单据序列操作的 API

序列对应表-COMMON_DOC_SEQUENCES

  1. -- Create table
  2. create table COMMON_DOC_SEQUENCES
  3. (
  4.   doc_type          VARCHAR2(45) not null,
  5.   pk1_value         VARCHAR2(50) not null,
  6.   pk2_value         VARCHAR2(50),
  7.   pk3_value         VARCHAR2(50),
  8.   pk4_value         VARCHAR2(75),
  9.   pk5_value         VARCHAR2(50),
  10.   next_seq_number   NUMBER,
  11.   created_by        NUMBER not null,
  12.   creation_date     DATE not null,
  13.   last_updated_by   NUMBER not null,
  14.   last_update_date  DATE not null,
  15.   last_update_login NUMBER
  16. );
  17. -- Create/Recreate indexes 
  18. create unique index COMMON_DOC_SEQUENCES_N1 on COMMON_DOC_SEQUENCES (DOC_TYPE, PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE);

序列对应包

  1. CREATE OR REPLACE PACKAGE "COMMON_DOC_SEQUENCE_UTL" AS
  2.   /*==================================================
  3.   Program Name:
  4.       COMMON_DOC_SEQUENCE_UTL
  5.   Description:
  6.       该程序包提供客户化系统单据序列操作的 API
  7.   History:
  8.   ==================================================*/
  9.   /*==================================================
  10.   Function Name :
  11.       next_seq_number
  12.   Description:
  13.       获取下一个单据号
  14.   Argument:
  15.       p_doc_type    : 单据类型
  16.       p_pk1_value   : 主关键字1
  17.       p_pk2_value   : 主关键字2
  18.       p_pk3_value   : 主关键字3
  19.       p_pk4_value   : 主关键字4
  20.       p_pk5_value   : 主关键字5
  21.       p_init_number : 初始序列号
  22.   Return:
  23.       NUMBER          : 返回单据号序列
  24.   History:
  25.   ==================================================*/
  26.   FUNCTION next_seq_number
  27.   (
  28.     p_doc_type    IN VARCHAR2,
  29.     p_pk1_value   IN VARCHAR2 DEFAULT NULL,
  30.     p_pk2_value   IN VARCHAR2 DEFAULT NULL,
  31.     p_pk3_value   IN VARCHAR2 DEFAULT NULL,
  32.     p_pk4_value   IN VARCHAR2 DEFAULT NULL,
  33.     p_pk5_value   IN VARCHAR2 DEFAULT NULL,
  34.     p_init_number IN NUMBER DEFAULT 1
  35.   ) RETURN NUMBER;
  37.   /*==================================================
  38.   Function Name :
  39.       next_seq_number
  40.   Description:
  41.       获取下一个单据号
  42.   Argument:
  43.       p_doc_type    : 单据类型
  44.       p_doc_prefix  ：前缀
  45.       p_seq_length  : 长度
  46.       p_pk1_value   : 主关键字1
  47.       p_pk2_value   : 主关键字2
  48.       p_pk3_value   : 主关键字3
  49.       p_pk4_value   : 主关键字4
  50.       p_pk5_value   : 主关键字5
  51.       p_init_number : 初始序列号
  52.   Return:
  53.       VARCHAR2      : 返回单据号
  54.   History:
  55.   ==================================================*/
  56.   FUNCTION next_seq_number
  57.   (
  58.     p_doc_type    IN VARCHAR2,
  59.     p_doc_prefix  IN VARCHAR2,
  60.     p_seq_length  IN NUMBER DEFAULT 0,
  61.     p_pk1_value   IN VARCHAR2 DEFAULT NULL,
  62.     p_pk2_value   IN VARCHAR2 DEFAULT NULL,
  63.     p_pk3_value   IN VARCHAR2 DEFAULT NULL,
  64.     p_pk4_value   IN VARCHAR2 DEFAULT NULL,
  65.     p_pk5_value   IN VARCHAR2 DEFAULT NULL,
  66.     p_init_number IN NUMBER DEFAULT 1
  67.   ) RETURN VARCHAR2;
  69.   /*==================================================
  70.   Function Name :
  71.       next_seq_number
  72.   Description:
  73.       获取当前单据号
  74.   Argument:
  75.       p_doc_type    : 单据类型
  76.       p_doc_prefix  ：前缀
  77.       p_seq_length  : 长度
  78.       p_pk1_value   : 主关键字1
  79.       p_pk2_value   : 主关键字2
  80.       p_pk3_value   : 主关键字3
  81.       p_pk4_value   : 主关键字4
  82.       p_pk5_value   : 主关键字5
  83.       p_init_number : 初始序列号
  84.   Return:
  85.       VARCHAR2      : 返回单据号
  86.   History:
  87.   ==================================================*/
  88.   FUNCTION curr_seq_number
  89.   (
  90.     p_doc_type    IN VARCHAR2,
  91.     p_doc_prefix  IN VARCHAR2,
  92.     p_seq_length  IN NUMBER DEFAULT 0,
  93.     p_pk1_value   IN VARCHAR2 DEFAULT NULL,
  94.     p_pk2_value   IN VARCHAR2 DEFAULT NULL,
  95.     p_pk3_value   IN VARCHAR2 DEFAULT NULL,
  96.     p_pk4_value   IN VARCHAR2 DEFAULT NULL,
  97.     p_pk5_value   IN VARCHAR2 DEFAULT NULL,
  98.     p_init_number IN NUMBER DEFAULT 1
  99.   ) RETURN VARCHAR2;
  100. END COMMON_DOC_SEQUENCE_UTL;
  101. /
  102. CREATE OR REPLACE PACKAGE BODY "COMMON_DOC_SEQUENCE_UTL" AS
  103.   /*==================================================
  104.   Program Name:
  105.       COMMON
  106.   Description:
  107.       该程序包提供客户化系统单据序列操作的API
  108.   History:
  109.   ==================================================*/
  110.   FUNCTION next_seq_number
  111.   (
  112.     p_doc_type    IN VARCHAR2,
  113.     p_pk1_value   IN VARCHAR2 DEFAULT NULL,
  114.     p_pk2_value   IN VARCHAR2 DEFAULT NULL,
  115.     p_pk3_value   IN VARCHAR2 DEFAULT NULL,
  116.     p_pk4_value   IN VARCHAR2 DEFAULT NULL,
  117.     p_pk5_value   IN VARCHAR2 DEFAULT NULL,
  118.     p_init_number IN NUMBER DEFAULT 1
  119.   ) RETURN NUMBER IS
  120.     PRAGMA AUTONOMOUS_TRANSACTION;
  121.     l_doc_type VARCHAR2(30);
  123.     CURSOR c_seq_del IS
  124.       SELECT *
  125.       FROM (SELECT ds.rowid row_id
  126.                   ,ds.next_seq_number
  127.             FROM COMMON_DOC_SEQUENCES ds
  128.             WHERE ds.doc_type = l_doc_type
  129.                   AND ds.pk1_value = nvl(p_pk1_value, '-1')
  130.                   AND ds.pk2_value = nvl(p_pk2_value, '-1')
  131.                   AND ds.pk3_value = nvl(p_pk3_value, '-1')
  132.                   AND ds.pk4_value = nvl(p_pk4_value, '-1')
  133.                   AND ds.pk5_value = nvl(p_pk5_value, '-1')
  134.             ORDER BY ds.next_seq_number) tt
  135.       WHERE rownum = 1;
  137.     CURSOR c_seq IS
  138.       SELECT ds.rowid row_id
  139.             ,ds.next_seq_number
  140.       FROM COMMON_DOC_SEQUENCES ds
  141.       WHERE ds.doc_type = p_doc_type
  142.             AND ds.pk1_value = nvl(p_pk1_value, '-1')
  143.             AND ds.pk2_value = nvl(p_pk2_value, '-1')
  144.             AND ds.pk3_value = nvl(p_pk3_value, '-1')
  145.             AND ds.pk4_value = nvl(p_pk4_value, '-1')
  146.             AND ds.pk5_value = nvl(p_pk5_value, '-1')
  147.       FOR UPDATE; -- NOWAIT;
  149.     l_row_id          VARCHAR2(18);
  150.     l_next_seq_number NUMBER;
  152.   BEGIN
  153.     l_doc_type := p_doc_type || 'DEL';
  154.     --先看是否存在删除的数据
  155.     OPEN c_seq_del;
  156.     FETCH c_seq_del
  157.       INTO l_row_id
  158.           ,l_next_seq_number;
  160.     IF c_seq_del%NOTFOUND THEN
  161.       --不存在断号
  162.       OPEN c_seq;
  163.       FETCH c_seq
  164.         INTO l_row_id
  165.             ,l_next_seq_number;
  166.       IF c_seq%NOTFOUND THEN
  167.         l_next_seq_number := nvl(p_init_number, 1);
  168.         INSERT INTO COMMON_DOC_SEQUENCES
  169.           (doc_type
  170.           ,pk1_value
  171.           ,pk2_value
  172.           ,pk3_value
  173.           ,pk4_value
  174.           ,pk5_value
  175.           ,next_seq_number
  176.           ,creation_date
  177.           ,created_by
  178.           ,last_update_date
  179.           ,last_updated_by
  180.           ,last_update_login)
  181.         VALUES
  182.           (p_doc_type
  183.           ,nvl(p_pk1_value, '-1')
  184.           ,nvl(p_pk2_value, '-1')
  185.           ,nvl(p_pk3_value, '-1')
  186.           ,nvl(p_pk4_value, '-1')
  187.           ,nvl(p_pk5_value, '-1')
  188.           ,l_next_seq_number
  189.           ,SYSDATE
  190.           ,1
  191.           ,SYSDATE
  192.           ,1
  193.           ,1);
  194.       ELSE
  195.         l_next_seq_number := l_next_seq_number + 1;
  196.         UPDATE COMMON_DOC_SEQUENCES
  197.         SET next_seq_number   = l_next_seq_number,
  198.             last_update_date  = SYSDATE,
  199.             last_updated_by   = 1,
  200.             last_update_login = 1
  201.         WHERE ROWID = l_row_id;
  202.       END IF;
  203.       CLOSE c_seq;
  205.     ELSE
  206.       --用删除掉的编号
  207.       DELETE FROM COMMON_DOC_SEQUENCES WHERE ROWID = l_row_id;
  208.     END IF;
  209.     COMMIT;
  211.     RETURN l_next_seq_number;
  212.   END next_seq_number;
  214.   FUNCTION next_seq_number
  215.   (
  216.     p_doc_type    IN VARCHAR2,
  217.     p_doc_prefix  IN VARCHAR2,
  218.     p_seq_length  IN NUMBER DEFAULT 0,
  219.     p_pk1_value   IN VARCHAR2 DEFAULT NULL,
  220.     p_pk2_value   IN VARCHAR2 DEFAULT NULL,
  221.     p_pk3_value   IN VARCHAR2 DEFAULT NULL,
  222.     p_pk4_value   IN VARCHAR2 DEFAULT NULL,
  223.     p_pk5_value   IN VARCHAR2 DEFAULT NULL,
  224.     p_init_number IN NUMBER DEFAULT 1
  225.   ) RETURN VARCHAR2 IS
  226.     l_next_seq_number NUMBER;
  227.     l_doc_number      VARCHAR2(150);
  228.   BEGIN
  229.     l_next_seq_number := next_seq_number(p_doc_type    => p_doc_type,
  230.                                          p_pk1_value   => p_pk1_value,
  231.                                          p_pk2_value   => p_pk2_value,
  232.                                          p_pk3_value   => p_pk3_value,
  233.                                          p_pk4_value   => p_pk4_value,
  234.                                          p_pk5_value   => p_pk5_value,
  235.                                          p_init_number => p_init_number);
  236.     IF p_seq_length IS NULL OR p_seq_length = 0 THEN
  237.       l_doc_number := p_doc_prefix || l_next_seq_number;
  238.     ELSE
  239.       IF length(l_next_seq_number) >= p_seq_length THEN
  240.         l_doc_number := p_doc_prefix || l_next_seq_number;
  241.       ELSE
  242.         l_doc_number := p_doc_prefix || lpad(l_next_seq_number, p_seq_length, '0');
  243.       END IF;
  244.     END IF;
  245.     RETURN l_doc_number;
  246.   END next_seq_number;
  248.   FUNCTION curr_seq_number
  249.   (
  250.     p_doc_type    IN VARCHAR2,
  251.     p_doc_prefix  IN VARCHAR2,
  252.     p_seq_length  IN NUMBER DEFAULT 0,
  253.     p_pk1_value   IN VARCHAR2 DEFAULT NULL,
  254.     p_pk2_value   IN VARCHAR2 DEFAULT NULL,
  255.     p_pk3_value   IN VARCHAR2 DEFAULT NULL,
  256.     p_pk4_value   IN VARCHAR2 DEFAULT NULL,
  257.     p_pk5_value   IN VARCHAR2 DEFAULT NULL,
  258.     p_init_number IN NUMBER DEFAULT 1
  259.   ) RETURN VARCHAR2 IS
  261.     PRAGMA AUTONOMOUS_TRANSACTION;
  263.     CURSOR c_seq IS
  264.       SELECT ds.rowid row_id
  265.             ,ds.next_seq_number
  266.       FROM COMMON_DOC_SEQUENCES ds
  267.       WHERE ds.doc_type = p_doc_type
  268.             AND ds.pk1_value = nvl(p_pk1_value, '-1')
  269.             AND ds.pk2_value = nvl(p_pk2_value, '-1')
  270.             AND ds.pk3_value = nvl(p_pk3_value, '-1')
  271.             AND ds.pk4_value = nvl(p_pk4_value, '-1')
  272.             AND ds.pk5_value = nvl(p_pk5_value, '-1')
  273.       FOR UPDATE; -- NOWAIT;
  275.     l_row_id          VARCHAR2(18);
  276.     l_next_seq_number NUMBER;
  277.   BEGIN
  278.     OPEN c_seq;
  279.     FETCH c_seq
  280.       INTO l_row_id
  281.           ,l_next_seq_number;
  282.     IF c_seq%NOTFOUND THEN
  283.       l_next_seq_number := next_seq_number(p_doc_type    => p_doc_type,
  284.                                            p_doc_prefix  => NULL,
  285.                                            p_seq_length  => p_seq_length,
  286.                                            p_pk1_value   => p_pk1_value,
  287.                                            p_pk2_value   => NULL,
  288.                                            p_pk3_value   => NULL,
  289.                                            p_pk4_value   => NULL,
  290.                                            p_pk5_value   => NULL,
  291.                                            p_init_number => 1);
  293.       INSERT INTO COMMON_DOC_SEQUENCES
  294.         (doc_type
  295.         ,pk1_value
  296.         ,pk2_value
  297.         ,pk3_value
  298.         ,pk4_value
  299.         ,pk5_value
  300.         ,next_seq_number
  301.         ,creation_date
  302.         ,created_by
  303.         ,last_update_date
  304.         ,last_updated_by
  305.         ,last_update_login)
  306.       VALUES
  307.         (p_doc_type
  308.         ,nvl(p_pk1_value, '-1')
  309.         ,nvl(p_pk2_value, '-1')
  310.         ,nvl(p_pk3_value, '-1')
  311.         ,nvl(p_pk4_value, '-1')
  312.         ,nvl(p_pk5_value, '-1')
  313.         ,l_next_seq_number
  314.         ,SYSDATE
  315.         ,1
  316.         ,SYSDATE
  317.         ,1
  318.         ,1);
  319.     END IF;
  320.     IF p_seq_length IS NULL OR p_seq_length = 0 THEN
  321.       l_next_seq_number := p_doc_prefix || l_next_seq_number;
  322.     ELSE
  323.       IF length(l_next_seq_number) >= p_seq_length THEN
  324.         l_next_seq_number := p_doc_prefix || l_next_seq_number;
  325.       ELSE
  326.         l_next_seq_number := p_doc_prefix || lpad(l_next_seq_number, p_seq_length, '0');
  327.       END IF;
  328.     END IF;
  330.     CLOSE c_seq;
  331.     COMMIT;
  333.     RETURN l_next_seq_number;
  334.   END curr_seq_number;
  335. END COMMON_DOC_SEQUENCE_UTL;
  336. /

使用案例

  1. --流水号获取
  2. declare
  3.   var_no number;
  4.   var_no_str varchar2(100);
  5. begin
  6.    --单独的号码
  7.     var_no := COMMON_DOC_SEQUENCE_UTL.next_seq_number('PAYMENT_WRITE_OFF','BATCHID-INVOICE_NO_NUMBER','','','','',1);
  8.     dbms_output.put_line(var_no);  
  9.     --带数量的前缀
  10.     var_no_str := COMMON_DOC_SEQUENCE_UTL.next_seq_number('PAYMENT_WRITE_OFF','-',3,'BATCHID-SAVEPREPAYAPPLYDATA','','','','',1); 
  11.     dbms_output.put_line(var_no_str);
  12. end;

第一次调用

  1. 1
  2. -001

第二次调用

  1. 2
  2. -002

查询生成结果

  1. --验证
  2. select t.*,t.rowid 
  3. from common_doc_sequences t 
  4. where doc_type = 'PAYMENT_WRITE_OFF'

手动序列-根据表总数据生成

手动序列-根据表总数据生成