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

一、创建序列

1、序列sql代码

create sequence id_seq        // 创建序列名：id_seqstart with 1                  // 从1开始increment by 1                // 每次增长1nomaxvalue                    // nomaxvalue(不设置最大值) ---最大值minvalue 1                    // 最小值cycle / nocycle               // nocycle：表示一直累加，不循环 ；cycle：表示循环cache n / nocache             // 缓存。cache 10 表示一次产生10个号

2、使用注意

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

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

        （3）使用缓存产生号，优点是提高效率，缺点是可能产生跳号。

二、创建存储过程

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

CREATE OR REPLACE PROCEDURESEQ_RESET(V_SEQNAME VARCHAR2) ASn  NUMBER(10);TSQL VARCHAR2(100);BEGIN  EXECUTE IMMEDIATE ‘select ‘ || V_SEQNAME|| ‘.nextval from dual‘ INTO n;  n := - (n - 1);  TSQL := ‘alter sequence ‘ || V_SEQNAME|| ‘ increment by ‘ || n;  EXECUTE IMMEDIATE TSQL;  EXECUTE IMMEDIATE ‘select ‘ ||V_SEQNAME || ‘.nextval from dual‘ INTO n;  TSQL := ‘alter sequence ‘ ||V_SEQNAME || ‘ increment by 1‘;  EXECUTE IMMEDIATE TSQL;END SEQ_RESET;

2、另一种方法

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

--使用此函数获取下一个序列的值FUNCTION GetSeqValue RETURN number IS    i number;    v_result number;BEGIN    select mod(trunc(sysdate)-to_Date('2014-01-01','yyyy-mm-dd'),2) into i from dual;    if i=1 then        select SEQ_A.NEXTVAL into v_result from dual;    else        select SEQ_B.NEXTVAL into v_result from dual;    end if;    return v_resultEND;--下面这个在数据库做成定时任务PROCEDURE ResetSeq IS    V_SeqName varchar2(100);BEGIN    select decode(mod(trunc(sysdate)-to_Date('2014-01-01','yyyy-mm-dd'),2),1,'SEQ_B','SEQ_A')    INTO V_SeqName from dual;    EXECUTE IMMEDIATE 'DROP SEQUENCE '||V_SeqName;    EXECUTE IMMEDIATE 'CREATE SEQUENCE '||V_SeqName||' increment by 1 start with 1 nocache nocycle';END;/

3、出现的问题及记录

        创建好存储过程之后

        （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的序列进行重新设置成初始的数值。

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

create or replace PROCEDURE SEQ_RESET(V_SEQNAME in VARCHAR2) ASn  NUMBER(10);TSQL VARCHAR2(100);BEGIN  execute immediate 'select '|| v_seqname ||'.nextval from dual' into n;  n := - (n-1);  --DBMS_OUTPUT.PUT_LINE('查询到的n值：'||n||'。');  TSQL := 'alter sequence '|| v_seqname ||' increment by '|| n;  EXECUTE IMMEDIATE TSQL;  EXECUTE IMMEDIATE 'select '|| v_seqname ||'.nextval from dual' INTO n;  --DBMS_OUTPUT.PUT_LINE('第二次查询到的n值：'||n||'。');  TSQL := 'alter sequence '|| v_seqname ||' increment by 1';  EXECUTE IMMEDIATE TSQL;  --DBMS_OUTPUT.PUT_LINE('调试成功！');END SEQ_RESET;

小结：

由于问题的记录文章没有按时记录，有些地方记得不太清楚了，后面再进行补充吧。