存储过程例子

create or replace package Inf_Sx97_Gis is


v_Regioncode           Resentity.Regioncode%type;
Rm_Staffno             varchar2(10);
Rm_Portstatus_Preuse   varchar2(5);
Rm_Portstatus_Used     varchar2(5);
Rm_Portstatus_Repreuse varchar2(5);
Rm_Portstatus_Reused   varchar2(5);
Port_Status_Usable     varchar2(10);


type Tport is table of Resport%rowtype;


type Tconn is table of Resconnect%rowtype;


type TPORTID is table of RESPORT.ID%type;


/*同步端子状态的接口存过*/
procedure P_Inf_UPD_RESPORT
(
v_installplace varchar2,
v_portseqnum   number,
v_status       varchar2,
v_linenum      number,
v_connlineid   number,
v_result       out varchar2,
v_msg          out varchar2
);
/*同步实体的接口存过*/
procedure p_Inf_all_resentity
(
v_type            integer,
c_resentitycode   varchar2,
v_resentitycode   varchar2,
v_resentityname   varchar2,
v_resentitytypeid number,
v_areacode        varchar2,
v_capacity        number,
v_installaddress  varchar2,
v_result          out varchar2,
v_msg             out varchar2
);


/*添加实体联系*/
function p_Add_EquipconnectLv
(
v_Equipcode     varchar2,
v_Column        number,
v_Beginportseq1 number,
v_Endportseq1   number,
v_Cablecode     varchar2,
v_Beginlineseq  number,
v_Endlineseq    number,
v_Jhcflag       number
) return varchar2;


/*删除实体联系*/
function p_Del_Equipconnect
(
v_Equipcode     varchar2,
v_Column        number,
v_Beginportseq1 number,
v_Endportseq1   number,
v_Cablecode     varchar2,
v_Beginlineseq  number,
v_Endlineseq    number,
v_Jhcflag       number
) return varchar2;


/*将单向成端修改为双向*/
procedure P_MOD_DOUBLE_CONNECT
(
V_EQUIPCODE    varchar2,
V_COLUMN       number,
V_BEGINPORTSEQ number,
V_ENDPORTSEQ   number,
V_CABLECODE    varchar2,
V_BEGINLINESEQ number,
V_ENDLINESEQ   number,
V_JHCFLAG      number,
R_RESULT       out varchar2,
R_ERROR        out varchar2
);
end Inf_Sx97_Gis;
/
create or replace package body Inf_Sx97_Gis is
procedure P_Inf_UPD_RESPORT
(
v_installplace varchar2,
v_portseqnum   number,
v_status       varchar2,
v_linenum      number,
v_connlineid   number,
v_result       out varchar2,
v_msg          out varchar2
) is
v_count     integer;
v_resportId number(20);
begin
select count(*)
into v_count
from resport j
where j.resentityid = (select Id from resentity bb where bb.resentitycode = v_installplace) and
j.portseqnum = v_portseqnum;

if v_count = 1 then
select id
into v_resportId
from resport j
where j.resentityid = (select Id from resentity bb where bb.resentitycode = v_installplace) and
j.portseqnum = v_portseqnum;

if v_resportId is null then
v_result := 'F';
v_msg    := '错误：没有找到上级端子 或者是下级端子';
else
insert into resport_tem
select * from resport where id = v_resportId;

update resport set status = v_status, linenum = v_linenum, connlineid = v_connlineid where Id = v_resportId;

delete from resport_tem where id = v_resportId;
v_result := 'T';
v_msg    := '同步端子状态97成功！' || v_resportId || v_status || v_connlineid;
end if;
else
v_result := 'F';
v_msg    := '错误：电缆没找到或者电缆有多条';
end if;
exception
when others then
v_result := 'F';
v_msg    := '错误： ' || sqlerrm;
end P_Inf_UPD_RESPORT;


procedure p_Inf_all_resentity
(
v_type            integer,
c_resentitycode   varchar2,
v_resentitycode   varchar2,
v_resentityname   varchar2,
v_resentitytypeid number,
v_areacode        varchar2,
v_capacity        number,
v_installaddress  varchar2,
v_result          out varchar2,
v_msg             out varchar2
) is
v_int integer;
v_id  number(20);
begin
if v_type = 1 then
--添加resentity
if v_resentitycode is null or v_resentitytypeid is null or v_areacode is null then
v_result := 'F';
v_msg    := '同步97resentity错误 ：传来的数据为空';
Else
      select Count(*) Into v_int from area where areacode=v_areacode;
      if v_int < 1 then
      v_result := 'F';
  v_msg    := '传来的97局向在97中找不到！';
      Else
  --id,resentitycode,resentitytypeid,areacode 是无重复的
  select seq_resentity.nextval into v_id from dual;
  insert into resentity_tem (Id) values (v_id);
  insert into resentity
  (Id, version, resentitycode, resentityname, resentitytypeid, superiorresentityid, regioncode, areacode,
  status, capacity, installaddress, installplace, installdate, remark)
  values
  (v_id, 0, v_resentitycode, v_resentityname, v_resentitytypeid, 0, 'SHAOXING', v_areacode, 'B', v_capacity,
  v_installaddress, null, to_char(sysdate, 'yyyymmddhh24'), null);
  delete from resentity_tem where id = v_id;
  v_result := 'T';
  v_msg    := '同步97resentity成功 ';
        end if;
end if;
elsif v_type = 2 then
--修改resentity
select count(*) into v_int from resentity where resentitycode = c_resentitycode;
if v_int = 1 Then
       select Count(*) Into v_int from area where areacode=v_areacode;
        if v_int < 1 then
        v_result := 'F';
    v_msg    := '传来的97局向在97中找不到！';
        Else
  select id into v_id from resentity where resentitycode = c_resentitycode;
  insert into resentity_tem (Id) values (v_id);
  update resentity j
  set j.resentitycode = v_resentitycode, j.resentityname = v_resentityname, j.resentitytypeid = v_resentitytypeid,
  j.areacode = v_areacode, j.capacity = v_capacity, j.Installaddress = v_installaddress
  where j.id = v_id;
  delete from resentity_tem where id = v_id;
  v_result := 'T';
  v_msg    := '修改同步97成功！';
          End If;
else
v_result := 'A';
v_msg    := '没找到对应要修改的' || v_int || '记录。resentitycode：' || c_resentitycode;
end if;
elsif v_type = 3 then
--删除resentity
if c_resentitycode is not null then
select id into v_id from resentity where resentitycode = c_resentitycode;
insert into resentity_tem (Id) values (v_id);
delete from resentity where id = v_id;
delete from resentity_tem where id = v_id;
v_result := 'T';
v_msg    := '删除同步97成功！';
end if;
else
v_result := 'F';
v_msg    := '错误的调用同步97实体表的存过，请联系webgis管理人员！';
end if;
exception
when others then
v_result := 'F';
v_msg    := '错误： ' || v_msg || substr(sqlerrm, 1, 100);
end p_Inf_all_resentity;


function p_Add_Rport
(
Resentityid    number,
Resporttypeid  number,
Portcoderuleid number,
Portseqnum     number,
Linenum        number
) return Resport%rowtype is
v_Port Resport%rowtype;
begin
v_Port.Resentityid    := Resentityid;
v_Port.Resporttypeid  := Resporttypeid;
v_Port.Portcoderuleid := Portcoderuleid;
v_Port.Portseqnum     := Portseqnum;
v_Port.Linenum        := Linenum;
v_Port.Version        := 0;
return v_Port;
end p_Add_Rport;


