Question

定义一个oracle 自定义函数 function

用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）中的函数

Answer 1

两种方法，建议你用触发器：
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;

Answer 2

这不是应该写一个触发器吗？