oracle数据库——序列用法以及序列重置的存储过程的创建以及问题记录

一、序列的用法

1、创建序列的sql代码

1.  
   create sequence id_seq // 创建序列名：id_seq
2.  
   start with 1 // 从1开始
3.  
   increment by 1 // 每次增长1
4.  
   maxvalue 999999 // 若为nomaxvalue表示不设置最大值。 ---最大值
5.  
   minvalue 1 // 最小值
6.  
   cycle / nocycle // nocycle：表示一直累加，不循环 ；cycle：表示循环
7.  
   cache n / nocache // 缓存。cache 10 表示一次产生10个号

2、使用注意

        （1）currval总是返回当前sequence的值，只有在第一次nextval初始化后，才能使用currval，否则会出错。每使用一次nextval，就会增加一次sequence的值，同一个语句里面要是有多个nextval，其数值就是不一样的；

        （2）第一次nextval返回的值是初始值：随后的nextval会自动增加定义的increment by值，然后返回增加后的值（任何用户都可以引用）；

        （3）使用缓存产生号，如果指定cache值，oracle就可以预先在内存里面放置一些sequence，这样存取会更快，cache里面取完后，oracle自动再取一组到cache，使用cache或许会跳号，比如数据库突然不正常down掉，cache中的sequence就会丢失，可以在定义sequence的时候，使用nocache防止这种情况。

         （4）什么时候使用sequence：

                ①不包含子查询，snapshot，view的select语句【用的少】

                ②insert语句的子查询中【用的较多】

                ③insert语句的values 中【用的多】

                ④update的set中【用的较多】

 

 

二、创建存储过程

1、创建名为seq_reset的存储过程（网上找的，存在问题）

1.  
   CREATE OR REPLACE PROCEDURESEQ_RESET(V_SEQNAME VARCHAR2) AS
2.  
   n NUMBER(10);
3.  
   TSQL VARCHAR2(100);
4.  
   BEGIN
5.  
   EXECUTE IMMEDIATE ‘select ‘ || V_SEQNAME|| ‘.nextval from dual‘ INTO n;
6.  
   n := - (n - 1);
7.  
   TSQL := ‘alter sequence ‘ || V_SEQNAME|| ‘ increment by ‘ || n;
8.  
   EXECUTE IMMEDIATE TSQL;
9.  
   EXECUTE IMMEDIATE ‘select ‘ ||V_SEQNAME || ‘.nextval from dual‘ INTO n;
10.  
    TSQL := ‘alter sequence ‘ ||V_SEQNAME || ‘ increment by 1‘;
11.  
    EXECUTE IMMEDIATE TSQL;
12.  
    END SEQ_RESET;

2、出现的问题及记录

        创建好存储过程之后

        （1）直接进行调试该存储过程时便出现错误：PLS-00306: 调用"存储过程名"时参数个数或类型错误.

        解决：一个原因可能是因为前台代码的参数与后台的存储过程的参数不一致，包括：参数的名称、个数、数据类型、参数类型、参数的顺序；

                   另一个原因：可能在前台的参数赋了个null值，传到后台存储过程后就报错了。

        （2）接着又出现问题：PLS-00357: 在此上下文中不允许表, 视图或序列引用 'ID_SEQ'

          原因：一个数据库表，视图或序列引用是在不适当的情况下发现的。这种提法，只能出现在SQL语句（不包括序列）或％TYPE和％ROWTYPE声明。       

          解决：拆除或搬迁掉非法参考（也可能是序列名未加引号，加上引号'id_seq'）

          另一个原因可能是sql语句中的空格有问题，导致无法正确执行，所以一定要检查sql语句的空格、分号的中英文等问题是否已解决，尤其是sql语句的最前面最容易留下空格。

        （3）解决完上述问题之后出现新问题：ORA-00940: 无效的 ALTER 命令.

          出现上诉问题是因为我的用户模式没有alter的权限，于是我登录了系统管理员的用户，给项目数据库的用户模式授权了相应的权限。       

        （4）解决完上述问题，居然还有问题，我tm要吐了：ORA-02286: 未指定 ALTER SEQUENCE 的选项。

           经过仔细查找发现问题依然是空格导致的，sequence sql命令后面的选项处没有加上空格，导致无法识别。因为这几个空格让我挠心抓肺了一整天，真是低级错误害死人啊！

         （5）又出现了问题，已吐血身亡：ORA-08004: 序列 ID_SEQ.NEXTVAL goes below MINVALUE 无法实例化。

