DSRC-RSE 제공 구간 관리.sql 3.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081
  1. SELECT TO_NUMBER(CASE WHEN EXISTS (SELECT 1 FROM TB_RSE_OFFR_SECT_MNGM)
  2. THEN (SELECT TRIM(MAX(TO_NUMBER(OFFR_SECT_ID)) + 1)
  3. FROM TB_RSE_OFFR_SECT_MNGM)
  4. ELSE '1' END) AS NEWID
  5. FROM DUAL
  6. SELECT TO_NUMBER(CASE WHEN EXISTS (SELECT 1 FROM TB_RSE_OFFR_SECT_MNGM)
  7. THEN (SELECT TRIM(MAX(TO_NUMBER(OBU_ENTR_DRCT_NMBR)) + 1)
  8. FROM TB_RSE_OFFR_SECT_MNGM
  9. WHERE ID = '')
  10. ELSE '1' END) AS AUTONUM
  11. FROM DUAL
  12. SELECT A.OFFR_SECT_ID, A.OFFR_DRCT_NM, A.CNGS_BASI_SPED, A.DELY_BASI_SPED,
  13. A.ID, A.OBU_ENTR_DRCT_NMBR, C.PRE_ID, C.IXR_DRCT_NUM,
  14. (SELECT ISTL_LCTN_NM FROM TB_RSE_MSTR WHERE ID = A.ID) TARGETNM,
  15. (SELECT ISTL_LCTN_NM FROM TB_RSE_MSTR WHERE ID = C.PRE_ID) PRENM
  16. FROM TB_RSE_OFFR_SECT_MNGM A,
  17. TB_RSE_OFFR_DRCT_INFR C
  18. WHERE A.ID = C.ID(+)
  19. AND A.OBU_ENTR_DRCT_NMBR = C.OBU_ENTR_DRCT_NMBR(+)
  20. ORDER BY TO_NUMBER(A.OFFR_SECT_ID)
  21. --RSE 제공 구간 관리(정보제공구간정보)
  22. SELECT OFFR_SECT_ID,
  23. OFFR_DRCT_NM,
  24. CNGS_BASI_SPED,
  25. DELY_BASI_SPED,
  26. ID,
  27. OBU_ENTR_DRCT_NMBR
  28. FROM TB_RSE_OFFR_SECT_MNGM
  29. ORDER BY OFFR_SECT_ID
  30. --RSE 제공 구간 정보(링크레벨2)
  31. SELECT OFFR_SECT_ID,
  32. IFSC_ID,
  33. ORD
  34. FROM TB_RSE_OFFR_SECT_INFR
  35. --RSE 제공 방향 정보
  36. SELECT ID,
  37. OBU_ENTR_DRCT_NMBR,
  38. PRE_ID,
  39. IXR_DRCT_NUM
  40. FROM TB_RSE_OFFR_DRCT_INFR
  41. SELECT A.OFFR_SECT_ID, A.OFFR_DRCT_NM, A.CNGS_BASI_SPED, A.DELY_BASI_SPED, A.ID, A.OBU_ENTR_DRCT_NMBR,
  42. B.IFSC_ID, B.ORD, C.PRE_ID, C.IXR_DRCT_NUM,
  43. D.F_NODE_ID, (SELECT NODE_NAME FROM TB_NODE WHERE NODE_ID = D.F_NODE_ID) F_NODENAME,
  44. D.T_NODE_ID, (SELECT NODE_NAME FROM TB_NODE WHERE NODE_ID = D.T_NODE_ID) T_NODENAME,
  45. (SELECT ISTL_LCTN_NM FROM TB_RSE_MSTR WHERE ID = A.ID) TARGETNM,
  46. (SELECT ISTL_LCTN_NM FROM TB_RSE_MSTR WHERE ID = C.PRE_ID) PRENM
  47. FROM TB_RSE_OFFR_SECT_MNGM A,
  48. TB_RSE_OFFR_SECT_INFR B,
  49. TB_RSE_OFFR_DRCT_INFR C,
  50. TB_IFSC D
  51. WHERE A.OFFR_SECT_ID = B.OFFR_SECT_ID(+)
  52. AND A.ID = C.ID(+)
  53. AND A.OBU_ENTR_DRCT_NMBR = C.OBU_ENTR_DRCT_NMBR(+)
  54. AND B.IFSC_ID = D.IFSC_ID(+)
  55. ORDER BY TO_NUMBER(A.OFFR_SECT_ID), B.ORD
  56. DELETE TB_RSE_OFFR_SECT_INFR WHERE OFFR_SECT_ID = :p01
  57. DELETE TB_RSE_OFFR_INFR_HS WHERE OFFR_SECT_ID = :p01
  58. DELETE TB_RSE_OFFR_SECT_MNGM WHERE OFFR_SECT_ID = :p01
  59. SELECT * FROM TB_RSE_OFFR_DRCT_INFR