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存储过程实例，从而实现数据报表的自动化生成。我们将从创建 Oracle 存储过程开始，接着指导读者如何在水晶报表中应用该存储过程。 一、...
在本例中，“Oracle存储过程实例使用显示游标”着重展示了如何在存储过程中调用函数，并通过游标来处理和更新数据。 首先，我们需要了解存储过程的基本结构。一个存储过程通常包含以下部分： 1. **声明部分**：在...
ORACLE数据库存储过程和mysql数据库存储过程实例，以及存储过程的优化。
Oracle存储过程是数据库管理系统中的一种重要特性，它允许开发者编写包含一系列SQL语句和PL/SQL块的可重用代码段。在这个“Oracle的一个简单存储过程实例”中，我们可以看到如何在Oracle环境中创建、调用和管理存储...
oracle存储过程实例１
这个"Oracle存储过程学习经典(实例)"资源显然是为初学者设计的，旨在帮助他们掌握如何创建、执行和管理存储过程。 存储过程在数据库管理中扮演着关键角色，它可以提升系统的性能，通过减少网络流量和提供预编译的...
下面，我们将深入探讨Oracle存储过程的关键概念，并通过一个具体的实例来理解其构建和执行流程。 ### Oracle存储过程概述 存储过程在数据库服务器上运行，可以包含控制流语句、变量定义、错误处理等结构，使其具备...
oracle存储过程实例PPT教案.pptx
本实例将探讨如何使用C#通过VS2010访问Oracle存储过程。 首先，你需要在VS2010中创建一个新的C#项目，选择相应的.NET Framework版本，如4.0。然后，确保你的系统已经安装了Oracle客户端或者ODP.NET（Oracle Data ...
在学习Oracle存储过程时，结合详细的文档如《oracle存储过程超详细使用手册.doc》和实例资料《oracle存储过程学习经典[语法+实例+调用].doc》会非常有帮助。这些文档通常会涵盖基础语法、实例解析、调用方法、异常...