oracle发送表jobsend,oracle储存过程，job,视图，触发器-Oracle

存储过程

create or replace procedure TestPro(Descerr out varchar2 ) is

begin

select * from test;

exception

when others then

Descerr :='接口表数据生成失败！'|| sqlerrm;

end TestPro;

job任务

declare Descerr varchar2(2000);

begin

--储存过程

TestPro(Descerr=>descerr);

commit;

end

视图

create or replace view TestView as

select id,name from test1;

触发器

create or replace trigger TestTrigger

alter insert or update on test1--两个表test1和test2

for each row

declare

--声明变量

lenNum Number(12,2);

status varchar2(50);

begin

select count(1) into lenNum from test2 t where t.id=:new.id;

--判断状态

case :new.ORDERSTATE

when '等待到款' then

begin

statusnum := 'WAIT_BUYER_PAY';

end;

when '等待发货' then

begin

statusnum := 'WAIT_SELLER_SEND_GOODS';

end;

end case;

--判断数量

if(lenNum<1) then

insert into test2

(name,stat)--或者用values(：new.name,:new.stat)

select name ,stat from test1 t where id=:new.id;

else

--update语句

end if;

end TestTrigger;