第一次自动生成架空导线

//
//
//改两处'团东甲线'和三处'2AB040'
//
//
--停止触发器
ALTER TABLE B$COMMON_N DISABLE ALL TRIGGERS;
ALTER TABLE b$CONNECTIVITY_N DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_N DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LN DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LN_SDOGEOM DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LB DISABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LB_SDOGEOM DISABLE ALL TRIGGERS;

declare
 i  number;
 flag number; --标记是否为开始杆塔
 flag_label number; --控制线路标注(现在为空一个标一个)
 v_lable_sdo_x2   B$SD_JKDX_LN_SDOGEOM.sdo_x2%type;
 v_lable_sdo_y2   B$SD_JKDX_LN_SDOGEOM.sdo_y2%type;
 v_symbol_sdo_x1  B$SD_JKDX_LN_SDOGEOM.sdo_x1%type;
 v_symbol_sdo_y1  B$SD_JKDX_LN_SDOGEOM.sdo_y1%type;
 v_symbol_sdo_x2  B$SD_JKDX_LN_SDOGEOM.sdo_x2%type;
 v_symbol_sdo_y2  B$SD_JKDX_LN_SDOGEOM.sdo_y2%type;
 v_prv_gt_sdo_x1  B$SD_GT_PT_SDOGEOM.sdo_x1%type;
 v_prv_gt_sdo_y1  B$SD_GT_PT_SDOGEOM.sdo_y1%type;
 v_gt_sdo_x1      B$SD_GT_PT_SDOGEOM.sdo_x1%type;
 v_gt_sdo_y1      B$SD_GT_PT_SDOGEOM.sdo_y1%type;

v_prv_gth B$SD_GT_N.gth%type;
v_gth  B$SD_GT_N.gth%type;

 v_g3e_fid        b$common_N.g3e_fid%type;   
 v_gt_g3e_fid     b$common_N.g3e_fid%type;     
 new_g3e_fid      b$common_n.g3e_fid%type;  
 new_jkdxlabel_g3e_id    b$common_n.g3e_id%type;  
 new_jkdxsymbol_g3e_id   b$common_n.g3e_id%type; 

 --标注的角度，通过架空导线的两个坐标求得
 v_sdo_orientation  B$SD_JKDX_LN_SDOGEOM.SDO_ORIENTATION%type;
 v_x                B$SD_JKDX_LN_SDOGEOM.sdo_x1%type;
 v_y                B$SD_JKDX_LN_SDOGEOM.sdo_y1%type;

 v_node1_id       B$connectivity_n.node1_id%type;
 v_node2_id       B$connectivity_n.node2_id%type;
 v_owner_id1      b$common_n.owner1_id%type;
 v_owner_id2      b$common_n.owner2_id%type;
 v_old_owner_id   b$common_n.owner1_id%type;
 v_old_node_id    b$connectivity_n.node2_id%type;

 --选出某线路的所有杆塔 ,杆塔的选出顺序从左到右，通过ORDER BY GTH DESC控制。左边大号用desc
 CURSOR c1 IS SELECT G3E_FID,GTH FROM B$SD_GT_N WHERE CD_XLMC='22B213' ORDER BY GTH;

begin
 flag:=0;   --第一个杆塔时为0，否则为1
 flag_label:=1;  --为1时架空导线有标注，为0时架空导线没有标注
 v_old_owner_id:=0;
 v_old_node_id:=0;
 v_g3e_fid:=23727920; --作为参考的架空导线，参考的架空导线的所有属性将被复制到新设备了
v_prv_gth:=0;
v_gth:=0;
 FOR c IN c1  LOOP
  v_gt_g3e_fid:=c.g3e_fid;

  --赋所属关系的值
  v_owner_id1:=v_old_owner_id;
  SELECT g3e_id INTO  v_owner_id2 FROM B$COMMON_N WHERE g3e_fid=v_gt_g3e_fid;
  v_old_owner_id:=v_owner_id2;

  IF flag!=0 THEN
  --赋连接关系的值
  v_node1_id:=v_old_node_id;
  SELECT g3e_node_seq.nextval INTO v_node2_id FROM dual;
  v_old_node_id:=v_node2_id;
  --取新加设备的g3e_fid与其它值
  SELECT g3e_fid_seq.nextval INTO new_g3e_fid FROM dual;
  SELECT sd_jkdx_ln_seq.nextval INTO new_jkdxsymbol_g3e_id FROM dual;
  SELECT sd_jkdx_Lb_seq.nextval INTO new_jkdxlabel_g3e_id FROM dual;
  
   --取出相邻的两个杆塔的坐标，作为生成架空导线的两个点的坐标
   ----取出当前杆塔的坐标
   SELECT sdo_x1,sdo_y1 INTO v_gt_sdo_x1,v_gt_sdo_y1 FROM B$SD_GT_PT_SDOGEOM WHERE G3E_FID=v_gt_g3e_fid;
   ----给架空导线的两个点赋值,并保留第二个点作为下一条直线的第一个点
   v_symbol_sdo_x1:=v_prv_gt_sdo_x1;
   v_symbol_sdo_y1:=v_prv_gt_sdo_y1;
   v_symbol_sdo_x2:=v_gt_sdo_x1;
   v_symbol_sdo_y2:=v_gt_sdo_y1;
 
   v_prv_gt_sdo_x1:=v_gt_sdo_x1;
   v_prv_gt_sdo_y1:=v_gt_sdo_y1;
