utic-its/reference/DataBase.SQL

-- db_comm.pc

-- SEL_Center

-- struct
-- {
--     char    szID[MAX_FETCH_ROWS][SIZE_CENTER_ID+1];         /* 센터 ID                       */
--     char    szName[MAX_FETCH_ROWS][SIZE_CENTER_NAME+1];     /* 센터 NAME                     */
--     char    szCommIP[MAX_FETCH_ROWS][SIZE_CENTER_IP+1];     /* 통신 IP                       */
--     int     nCommPort[MAX_FETCH_ROWS];                      /* 통신 포트                     */
--     char    szUser[MAX_FETCH_ROWS][SIZE_CENTER_USER+1];     /* 사용자명                      */
--     char    szPwd[MAX_FETCH_ROWS][SIZE_CENTER_PWD+1];       /* 사용자비밀번호                */
--     int     nHeartbeatCycle[MAX_FETCH_ROWS];                /* 허트비트 주기                 */
--     int     nResTime[MAX_FETCH_ROWS];                       /* 응답시간                      */
--     int     nDatagramSize[MAX_FETCH_ROWS];                  /* 데이터그램 크기               */
-- } rec;

/*
************************************************************************************************
*  센터 테이블에서 서버 정보를 읽어온다.
************************************************************************************************
*/
SELECT SUBSTR(centerid, 1, 10), SUBSTR(centerinfo, 1, 20),
       ipaddress, commport, 
       datexuser, datexpasswd, heartbeatcycle, restime, datagramsize
  FROM CENTER
  WHERE centerflag = 'M'
    AND exeyn = 'Y'
  ORDER BY centerid;

/*
************************************************************************************************
*  센터 테이블에서 클라이언트 정보를 읽어온다.
************************************************************************************************
*/
SELECT SUBSTR(centerid, 1, 10), SUBSTR(centerinfo, 1, 20),
       ipaddress, commport,
       datexuser, datexpasswd, heartbeatcycle, restime, datagramsize
  FROM CENTER
 WHERE centerflag = 'L'
   AND exeyn = 'Y'
ORDER BY centerid;


-- db_asnc.pc

-- INS_RcvTraffic

-- struct
-- {
--     char  szRegDate[MAX_DB_INS_ROWS][SIZE_DATETIME+1];
--     char  szFromCenterId[MAX_DB_INS_ROWS][SIZE_CENTER_ID+1];
--     char  szLinkNumber[MAX_DB_INS_ROWS][SIZE_LINK_ID+1];
--     char  szToCenterId[MAX_DB_INS_ROWS][SIZE_CENTER_ID+1];
--     int   nSpeedRate[MAX_DB_INS_ROWS];
--     int   nTravelTimeQuantity[MAX_DB_INS_ROWS];
--     int   nDelayQuanity[MAX_DB_INS_ROWS];
--     int   nDensityRate[MAX_DB_INS_ROWS];
-- } rec;

INSERT INTO RCV_LINK_TRAFFIC(regdate,
                             fromcenterid,
                             linknumber,
                             tocenterid,
                             logdate,
                             speedrate,
                             traveltimequantity,
                             delayquanity,
                             densityrate
)
VALUES(TO_DATE(:rec.szRegDate, 'YYYYMMDDHH24MISS'),
       :rec.szFromCenterId,
       :rec.szLinkNumber,
       :rec.szToCenterId,
       SYSDATE,
       :rec.nSpeedRate,
       :rec.nTravelTimeQuantity,
       :rec.nDelayQuanity,
       :rec.nDensityRate
      );

-- INS_RcvIncident

-- struct
-- {
--     char  szToCenterId[MAX_DB_ROWS_SMALL][SIZE_CENTER_ID+1];
--     char  szLinkNumber[MAX_DB_ROWS_SMALL][SIZE_LINK_ID+1];
--     char  szNodeNumber[MAX_DB_ROWS_SMALL][SIZE_NODE_ID+1];
--     char  szFromCenterId[MAX_DB_ROWS_SMALL][SIZE_CENTER_ID+1];
--     char  szContactOrganizationNameText[MAX_DB_ROWS_SMALL][128+1];
--     int   nDescriptionTypeIncidentCode[MAX_DB_ROWS_SMALL];
--     char  szDescriptionTypeIncidentOther[MAX_DB_ROWS_SMALL][256+1];
--     int   nIncidentVehiclesInvolvedCode[MAX_DB_ROWS_SMALL];
--     char  szIncidentVehiclesInvolvedOther[MAX_DB_ROWS_SMALL][256+1];
--     int   nIncidentStatusCode[MAX_DB_ROWS_SMALL];
--     char  szIncidentStatusOther[MAX_DB_ROWS_SMALL][256+1];
--     int   nUpdateTypeCode[MAX_DB_ROWS_SMALL];
--     char  szUpdateTypeOther[MAX_DB_ROWS_SMALL][256+1];
-- } rec;