--插resportlog表
procedure p_Insert_Resportlog
(
Port   in Resport%rowtype,
Action varchar2
) is
begin
insert into Resportlog
(Id, Resportid, Resportversion, Resentityid, Resporttypeid, Portcoderuleid, Portseqnum, Status, Linenum,
Uplinenumber, Downlinenumber, Connlineid, Action, Staffno)
values
(Seq_Resportlog.nextval, Port.Id, Port.Version, Port.Resentityid, Port.Resporttypeid, Port.Portcoderuleid,
Port.Portseqnum, Port.Status, Port.Linenum, Port.Uplinenumber, Port.Downlinenumber, Port.Connlineid, Action,
Rm_Staffno);
end;
--插入resport表
procedure p_Insert_Resport(Port in out Resport%rowtype) is
begin
select seq_resport.nextval into port.id from dual;
--Port.Id := Port.Resentityid || Lpad(To_Char(Port.Portseqnum), 13, '0');
insert into Resport
(Id, Version, Resentityid, Resporttypeid, Portcoderuleid, Portseqnum, Status, Linenum, Uplinenumber,
Downlinenumber, Connlineid)
values
(Port.Id, Port.Version, Port.Resentityid, Port.Resporttypeid, Port.Portcoderuleid, Port.Portseqnum, Port.Status,
Port.Linenum, Port.Uplinenumber, Port.Downlinenumber, Port.Connlineid);
p_Insert_Resportlog(Port, 'AN');
end;
--根据联系生成上下级端子信息
procedure p_Generate_Portinfo
(
Rconn       Resconnect%rowtype,
Ownportlist in out Tport,
Upportlist  in out Tport
) is
v_Portseqnum Resport.Portseqnum%type;
v_Port       Resport%rowtype;
begin
v_Portseqnum := Rconn.Beginportseqnum;
while v_Portseqnum <= Rconn.Endportseqnum loop
v_Port       := p_Add_Rport(Rconn.Resentityid, Rconn.Resporttypeid, Rconn.Portcoderuleid, v_Portseqnum, 0);
v_Portseqnum := v_Portseqnum + 1;
Ownportlist.extend;
Ownportlist(Ownportlist.last) := v_Port;
end loop;
v_Portseqnum := Rconn.Superiorbeginportseqnum;
while v_Portseqnum <= Rconn.Superiorendportseqnum loop
v_Port       := p_Add_Rport(Rconn.Superiorresentityid, Rconn.Superiorresporttypeid, Rconn.Superiorportcoderuleid,
v_Portseqnum, 0);
v_Portseqnum := v_Portseqnum + 1;
Upportlist.extend;
Upportlist(Upportlist.last) := v_Port;
end loop;
end;


--更新resport表
procedure p_Update_Resport
(
Oldport Resport%rowtype,
Port    in out Resport%rowtype
) is
begin
p_Insert_Resportlog(Oldport, 'UO');
insert into resport_tem
select * from resport where id = Port.Id;
update Resport
set Version = Port.Version + 1, Resentityid = Port.Resentityid, Resporttypeid = Port.Resporttypeid,
Portcoderuleid = Port.Portcoderuleid, Portseqnum = Port.Portseqnum, Status = Port.Status, Linenum = Port.Linenum,
Uplinenumber = Port.Uplinenumber, Downlinenumber = Port.Downlinenumber, Connlineid = Port.Connlineid
where Id = Port.Id;
delete from resport_tem where id = Port.Id;
Port.Version := Port.Version + 1;
p_Insert_Resportlog(Port, 'UN');
end;


--对本级端子重新赋值
procedure p_Ownportinfo_Again
(
Ownportlist in out Tport,
Oldownlist  in out Tport
) is
v_Ifexist number(3);
begin
if Oldownlist.count = 0 then
for i in Ownportlist.first .. Ownportlist.last loop
Ownportlist(i).Uplinenumber := 1;
Ownportlist(i).Downlinenumber := 0;
Ownportlist(i).Status := Port_Status_Usable;
end loop;
else
for i in Ownportlist.first .. Ownportlist.last loop
v_Ifexist := 0;
for j in Oldownlist.first .. Oldownlist.last loop
if Ownportlist(i).Portseqnum = Oldownlist(j).Portseqnum then
v_Ifexist := 1;
Ownportlist(i).Id := Oldownlist(j).Id;
Ownportlist(i).Downlinenumber := Oldownlist(j).Downlinenumber;
Ownportlist(i).Linenum := Oldownlist(j).Linenum;
Ownportlist(i).Status := Oldownlist(j).Status;
Ownportlist(i).Version := Oldownlist(j).Version;
Ownportlist(i).Connlineid := Oldownlist(j).Connlineid;
Ownportlist(i).Uplinenumber := Oldownlist(j).Uplinenumber + 1;
goto Endloop;
end if;
end loop;
if v_Ifexist = 0 then
Ownportlist(i).Uplinenumber := 1;
Ownportlist(i).Downlinenumber := 0;
Ownportlist(i).Status := Port_Status_Usable;
end if;
<<endloop>>
null;
end loop;
end if;
end;


--得到旧 上下级端子
procedure p_Get_Portinfo
(
Rconn      Resconnect%rowtype,
Oldownlist out Tport,
Olduplist  out Tport
) is
begin
select * bulk collect
into Oldownlist
from Resport
where Resentityid = Rconn.Resentityid /*and Resporttypeid = Rconn.Resporttypeid and
                                                                                                                                                                                              Portcoderuleid = Rconn.Portcoderuleid*/
and Portseqnum between Rconn.Beginportseqnum and Rconn.Endportseqnum
order by Portseqnum;
select * bulk collect
into Olduplist
from Resport
where Resentityid = Rconn.Superiorresentityid /*and Resporttypeid = Rconn.Superiorresporttypeid and
                                                                                                                                                                                              Portcoderuleid = Rconn.Superiorportcoderuleid*/
and Portseqnum between Rconn.Superiorbeginportseqnum and Rconn.Superiorendportseqnum
order by Portseqnum;
end;


