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 存储过程实例
C#访问Oracle存储过程实例源码,C#、VS2010、Oracle、存储过程
ORACLE数据库存储过程和mysql数据库存储过程实例,以及存储过程的优化。
oracle存储过程语法及实例讲解,oracle存储过程语法及实例讲解
oracle存储过程实例1
C#访问Oracle存储过程实例源码,C#、VS2010、Oracle、存储过程 本人收藏了3年的资源 现放出 都是总结了很多系统 软件项目实施过程中的经验的 慢慢积累的
oracle的一个简单存储过程实例,有原文件和使用说明。
oracle存储过程实例PPT教案.pptx
oracle存储过程学习经典(实例),适合刚学习存储过程的广大程序员们