你要先新增一個SEQUENCE 然後在開一個Tigger.
至於SEQENCE ,Tigger 這些妳先去查一個相關資料
下面是我開過的範例 你可以參考一下:
CREATE SEQUENCE S_Trade2;Create or replace Trigger T_Trade2
Before Insert On M_Trade2
for each row
declare
begin
M_Trade2.M_Key= S_Trade2.NextVal
endcreate or replace trigger T_Trade2
before insert on M_Trade2
for each row
declare
begin
if :New.M_Key is null then
select s_Trade2.nextval into :M_Trade2.M_Key from dual
end if
end
至於SEQENCE ,Tigger 這些妳先去查一個相關資料
下面是我開過的範例 你可以參考一下:
CREATE SEQUENCE S_Trade2;Create or replace Trigger T_Trade2
Before Insert On M_Trade2
for each row
declare
begin
M_Trade2.M_Key= S_Trade2.NextVal
endcreate or replace trigger T_Trade2
before insert on M_Trade2
for each row
declare
begin
if :New.M_Key is null then
select s_Trade2.nextval into :M_Trade2.M_Key from dual
end if
end
Select name,num,flag1,flag2,max(no)+1 From Table2 Where name=name
INSERT INTO T_RANK_I
SELECT NAME,NUM,FLAG1,FLAG2,
(SELECT COUNT(NAME) FROM T_RANK B WHERE A.NAME = B.NAME AND A.ROWID > B.ROWID) NO
FROM T_RANK A2.使用RANK的时候:INSERT INTO T_RANK_I
SELECT NAME,NUM,FLAG1,FLAG2,
(RANK() OVER (PARTITION BY NAME ORDER BY NAME,NUM,FLAG1,FLAG2) - 1 ) NO
FROM T_RANK
建立第二张表name,max_no
然后插入之前需要锁表,select max_no from table2 where name = XXX for update;
取得no,
再insert table,update table2,最后一起commit
不过我觉得还是应该考虑建个序列!
Select name,num,flag1,flag2,count(name)+1 From Table2 Where name=name