隻能用過程來處理,即在插入數據後調用一次存儲過程 --example SQL> SELECT * FROM BAO_TEMP 2 WHERE COL1='B' 3 ORDER BY COL2 DESC 4 /COL1 COL2 SEQ ---------- ---------- ---------- B 31 B 21 B 11 ------------------------------------------- SQL> create or replace procedure pro_test 2 ( 3 new_col1 in varchar2 4 ) 5 as 6 t number(10); 7 cursor mycur is select * from bao_temp 8 where col1 = new_col1 order by col2; 9 record mycur%rowtype; 10 begin 11 t:=1; 12 open mycur; 13 loop 14 fetch mycur into record; 15 exit when mycur%notfound; 16 update bao_temp set seq = t 17 where col2=record.col2; 18 t:= t+1; 19 commit; 20 end loop; 21 close mycur; 22 end; 23 /程序已被建立目前歷時: 00:00:00.56 SQL> insert into bao_temp 2 (col1,col2) values ('B',45) 3 /建立了 1 列目前歷時: 00:00:00.47 SQL> commit;確認完成目前歷時: 00:00:00.16 SQL> exec pro_test('B')PL/SQL 程序順利完成目前歷時: 00:00:00.94 SQL> SELECT * FROM BAO_TEMP 2 WHERE COL1='B' 3 ORDER BY COL2 DESC 4 /COL1 COL2 SEQ ---------- ---------- ---------- B 45 4 B 31 3 B 21 2 B 11 1選取了 4 列目前歷時: 00:00:00.47
呵呵,我的意思是after不可以 before可以的,修改如下: create or replace trigger king before insert on tb_1 for each row declare temp tb_1.col1%type; t number:=1; cursor mycur is select * from tb_1 where col1=temp order by col2 desc; record mycur%rowtype; begin temp:=:new.col1; open mycur; loop fetch mycur into record; exit when mycur%notfound; update tb_1 set seq=t where col2=record.col2; t:=t+1; end loop; close mycur; :new.seq:=t;--加這兒就可以了 end;
--example: SQL> create or replace trigger king 2 before insert on bao_temp 3 for each row 4 declare 5 temp bao_temp.col1%type; 6 t number:=1; 7 cursor mycur is select * from bao_temp where col1=temp order by col2 desc; 8 record mycur%rowtype; 9 begin 10 temp:=:new.col1; 11 open mycur; 12 loop 13 fetch mycur into record; 14 exit when mycur%notfound; 15 update bao_temp set seq=t where col2=record.col2; 16 t:=t+1; 17 end loop; 18 close mycur; 19 :new.seq:=t; 20 end; 21 /觸發程式已被建立目前歷時: 00:00:00.62 SQL> insert into bao_temp (col1,col2) values ('C',78) 2 /建立了 1 列目前歷時: 00:00:00.15 SQL> SELECT * FROM BAO_TEMP WHERE COL1='C' 2 /COL1 COL2 SEQ ---------- ---------- ---------- C 31 2 C 11 4 C 21 3 C 56 1 C 78 5選取了 5 列目前歷時: 00:00:00.31
在最後加上:new.seq=t就可以了 不過要記得 是before
to baojianjun(包子) 呵呵~你和我错的一样,如果我插入这样一个记录('A',99,99) 这个记录给的seq应该是1但是现在给的缺是最后一个
oracle里触发器里能调用过程吗? create or replace trigger t_name after insert on tn_1 for each row begin exec pro_test(:new.col1); end; 这样好象不对~该怎么写啊?
問題解決了: --example SQL> create or replace trigger king 2 before insert on bao_temp 3 for each row 4 declare 5 temp bao_temp.col1%type; 6 t number:=1; 7 i number:=1; 8 iscol2 number(10); 9 cursor mycur is select * from bao_temp 10 where col1=temp order by col2 desc; 11 record mycur%rowtype; 12 begin 13 temp:=:new.col1; 14 15 open mycur; 16 loop 17 fetch mycur into record; 18 exit when mycur%notfound; 19 20 iscol2:= record.col2; 21 22 if (iscol2 >= :new.col2) then 23 update bao_temp set seq=t 24 where col2=record.col2; 25 end if ; 26 if (iscol2 < :new.col2) and (i=1) then 27 :new.seq:=t; 28 i:=i+1; 29 end if ; 30 if (iscol2 < :new.col2) and (i>1) then 31 update bao_temp set seq=t+1 32 where col2=record.col2; 33 end if ; 34 35 36 t:=t+1; 37 38 end loop; 39 close mycur; 40 41 end; 42 /觸發程式已被建立目前歷時: 00:00:00.63 SQL> insert into bao_temp values ('A',23,23) 2 /建立了 1 列目前歷時: 00:00:00.16 SQL> SELECT * FROM BAO_TEMP WHERE COL1='A' 2 ORDER BY COL2 DESC 3 /COL1 COL2 SEQ ---------- ---------- ---------- A 31 1 A 23 2 A 21 3 A 11 4選取了 4 列目前歷時: 00:00:00.31 SQL> insert into bao_temp values ('A',99,99) 2 /建立了 1 列目前歷時: 00:00:00.16 SQL> SELECT * FROM BAO_TEMP WHERE COL1='A' 2 ORDER BY COL2 DESC 3 /COL1 COL2 SEQ ---------- ---------- ---------- A 99 1 A 31 2 A 23 3 A 21 4 A 11 5選取了 5 列目前歷時: 00:00:00.16 SQL> insert into bao_temp values ('A',78,78) 2 /建立了 1 列目前歷時: 00:00:00.16 SQL> SELECT * FROM BAO_TEMP WHERE COL1='A' 2 ORDER BY COL2 DESC 3 /COL1 COL2 SEQ ---------- ---------- ---------- A 99 1 A 78 2 A 31 3 A 23 4 A 21 5 A 11 6選取了 6 列目前歷時: 00:00:00.47
好漂亮的i,我太弱了~包子留个msn以后指点我吧~谢谢了
to baojianjun(包子) 其实还是错了,要在loop后加上 if :new.col2<record.col2 then :new.seq:=t; end if;
to baojianjun(包子) 我还有问题想问,这个表做修改和删除的触发器怎么做?我搞了一上午了没结果
什么也不要处理了: 只要创建一个视图:Select m.col1,n.col2,(Select Count(1) From tb_1 Where col1=m.col1 And col2 >=n.col2) as seq From (select Distinct(t1.col1) As col1 from tb_1 t1) m ,(select col1 As col1 ,col2 from tb_1 t Order By col2 Desc) n Where m.col1=n.col1
我也想了一下,現在還沒有思路,好像delete的操作和insert 不太一樣有結果我發消息給你
create or replace trigger king after delete on bao_temp declare t number:=1; cursor cur_col1 is select distinct col1 from bao_temp ; temp bao_temp.col1%type; cursor mycur is select * from bao_temp where col1=temp order by col2 desc; record mycur%rowtype; begin open cur_col1; loop fetch cur_col1 into temp; exit when cur_col1%notfound; t:=1;
open mycur; loop fetch mycur into record; exit when mycur%notfound; update bao_temp set seq=t where col1 =temp and col2=record.col2;
t:=t+1; end loop; close mycur; end loop; close cur_col1; end;------------ SQL> select * from bao_temp order by col1,col2 desc 2 /COL1 COL2 SEQ ---------- ---------- ---------- A 31 100 A 21 100 A 11 100 B 45 100 B 31 100 B 21 100 B 11 100 C 78 100 C 56 100 C 31 100 C 21 100 C 11 100選取了 12 列目前歷時: 00:00:00.94 SQL> delete from bao_temp where col1='C' and col2=56 ;刪除了 1 列目前歷時: 00:00:00.16 SQL> select * from bao_temp order by col1,col2 desc 2 /COL1 COL2 SEQ ---------- ---------- ---------- A 31 1 A 21 2 A 11 3 B 45 1 B 31 2 B 21 3 B 11 4 C 78 1 C 31 2 C 21 3 C 11 4選取了 11 列目前歷時: 00:00:00.78隻能使用語句級的觸發器來處理,但是當數據量大的時候會比較的慢, 因為這樣是對全表的操作,個人不建議使用。以上例子隻是作為參考。 使用語句後加存儲過程,數據處理量會少很多,隻是處理比較麻煩。update的操作是類似。
--example
SQL> SELECT * FROM BAO_TEMP
2 WHERE COL1='B'
3 ORDER BY COL2 DESC
4 /COL1 COL2 SEQ
---------- ---------- ----------
B 31
B 21
B 11
-------------------------------------------
SQL> create or replace procedure pro_test
2 (
3 new_col1 in varchar2
4 )
5 as
6 t number(10);
7 cursor mycur is select * from bao_temp
8 where col1 = new_col1 order by col2;
9 record mycur%rowtype;
10 begin
11 t:=1;
12 open mycur;
13 loop
14 fetch mycur into record;
15 exit when mycur%notfound;
16 update bao_temp set seq = t
17 where col2=record.col2;
18 t:= t+1;
19 commit;
20 end loop;
21 close mycur;
22 end;
23 /程序已被建立目前歷時: 00:00:00.56
SQL> insert into bao_temp
2 (col1,col2) values ('B',45)
3 /建立了 1 列目前歷時: 00:00:00.47
SQL> commit;確認完成目前歷時: 00:00:00.16
SQL> exec pro_test('B')PL/SQL 程序順利完成目前歷時: 00:00:00.94
SQL> SELECT * FROM BAO_TEMP
2 WHERE COL1='B'
3 ORDER BY COL2 DESC
4 /COL1 COL2 SEQ
---------- ---------- ----------
B 45 4
B 31 3
B 21 2
B 11 1選取了 4 列目前歷時: 00:00:00.47
before可以的,修改如下:
create or replace trigger king
before insert on tb_1
for each row
declare
temp tb_1.col1%type;
t number:=1;
cursor mycur is select * from tb_1 where col1=temp order by col2 desc;
record mycur%rowtype;
begin
temp:=:new.col1;
open mycur;
loop
fetch mycur into record;
exit when mycur%notfound;
update tb_1 set seq=t where col2=record.col2;
t:=t+1;
end loop;
close mycur;
:new.seq:=t;--加這兒就可以了
end;
SQL> create or replace trigger king
2 before insert on bao_temp
3 for each row
4 declare
5 temp bao_temp.col1%type;
6 t number:=1;
7 cursor mycur is select * from bao_temp where col1=temp order by col2 desc;
8 record mycur%rowtype;
9 begin
10 temp:=:new.col1;
11 open mycur;
12 loop
13 fetch mycur into record;
14 exit when mycur%notfound;
15 update bao_temp set seq=t where col2=record.col2;
16 t:=t+1;
17 end loop;
18 close mycur;
19 :new.seq:=t;
20 end;
21 /觸發程式已被建立目前歷時: 00:00:00.62
SQL> insert into bao_temp (col1,col2) values ('C',78)
2 /建立了 1 列目前歷時: 00:00:00.15
SQL> SELECT * FROM BAO_TEMP WHERE COL1='C'
2 /COL1 COL2 SEQ
---------- ---------- ----------
C 31 2
C 11 4
C 21 3
C 56 1
C 78 5選取了 5 列目前歷時: 00:00:00.31
不過要記得 是before
呵呵~你和我错的一样,如果我插入这样一个记录('A',99,99)
这个记录给的seq应该是1但是现在给的缺是最后一个
create or replace trigger t_name
after insert on tn_1
for each row
begin
exec pro_test(:new.col1);
end;
这样好象不对~该怎么写啊?
--example
SQL> create or replace trigger king
2 before insert on bao_temp
3 for each row
4 declare
5 temp bao_temp.col1%type;
6 t number:=1;
7 i number:=1;
8 iscol2 number(10);
9 cursor mycur is select * from bao_temp
10 where col1=temp order by col2 desc;
11 record mycur%rowtype;
12 begin
13 temp:=:new.col1;
14
15 open mycur;
16 loop
17 fetch mycur into record;
18 exit when mycur%notfound;
19
20 iscol2:= record.col2;
21
22 if (iscol2 >= :new.col2) then
23 update bao_temp set seq=t
24 where col2=record.col2;
25 end if ;
26 if (iscol2 < :new.col2) and (i=1) then
27 :new.seq:=t;
28 i:=i+1;
29 end if ;
30 if (iscol2 < :new.col2) and (i>1) then
31 update bao_temp set seq=t+1
32 where col2=record.col2;
33 end if ;
34
35
36 t:=t+1;
37
38 end loop;
39 close mycur;
40
41 end;
42 /觸發程式已被建立目前歷時: 00:00:00.63
SQL> insert into bao_temp values ('A',23,23)
2 /建立了 1 列目前歷時: 00:00:00.16
SQL> SELECT * FROM BAO_TEMP WHERE COL1='A'
2 ORDER BY COL2 DESC
3 /COL1 COL2 SEQ
---------- ---------- ----------
A 31 1
A 23 2
A 21 3
A 11 4選取了 4 列目前歷時: 00:00:00.31
SQL> insert into bao_temp values ('A',99,99)
2 /建立了 1 列目前歷時: 00:00:00.16
SQL> SELECT * FROM BAO_TEMP WHERE COL1='A'
2 ORDER BY COL2 DESC
3 /COL1 COL2 SEQ
---------- ---------- ----------
A 99 1
A 31 2
A 23 3
A 21 4
A 11 5選取了 5 列目前歷時: 00:00:00.16
SQL> insert into bao_temp values ('A',78,78)
2 /建立了 1 列目前歷時: 00:00:00.16
SQL> SELECT * FROM BAO_TEMP WHERE COL1='A'
2 ORDER BY COL2 DESC
3 /COL1 COL2 SEQ
---------- ---------- ----------
A 99 1
A 78 2
A 31 3
A 23 4
A 21 5
A 11 6選取了 6 列目前歷時: 00:00:00.47
其实还是错了,要在loop后加上
if :new.col2<record.col2 then
:new.seq:=t;
end if;
我还有问题想问,这个表做修改和删除的触发器怎么做?我搞了一上午了没结果
只要创建一个视图:Select m.col1,n.col2,(Select Count(1) From tb_1 Where col1=m.col1 And col2 >=n.col2) as seq From
(select Distinct(t1.col1) As col1 from tb_1 t1) m ,(select col1 As col1 ,col2 from tb_1 t Order By col2 Desc) n
Where m.col1=n.col1
after delete on bao_temp
declare
t number:=1;
cursor cur_col1 is
select distinct col1
from bao_temp ;
temp bao_temp.col1%type;
cursor mycur is select * from bao_temp
where col1=temp
order by col2 desc;
record mycur%rowtype;
begin
open cur_col1;
loop
fetch cur_col1 into temp;
exit when cur_col1%notfound; t:=1;
open mycur;
loop
fetch mycur into record;
exit when mycur%notfound; update bao_temp set seq=t
where col1 =temp
and col2=record.col2;
t:=t+1; end loop;
close mycur;
end loop;
close cur_col1;
end;------------
SQL> select * from bao_temp order by col1,col2 desc
2 /COL1 COL2 SEQ
---------- ---------- ----------
A 31 100
A 21 100
A 11 100
B 45 100
B 31 100
B 21 100
B 11 100
C 78 100
C 56 100
C 31 100
C 21 100
C 11 100選取了 12 列目前歷時: 00:00:00.94
SQL> delete from bao_temp where col1='C' and col2=56 ;刪除了 1 列目前歷時: 00:00:00.16
SQL> select * from bao_temp order by col1,col2 desc
2 /COL1 COL2 SEQ
---------- ---------- ----------
A 31 1
A 21 2
A 11 3
B 45 1
B 31 2
B 21 3
B 11 4
C 78 1
C 31 2
C 21 3
C 11 4選取了 11 列目前歷時: 00:00:00.78隻能使用語句級的觸發器來處理,但是當數據量大的時候會比較的慢,
因為這樣是對全表的操作,個人不建議使用。以上例子隻是作為參考。
使用語句後加存儲過程,數據處理量會少很多,隻是處理比較麻煩。update的操作是類似。