请教 这个触发器的作用?还有 Pkg_BldroomData_Part.v_NumEntries 的值是从那里传过来的?Create Or Replace Trigger "PUBR".Tri_Bldroom_AllAiu_Part
After Insert Or Delete Or Update Of Part On bldroom
Declare
l_cnt number(8);
l_ecode bldroom.Ecode%type;
l_bldroomid bldroom.bldroomid%type;
l_businesstype bldroom.businesstype%type;
l_isEcodeMustUnique varchar2(100);
Begin
for v_LoopIndex in 1..Pkg_BldroomData_Part.v_NumEntries loop
--[获取变量]--
l_bldroomid := Pkg_BldroomData_Part.v_bldroomids(v_LoopIndex);
l_businesstype := Pkg_BldroomData_Part.v_businesstypes(v_LoopIndex);
---更新Ecode
select (select decode(hillno,'无',null,hillno) from building where buildid=br.buildid)||(select decode(buildno,'无',null,buildno)
from building where buildid=br.buildid)||part into l_ecode from bldroom br where bldroomid=l_bldroomid;
update bldroom set ecode=l_ecode where bldroomid=l_bldroomid;
--[判断Ecode是否唯一]--
--[获取系统参数]--
select value into l_isEcodeMustUnique from ttsysparams where paramid=376;
select count(bldroomid) into l_cnt from bldroom where resopertype=1 and businesstype=l_businesstype and Ecode=l_Ecode;
if l_cnt>1 then
if l_isEcodeMustUnique='-1' then
raise_application_error(-20001,'[当前房屋(bldroomid='||l_bldroomid||')对应房屋编号(Ecode)信息已经存在且有效,您不能维护重复的房屋编号!]');
end if;
end if;
end loop;
Pkg_BldroomData_Part.v_NumEntries := 0;
End Tri_Bldroom_AllAiu_Part;
After Insert Or Delete Or Update Of Part On bldroom
Declare
l_cnt number(8);
l_ecode bldroom.Ecode%type;
l_bldroomid bldroom.bldroomid%type;
l_businesstype bldroom.businesstype%type;
l_isEcodeMustUnique varchar2(100);
Begin
for v_LoopIndex in 1..Pkg_BldroomData_Part.v_NumEntries loop
--[获取变量]--
l_bldroomid := Pkg_BldroomData_Part.v_bldroomids(v_LoopIndex);
l_businesstype := Pkg_BldroomData_Part.v_businesstypes(v_LoopIndex);
---更新Ecode
select (select decode(hillno,'无',null,hillno) from building where buildid=br.buildid)||(select decode(buildno,'无',null,buildno)
from building where buildid=br.buildid)||part into l_ecode from bldroom br where bldroomid=l_bldroomid;
update bldroom set ecode=l_ecode where bldroomid=l_bldroomid;
--[判断Ecode是否唯一]--
--[获取系统参数]--
select value into l_isEcodeMustUnique from ttsysparams where paramid=376;
select count(bldroomid) into l_cnt from bldroom where resopertype=1 and businesstype=l_businesstype and Ecode=l_Ecode;
if l_cnt>1 then
if l_isEcodeMustUnique='-1' then
raise_application_error(-20001,'[当前房屋(bldroomid='||l_bldroomid||')对应房屋编号(Ecode)信息已经存在且有效,您不能维护重复的房屋编号!]');
end if;
end if;
end loop;
Pkg_BldroomData_Part.v_NumEntries := 0;
End Tri_Bldroom_AllAiu_Part;
解决方案 »
- oracle一个用户所属的表存在两个表空间了,怎么合并起来
- 多表更新问题
- oracle安装到27%时jrew.exe已停止工作
- 想深入沟通Oracle技术吗?请大家到这里(Oracle技术论坛www.oraclebbs.com)来一起讨论!
- 求助:关于服务!
- 有谁知道如何安装MapViewer么?oracle as的安装
- 哪里有oracle9i的性能评估报告?
- 以游标方式返回数据集的存储过程,游标需要人工关闭吗?
- 客户端sql plus 能够连上服务器,可是通过程序却报ORA-03121:NO INTERFACE DRIVER CONNECTED-FOUNCTION NOT PERFORMED.
- 朋友! 帮帮忙啊!救命的! 我等!
- 一个Oracle查询问题!
- java.sql.SQLException:
在Pkg_BldroomData_Part包的包头去找一下,例:
--声明自定义索引表类型
type arrtype is table of number index by pls_integer;
--声明此类型的变量
a arrtype;