INSERT INTO RCV_INCIDENT(tocenterid,
                         link_linkidnumber,
                         node_nodeidnumber,
                         logdate,
                         fromcenterid,
                         contactorganizationnametext,
                         descriptiontypeincidentcode,
                         descriptiontypeincidentother,
                         incidentvehiclesinvolvedcode,
                         incidentvehiclesinvolvedother,
                         incidentstatuscode,
                         incidentstatusother,
                         updatetypecode,
                         updatetypeother,
                         sendyn)
VALUES(:rec.szToCenterId,
       :rec.szLinkNumber,
       :rec.szNodeNumber,
       SYSDATE,
       :rec.szFromCenterId,
       :rec.szContactOrganizationNameText,
       :rec.nDescriptionTypeIncidentCode,
       :rec.szDescriptionTypeIncidentOther,
       :rec.nIncidentVehiclesInvolvedCode,
       :rec.szIncidentVehiclesInvolvedOther,
       :rec.nIncidentStatusCode,
       :rec.szIncidentStatusOther,
       :rec.nUpdateTypeCode,
       :rec.szUpdateTypeOther,
       'N');


-- INS_RcvLog
INSERT INTO RCV_LOG(logdate, infokind, tocenterid, dupkey, fromcenterid, datacnt)
VALUES(SYSDATE, :pLog->szInfoKind, :pLog->szToCenterId, NVL(:pLog->szDupKey, 'DupKey'), :pLog->szFromCenterId, :pLog->DataCnt);

-- INS_CenterReceive
INSERT INTO CENTER_RECEIVE@UTISDB(LOGDATE, CENTERID, INFOTYPE, SEQ, DATACNT)
VALUES(SYSDATE, :pLog->szFromCenterId, 'R1', :seq, :pLog->DataCnt);


-- db_svcp.pc

-- SEL_CheckTraffic
SELECT TO_CHAR(regdate, 'YYYYMMDDHH24MISS')
FROM TRAFFIC_CENTER@UTISDB
WHERE regdate > (SYSDATE - 15/1440)
  AND ROWNUM < 2;

-- SEL_TrafficCenter

-- struct
-- {
--     char    szLinkId[MAX_FETCH_ROWS][SIZE_LINK_ID+1];
--     int     nSpeed[MAX_FETCH_ROWS];
--     int     nTravelTime[MAX_FETCH_ROWS];
--     int     nTrafficGrade[MAX_FETCH_ROWS];
--     int     nDataResType[MAX_FETCH_ROWS];
-- } rec;

SELECT linkid,
       NVL(speed, 0) speed,
       NVL(traveltime, 0) traveltime,
       NVL(TO_NUMBER(trafficgrade), 0) trafficgrade,
       DECODE(datarestype, 'S', 1, 0) datarestype
FROM TRAFFIC_CENTER@UTISDB
WHERE regdate > (SYSDATE - 15/1440)
  AND linklevel = 1
  AND missvalueyn = 'R'
--AND datarestype IN ('P', 'G', 'S')
ORDER BY linkid;

-- INS_SndIncident
INSERT INTO SND_INCIDENT(tocenterid, link_linkidnumber, logdate, fromcenterid, node_nodeidnumber,
                         contactorganizationnametext, descriptiontypeincidentcode, descriptiontypeincidentother,
                         incidentvehiclesinvolvedcode, incidentvehiclesinvolvedother, incidentstatuscode,
                         incidentstatusother, updatetypecode, updatetypeother, sendyn)