--判断 是否有上下级内容重叠
procedure p_Judge_Ifexistrepeatport
(
Ownportlist in out Tport,
Upportlist  in out Tport
) is
v_Count number(8);
begin
for i in Ownportlist.first .. Ownportlist.last loop
if Ownportlist(i).Id is not null and Upportlist(i).Id is not null then
select count(*)
into v_Count
from Resportrelation
where Resportid = Ownportlist(i).Id and Superiorresportid = Upportlist(i).Id;
if v_Count <> 0 then
Raise_Application_Error(-20901,
'本级' || Ownportlist(i).Portseqnum || '与上级' || Upportlist(i)
.Portseqnum || '存在重复成端！');
end if;
end if;
end loop;
end;
--新增、更新端子
procedure p_Generate_Portinfoend
(
Portlist in out Tport,
Oldlist  Tport
) is
v_i number(9) := 0;
begin
for i in Portlist.first .. Portlist.last loop
if Portlist(i).Id is not null then
v_i := v_i + 1;
--p_insert_ResPortLog(portlist(i), 'UO');
p_Update_Resport(Oldlist(v_i), Portlist(i));
else
p_Insert_Resport(Portlist(i));
end if;
end loop;
end;
--加入端子上下级关系
procedure p_Add_Resportrelation
(
Ownportlist Tport,
Upportlist  Tport
) is
begin
for i in Ownportlist.first .. Ownportlist.last loop
insert into Resportrelation
(Id, Superiorresportid, Resportid)
values
(Seq_Resportrelation.nextval, Upportlist(i).Id, Ownportlist(i).Id);
end loop;
end;
--插入resconnectlog
procedure p_Insert_Resconnectlog
(
Conn   Resconnect%rowtype,
Action varchar2
) is
begin
insert into Resconnectlog
(Id, Resconnectid, Resentityid, Resentityversion, Resporttypeid, Portcoderuleid, Beginportcode, Endportcode,
Beginportseqnum, Endportseqnum, Parity, Superiorresentityid, Superiorresentityversion, Superiorresporttypeid,
Superiorportcoderuleid, Superiorbeginportcode, Superiorendportcode, Superiorbeginportseqnum,
Superiorendportseqnum, Superiorparity, type, Priorgrade, Action, Staffno)
values
(Seq_Resconnectlog.nextval, Conn.Id, Conn.Resentityid, Conn.Resentityversion, Conn.Resporttypeid,
Conn.Portcoderuleid, Conn.Beginportseqnum, Conn.Endportseqnum, Conn.Beginportseqnum, Conn.Endportseqnum,
Conn.Parity, Conn.Superiorresentityid, Conn.Superiorresentityversion, Conn.Superiorresporttypeid,
Conn.Superiorportcoderuleid, Conn.Superiorbeginportseqnum, Conn.Superiorendportseqnum,
Conn.Superiorbeginportseqnum, Conn.Superiorendportseqnum, Conn.Superiorparity, Conn.type, Conn.Priorgrade, Action,
Rm_Staffno);
end;
--插入resconnect
procedure p_Insert_Resconnect(Conn in out Resconnect%rowtype) is
begin
select Seq_Resconnect.nextval into Conn.Id from Dual;
insert into resconnect_tem values (conn.id);
insert into Resconnect
(Id, Resentityid, Resentityversion, Resporttypeid, Portcoderuleid, Beginportcode, Endportcode, Beginportseqnum,
Endportseqnum, Parity, Superiorresentityid, Superiorresentityversion, Superiorresporttypeid,
Superiorportcoderuleid, Superiorbeginportcode, Superiorendportcode, Superiorbeginportseqnum,
Superiorendportseqnum, Superiorparity, type, Priorgrade)
values
(Conn.Id, Conn.Resentityid, Conn.Resentityversion, Conn.Resporttypeid, Conn.Portcoderuleid, Conn.Beginportseqnum,
Conn.Endportseqnum, Conn.Beginportseqnum, Conn.Endportseqnum, Conn.Parity, Conn.Superiorresentityid,
Conn.Superiorresentityversion, Conn.Superiorresporttypeid, Conn.Superiorportcoderuleid,
Conn.Superiorbeginportseqnum, Conn.Superiorendportseqnum, Conn.Superiorbeginportseqnum,
Conn.Superiorendportseqnum, Conn.Superiorparity, Conn.type, Conn.Priorgrade);
delete from resconnect_tem where id = Conn.Id;
p_Insert_Resconnectlog(Conn, 'AN');
end;
--判断联系新增的端子是否超过实体容量
procedure p_Judge_Ifovercapacity
(
Ownportlist       in out Tport,
v_Resentityid     number,
Upportlist        in out Tport,
v_Lineresentityid number
) is
v_Ownpcap number(6);
v_Uppcap  number(6);
v_Owncap  number(6);
v_Owncode varchar2(60);
v_Upcap   number(6);
v_Upcode  varchar2(60);
begin
if Ownportlist.count is not null then
select count(*) into v_Ownpcap from Resport where Resentityid = v_Resentityid;
select Capacity, Resentitycode into v_Owncap, v_Owncode from Resentity where Id = v_Resentityid;
if v_Owncap < v_Ownpcap then
Raise_Application_Error(-20901, '本级实体：' || v_Owncode || '超出容量！');
end if;
end if;
if Upportlist.count is not null then
select count(*) into v_Uppcap from Resport where Resentityid = v_Lineresentityid;
select Capacity, Resentitycode into v_Upcap, v_Upcode from Resentity where Id = v_Lineresentityid;
if v_Upcap < v_Uppcap then
Raise_Application_Error(-20901, '上级实体：' || v_Upcode || '超出容量！');
end if;
end if;
end;


--更新复接端子状态
procedure p_Update_Fj
(
Ownportlist Tport,
Upportlist  Tport
) is
v_status   varchar2(2);
v_upstatus varchar2(2);
begin
for i in Ownportlist.first .. Ownportlist.last loop
if Ownportlist(i).Connlineid is not null then
select status into v_status from resport where id = Ownportlist(i).id;
select status into v_upstatus from resport where id = Upportlist(i).id;
if instr('BI', v_status) > 0 or instr('BI', v_upstatus) > 0 then
Rmutils.p_Update_Resport_Dup(Ownportlist(i).connlineid, Ownportlist(i).id, 'BI', 'I');
end if;
if instr('DH', v_status) > 0 or instr('DH', v_upstatus) > 0 then
Rmutils.p_Update_Resport_Dup(Ownportlist(i).connlineid, Ownportlist(i).id, 'DH', 'H');
end if;
end if;
end loop;
end;


--判断 上下连数是否超出范围
procedure p_Judge_Ifovermax(Portlist in out Tport) is
v_Maxuplinenumber   number(5);
v_Maxdownlinenumber number(5);
v_Resentitycode     varchar2(60);
begin
if Portlist.count <> 0 then
for i in Portlist.first .. Portlist.last loop
select Maxuplinenumber, Maxdownlinenumber
into v_Maxuplinenumber, v_Maxdownlinenumber
from Resporttype
where Id = Portlist(i).Resporttypeid;
if Portlist(i).Uplinenumber > v_Maxuplinenumber then
select Resentitycode into v_Resentitycode from Resentity where Id = Portlist(i).Resentityid;
Raise_Application_Error(-20901,
'实体编码：' || v_Resentitycode || ' 端子编码：' || Portlist(i)
.Portseqnum || '  此端子的上连数超出了定义!');
end if;
if Portlist(i).Downlinenumber > v_Maxdownlinenumber then
select Resentitycode into v_Resentitycode from Resentity where Id = Portlist(i).Resentityid;
Raise_Application_Error(-20901,
'实体编码：' || v_Resentitycode || ' 端子编码：' || Portlist(i)
.Portseqnum || '  此端子的下连数超出了定义!');
end if;
end loop;
end if;
end;


--判断 上下级中是否存在产品实例，并更新相关信息
procedure p_Deal_Existsprodinst
(
Ownportlist in out Tport,
Upportlist  in out Tport
) is
begin
for i in Ownportlist.first .. Ownportlist.last loop
if Ownportlist(i).Connlineid is not null then
if Upportlist(i).Connlineid is null then
--Upportlist(i).Status := Ownportlist(i).Status;
Upportlist(i).Connlineid := Ownportlist(i).Connlineid;
Upportlist(i).Linenum := Ownportlist(i).Linenum;
else
if Ownportlist(i).Connlineid <> Upportlist(i).Connlineid then
Raise_Application_Error(-20901, '上下级同一端子有不同用户！');
end if;
end if;
else
if Upportlist(i).Connlineid is not null then
--Ownportlist(i).Status := Upportlist(i).Status;
Ownportlist(i).Connlineid := Upportlist(i).Connlineid;
Ownportlist(i).Linenum := Upportlist(i).Linenum;
end if;
end if;
end loop;
end;


