用oracle 自定義函數 function定義一個能對交易記錄表tbl_business（business_id，user_id，business_date,business_amt,content）如（1 ，1 ，2011/4/19 ，23012 00，‘七匹狼鞋子’）的交易額business_amt進行判斷，當交易額大于200萬時將該交易記錄添加到風險交易表tbl_Rbusiness（business_id，user_id，business_date,business_amt）中的函數
2 回答
MM們
TA貢獻1886條經驗 獲得超2個贊
兩種方法，建議你用觸發器：
create or replace trigger business
after insert on tbl_business
for each row
begin
if :new.business_amt =2000000 then
insert into tbl_Rbusiness(business_id,user_id,business_date,business_amt) values (:new.business_id,:new.user_id,:new.business_date,:new.business_amt);
end if;
end;
函數:
create or replace function amount return number as
v_exists number;
begin
for v in (select * from tbl_business) loop
if (v.business_amt = 2000000) then
select count(*) into v_exists from ip
where business_id = v.business_id
and user_id = v.user_id
and business_date = v.business_date
and business_amt = v.business_amt;
if v_exists = 0 then
insert into ip (business_id,user_id,business_date,business_amt)
values (v.business_id,v.user_id,v.business_date,v.business_amt);
commit;
end if;
end if;
end loop;
end;
- 2 回答
- 0 關注
- 176 瀏覽
添加回答
舉報
0/150
提交
取消