经过查找发现是前面在调试几次后存储过程中的n值已不是初始的值，而且出现的错误中断处每次也不同，导致再次调试时出现了“序列.nextval"的值低于MINVALUE的错误，应该把id_seq的序列进行重新设置成初始的数值。

3、最后调试完成，最终的SQL代码：

 

1.  
   create or replace PROCEDURE SEQ_RESET(V_SEQNAME in VARCHAR2) AS
2.  
   n NUMBER(10);
3.  
   TSQL VARCHAR2(100);
4.  
   BEGIN
5.  
     execute immediate 'select '|| v_seqname ||'.nextval from dual' into n;
6.  
     DBMS_OUTPUT.PUT_LINE('序列重置前初始化查询到的n+1值：'|| n||'。');
7.  
     n := - n;
8.  
   --alter属于DDL语言，DDL操作是隐形提交的，所以在触发器中调用时报错:触发器中不能commit或rollback 
9.  
     TSQL := 'alter sequence '|| v_seqname ||' increment by '|| n;
10.  
      EXECUTE IMMEDIATE TSQL;
11.  
      EXECUTE IMMEDIATE 'select '|| v_seqname ||'.nextval from dual' INTO n;
12.  
      DBMS_OUTPUT.PUT_LINE('序列重置成功后查询到的n值：'||n||'。');
13.  
      TSQL := 'alter sequence '|| v_seqname ||' increment by 1';
14.  
      EXECUTE IMMEDIATE TSQL;
15.  
      commit;
16.  
    END VIB_SEQ_RESET;

 

4、如何执行该存储过程

          重置序列可以使用语句：call rseq(‘seqname’);

          但是有时候向表里插入数据，还是提示重复值，可以重建索引：

          alter index PK_UINLGORT rebuild;//重建索引

          再有问题，再对表进行分析，然后重建索引：

          Analyze table TABLE_NAME compute statistics;

          analyze table TABLE_NAME compute statistics for all indexes ;

 

小结：

        把序列重置的存储过程的原理是这样的：

        首先获取到当前序列的值N，然后将序列的增量修改为 -N，然后再执行nextval来获取下一个值，从而下一个值变成了 N + ( - N) = 0，最后再把序列的增量修改回原来的值，序列重置成功，这样重置与重置为1相比，有个好处是重置成0，再执行nextval可以获取到1，这样就可以直接使用序列生成的值了！

 

PS：另一种方法

 

        网上找到了好多种序列重置的方法，大多都是进行删除序列从而重建序列来达到重置的功能，但是这种方法有个弊端就是，当数据库较复杂，且序列被多个表、视图及存储过程调用的时候，删除序列容易造成数据库的错误，所以找到了另一种方法（下为删除重建的sql代码，该方法是建立了两个序列，然后通过两个序列轮流使用轮流删除来实现的）

1.  
   --使用此函数获取下一个序列的值
2.  
   FUNCTION GetSeqValue RETURN number IS
3.  
   i number;
4.  
   v_result number;
5.  
   BEGIN
6.  
   select mod(trunc(sysdate)-to_Date('2014-01-01','yyyy-mm-dd'),2) into i from dual;
7.  
   if i=1 then
8.  
   select SEQ_A.NEXTVAL into v_result from dual;
9.  
   else
10.  
    select SEQ_B.NEXTVAL into v_result from dual;
11.  
    end if;
12.  
    return v_result
13.  
    END;
14.  
    --下面这个在数据库做成定时任务
15.  
    PROCEDURE ResetSeq IS
16.  
    V_SeqName varchar2(100);
17.  
    BEGIN
18.  
    select decode(mod(trunc(sysdate)-to_Date('2014-01-01','yyyy-mm-dd'),2),1,'SEQ_B','SEQ_A')
19.  
    INTO V_SeqName from dual;
20.  
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||V_SeqName;
21.  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE '||V_SeqName||' increment by 1 start with 1 nocache nocycle';
22.  
    END

     

    转载：https://blog.csdn.net/xzsfg6825/article/details/75577415?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromBaidu-1.nonecase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromBaidu-1.nonecase

     

     

    这里记录一个问题，在存储过程中设置自增序列时，必须设置minvalue否则会报错。

     

     

    create or replace procedure p_reset_seq( p_seq_name in varchar2 )
    is
        l_val number;
    begin
    

        execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;

        execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0 maxvalue 9999 nocycle nocache'; --把 cycle 和 cache 关闭，否则会报错
        execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
        execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0 maxvalue 9999 cycle cache 20'; --重新打开 cycle 和 cache
    end;