--对上级端子重新赋值
procedure p_Upportinfo_Again
(
Upportlist in out Tport,
Olduplist  in out Tport
) is
v_Ifexist number(3);
begin
if Olduplist.count = 0 then
for i in Upportlist.first .. Upportlist.last loop
Upportlist(i).Downlinenumber := 1;
Upportlist(i).Uplinenumber := 0;
Upportlist(i).Status := Port_Status_Usable;
end loop;
else
for i in Upportlist.first .. Upportlist.last loop
v_Ifexist := 0;
for j in Olduplist.first .. Olduplist.last loop
if Upportlist(i).Portseqnum = Olduplist(j).Portseqnum then
v_Ifexist := 1;
Upportlist(i).Id := Olduplist(j).Id;
Upportlist(i).Uplinenumber := Olduplist(j).Uplinenumber;
Upportlist(i).Linenum := Olduplist(j).Linenum;
Upportlist(i).Status := Olduplist(j).Status;
Upportlist(i).Version := Olduplist(j).Version;
Upportlist(i).Connlineid := Olduplist(j).Connlineid;
Upportlist(i).Downlinenumber := Olduplist(j).Downlinenumber + 1;
goto Endloop;
end if;
end loop;
if v_Ifexist = 0 then
Upportlist(i).Downlinenumber := 1;
Upportlist(i).Uplinenumber := 0;
Upportlist(i).Status := Port_Status_Usable;
end if;
<<endloop>>
null;
end loop;
end if;
end;


--新增resconnect对象
function p_Add_Rconnect
(
Resentityid              number,
Resentityversion         number,
Resporttypeid            number,
Portcoderuleid           number,
Beginportseqnum          number,
Endportseqnum            number,
Parity                   varchar2,
Superiorresentityid      number,
Superiorresentityversion number,
Superiorresporttypeid    number,
Superiorportcoderuleid   number,
Superiorbeginportseqnum  number,
Superiorendportseqnum    number,
Superiorparity           varchar2,
v_Type                   varchar2,
Priorgrade               number
) return Resconnect%rowtype is
Conn Resconnect%rowtype;
begin
Conn.Resentityid              := Resentityid;
Conn.Resentityversion         := Resentityversion;
Conn.Resporttypeid            := Resporttypeid;
Conn.Portcoderuleid           := Portcoderuleid;
Conn.Beginportseqnum          := Beginportseqnum;
Conn.Endportseqnum            := Endportseqnum;
Conn.Parity                   := Parity;
Conn.Superiorresentityid      := Superiorresentityid;
Conn.Superiorresentityversion := Superiorresentityversion;
Conn.Superiorresporttypeid    := Superiorresporttypeid;
Conn.Superiorportcoderuleid   := Superiorportcoderuleid;
Conn.Superiorbeginportseqnum  := Superiorbeginportseqnum;
Conn.Superiorendportseqnum    := Superiorendportseqnum;
Conn.Superiorparity           := Superiorparity;
Conn.type                     := v_Type;
Conn.Priorgrade               := Priorgrade;
return Conn;
end p_Add_Rconnect;
--新增联系 
function p_Add_Equipconnect
(
v_Equipcode     varchar2,
v_Column        number,
v_Beginportseq1 number,
v_Endportseq1   number,
v_Cablecode     varchar2,
v_Beginlineseq  number,
v_Endlineseq    number,
v_Jhcflag       number
) return varchar2 is
r_Error              varchar2(400);
v_Resentitytypeid    Resentity.Resentitytypeid%type;
v_Beginportseq       Resconnect.Beginportseqnum%type;
v_Endportseq         Resconnect.Beginportseqnum%type;
v_Resentityid        Resport.Resentityid%type;
v_Resporttypeid      Resport.Resporttypeid%type;
v_Portcoderuleid     Resport.Portcoderuleid%type;
v_Version            Resentity.Version%type;
v_Lineversion        Resentity.Version%type;
v_Lineresentityid    Resport.Resentityid%type;
v_Lineresporttypeid  Resport.Resporttypeid%type;
v_Lineportcoderuleid Resport.Portcoderuleid%type;
v_Conn               Resconnect%rowtype;
Tconn1               Tconn := Tconn();
v_Parity             char(1) := 'C';
v_Type               varchar2(2) := '00';
v_Priorgrade         number(5) := 1;
Ownportlist          Tport;
Oldownlist           Tport;
Upportlist           Tport;
Olduplist            Tport;
begin
--新增联系前的判断
if v_Endportseq1 - v_Beginportseq1 <> v_Endlineseq - v_Beginlineseq then
return '实体 电缆 端子范围不一致!';
end if;
begin
select a.Id, b.Id, c.Portcoderuleid, a.Version, b.Resentitytypeid
into v_Resentityid, v_Resporttypeid, v_Portcoderuleid, v_Version, v_Resentitytypeid
from Resentity a, Resporttype b, Resporttypecoderule c
where Resentitycode = v_Equipcode and a.Resentitytypeid like b.Resentitytypeid(+) and c.Resporttypeid(+) = b.Id;
exception
when others then
r_Error := '根据编码:' || v_Equipcode || '没有找到设备！';
return r_Error;
end;
if v_Resporttypeid is null then
r_Error := '根据编码:' || v_Equipcode || '没有找到端子类型！';
return r_Error;
end if;
if v_Portcoderuleid is null then
r_Error := '根据编码:' || v_Equipcode || '没有找到端子规则！';
return r_Error;
end if;
begin
select a.Id, b.Id, c.Portcoderuleid, a.Version
into v_Lineresentityid, v_Lineresporttypeid, v_Lineportcoderuleid, v_Lineversion
from Resentity a, Resporttype b, Resporttypecoderule c
where Resentitycode = v_Cablecode and a.Resentitytypeid like b.Resentitytypeid(+) and c.Resporttypeid(+) = b.Id;
exception
when others then
r_Error := '根据编码:' || v_Cablecode || '没有找到电缆！';
return r_Error;
end;
if v_Lineresporttypeid is null then
r_Error := '根据编码:' || v_Cablecode || '没有找到电缆端子类型！';
return r_Error;
end if;
if v_Lineportcoderuleid is null then
r_Error := '根据编码:' || v_Cablecode || '没有找到电缆端子规则！';
return r_Error;
end if;
--得到新增tconn对象所需的变量
if v_Resentitytypeid = 33 then
v_Beginportseq := v_Beginportseq1 + 10000 * v_Column;
v_Endportseq   := v_Endportseq1 + 10000 * v_Column;
else
v_Beginportseq := v_Beginportseq1;
v_Endportseq   := v_Endportseq1;
end if;
begin
--得到需要新增的resconnect对象
if v_Jhcflag = 1 or v_Jhcflag = 0 then
v_Conn := p_Add_Rconnect(v_Lineresentityid, v_Lineversion, v_Lineresporttypeid, v_Lineportcoderuleid,
v_Beginlineseq, v_Endlineseq, v_Parity, v_Resentityid, v_Version, v_Resporttypeid,
v_Portcoderuleid, v_Beginportseq, v_Endportseq, v_Parity, v_Type, v_Priorgrade);
Tconn1.extend;
Tconn1(Tconn1.last) := v_Conn;
end if;
if v_Jhcflag = 2 or v_Jhcflag = 0 then
v_Conn := p_Add_Rconnect(v_Resentityid, v_Version, v_Resporttypeid, v_Portcoderuleid, v_Beginportseq,
v_Endportseq, v_Parity, v_Lineresentityid, v_Lineversion, v_Lineresporttypeid,
v_Lineportcoderuleid, v_Beginlineseq, v_Endlineseq, v_Parity, v_Type, v_Priorgrade);
Tconn1.extend;
Tconn1(Tconn1.last) := v_Conn;
end if;
--用tconn循环
if Tconn1.count > 0 then
for i in Tconn1.first .. Tconn1.last loop
v_Conn      := Tconn1(i);
Ownportlist := Tport();
Oldownlist  := Tport();
Upportlist  := Tport();
Olduplist   := Tport();
--得到新旧 上下级端子
p_Get_Portinfo(v_Conn, Oldownlist, Olduplist);
p_Generate_Portinfo(v_Conn, Ownportlist, Upportlist);
--根据新旧 上下级端子重新赋值
p_Ownportinfo_Again(Ownportlist, Oldownlist);
p_Upportinfo_Again(Upportlist, Olduplist);
p_Deal_Existsprodinst(Ownportlist, Upportlist);
p_Judge_Ifovermax(Ownportlist);
p_Judge_Ifovermax(Upportlist);
p_Judge_Ifexistrepeatport(Ownportlist, Upportlist);
p_Generate_Portinfoend(Ownportlist, Oldownlist);
p_Generate_Portinfoend(Upportlist, Olduplist);
p_Add_Resportrelation(Ownportlist, Upportlist);
p_Judge_Ifovercapacity(Ownportlist, v_Resentityid, Upportlist, v_Lineresentityid);
p_Insert_Resconnect(v_Conn);
end loop;
end if;
--根据工单和用户资源信息更新Resport表
begin