SELECT '%s',
        LINK_ID,
        SYSDATE,
        '%s',
        INCIDENT_ID,
        '경찰청정보연계',
        TO_NUMBER(INCIDENT_TYPE_CD),
        SUBSTRB(REPLACE(INCIDENT_TITLE,'::',' '),1,255),
        TO_NUMBER(INCIDENT_SUBTYPE_CD),
        SUBSTRB(INCIDENT_DESC,1,255),
        NVL(TO_NUMBER(SUBSTR(INCIDENT_TYPE_CD,4,2)), 2),
        TO_CHAR(LOCATION_DATA_X, '000.000000000000')||TO_CHAR(LOCATION_DATA_Y, '00.0000000000000')||ADDRESS_NEW,
        TO_NUMBER(SUBSTR(INCIDENT_GRADE_CD,5,1)),
        TO_CHAR(REPORT_DATE, 'YYYYMMDDHH24MISS')||TO_CHAR(START_DATE, 'YYYYMMDDHH24MISS')||TO_CHAR(END_DATE, 'YYYYMMDDHH24MISS'),
        'N'
   FROM UTISINCI.TB_IMS_DATA@UTISDB I, CENTER_REGION R
  WHERE NOT EXISTS (SELECT 'X'
                      FROM SND_INCIDENT
                     WHERE I.INCIDENT_ID = node_nodeidnumber
                       AND tocenterid = '%s')
                       AND REPORT_DATE > SYSDATE - 720/1440
                       AND R.centerid = '%s'
                       AND LENGTH(LINK_ID) = 10
                       AND LOCATION_DATA_X > 100
                       AND LOCATION_DATA_Y < 100
                       AND INCIDENT_GRADE_CD IN ('A0401','A0402')
                       AND R.regioncd = TO_NUMBER(SUBSTR(LINK_ID, 1, 3)
                   )
-- pToCenterId, pFromCenterId, pToCenterId, pToCenterId);


-- struct
-- {
--     char    szLinkId[MAX_FETCH_ROWS][SIZE_LINK_ID+1];
--     char    szNodeId[MAX_FETCH_ROWS][SIZE_NODE_ID+1];
--     char    szContactOrganizationNameText[MAX_FETCH_ROWS][128+1];
--     int     nDescriptionTypeIncidentCode[MAX_FETCH_ROWS];
--     char    szDescriptionTypeIncidentOther[MAX_FETCH_ROWS][256+1];
--     int     nIncidentVehiclesInvolvedCode[MAX_FETCH_ROWS];
--     char    szIncidentVehiclesInvolvedOther[MAX_FETCH_ROWS][256+1];
--     int     nIncidentStatusCode[MAX_FETCH_ROWS];
--     char    szIncidentStatusOther[MAX_FETCH_ROWS][256+1];
--     int     nUpdateTypeCode[MAX_FETCH_ROWS];
--     char    szUpdateTypeOther[MAX_FETCH_ROWS][256+1];
-- } rec;

-- SEL_SndIncident
SELECT link_linkidnumber, node_nodeidnumber, contactorganizationnametext,
       descriptiontypeincidentcode, descriptiontypeincidentother,
       incidentvehiclesinvolvedcode, incidentvehiclesinvolvedother,
       incidentstatuscode, incidentstatusother,
       updatetypecode, updatetypeother
FROM SND_INCIDENT
WHERE tocenterid = :pToCenterId
  AND logdate > SYSDATE - 720/1440
  AND fromcenterid = :pFromCenterId
  AND sendyn != 'Y'
  AND ROWNUM < 100;

-- UPD_SndIncident
UPDATE SND_INCIDENT
SET sendyn = 'Y'
WHERE tocenterid = :pToCenterId
  AND logdate > SYSDATE - 720/1440
  AND fromcenterid = :pFromCenterId
  AND sendyn != 'Y'
  AND ROWNUM < 100;


-- INS_SndLog
INSERT INTO SND_LOG(logdate, infokind, tocenterid, fromcenterid, datacnt)
VALUES(SYSDATE, :pLog->szInfoKind, :pLog->szToCenterId, :pLog->szFromCenterId, :pLog->DataCnt);

-- INS_CenterSend
INSERT INTO CENTER_SEND@UTISDB(logdate, centerid, infotype, datacnt)
VALUES(SYSDATE, :pLog->szToCenterId, :pInfoType, :pLog->DataCnt);

-- DEL_CheckIncident
DELETE SND_INCIDENT S
WHERE EXISTS (SELECT 'X'
                FROM INCIDENT_CENTER@UTISDB
               WHERE incidentid = S.node_nodeidnumber
                 AND reportdate != TO_DATE(SUBSTR(S.updatetypeother, 1, 14), 'YYYYMMDDHH24MISS'));



-- db_delp.pc

-- DEL_SndIncident
DELETE FROM SND_INCIDENT
WHERE logdate < SYSDATE - 720/1440;

-- DEL_SndLog
DELETE FROM SND_LOG
WHERE logdate < SYSDATE - 2;

-- DEL_RcvTraffic
DELETE FROM RCV_LINK_TRAFFIC
WHERE logdate < SYSDATE - 15/1440;

-- DEL_RcvIncident
DELETE FROM RCV_INCIDENT
WHERE logdate < SYSDATE - 3;

-- DEL_RcvLog
DELETE FROM RCV_LOG
WHERE logdate < SYSDATE - 1;