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

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

存储过程

1 create or replace procedure TestPro(Descerr out  varchar2 ) is2 begin3     select * from test;4 exception5     when others then6         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  TestTrigger    alter insert or update on test1--两个表test1和test2    for each rowdeclare    --声明变量    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;