update Resport a
set (Status, Connlineid, Linenum) = (select 'D', max(Connlineid), 1
from Svcordlinedet b
where b.Resourceid = a.Id and not exists
(select 1 from Changeresourcelog where Newsvcordid = b.Svcordid))
where Resentityid = v_Resentityid and Portseqnum between v_Beginportseq and v_Endportseq and
Instr('BDI', Status) = 0 and exists
(select 1
from Svcordlinedet c
where c.Resourceid = a.Id and not exists (select 1 from Changeresourcelog where Newsvcordid = c.Svcordid));
update Resport a
set (Status, Connlineid, Linenum) = (select 'B', Connlineid, 1
from Connlinedet b
where b.Resourceid = a.Id and Rownum = 1)
where Resentityid = v_Resentityid and Portseqnum between v_Beginportseq and v_Endportseq and
Instr('B', Status) = 0 and exists (select 1 from Connlinedet c where c.Resourceid = a.Id);

update Resport a
set (Status, Connlineid, Linenum) = (select 'D', max(Connlineid), 1
from Svcordlinedet b
where b.Resourceid = a.Id and not exists
(select 1 from Changeresourcelog where Newsvcordid = b.Svcordid))
where Resentityid = v_Lineresentityid and Portseqnum between v_Beginlineseq and v_Endlineseq and
Instr('BDI', Status) = 0 and exists
(select 1
from Svcordlinedet c
where c.Resourceid = a.Id and not exists (select 1 from Changeresourcelog where Newsvcordid = c.Svcordid));
update Resport a
set (Status, Connlineid, Linenum) = (select 'B', Connlineid, 1
from Connlinedet b
where b.Resourceid = a.Id and Rownum = 1)
where Resentityid = v_Lineresentityid and Portseqnum between v_Beginlineseq and v_Endlineseq and
Instr('B', Status) = 0 and exists (select 1 from Connlinedet c where c.Resourceid = a.Id);
exception
when others then
null;
end;
p_Update_Fj(Ownportlist, Upportlist);
exception
when others then
r_Error := substrb(sqlerrm, 0, 400);
return r_Error;
end;
return 'S';
end p_Add_Equipconnect;


--新增联系 
function p_Add_EquipconnectLv
(
v_Equipcode     varchar2,
v_Column        number,
v_Beginportseq1 number,
v_Endportseq1   number,
v_Cablecode     varchar2,
v_Beginlineseq  number,
v_Endlineseq    number,
v_Jhcflag       number
) return varchar2 is
r_Error              varchar2(400);
v_Resentitytypeid    Resentity.Resentitytypeid%type;
v_Beginportseq       Resconnect.Beginportseqnum%type;
v_Endportseq         Resconnect.Beginportseqnum%type;
v_Resentityid        Resport.Resentityid%type;
v_Resporttypeid      Resport.Resporttypeid%type;
v_Portcoderuleid     Resport.Portcoderuleid%type;
v_Version            Resentity.Version%type;
v_Linetypeid         Resentity.Resentitytypeid%type;
v_Lineversion        Resentity.Version%type;
v_Lineresentityid    Resport.Resentityid%type;
v_Lineresporttypeid  Resport.Resporttypeid%type;
v_Lineportcoderuleid Resport.Portcoderuleid%type;
v_Conn               Resconnect%rowtype;
Tconn1               Tconn := Tconn();
v_Parity             char(1) := 'C';
v_Type               varchar2(2) := '00';
v_Priorgrade         number(5) := 1;
Ownportlist          Tport;
Oldownlist           Tport;
Upportlist           Tport;
Olduplist            Tport;
begin
--新增联系前的判断

