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;      

邀月注：本文版權由邀月和部落格園共同所有，轉載請注明出處。

助人等于自助!  [email protected]