Oracle中序列(SEQUENCE)的使用一例

曾经在触发器中使用序列(SEQUENCE):

create or replace trigger TRI_SUPPLIER
  before insert on   SUPPLIER
  for each row
begin
  select seq_supid.NEXTVAL into:new.supcode from dual;
  select seq_supid.CURRVAL into:new.supID from dual;
end;

显然,忽略了并发,修改后如下:

create or replace trigger TRI_SUPPLIER
  before insert on   SUPPLIER
  for each row
declare  v_supval number(20);
begin
  select  seq_supid.NEXTVAL into v_supval  from dual;
  select v_supval  into:new.supcode from dual;
  select v_supval  into:new.supID from dual;
end;

 再附上一个使用触发器防止重复记录的例子:

 

CREATE OR REPLACE TRIGGER TRI_ASSETPROJECT
  BEFORE INSERT ON ASSETPROJECT
  FOR EACH ROW
DECLARE V_PROJECTID NUMBER(20) :=0;
 V_TMP VARCHAR2(100) :='';
BEGIN
     SELECT :NEW.PROJECTCODE INTO V_TMP FROM DUAL;
     dbms_output.put_line(V_TMP);
     SELECT PROJECTID INTO V_PROJECTID  FROM ASSETPROJECT WHERE PROJECTCODE = :NEW.PROJECTCODE and rownum=1 ;    
  IF (NVL(V_PROJECTID,0)<=0) THEN ----如果不存在编码记录
      begin
       SELECT SEQ_ASSETPROJECTID.NEXTVAL INTO :NEW.PROJECTID FROM DUAL;
       dbms_output.put_line('New ID ');
      end;
    ELSE --如果已存在编码记录
      begin
       --SELECT :V_PROJECTID INTO:NEW.PROJECTID FROM DUAL;
       RAISE_APPLICATION_ERROR(-20001, '项目编码已存在,不允许重复添加!'); 
       dbms_output.put_line('Old ID ');
      end;
    end if;
END;

 

 

posted @ 2013-06-27 14:45 邀月 阅读(...) 评论(...) 编辑 收藏