你的位置:首页 > 数据库

[数据库]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;