oracle储存过程，job,视图，触发器（记性不好，写个例子自己记）

oracle储存过程，job,视图，触发器（记性不好，写个例子自己记）

存储过程

1 create or replace procedure TestPro(Descerr out  varchar2 ) is
2 begin
3     select * from test;
4 exception
5     when others then
6         Descerr :='接口表数据生成失败！'|| sqlerrm;
7 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  TestTriggeralter insert or update on test1--两个表test1和test2for each row
declare--声明变量lenNum Number(12,2);status varchar2(50);
beginselect count(1) into lenNum from test2 t where t.id=:new.id;
--判断状态
case :new.ORDERSTATEwhen '等待到款' thenbeginstatusnum := 'WAIT_BUYER_PAY';end;when '等待发货' thenbeginstatusnum := 'WAIT_SELLER_SEND_GOODS';end;
end case;
--判断数量
if(lenNum<1) theninsert 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;

转载于:.html