begin
select a.Id, b.Id, c.Portcoderuleid, a.Version, b.Resentitytypeid
into v_Resentityid, v_Resporttypeid, v_Portcoderuleid, v_Version, v_Resentitytypeid
from Resentity a, Resporttype b, Resporttypecoderule c
where Resentitycode = v_Equipcode and a.Resentitytypeid like b.Resentitytypeid(+) and c.Resporttypeid(+) = b.Id;
exception
when others then
r_Error := '根据编码:' || v_Equipcode || '没有找到设备！';
return r_Error;
end;
if v_Resporttypeid is null then
r_Error := '根据编码:' || v_Equipcode || '没有找到端子类型！';
return r_Error;
end if;
if v_Portcoderuleid is null then
r_Error := '根据编码:' || v_Equipcode || '没有找到端子规则！';
return r_Error;
end if;
begin
select a.Id, b.Id, c.Portcoderuleid, a.Version
into v_Lineresentityid, v_Lineresporttypeid, v_Lineportcoderuleid, v_Lineversion
from Resentity a, Resporttype b, Resporttypecoderule c
where Resentitycode = v_Cablecode and a.Resentitytypeid like b.Resentitytypeid(+) and c.Resporttypeid(+) = b.Id;
exception
when others then
r_Error := '根据编码:' || v_Cablecode || '没有找到电缆！';
return r_Error;
end;
if v_Lineresporttypeid is null then
r_Error := '根据编码:' || v_Cablecode || '没有找到电缆端子类型！';
return r_Error;
end if;
if v_Lineportcoderuleid is null then
r_Error := '根据编码:' || v_Cablecode || '没有找到电缆端子规则！';
return r_Error;
end if;
--得到新增tconn对象所需的变量
if v_Resentitytypeid = 33 then
v_Beginportseq := v_Beginportseq1 + 10000 * v_Column;
v_Endportseq   := v_Endportseq1 + 10000 * v_Column;
else
v_Beginportseq := v_Beginportseq1;
v_Endportseq   := v_Endportseq1;
end if;
begin
--得到需要新增的resconnect对象
if v_Jhcflag = 1 or v_Jhcflag = 0 then
v_Conn := p_Add_Rconnect(v_Lineresentityid, v_Lineversion, v_Lineresporttypeid, v_Lineportcoderuleid,
v_Beginlineseq, v_Endlineseq, v_Parity, v_Resentityid, v_Version, v_Resporttypeid,
v_Portcoderuleid, v_Beginportseq, v_Endportseq, v_Parity, v_Type, v_Priorgrade);
Tconn1.extend;
Tconn1(Tconn1.last) := v_Conn;
end if;
if v_Jhcflag = 2 or v_Jhcflag = 0 then
v_Conn := p_Add_Rconnect(v_Resentityid, v_Version, v_Resporttypeid, v_Portcoderuleid, v_Beginportseq,
v_Endportseq, v_Parity, v_Lineresentityid, v_Lineversion, v_Lineresporttypeid,
v_Lineportcoderuleid, v_Beginlineseq, v_Endlineseq, v_Parity, v_Type, v_Priorgrade);
Tconn1.extend;
Tconn1(Tconn1.last) := v_Conn;
end if;
--用tconn循环
if Tconn1.count > 0 then
for i in Tconn1.first .. Tconn1.last loop
v_Conn      := Tconn1(i);
Ownportlist := Tport();
Oldownlist  := Tport();
Upportlist  := Tport();
Olduplist   := Tport();
--得到新旧 上下级端子
p_Get_Portinfo(v_Conn, Oldownlist, Olduplist);
p_Generate_Portinfo(v_Conn, Ownportlist, Upportlist);
--根据新旧 上下级端子重新赋值
p_Ownportinfo_Again(Ownportlist, Oldownlist);
p_Upportinfo_Again(Upportlist, Olduplist);
p_Deal_Existsprodinst(Ownportlist, Upportlist);
p_Judge_Ifovermax(Ownportlist);
p_Judge_Ifovermax(Upportlist);
p_Judge_Ifexistrepeatport(Ownportlist, Upportlist);
p_Generate_Portinfoend(Ownportlist, Oldownlist);
p_Generate_Portinfoend(Upportlist, Olduplist);
p_Add_Resportrelation(Ownportlist, Upportlist);
p_Judge_Ifovercapacity(Ownportlist, v_Resentityid, Upportlist, v_Lineresentityid);
p_Insert_Resconnect(v_Conn);
end loop;
end if;
--根据工单和用户资源信息更新Resport表
begin
--在线工单不可能割接，所以没有状态为'D'的可能 所以注释以下的代码
/*    update Resport a
                set (Status, Connlineid, Linenum) = (select 'D', max(Connlineid), 1
                                                      from Svcordlinedet b
                                                      where b.Resourceid = a.Id and not exists
                                                       (select 1 from Changeresourcelog where Newsvcordid = b.Svcordid))
                where Resentityid = v_Resentityid and Portseqnum between v_Beginportseq and v_Endportseq and
                      Instr('BDI', Status) = 0 and exists
                 (select 1
                       from Svcordlinedet c
                       where c.Resourceid = a.Id and not exists (select 1 from Changeresourcelog where Newsvcordid = c.Svcordid));
        */
-- 插入中间表 否则不让修改端子状态
insert into resport_tem
select *
from resport a
where Resentityid in v_Resentityid and Portseqnum between v_Beginportseq and v_Endportseq and
Instr('B', Status) = 0 and exists (select 1 from Connlinedet c where c.Resourceid = a.Id);
update Resport a
set (Status, Connlineid, Linenum) = (select 'B', Connlineid, 1
from Connlinedet b
where b.Resourceid = a.Id and Rownum = 1)
where Resentityid = v_Resentityid and Portseqnum between v_Beginportseq and v_Endportseq and
Instr('B', Status) = 0 and exists (select 1 from Connlinedet c where c.Resourceid = a.Id);
delete from resport_tem
where Resentityid in v_Resentityid and Portseqnum between v_Beginportseq and v_Endportseq and
Instr('B', Status) = 0;
/* update Resport a
                set (Status, Connlineid, Linenum) = (select 'D', max(Connlineid), 1
                                                      from Svcordlinedet b
                                                      where b.Resourceid = a.Id and not exists
                                                       (select 1 from Changeresourcelog where Newsvcordid = b.Svcordid))
                where Resentityid = v_Lineresentityid and Portseqnum between v_Beginlineseq and v_Endlineseq and
                      Instr('BDI', Status) = 0 and exists
                 (select 1
                       from Svcordlinedet c
                       where c.Resourceid = a.Id and not exists (select 1 from Changeresourcelog where Newsvcordid = c.Svcordid));
        */

insert into resport_tem
select *
from resport a
where Resentityid in v_Lineresentityid and Portseqnum between v_Beginlineseq and v_Endlineseq and
Instr('B', Status) = 0 and exists (select 1 from Connlinedet c where c.Resourceid = a.Id);

update Resport a
set (Status, Connlineid, Linenum) = (select 'B', Connlineid, 1
from Connlinedet b
where b.Resourceid = a.Id and Rownum = 1)
where Resentityid = v_Lineresentityid and Portseqnum between v_Beginlineseq and v_Endlineseq and
Instr('B', Status) = 0 and exists (select 1 from Connlinedet c where c.Resourceid = a.Id);
delete from resport_tem
where Resentityid in v_Lineresentityid and Portseqnum between v_Beginlineseq and v_Endlineseq and
Instr('B', Status) = 0;
exception
when others then
null;
end;
p_Update_Fj(Ownportlist, Upportlist);
exception
when others then
r_Error := substrb(sqlerrm, 0, 400);
return r_Error;
end;
return 'S';
end p_Add_EquipconnectLv;
--找出portlist
procedure p_Add_Resporttoportlist
(
Conn        Resconnect%rowtype,
Ownportlist in out Tport,
Upportlist  in out Tport
) is
begin
select * bulk collect
into Ownportlist
from Resport a
where a.Resentityid = Conn.Resentityid and a.Portseqnum between Conn.Beginportseqnum and Conn.Endportseqnum
order by Portseqnum;
select * bulk collect
into Upportlist
from Resport
where Resentityid = Conn.Superiorresentityid and Portseqnum between Conn.Superiorbeginportseqnum and
Conn.Superiorendportseqnum
order by Portseqnum;
end;
--删端子和端子联系
procedure p_Delete_Resportandrel
(
Ownport in out Resport%rowtype,
Upport  in out Resport%rowtype
) is
Oldport Resport%rowtype;
--v_Resportid number(20);
begin
--本级
if Ownport.Uplinenumber = 1 and Ownport.Downlinenumber <= 0 then
/*select seq_resport.Nextval into v_Resportid from dual;
      --v_Resportid := Ownport.Resentityid || Lpad(Ownport.Portseqnum, 13, 0);
      if Ownport.Status in ('B', 'D', 'H', 'I') and Ownport.Id <> v_Resportid then
        update Connlinedet set Resourceid = v_Resportid where Resourceid = Ownport.Id;
        update Svcordlinedet set Resourceid = v_Resportid where Resourceid = Ownport.Id;
      end if;*/
delete from Resport where Id = Ownport.Id;
p_Insert_Resportlog(Ownport, 'DO');
else
Oldport              := Ownport;
Ownport.Uplinenumber := Ownport.Uplinenumber - 1;
p_Update_Resport(Oldport, Ownport);
end if;
--上级
if Upport.Downlinenumber = 1 and Upport.Uplinenumber <= 0 then
/*select seq_resport.Nextval into v_Resportid from dual;
      --v_Resportid := Upport.Resentityid || Lpad(Upport.Portseqnum, 13, 0);
      if Upport.Status in ('B', 'D', 'H', 'I') and Upport.Id <> v_Resportid then
        update Connlinedet set Resourceid = v_Resportid where Resourceid = Upport.Id;
        update Svcordlinedet set Resourceid = v_Resportid where Resourceid = Upport.Id;
      end if;*/
