一次建线图设备的导入(一期到二期)----变电所母线

alter table b$common_n disable all triggers;
alter table b$connectivity_n disable all triggers;
alter table b$bd_mx_n disable all triggers;
alter table b$bd_mx_ln disable all triggers;
alter table b$bd_mx_ln_sdogeom disable all triggers;
alter table b$bd_mx_lb disable all triggers;
alter table b$bd_mx_lb_sdogeom disable all triggers;
commit;

declare
 num   number;
 new_g3e_fno number;
 g3e_cno_n number;
 g3e_cno_pt number;
 g3e_cno_lb number;

 new_g3e_fid b$comm_table.g3e_fid%type;
 new_g3e_id b$comm_table.g3e_id%type;
 new_label_g3e_id b$comm_table.g3e_id%type;
 new_g3e_detailid  b$bd_mxline.g3e_detailid%type;

 v_g3e_fid b$comm_table.g3e_fid%type;
 v_g3e_fno b$comm_table.g3e_fno%type;
 v_g3e_detailid  b$bd_mxline.g3e_detailid%type;
 v_x b$bd_mxline_sdogeom.sdo_x1%type;
 v_y b$bd_mxline_sdogeom.sdo_y1%type;

 CURSOR c1 IS SELECT g3e_fid from b$bd_mxline WHERE  ltt_status IS NULL AND g3e_detailid=v_g3e_detailid And g3e_fid not in (select g3e_fid from b$bd_yxline where ltt_status='DELETE');
begin
 num:=0;
 v_x:=-1245795;
 v_y:=6300;

 new_g3e_detailid:=1162;
 v_g3e_detailid:=835;

 new_g3e_fno:=431;
 g3e_cno_n:=30331;
 g3e_cno_pt:=30332;
 g3e_cno_lb:=30333;

 FOR i IN c1  LOOP
  SELECT g3e_fid_seq.nextval INTO new_g3e_fid FROM dual;
  SELECT bd_mxline_seq.nextval INTO new_g3e_id FROM dual;
  SELECT bd_mxlabel_seq.nextval INTO new_label_g3e_id FROM dual;
  v_g3e_fid:=i.g3e_fid;

insert into z_zeng@zeng values(v_g3e_fid,new_g3e_fid,'变电所母线',sysdate,'z','靖宇一次变电所');
  commit;

--公共表
  INSERT INTO b$common_n@zeng
   (g3e_id,g3e_fno,g3e_cno,g3e_fid,g3e_cid,sbmc, cd_ssdw,cd_ejdw,cd_bds,cd_dydj,cd_zt,ltt_date)
  SELECT
    common_n_seq.nextval@zeng,new_g3e_fno,30,new_g3e_fid,g3e_cid,sbmc,cd_ssdyj,cd_ssejdw,cd_ssbds,cd_dydj,cd_zt,sysdate
  FROM b$comm_table
  WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

--连接关系表
  INSERT INTO B$CONNECTIVITY_N@zeng
   (g3e_fno,g3e_fid,g3e_cno,g3e_cid,g3e_id,node1_id,node2_id,ltt_date)
  SELECT
   new_g3e_fno,new_g3e_fid,31,g3e_cid,connectivity_n_seq.nextval@zeng,node1_id,node2_id,sysdate
  from B$CONNECTIVITY_N
  WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

--属性表
  INSERT INTO b$bd_mx_n@zeng
   (g3e_id,g3e_fno,g3e_cno,g3e_fid,g3e_cid,ltt_date,JM,CCRQ,CD_SCCJ)
  SELECT
   bd_mx_n_seq.nextval@zeng,new_g3e_fno,g3e_cno_n,new_g3e_fid,g3e_cid,sysdate,JM,CCRQ,SCCJ
  FROM b$bd_mx
  WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;
--图形符合
  INSERT INTO b$bd_mx_ln@zeng
   (g3e_id,g3e_fno,g3e_cno,g3e_fid,g3e_cid,g3e_detailid, ltt_date)
  SELECT
   new_g3e_id,new_g3e_fno,g3e_cno_pt,new_g3e_fid,g3e_cid,new_g3e_detailid,sysdate
  FROM b$bd_mxline
  WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

  INSERT INTO b$bd_mx_ln_sdogeom@zeng
   (sdo_gid,sdo_eseq,sdo_etype,sdo_seq,gdo_attributes, sdo_x1,sdo_y1,sdo_x2,sdo_y2,sdo_x3,sdo_y3,sdo_x4,sdo_y4,
    g3e_id, g3e_fno,g3e_cno,g3e_fid,g3e_cid, ltt_date)
  SELECT
    new_g3e_id,sdo_eseq,sdo_etype,sdo_seq,gdo_attributes, sdo_x1+v_x, sdo_y1+v_y,sdo_x2+v_x, sdo_y2+v_y, sdo_x3+v_x, sdo_y3+v_y,sdo_x4+v_x, sdo_y4+v_y,
    bd_mx_ln_SDOGEOM_seq.nextval@zeng, new_g3e_fno,g3e_cno_pt,new_g3e_fid,g3e_cid,sysdate
  FROM b$bd_mxline_sdogeom
  WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;
--标注
  INSERT INTO b$bd_mx_lb@zeng
   (g3e_id,g3e_fno,g3e_cno,g3e_fid,g3e_cid,g3e_detailid, ltt_date)
  SELECT
   new_label_g3e_id,new_g3e_fno,g3e_cno_lb,new_g3e_fid,g3e_cid,new_g3e_detailid,sysdate
  FROM b$bd_mxlabel
  WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

  INSERT INTO b$bd_mx_lb_sdogeom@zeng
   (sdo_gid,sdo_eseq,sdo_etype,sdo_seq,gdo_attributes,sdo_orientation, sdo_x1,sdo_y1,
    g3e_id, g3e_fno,g3e_cno,g3e_fid,g3e_cid, ltt_date )
  SELECT
    new_label_g3e_id,sdo_eseq,sdo_etype,sdo_seq,gdo_attributes, 0,sdo_x1+v_x, sdo_y1+v_y,
    bd_mx_lb_sdogeom_seq.nextval@zeng, new_g3e_fno,g3e_cno_lb,new_g3e_fid,g3e_cid,sysdate
  FROM b$bd_mxlabel_sdogeom
  WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

  num:=num+1;
  END LOOP;
  commit;

  dbms_output.put_line('共添加了'||num||' 个设备。');
end;
/

alter table b$common_n enable all triggers;
alter table b$connectivity_n enable all triggers;
alter table b$bd_mx_n enable all triggers;
alter table b$bd_mx_ln enable all triggers;
alter table b$bd_mx_ln_sdogeom enable all triggers;
alter table b$bd_mx_lb enable all triggers;
alter table b$bd_mx_lb_sdogeom enable all triggers;
commit;