如何在一个表上创建一个这样的自增字段，如：2004-0001,2004-0002....2005-0001...

Question

我又改了，还不行。
declare
  myYear      Varchar(10);
  myYearCount NUMBER(10);
BEGIN
  select to_char(sysdate,'yyyy') into myYear from dual;
  select count(id) into myYearCount from foo where id like myYear+'%';
  IF myYearCount<1 then 
    drop sequence foo_seq;
    CREATE SEQUENCE foo_seq INCREMENT BY 1 START WITH 1 
    MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE 
    NOCACHE NOORDER;  
  END IF;
  SELECT to_char(sysdate,'yyyy')||'-'||substr(to_char(foo_seq.nextval+10000),2,4) into :new.id from dual; 
END;

Answer 1

drop table foo; create table foo( 
  id varchar(9) primary key, --字符型
  data varchar2(100)
                 ); create sequence foo_seq 
start with 1 increment by 1 maxvalue 9999; create or replace trigger bifer_foo_id_pk 
  before insert 
    on foo 
    for each row begin 
/*
  select count(id) into myYearCount from foo ;
  if myYearCount<1 then begin
    execute immediate ' drop sequence foo_seq';
    execute immediate 'CREATE SEQUENCE foo_seq INCREMENT BY 1 START WITH 1 
    MAXVALUE 9999 MINVALUE 1 NOCYCLE 
    NOCACHE NOORDER ';  
 end;
  end IF;
*/ 不能删除序列，会报错的。不要用在trigger种。
select to_char(sysdate,'yyyy')||'-'||substr(to_char(foo_seq.nextval+10000),2,4) into :new.id 
from dual; 
end;
/
--调试

Answer 2

经过试验，不可以在触发器中使用
execute immediate ' drop sequence foo_seq';
只能自己手动删除，创建。

Answer 3

welyngj兄,你也是夜猫子.
那么象我这样的字增字段,该如何实现呢?
难道每年刚开始的时候,我都主动去将 sequence 删除?这太难了

Answer 4

declare
  myYear      Varchar(10);
  myYearCount NUMBER(10);
BEGIN
  select to_char(sysdate,'yyyy') into myYear from dual;
  select count(id) into myYearCount from foo where id like myYear+'%';
  IF myYearCount<1 then 
    drop sequence foo_seq;
    CREATE SEQUENCE foo_seq INCREMENT BY 1 START WITH 1 
    MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE 
    NOCACHE NOORDER;  
  END IF;
  SELECT to_char(sysdate,'yyyy')||'-'||substr(to_char(foo_seq.nextval+10000),2,4) into :new.id from dual; 
END;这个是无名过程:new.id从何而来，触发器里是不能使用动态SQL语句

Answer 5

以前好像写过 trigger 中是可以支持动态sql的 用自治事务
但为什么要写在 trigger中 这中一年就改一次seq的情况 可以考虑用job完成

Answer 6

难道每年刚开始的时候,我都主动去将 sequence 删除?
---------------------------------------------
也不难呀！知识麻烦一点。

Answer 7

我的问题是，你的序列根本没有用。
你其实直接用你的程序里面的那个Count的值，完全可替代你的sequence，
或者选择，MAX的那个（假如你的程序会删除里面的ID的话），
这样，就没有你碰到的问题。因为，你的ID前4位是年，中间一个横线，最后的4位，用数据库里面最大的那个加1就好了，我的理解有错误吗？

Answer 8

create sequence时如何创建成000031或者A00031这种的sequence？
1. 建序列：create sequence s1 start with 1 increment by 1
2.  insert into t1(col) values('A' ||lpad(to_char(s1.nextval),10,'0'));

Answer 9

1. 建序列：create sequence s1 start with 1 increment by 12. insert into t4(d1) values(to_char(sysdate,'yyyy')||'-'||lpad(to_char  (s1.nextval),4,'0'));
commit;

Answer 10

Croatia(Croatia)的建议，我不敢同意，这在数据量大的库里，一定会影响速度的，当然，如果将这字段保存到其它表的话，那将又是另外的一回事！

Answer 11

to freddy2003()
那第二年年初开工的时候，我必须还是要到每个用户那里去重建sequence  ：）同意  wupangzi(无本之木) 的意见！那么能不能换一个思路：建一个job，让他在每年1月1日0时0分，重建sequence。这样可以吗？

Answer 12

根据你的情况，提供一种权宜之计。
    这个问题的关键点在于如何在每到一年，将sequence的值复位。所以可将sequence建成circle，每年的第一次条件触发后，用循环将sequence的值达到最大值，最后到1。这样做可以保证理论上的要求达到了。
    但是缺点也很明显，首先很笨！每次想到要让这个sequence不停的加1到最大，只是为了将其变成1，就觉得运行此程序的计算机一定会狂骂编程的人（嘿嘿，程序不是我编的哟）；当然，最重要的原因是如果sequence的最大值很大的话，每年第一次的运算时间会很长。唉，每年都要扮处女。不过还好，只有四位数，破瓜之痛应该能忍。
    呵呵，忍不忍，就看楼主你了。

Answer 13

实在没有办法了，不用sequence.可这样写，也不行
declare
  myYear      Varchar(10);
  myYearCount NUMBER(10);
BEGIN
  select to_char(sysdate,'yyyy') into myYear from dual;
  select count(id) into myYearCount from foo where id like myYear+'%';
  :new.id=to_char(sysdate,'yyyy')||'-'||lpad(to_char(myYearCount+1),4,'0');
  commit;
