create or replace procedure up_Reg_FirstReg
(
nOpTypeNum number,
nRecYear number,
nRecNum number,
aRegUserName varchar2,
aCheckFlag number,
aSucc out integer,
aReason out varchar2
)
/**************************************************************************
功 能：
输入参数：
输出参数：
创建日期：
策 略：
**************************************************************************/
is
vSql varchar2(4000);
nBuildID number;
nHouseID number;
vTempRc pk_var.RC;
vTbrghouseRow tbrghouse@DLREGBOOK%rowtype;
vTbRgBuildRow TbRgBuild@DLREGBOOK%rowtype;
vTbCadHouseDWGRow TbRgHouseCadDWG@DLREGBOOK%rowtype;
vRgInfoFid number(15,0);
vRegBookKey number(15,0);
sBlockNum varchar2(4000);
sLandCert varchar2(4000);
sUseDate varchar2(4000);
sLandProp varchar2(4000);
sNationalLandGetType varchar2(4000);
sCollectiveLandUseType varchar2(4000);
nWorkUnitID number;
vReghousenum varchar2(32);
vSitnumgathe varchar2(200);
vArchitarea number(32,2);
vRightRangeNum number;
vSucc number;
vTempRcA pk_var.RC;
vHouseCount number;
vReceiveSitNumGather varchar2(1000);
begin
aSucc := 0;
up_Reg_GetLandInfo(nOpTypeNum, nRecYear, nRecNum,
sBlockNum, sLandCert, sUseDate,
sLandProp, sNationalLandGetType, sCollectiveLandUseType,
vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
nWorkUnitID := uf_reg_getworkunit;
vSql :='select count(*)
from tbwkhouse a
where a.OpTypeNum = :OpTypeNum3
and a.RecYear = :RecYear3
and a.RecNum = :RecNum3';
execute immediate vSql into vHouseCount using nOpTypeNum, nRecYear, nRecNum;
vSql :=
'select
a.RecNumGather, a.oppartnum, trim(b.sitnumgather) SitNumGather, b.district,
b.roadcode,trim(b.sitnumgather) SitNumGather,b.cadid,
c.buildingname,b.unitname,b.floorname,b.doorname,
b.buildingid,b.unitnum,b.floornum,b.doornum,
b.totalfloorcounts,b.architarea,
b.netarearea,b.apportarea,b.alonearea,
b.houseusage,
b.housetype,
b.architstruct,
UF_Reg_GetBuildDate(b.completedyear) BuildDate,
b.buildingid,b.houseid,b.fid,b.sitnumgather,
b.blocknum,b.landcertnum,
b.landcharacter,
b.nationallandgettype, b.collectivelandusetype,
b.slandusedate
from tbwkbasicinfo a,tbwkhouse b,tbwkbuilding c,
(select /*+ rule */ d1.fid, d1.buildingid, c1.apportarea
from tbwkbuilding d1,
(select a1.buildingid, sum(nvl(a1.apportarea, 0)) apportarea
from tbwkhouse a1
where a1.OpTypeNum = :OpTypeNum1
and a1.RecYear = :RecYear1
and a1.RecNum = :RecNum1
group by a1.buildingid) c1
where d1.buildingid = c1.buildingid) e
where a.fid = b.basicinfoid
and b.buildingid=c.buildingid
and b.houseid > 0
and b.houserighttype=''3''
and a.OpTypeNum = :OpTypeNum3 and a.RecYear = :RecYear3 and a.RecNum = :RecNum3
and b.buildingid = e.buildingid(+)
';
--取出游标数据
open vTempRc for vSql using nOpTypeNum, nRecYear, nRecNum,
nOpTypeNum, nRecYear, nRecNum;
loop
fetch vTempRc into
vTbrghouseRow.RECNUMGATHER, vTbrghouseRow.oppartnum, vTbrghouseRow.SITNUMGATHER, vTbrghouseRow.DISTRICT,
vTbrghouseRow.ROADCODE, vTbrghouseRow.SITNAME, vTbCadHouseDWGRow.cadhousenum,
vTbrghouseRow.BUILDNAME, vTbrghouseRow.UNITNAME, vTbrghouseRow.FLOORNAME, vTbrghouseRow.DOORNAME,
vTbrghouseRow.BUILDNUM, vTbrghouseRow.UNITNUM, vTbrghouseRow.FLOORNUM, vTbrghouseRow.DOORNUM,
vTbrghouseRow.TOTALFLOORCOUNTS, vTbrghouseRow.ARCHITAREA,
vTbrghouseRow.NETAREAREA, vTbrghouseRow.APPORTAREA, vTbrghouseRow.ALONEAREA,
vTbrghouseRow.HOUSEUSAGE,
vTbrghouseRow.HOUSETYPE,
vTbrghouseRow.ARCHITSTRUCT,
vTbrghouseRow.COMPLETEDYEAR,
nBuildID, nHouseID, vRightRangeNum, vReceiveSitNumGather,
vTbrghouseRow.BlockNum, vTbrghouseRow.LANDCERTNUM,
vTbrghouseRow.LANDCHARACTER,
vTbrghouseRow.NATIONALLANDGETTYPE, vTbrghouseRow.COLLECTIVELANDUSETYPE,
vTbrghouseRow.SLANDUSEDATE;
exit when vTempRc%notfound;
if vHouseCount = 1 then
if vReceiveSitNumGather is not null then
vTbrghouseRow.SITNUMGATHER := vReceiveSitNumGather;
end if;
end if;
up_Reg_GetTbRgInfoID(nOpTypeNum, nRecYear, nRecNum,
nBuildID, nHouseID,
vTbrghouseRow.DISTRICT, vTbrghouseRow.COMPLETEDYEAR,
vRgInfoFid, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
vTbrghouseRow.RgInfoFid := vRgInfoFid;
vTbrghouseRow.RegUserName := aRegUserName;
up_REG_Insert_House(vTbrghouseRow, vTbCadHouseDWGRow, '', aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
up_REG_Insert_HouseCAD(vTbCadHouseDWGRow.cadhousenum, vRgInfoFid, vTbrghouseRow.DISTRICT, aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, '写入房屋CAD信息时出错！', true);
end if;
vRegBookKey := UF_GetMaxRegBookKey@DLREGBOOK(vTbrghouseRow.DISTRICT);
vSql :=
' Insert into TbRgDroit@DLREGBOOK
(FID, RECNUMGATHER, OpPartNum, OwnerName,
RegDate, RegUserName, RgInfoFid, CheckFlag)
values
( :FID, :RECNUMGATHER, :OpPartNum, :OwnerName,
sysdate, :RegUserName, :RgInfoFid, :CheckFlag)';
execute immediate vSql using
vRegBookKey, vTbrghouseRow.RECNUMGATHER, vTbrghouseRow.oppartnum, '全体业主',
aRegUserName, vRgInfoFid, aCheckFlag;
vRegBookKey := UF_GetMaxRegBookKey@DLREGBOOK(vTbrghouseRow.DISTRICT);
vSql := 'insert into TbRgOwnerDetail@DLREGBOOK
(FID, RECNUMGATHER, OWNERNAME,
RGINFOFID)
values
(:FID, :RECNUMGATHER, :OWNERNAME,
:RGINFOFID)';
execute immediate vSql using vRegBookKey, vTbrghouseRow.RECNUMGATHER, '全体业主',
vRgInfoFid;
end loop;
close vTempRc;
--打开房表游标
vSql :=
'select
a.RecNumGather, a.oppartnum, trim(b.sitnumgather) SitNumGather, b.district,
b.roadcode,trim(b.sitnumgather) SitNumGather,b.cadid,
c.buildingname,b.unitname,b.floorname,b.doorname,
b.buildingid,b.unitnum,b.floornum,b.doornum,
b.totalfloorcounts,b.architarea,
b.netarearea,b.apportarea,b.alonearea,
b.houseusage,
b.housetype,
b.architstruct,
UF_Reg_GetBuildDate(b.completedyear) BuildDate,
b.buildingid,b.houseid,b.fid,b.sitnumgather,
b.blocknum,b.landcertnum,
b.landcharacter,
b.nationallandgettype, b.collectivelandusetype,
b.slandusedate,
c.buildingsite, e.apportarea
from tbwkbasicinfo a,tbwkhouse b,tbwkbuilding c,
(select /*+ rule */ d1.fid, d1.buildingid, c1.apportarea
from tbwkbuilding d1,
(select a1.buildingid, sum(nvl(a1.apportarea, 0)) apportarea
from tbwkhouse a1
where a1.OpTypeNum = :OpTypeNum1
and a1.RecYear = :RecYear1
and a1.RecNum = :RecNum1
group by a1.buildingid) c1
where d1.buildingid = c1.buildingid) e
where a.fid = b.basicinfoid
and b.buildingid=c.buildingid
and b.houseid > 0
and a.OpTypeNum = :OpTypeNum3 and a.RecYear = :RecYear3 and a.RecNum = :RecNum3
and b.buildingid = e.buildingid(+)
and b.houserighttype in (''1'',''2'')
';
--取出游标数据
open vTempRc for vSql using nOpTypeNum, nRecYear, nRecNum,
nOpTypeNum, nRecYear, nRecNum
;
loop
fetch vTempRc into
vTbrghouseRow.RECNUMGATHER, vTbrghouseRow.oppartnum, vTbrghouseRow.SITNUMGATHER, vTbrghouseRow.DISTRICT,
vTbrghouseRow.ROADCODE, vTbrghouseRow.SITNAME, vTbCadHouseDWGRow.cadhousenum,
vTbrghouseRow.BUILDNAME, vTbrghouseRow.UNITNAME, vTbrghouseRow.FLOORNAME, vTbrghouseRow.DOORNAME,
vTbrghouseRow.BUILDNUM, vTbrghouseRow.UNITNUM, vTbrghouseRow.FLOORNUM, vTbrghouseRow.DOORNUM,
vTbrghouseRow.TOTALFLOORCOUNTS, vTbrghouseRow.ARCHITAREA,
vTbrghouseRow.NETAREAREA, vTbrghouseRow.APPORTAREA, vTbrghouseRow.ALONEAREA, vTbrghouseRow.HOUSEUSAGE,
vTbrghouseRow.HOUSETYPE,
vTbrghouseRow.ARCHITSTRUCT,
vTbrghouseRow.COMPLETEDYEAR,
nBuildID, nHouseID, vRightRangeNum, vReceiveSitNumGather,
vTbrghouseRow.BlockNum, vTbrghouseRow.LANDCERTNUM,
vTbrghouseRow.LANDCHARACTER,
vTbrghouseRow.NATIONALLANDGETTYPE, vTbrghouseRow.COLLECTIVELANDUSETYPE,
vTbrghouseRow.SLANDUSEDATE,
vTbRgBuildRow.BuildName, vTbRgBuildRow.CommTotalAreas;
exit when vTempRc%notfound;
if vHouseCount = 1 then
if vReceiveSitNumGather is not null then
vTbrghouseRow.SITNUMGATHER := vReceiveSitNumGather;
end if;
end if;
up_Reg_GetTbRgInfoID(nOpTypeNum, nRecYear, nRecNum,
nBuildID, nHouseID,
vTbrghouseRow.DISTRICT, vTbrghouseRow.COMPLETEDYEAR,
vRgInfoFid, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
vTbrghouseRow.RgInfoFid := vRgInfoFid;
vTbrghouseRow.RegUserName := aRegUserName;
up_REG_Insert_House(vTbrghouseRow, vTbCadHouseDWGRow, '', aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
up_REG_Insert_HouseCAD(vTbCadHouseDWGRow.cadhousenum, vRgInfoFid, vTbrghouseRow.DISTRICT, aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, '写入房屋CAD信息时出错！', true);
end if;
up_Reg_InsertDroit(nOpTypeNum, nRecYear, nRecNum, vTbrghouseRow.RgInfoFid, vRightRangeNum,
vTbrghouseRow.DISTRICT, vTbrghouseRow.RegUserName, aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
if vTbRgBuildRow.CommTotalAreas > 0 then
vRegBookKey := UF_GetMaxRegBookKey@DLREGBOOK(vTbrghouseRow.DISTRICT);
vSql :=
' insert into TbRgBuild@DLREGBOOK
(FID,RecNumGather, Buildname,
Commtotalareas, Owner,
Regdate, Regusername, Rginfofid)
values
(:FID, :Buildname, :RecNumGather,
:Commtotalareas, :Owner,
sysdate, :RegUserName, :RgInfoFid)';
execute immediate vSql using
vRegBookKey,vTbrghouseRow.RECNUMGATHER, vTbRgBuildRow.BuildName,
vTbRgBuildRow.CommTotalAreas, '该幢所有业主',
aRegUserName, vRgInfoFid;
end if;
vsql := 'select b.reghousenum, a.sitnumgather, a.architarea
from tbwkhouse a, tbrginfo@DLREGBOOK b
where a.OpTypeNum = :OpTypeNum
and a.RecYear = :RecYear
and a.RecNum = :RecNum
and a.houserighttype=''3''
and a.houseid=b.houseid
and b.workunit = :workunit ';
open vTempRcA for vsql using nOpTypeNum, nRecYear, nRecNum, nWorkUnitID;
loop
fetch vTempRcA into vReghousenum, vSitnumgathe, vArchitarea;
exit when vTempRcA%notfound;
vRegBookKey := UF_GetMaxRegBookKey@DLREGBOOK(vTbrghouseRow.DISTRICT);
vsql := 'insert into TbRgBuild@DLREGBOOK
(Fid,RecNumGather, Buildnumber, Buildname, Commtotalareas, Owner,
Regdate, Regusername, Rginfofid, CheckFlag)
values(:Fid,:RecNumGather, :Buildnumber, :Buildname, :Commtotalareas, :Owner,
sysdate, :Regusername, :Rginfofid, :CheckFlag)';
execute immediate vSql using
vRegBookKey,vSitnumgathe, vReghousenum, vSitnumgathe, vArchitarea, '全体业主',
aRegUserName, vRgInfoFid, aCheckFlag;
end loop;
close vTempRcA;
end loop;
close vTempRc;
--commit;
aSucc := 1;
exception
when others then
--rollback;
aSucc := 0;
aReason := 'up_Reg_FirstReg'||SubStr(sqlerrm, 1, 200)||aReason;
end up_Reg_FirstReg;
相关推荐
主要介绍了oracle存储过程实例,实现oracle查询数据分页，大家参考使用吧
oracle 存储过程 实例 教程 oracle 存储过程 实例 教程 对于初学者来说是很好的例题
php 访问oracle 存储过程实例详解 比如我的本地Oracle数据库有一个package,里面有一个存储过程: create or replace package PKG_TRANS_REL is -- Author : test -- Created : -- Purpose : test -- Public type...
Oracle存储过程返回游标有两种实现方法一种是声明系统游标,一种是声明自定义游标，本文将详细介绍，需要了解的朋友可以参考下
ORACLE数据库存储过程和mysql数据库存储过程实例，以及存储过程的优化。
主要介绍了Oracle存储过程循环语法,结合实例形式分析了Oracle基本的while、for循环使用方法,具有一定参考借鉴价值,需要的朋友可以参考下
二种oracle存储过程创建表分区实例