--取出当前杆塔号
 SELECT gth INTO v_gth FROM B$SD_GT_N WHERE G3E_FID=v_gt_g3e_fid;  

   ----给架空导线的标注坐标赋值
   v_lable_sdo_x2:=(v_symbol_sdo_x1+v_symbol_sdo_x2)*0.5;
   v_lable_sdo_y2:=(v_symbol_sdo_y1+v_symbol_sdo_y2)*0.5-10;
   v_x:=v_symbol_sdo_x2-v_symbol_sdo_x1;
   v_y:=v_symbol_sdo_y2-v_symbol_sdo_y1;
   --左高的用ATAN2(v_y,v_x)
   v_sdo_orientation:=ATAN2(v_y,v_x);

   --生成架空导线的图形
   ----往公共表里插入记录,建立架空导线与杆塔的所属关系
   INSERT INTO B$COMMON_N
    ( G3E_ID,G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,CD_SSDW,SBMC,CD_DYDJ,OWNER1_ID,OWNER2_ID,CD_EJDW,CD_ZT,CD_JKZT,SBDM)
   SELECT
     common_n_seq.nextval,G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,CD_SSDW,'新肥线',CD_DYDJ,v_owner_id1,v_owner_id2,CD_EJDW,CD_ZT,CD_JKZT,'22B213'
   FROM B$COMMON_N
   WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

   ----生成图形
   INSERT INTO B$SD_JKDX_N    
    (G3E_ID,G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,CD_XLMC,CD_XLDM,QSGH,ZZGH)
   SELECT
     sd_jkdx_N_seq.nextval,G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,'新肥线','22B213',v_prv_gth,v_gth
   FROM B$SD_JKDX_N
   WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;
v_prv_gth:=v_gth;
   INSERT INTO  B$SD_JKDX_LN
    (GDO_GID,G3E_ID, G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,G3E_GEOMETRY,LTT_DATE)
   SELECT
    GDO_GID,new_jkdxsymbol_g3e_id, G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,G3E_GEOMETRY,sysdate
   FROM B$SD_JKDX_LN
   WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

   INSERT INTO  B$SD_JKDX_LN_SDOGEOM
    (SDO_GID,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,
     GDO_RADIUS,SDO_ORIENTATION,SDO_X1, SDO_Y1, SDO_X2, SDO_Y2,
     G3E_ID, G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,LTT_DATE)
   SELECT
     new_jkdxsymbol_g3e_id,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,
     GDO_RADIUS,SDO_ORIENTATION,v_symbol_sdo_x1, v_symbol_sdo_y1, v_symbol_sdo_x2, v_symbol_sdo_y2,
     sd_jkdx_ln_sdogeom_seq.nextval, G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,sysdate
   FROM B$SD_JKDX_LN_SDOGEOM
   WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

   ----生成标注,并间隔一个标注一个
   IF flag_label=1 THEN
    INSERT INTO  B$SD_JKDX_LB
     (GDO_GID,G3E_ALIGNMENT,G3E_ID,G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,G3E_GEOMETRY,LTT_DATE)
    SELECT
     GDO_GID,G3E_ALIGNMENT,new_jkdxlabel_g3e_id,G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,G3E_GEOMETRY,sysdate
    FROM B$SD_JKDX_LB
    WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;

    INSERT INTO  B$SD_JKDX_LB_SDOGEOM
     (SDO_GID,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,
      GDO_RADIUS,SDO_ORIENTATION,SDO_X1,SDO_Y1,
      G3E_ID,G3E_FNO,G3E_CNO,G3E_FID,G3E_CID,LTT_DATE)
    SELECT
      new_jkdxlabel_g3e_id,SDO_ESEQ,SDO_ETYPE,SDO_SEQ,GDO_ATTRIBUTES,GDO_NORMAL1,GDO_NORMAL2,GDO_NORMAL3,
      GDO_RADIUS,v_sdo_orientation,v_lable_sdo_x2,v_lable_sdo_y2,
      sd_jkdx_lb_sdogeom_seq.nextval,G3E_FNO,G3E_CNO,new_g3e_fid,G3E_CID,sysdate
    FROM B$SD_JKDX_lb_SDOGEOM
    WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;
    flag_label:=0;
   ELSE
     flag_label:=1;
   END IF;

 --建立架空导线与架空导线的电力连接关系
   INSERT INTO  B$CONNECTIVITY_N
    (G3E_FNO,G3E_FID,G3E_CNO,G3E_CID,G3E_ID,NODE1_ID,NODE2_ID,
     PHASE,LTT_DATE)
   SELECT
     G3E_FNO,new_g3e_fid,G3E_CNO,G3E_CID,connectivity_n_seq.nextval,v_node1_id,v_node2_id,
     PHASE,sysdate
   FROM B$CONNECTIVITY_N
   WHERE  g3e_fid=v_g3e_fid AND ltt_status IS NULL;
   i:=i+1;
  ELSE
    flag:=1;
    --取出第一个杆塔的坐标作为第一条架空导线的第一个坐标
    SELECT sdo_x1,sdo_y1 INTO v_prv_gt_sdo_x1,v_prv_gt_sdo_y1 FROM B$SD_GT_PT_SDOGEOM WHERE G3E_FID=v_gt_g3e_fid;
  END IF;
  end loop;
  dbms_output.put_line('添加了'||i||' 条架空导线。');
end;

/

--启用触发器
ALTER TABLE B$COMMON_N ENABLE ALL TRIGGERS;
ALTER TABLE b$CONNECTIVITY_N enable all triggers;
ALTER TABLE B$SD_JKDX_N enable all triggers;
ALTER TABLE B$SD_JKDX_LN ENABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LN_SDOGEOM ENABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LB ENABLE ALL TRIGGERS;
ALTER TABLE B$SD_JKDX_LB_SDOGEOM ENABLE ALL TRIGGERS;