END;

Answer 14

以下修改基于楼主第一贴建一个最大为9999,最小为1，可循环的sequence
    CREATE SEQUENCE foo_seq INCREMENT BY 1 START WITH 1 
    MAXVALUE 9999 MINVALUE 1 CYCLE 触发器的修改：  --变量定义中加入一个数字变量
  seq_val number ;  --将原触发器中的if到end if修改如下
  if myYearCount<1 then 
    select foo_seq.currval into seq_val from dual;
    --以下循环执行完以后，foo_seq.curr等于9999，下次取foo_seq.nextval就等于1
    while seq_val<9999 loop
    select foo_seq.nextval into seq_val from dual;
    end loop;
  end IF;

Answer 15

select count(id) into myYearCount from foo where id like myYear+'%';不能用加号，这个是ORACEL，用 || 。

Answer 16

顺便问一下：
如果现在数据库里面已经有记录了，比如已经有2004-0120。
而我建的squence是从1开始的，那么我
在sql plus窗口中做一个什么操作，可以让squence当前的数值达到121呢？
万分感谢，我这就试一试。

Answer 17

最后这样，我再测试以下：
declare
  myYear      Varchar(10);
  myYearCount NUMBER(10);
  seq_val     NUMBER(5);
BEGIN
  select (to_char(sysdate,'yyyymm')||'%') into myYear from dual;
  select count(HIRENO) into myYearCount from CARHIRE where HIRENO like myYear;
  if myYearCount<1 then 
    select CARHIRE_SQUENCE.currval into seq_val from dual;
    --以下循环执行完以后，MY_seq.curr等于9999，下次取MY_seq.nextval就等于1
    while seq_val<9999 loop
      select CARHIRE_SQUENCE.nextval into seq_val from dual;
    end loop;
  end IF;
  select to_char(sysdate,'yyyymm')||'-'||lpad(to_char(CARHIRE_SQUENCE.nextval),4,'0') into :new.HIRENO from dual;
END;

Answer 18

我创建了carhire_squence
也有carhire表。可插入纪录时还是出错了。
SQL>  insert into carhire(carno,carrysum)values('xin-24280',12.34);
 insert into carhire(carno,carrysum)values('xin-24280',12.34)
             *
ERROR 位于第 1 行:
ORA-08002: 序列CARHIRE_SQUENCE.CURRVAL 尚未在此进程中定义
ORA-06512: 在"JAMIS.T_INSERT_CARHIRE", line 9
ORA-04088: 触发器 'JAMIS.T_INSERT_CARHIRE' 执行过程中出错急，在线等 胖子等诸位高人解答

Answer 19

不好意思，sequence刚建立currval是空的，所以出错了。
我刚调试过了，只要手动执行
select carhire_squence.nextval from dual 
一次就可以了。
至于你的程序，两种方法：
1 修改一下来防止第一次运行时候的问题
2 不用修改，在建立carhire_squence时候执行一下那条语句，让currval有值就可以不用管了。

Answer 20

问题解决没有？
我帮你找了一个可以较好解决这个问题的贴子
http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=160143
其实思路我和他的差不多，不过我的真的只能算权宜之计，建议用他的方法，虽然要涉及到权限和动态sql，不过有他代码了，相信对你来说不会太麻烦。

Answer 21

我直接将currval改成nextval可以吗？
我试了一下，好像没问题declare
  myYear      Varchar(10);
  myYearCount NUMBER(10);
  seq_val     NUMBER(5);
BEGIN
  select (to_char(sysdate,'yyyymm')||'%') into myYear from dual;
  select count(HIRENO) into myYearCount from CARHIRE where HIRENO like myYear;
  if myYearCount<1 then 
    select CARHIRE_SQUENCE.nextval into seq_val from dual;
    --以下循环执行完以后，MY_seq.curr等于9999，下次取MY_seq.nextval就等于1
    while seq_val<9999 loop
      select CARHIRE_SQUENCE.nextval into seq_val from dual;
    end loop;
  end IF;
  select to_char(sysdate,'yyyymm')||'-'||lpad(to_char(CARHIRE_SQUENCE.nextval),4,'0') into :new.HIRENO from dual;
END;
你介绍的新方法，不简单，我还得好好看看，这句话是什么意思？
dbms_job.submit(jobno,'pro;',sysdate,'sysdate+17/24+1');  谢谢了

Answer 22

还有一个老问题，能不能
select 121 into CARHIRE_SQUENCE.currval from dual;
这样来改变squence的当前值？？？？？？

Answer 23

1、目前还没听说序列可以这样改变的oracle版本，但愿以后会有吧，呵呵~~
2、支持！呵呵，原来同一个问题解决方法倒蛮多的，我以前只会胖子老兄的；学习ing ……

Answer 24

我直接将currval改成nextval可以吗？
理论上没有问题，但如果currval=9999则会让程序多执行9999的空循环，不划算。dbms_job.submit(jobno,'pro;',sysdate,'sysdate+17/24+1');  
这是创建job的语句，用来定时触发执行名为pro的过程改变序列当前值的方法我给你推荐的文档里面有
alter  sequence  sqtest  increment  by  4
你这里要用则要涉及动态SQL，你仔细研究一下那篇文章吧，对你的问题很有帮助的