模式：
create or replace procedure 过程名(参数名 参数类型,...)
is
变量名 变量类型;
begin
过程内容;
end 过程名;
/
show err;
举例：
create or replace procedure proc_blddesignusagearea(p_buildid number) is
/*
函数功能：统计楼栋下房屋的用途、面积、套数等信息
*/
l_buildarea bldroom.buildarea%type;
l_usearea bldroom.usearea%type;
l_publicarea bldroom.publicarea%type;
l_sets building.sets%type;
l_cnt1 number:=0;
begin
--[blddesignusagearea A，bldroom B，将属于A而不属于B的用途删掉]--
for brrs in (select nvl(designusage,'') designusage from blddesignusagearea where buildid=p_buildid minus select distinct nvl(designusage,'') designusage from bldroom where buildid=p_buildid and resopertype in (1,4))
loop
delete from bldDesignusagearea where buildid=p_buildid and nvl(designusage,'')=brrs.designusage;
end loop;
--[将同属于A、B的用途更新，将不属于A但属于B的用途新增]--
for brrs in (select distinct nvl(designusage,'') designusage from bldroom where buildid=p_buildid and resopertype in (1,4))
loop
select sum(buildarea),sum(usearea),sum(publicarea),count(bldroomid) into l_buildarea,l_usearea,l_publicarea,l_sets from bldroom where buildid=p_buildid and resopertype in (1,4) and nvl(designusage,'')=brrs.designusage;
select count(*) into l_cnt1 from blddesignusagearea where buildid=p_buildid and nvl(designusage,'')=brrs.designusage;
if l_cnt1=1 then --[楼栋下该用途存在只更新面积]--
if l_sets>0 then
update bldDesignusagearea set buildarea=l_buildarea,usearea=l_usearea,publicarea=l_publicarea,sets=l_sets where buildid=p_buildid and nvl(designusage,'')=brrs.designusage;
else
delete from bldDesignusagearea where buildid=p_buildid and nvl(designusage,'')=brrs.designusage;
end if;
elsif l_cnt1=0 then --[楼栋下该用途不存在新增记录]--
if l_sets>0 then
insert into bldDesignusagearea (buildid,designusage,buildarea,usearea,publicarea,sets) select p_buildid,brrs.designusage,l_buildarea,l_usearea,l_publicarea,l_sets from dual;
-- else
end if;
end if;
end loop;
end proc_blddesignusagearea;
/
show err;
