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  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;

 

转载于:https://www.cnblogs.com/valiant1882331/p/4861409.html