delete from Resport where Id = Upport.Id;
p_Insert_Resportlog(Upport, 'DO');
else
Oldport               := Upport;
Upport.Downlinenumber := Upport.Downlinenumber - 1;
p_Update_Resport(Oldport, Upport);
end if;
--删除端子间联系
delete from Resportrelation where Resportid = Ownport.Id and Superiorresportid = Upport.Id;
end;
--删除要删除的联系中的所有端子
procedure p_Delete_Resportincon
(
Ownportlist in out Tport,
Upportlist  in out Tport
) is
begin
for i in Ownportlist.first .. Ownportlist.last loop
p_Delete_Resportandrel(Ownportlist(i), Upportlist(i));
end loop;
end;
--删resconnect表
procedure p_Delete_Resconnect(Conn Resconnect%rowtype) is
begin
insert into resconnect_tem values (Conn.Id);
delete from Resconnect where Id = Conn.Id;
delete from resconnect_tem where id = Conn.Id;
p_Insert_Resconnectlog(Conn, 'DO');
end;
--判断是否可以删成端 Y表示可以删除，N为不能
function f_Judge_Ifcandelete
(
v_Entityid    number,
v_Beginseqnum number,
v_Endseqnum   number
) return varchar2 is
v_Iscan  varchar2(2);
portlist Tport;
v_seqnum varchar2(400);
--v_Num   number(8);
cursor C1 is
select resprojectjobid, sum(decode(id, null, 1, 0)) wfw,
sum(decode(connlineid, null, 0, decode(yscgd, 0, decode(LJS, 1, 1, 0), 0))) wscgd
from (select c.resprojectjobid, c.id, a.connlineid,
(select count(*)
from projectuserinfo1 d
where d.resprojectjobid = c.resprojectjobid and d.connlineid = a.connlineid and
d.workorderid is not null) yscgd, a.uplinenumber + a.downlinenumber LJS
from closeupdefine c, closeupresport b, resport a
where a.resentityid = v_Entityid and portseqnum between v_Beginseqnum and v_Endseqnum and
a.status in ('B', 'D') and b.resportid(+) = a.id and c.id(+) = b.closeupdefineid)
group by resprojectjobid;
begin
v_Iscan := 'Y';
for R1 in C1 loop
if R1.wfw > 0 then
Raise_Application_Error(-20903, '割接批次:' || R1.resprojectjobid || '中直连的端子中存在产品实例且未封网!');
end if;
if R1.wscgd > 0 then
select b.* bulk collect
into portlist
from projectuserinfo1 a, resport b
where a.resprojectjobid(+) = R1.resprojectjobid and b.resentityid = v_Entityid and
b.portseqnum between v_Beginseqnum and v_Endseqnum and a.connlineid(+) = b.connlineid and
a.workorderid is null and b.connlineid is not null;
for i in portlist.first .. portlist.last loop
if i = 1 then
v_seqnum := portlist(i).portseqnum;
elsif i < 51 then
v_seqnum := v_seqnum || '...';
goto gjpc;
else
v_seqnum := v_seqnum || ',' || portlist(i).portseqnum;
end if;
end loop;
<<gjpc>>
null;
Raise_Application_Error(-20903,
'割接批次：' || R1.resprojectjobid || '中实体:' ||
rmdecode.f_get_resentitycode(v_Entityid) || '端子序为：' || v_seqnum ||
'存在的产品实例已封网但没有生成割接工单或端子状态有误!');
end if;
end loop;
/*select count(*)
    into v_Num
    from Resport a
    where Resentityid = v_Entityid and Portseqnum between v_Beginseqnum and v_Endseqnum and
          Status in ('B', 'D', 'H', 'I') and not exists (select * from Closeupresport where Resportid = a.Id);
    if v_Num > 0 then
      v_Iscan := 'N';
    end if;*/
return v_Iscan;
end;


--删除联系
function p_Del_Equipconnect
(
v_Equipcode     varchar2,
v_Column        number,
v_Beginportseq1 number,
v_Endportseq1   number,
v_Cablecode     varchar2,
v_Beginlineseq  number,
v_Endlineseq    number,
v_Jhcflag       number
) return varchar2 is
r_Error           varchar2(400);
v_Resentitytypeid Resentity.Resentitytypeid%type;
v_Beginportseq    Resconnect.Beginportseqnum%type;
v_Endportseq      Resconnect.Beginportseqnum%type;
v_Resentityid     Resport.Resentityid%type;
v_Lineresentityid Resport.Resentityid%type;
v_Conn            Resconnect%rowtype;
Tconn1            Tconn := Tconn();
Ownportlist       Tport;
Upportlist        Tport;
begin
if v_Endportseq1 - v_Beginportseq1 <> v_Endlineseq - v_Beginlineseq then
return '实体 电缆 端子范围不一致!';
end if;
begin
select Resentitytypeid, Id
into v_Resentitytypeid, v_Resentityid
from Resentity
where Regioncode = v_Regioncode and Resentitycode = v_Equipcode;
exception
when others then
r_Error := '根据编码:' || v_Equipcode || '没有找到设备！';
return r_Error;
end;
begin
select Id into v_Lineresentityid from Resentity where Regioncode = v_Regioncode and Resentitycode = v_Cablecode;
exception
when others then
r_Error := '根据编码:' || v_Cablecode || '没有找到电缆！';
return r_Error;
end;
--得到新增tconn对象所需的变量
if v_Resentitytypeid = 33 then
v_Beginportseq := v_Beginportseq1 + 10000 * v_Column;
v_Endportseq   := v_Endportseq1 + 10000 * v_Column;
else
v_Beginportseq := v_Beginportseq1;
v_Endportseq   := v_Endportseq1;
end if;
begin
/*  --判断要删除的联系中是否存在产品实例
      if Is_Judge = 'Y' then
        v_Isdelentity := f_Judge_Ifcandelete(v_Resentityid, v_Beginportseq, v_Endportseq);
        v_Isdelline   := f_Judge_Ifcandelete(v_Lineresentityid, v_Beginlineseq, v_Endlineseq);
        \*if v_Isdelentity = 'N' or v_Isdelline = 'N' then
          Raise_Application_Error(-20903, '直连的端子中存在产品实例!');
        end if;*\
      end if;*/
begin
if v_Jhcflag = 1 or v_Jhcflag = 0 then
select *
into v_Conn
from Resconnect a
where a.Resentityid = v_Lineresentityid and a.Beginportseqnum = v_Beginlineseq and
a.Endportseqnum = v_Endlineseq and a.Superiorresentityid = v_Resentityid and
a.Superiorbeginportseqnum = v_Beginportseq and a.Superiorendportseqnum = v_Endportseq;
Tconn1.extend;
Tconn1(Tconn1.last) := v_Conn;
end if;
if v_Jhcflag = 2 or v_Jhcflag = 0 then
select *
into v_Conn
from Resconnect a
where a.Resentityid = v_Resentityid and a.Beginportseqnum = v_Beginportseq and a.Endportseqnum = v_Endportseq and
a.Superiorresentityid = v_Lineresentityid and a.Superiorbeginportseqnum = v_Beginlineseq and
a.Superiorendportseqnum = v_Endlineseq;
Tconn1.extend;
Tconn1(Tconn1.last) := v_Conn;
end if;
exception
when others then
Raise_Application_Error(-20901, '没有找到对应的联系！');
end;
if Tconn1 is not null and Tconn1.count > 0 then
for i in Tconn1.first .. Tconn1.last loop
Ownportlist := Tport();
Upportlist  := Tport();
p_Add_Resporttoportlist(Tconn1(i), Ownportlist, Upportlist);
p_Delete_Resportincon(Ownportlist, Upportlist);
p_Delete_Resconnect(Tconn1(i));
end loop;
end if;
exception
when others then
r_Error := substrb(sqlerrm, 0, 400);
return r_Error;
end;
return 'S';
end;


