oracle儲存過程，job,視圖，觸發器（記性不好，寫個例子自己記），oraclejob

oracle儲存過程，job,視圖，觸發器（記性不好，寫個例子自己記），oraclejob

存儲過程

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;