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;

Oracle中序列（SEQUENCE）的使用一例

相关推荐