快捷搜索:  as  2018  FtCWSyGV  С˵  test  xxx  Ψһ  w3viyKQx

伟德国际官方app下载安卓版:Oracle序列(SEQUENCE)使用示例



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

create or replace trigger TRI_SUPPLIE伟德国际官方app下载安卓版R

before insert onSUPPLIER

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 onSUPPLIER

for each row

declarev_supval number(20);

begin

selectseq_supid.NEXTVAL into v_supvalfro伟德国际官方app下载安卓版m dual;

select v_supvalinto:new.supcode from dual;

select v_supvalinto: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 PROJEC伟德国际官方app下载安卓版TID INTO V_PROJECTIDFROM ASSETPROJECT WHERE PROJECTCODE = :NEW.PROJECTCODE and rownum=1 ;

IF (NVL(V_PROJECTID,0)0) THEN -伟德国际官方app下载安卓版---假如不存在编码记录

begin

SELECT SEQ_ASSETPROJECTID.NEXTVAL INTO :NEW.PROJECTID FR伟德国际官方app下载安卓版OM 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;

您可能还会对下面的文章感兴趣: