oracle存储过程

现在的位置: 首页 > 综合 > 正文

oracle存储过程

2017年12月19日 ⁄ 综合 ⁄ 共 6083字 ⁄ 字号小中大 ⁄ 评论关闭

存储过程

create or replace procedure pollution_level_judge(pollution_level in hm_pollution_level.cu_level%type,pollution_level_description out varchar)as icount number;
 begin
   if pollution_level<1 then
   pollution_level_description:='清洁';
   elsif pollution_level<2 then
   pollution_level_description:='轻污染';
   elsif pollution_level<3 then
   pollution_level_description:='污染';
   elsif pollution_level<5 then
   pollution_level_description:='重污染';
   else
   pollution_level_description:='严重污染';
   end if;
   -- DBMS_OUTPUT.PUT_LINE('测试');
   exception
      when too_many_rows then
      DBMS_OUTPUT.PUT_LINE('返回值多于1行');
      when others then
      DBMS_OUTPUT.PUT_LINE('在pollution_level_judge存储过程中出错！');
end;

测试存储过程

declare
         realsal hm_pollution_level.cu_level%type;
         realname varchar(40);
         --realjob varchar(40);
  begin   --//存储过程调用开始
         realsal:=5.2;
        realname:='个人';
       -- realjob:='CLERK';
 pollution_level_judge(realsal,realname); --    －－必须按顺序
 DBMS_OUTPUT.PUT_LINE(realname);
       -- DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
 END;--  //过程调用结束

触发器中调用存储过程

create or replace trigger singleFactorMethod
--after insert or update
after insert
on hm_v2
for each row
declare
cd_standard hm_standard.cd_standard%type;
cd_level_description nvarchar2(20);
hg_standard hm_standard.hg_standard%type;
hg_level_description nvarchar2(20);
dp_as_standard hm_standard.dp_as_standard%type;
dp_as_level_description nvarchar2(20);
cu_standard hm_standard.cu_standard%type;
cu_level_description nvarchar2(20);
pb_standard hm_standard.pb_standard%type;
pb_level_description nvarchar2(20);
cr_standard hm_standard.cr_standard%type;
cr_level_description nvarchar2(20);
zn_standard hm_standard.zn_standard%type;
zn_level_description nvarchar2(20);
ni_standard hm_standard.ni_standard%type;
ni_level_description nvarchar2(20);
begin
SELECT cd_standard INTO cd_standard FROM hm_standard;
SELECT hg_standard INTO hg_standard FROM hm_standard;
SELECT dp_as_standard INTO dp_as_standard FROM hm_standard;
SELECT cu_standard INTO cu_standard FROM hm_standard;
SELECT pb_standard INTO pb_standard FROM hm_standard;
SELECT cr_standard INTO cr_standard FROM hm_standard;
SELECT zn_standard INTO zn_standard FROM hm_standard;
SELECT ni_standard INTO ni_standard FROM hm_standard;
pollution_level_judge(:new.cd/cd_standard,cd_level_description); -- 调用存储过程
pollution_level_judge(:new.hg/hg_standard,hg_level_description); -- 调用存储过程
pollution_level_judge(:new.dp_as/dp_as_standard,dp_as_level_description); -- 调用存储过程
pollution_level_judge(:new.cu/cu_standard,cu_level_description); -- 调用存储过程
pollution_level_judge(:new.pb/pb_standard,pb_level_description); -- 调用存储过程
pollution_level_judge(:new.cr/cr_standard,cr_level_description); -- 调用存储过程
pollution_level_judge(:new.zn/zn_standard,zn_level_description); -- 调用存储过程
pollution_level_judge(:new.ni/ni_standard,ni_level_description); -- 调用存储过程
insert into hm_pollution_level_description values(:new.objectid,cd_level_description,hg_level_description,dp_as_level_description,cu_level_description,pb_level_description,cr_level_description,zn_level_description,ni_level_description);
insert into hm_pollution_level values(:new.objectid,:new.cd/cd_standard,:new.hg/hg_standard,:new.dp_as/dp_as_standard,:new.cu/cu_standard,:new.pb/pb_standard,:new.cr/cr_standard,:new.zn/zn_standard,:new.ni/ni_standard);
end;

update触发器调用存储过程

create or replace trigger singleFactorMethod_update
--after insert or update
after update
on hm_v2
for each row
declare
cd_standard hm_standard.cd_standard%type;
cd_level_description_new nvarchar2(20);
hg_standard hm_standard.hg_standard%type;
hg_level_description_new nvarchar2(20);
dp_as_standard hm_standard.dp_as_standard%type;
dp_as_level_description_new nvarchar2(20);
cu_standard hm_standard.cu_standard%type;
cu_level_description_new nvarchar2(20);
pb_standard hm_standard.pb_standard%type;
pb_level_description_new nvarchar2(20);
cr_standard hm_standard.cr_standard%type;
cr_level_description_new nvarchar2(20);
zn_standard hm_standard.zn_standard%type;
zn_level_description_new nvarchar2(20);
ni_standard hm_standard.ni_standard%type;
ni_level_description_new nvarchar2(20);
begin
SELECT cd_standard INTO cd_standard FROM hm_standard;
SELECT hg_standard INTO hg_standard FROM hm_standard;
SELECT dp_as_standard INTO dp_as_standard FROM hm_standard;
SELECT cu_standard INTO cu_standard FROM hm_standard;
SELECT pb_standard INTO pb_standard FROM hm_standard;
SELECT cr_standard INTO cr_standard FROM hm_standard;
SELECT zn_standard INTO zn_standard FROM hm_standard;
SELECT ni_standard INTO ni_standard FROM hm_standard;
pollution_level_judge(:new.cd/cd_standard,cd_level_description_new); -- 调用存储过程
pollution_level_judge(:new.hg/hg_standard,hg_level_description_new); -- 调用存储过程
pollution_level_judge(:new.dp_as/dp_as_standard,dp_as_level_description_new); -- 调用存储过程
pollution_level_judge(:new.cu/cu_standard,cu_level_description_new); -- 调用存储过程
pollution_level_judge(:new.pb/pb_standard,pb_level_description_new); -- 调用存储过程
pollution_level_judge(:new.cr/cr_standard,cr_level_description_new); -- 调用存储过程
pollution_level_judge(:new.zn/zn_standard,zn_level_description_new); -- 调用存储过程
pollution_level_judge(:new.ni/ni_standard,ni_level_description_new); -- 调用存储过程
--insert into hm_pollution_level_description values(:new.objectid,cd_level_description,hg_level_description,dp_as_level_description,cu_level_description,pb_level_description,cr_level_description,zn_level_description,ni_level_description);
update hm_pollution_level_description set cd_level_description=cd_level_description_new,hg_level_description=hg_level_description_new,dp_as_level_description=dp_as_level_description_new,cu_level_description=cu_level_description_new,pb_level_description=pb_level_description_new,cr_level_description=cr_level_description_new,zn_level_description=zn_level_description_new,ni_level_description=ni_level_description_new where objectid=:new.objectid;
update hm_pollution_level set cd_level=:new.cd/cd_standard,hg_level=:new.hg/hg_standard ,dp_as_level=:new.dp_as/dp_as_standard ,cu_level=:new.cu/cu_standard,pb_level=:new.pb/pb_standard,cr_level=:new.cr/cr_standard ,zn_level=:new.zn/zn_standard ,ni_level=:new.ni/ni_standard where objectid=:new.objectid;
end;

create or replace trigger singleFactorMethod_delete
--after insert or update
after delete
on hm_v2
for each row
begin
  delete from hm_pollution_level where objectid=:old.objectid;
  delete from hm_pollution_level_description where objectid=:old.objectid;
end;

数据库表备份