/*将单向成端修改为双向*/
procedure P_MOD_DOUBLE_CONNECT
(
V_EQUIPCODE    varchar2,
V_COLUMN       number,
V_BEGINPORTSEQ number,
V_ENDPORTSEQ   number,
V_CABLECODE    varchar2,
V_BEGINLINESEQ number,
V_ENDLINESEQ   number,
V_JHCFLAG      number,
R_RESULT       out varchar2,
R_ERROR        out varchar2
) is
V_SUPENTITYID number(20);
V_SUPBEGIN    number(20);
V_SUPEND      number(20);
V_ENTITYID    number(20);
V_BEGIN       number(20);
V_END         number(20);
V_EQUIPID     number(20);
V_EQUIPTYPEID number(20);
V_CABLEID     number(20);
V_CONNID      number(20);
SUPPORTARR    TPORTID := TPORTID();
PORTARR       TPORTID := TPORTID();
begin
begin
begin
select ID, RESENTITYTYPEID
into V_EQUIPID, V_EQUIPTYPEID
from RESENTITY
where REGIONCODE = V_REGIONCODE and RESENTITYCODE = V_EQUIPCODE;
select ID into V_CABLEID from RESENTITY where REGIONCODE = V_REGIONCODE and RESENTITYCODE = V_CABLECODE;
exception
when others then
RAISE_APPLICATION_ERROR(-20901, '根据编码查未找到设备或者电缆!');
end;
if V_ENDLINESEQ - V_BEGINLINESEQ <> V_ENDPORTSEQ - V_BEGINPORTSEQ then
RAISE_APPLICATION_ERROR(-20901, '设备电缆 端子范围不一致!');
end if;
--电缆出设备,设备为上级,电缆为本级
if V_JHCFLAG = 1 then
V_SUPENTITYID := V_EQUIPID;
if V_EQUIPTYPEID = 33 then
V_SUPBEGIN := 10000 * V_COLUMN + V_BEGINPORTSEQ;
V_SUPEND   := 10000 * V_COLUMN + V_ENDPORTSEQ;
else
V_SUPBEGIN := V_BEGINPORTSEQ;
V_SUPEND   := V_ENDPORTSEQ;
end if;
V_ENTITYID := V_CABLEID;
V_BEGIN    := V_BEGINLINESEQ;
V_END      := V_ENDLINESEQ;
--电缆入设备,设备为本级,电缆为上级
elsif V_JHCFLAG = 2 then
V_ENTITYID := V_EQUIPID;
if V_EQUIPTYPEID = 33 then
V_BEGIN := 10000 * V_COLUMN + V_BEGINPORTSEQ;
V_END   := 10000 * V_COLUMN + V_ENDPORTSEQ;
else
V_BEGIN := V_BEGINPORTSEQ;
V_END   := V_ENDPORTSEQ;
end if;
V_SUPENTITYID := V_CABLEID;
V_SUPBEGIN    := V_BEGINLINESEQ;
V_SUPEND      := V_ENDLINESEQ;
else
RAISE_APPLICATION_ERROR(-20901, '参数 V_JHCFLAG 为双向!');
end if;
begin
select ID
into V_CONNID
from RESCONNECT R
where R.SUPERIORRESENTITYID = V_SUPENTITYID and R.SUPERIORBEGINPORTSEQNUM = V_SUPBEGIN and
R.SUPERIORENDPORTSEQNUM = V_SUPEND and R.RESENTITYID = V_ENTITYID and BEGINPORTSEQNUM = V_BEGIN and
ENDPORTSEQNUM = V_END;
exception
when others then
RAISE_APPLICATION_ERROR(-20901, '根据参数未找到成端!');
end;
/*插入中间表*/
insert into resport_tem
select * from resport n where RESENTITYID = V_SUPENTITYID and PORTSEQNUM between V_SUPBEGIN and V_SUPEND;
update RESPORT
set UPLINENUMBER = UPLINENUMBER + 1
where RESENTITYID = V_SUPENTITYID and PORTSEQNUM between V_SUPBEGIN and V_SUPEND;
/*删除中间表*/
delete from resport_tem where RESENTITYID = V_SUPENTITYID and PORTSEQNUM between V_SUPBEGIN and V_SUPEND;
/*插入中间表*/
insert into resport_tem
select * from resport where RESENTITYID = V_ENTITYID and PORTSEQNUM between V_BEGIN and V_END;
update RESPORT
set DOWNLINENUMBER = DOWNLINENUMBER + 1
where RESENTITYID = V_ENTITYID and PORTSEQNUM between V_BEGIN and V_END;
/*删除中间表*/
delete from resport_tem where RESENTITYID = V_ENTITYID and PORTSEQNUM between V_BEGIN and V_END;
select ID bulk collect
into SUPPORTARR
from RESPORT
where RESENTITYID = V_SUPENTITYID and PORTSEQNUM between V_SUPBEGIN and V_SUPEND;
select ID bulk collect
into PORTARR
from RESPORT
where RESENTITYID = V_ENTITYID and PORTSEQNUM between V_BEGIN and V_END;
if SUPPORTARR.count > 0 and PORTARR.count > 0 and SUPPORTARR.count = PORTARR.count then
for I in SUPPORTARR.first .. PORTARR.last loop
insert into RESPORTRELATION values (SEQ_RESPORTRELATION.nextval, PORTARR(I), SUPPORTARR(I));
end loop;
end if;
insert into RESCONNECT
select SEQ_RESCONNECT.nextval, SUPERIORRESENTITYID, SUPERIORRESENTITYVERSION, SUPERIORRESPORTTYPEID,
SUPERIORPORTCODERULEID, SUPERIORBEGINPORTCODE, SUPERIORENDPORTCODE, SUPERIORBEGINPORTSEQNUM,
SUPERIORENDPORTSEQNUM, SUPERIORPARITY, RESENTITYID, RESENTITYVERSION, RESPORTTYPEID, PORTCODERULEID,
BEGINPORTCODE, ENDPORTCODE, BEGINPORTSEQNUM, ENDPORTSEQNUM, PARITY, type, PRIORGRADE
from RESCONNECT
where ID = V_CONNID;
R_RESULT := 'S';
exception
when others then
R_RESULT := 'F';
R_ERROR  := 'p_mod_double_connect:' || sqlerrm;
end;
end P_MOD_DOUBLE_CONNECT;


--查找区域
procedure p_Set_Region is
begin
if v_Regioncode is null then
v_Regioncode := 'SHAOXING';
end if;
end;
begin
-- Initialization
p_Set_Region;
Rm_Staffno             := 'GIS';
Port_Status_Usable     := 'A'; --空好
Rm_Portstatus_Preuse   := 'D'; --预占
Rm_Portstatus_Used     := 'B'; --占用
Rm_Portstatus_Repreuse := 'H'; --复接预占
Rm_Portstatus_Reused   := 'I'; --复接占用
end Inf_Sx97_Gis;
/