車日報ストレージプロセスおよびトリガ(バックアップ)
8629 ワード
カージャーナルストレージプロセス
対応トリガ
CREATE OR REPLACE PROCEDURE AY_GPS_VEHICLE_KILOMETER_SP (
/************************************************************************************
* PROGRAM ID: AOYANG.AY_GPS_VEHICLE_KILOMETER_SP
* PROGRAM NAME:
* PARAMETER:
* VERSION: V1.0
* DESCRIPTION: AOYANG.AY_GPS_VEHICLE_KILOMETER_SP
* REVERSION HISTORY:
* DATE VER NAME DESCRIPTION
* ----------- ------ ------------- ----------------------------------------------
* 2017/04/25 V1.0 YAN XUN JIAN
* ----------- ------ ------------- ----------------------------------------------
*
*************************************************************************************/
ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2
) AUTHID CURRENT_USER
IS
x_error_desc VARCHAR2 (2000) ;
x_conc_status BOOLEAN ;
x_VEHICLE_NO VARCHAR2 (50 BYTE) ;
v_gps_date VARCHAR2 (50 BYTE) ;
v_newVal NUMBER (12) ;
--SELECT CREATECODE_ID,VEHICLE_NO FROM CMS_GPS_VEHICLE
--------------- -------------------
CURSOR Get_GPS_VEHICLE IS
SELECT VEHICLE_NO FROM CMS_GPS_VEHICLE ;
BEGIN
SELECT TO_CHAR ((SYSDATE - 1), 'YYYY-MM-DD') INTO v_gps_date FROM DUAL ;
SELECT CMS_GPS_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL ; --
FOR GPS_VEHICLEList IN Get_GPS_VEHICLE LOOP
IF Get_GPS_VEHICLE % NOTFOUND THEN EXIT ;
END IF ;
x_VEHICLE_NO := GPS_VEHICLEList.VEHICLE_NO ;
INSERT INTO AOYANG.CMS_GPS_VEHICLE_KILOMETER (
VEHICLE_NO,
START_GPS_DATE,
END_GPS_DATE,
GPS_START_KILOMETER,
GPS_END_KILOMETER,
GPS_SUMMARY_KILOMETER,
GPS_DATE,
CREATION_DATE,
CREATION_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_BY
) SELECT DISTINCT
VEHICLE_NO,
START_GPS_DATE,
END_GPS_DATE,
SUM (START_GPS_KILOMETER) GPS_START_KILOMETER,
SUM (END_GPS_KILOMETER) GPS_END_KILOMETER,
SUM (END_GPS_KILOMETER) - SUM (START_GPS_KILOMETER) GPS_SUMMARY_KILOMETER,
TO_CHAR ((SYSDATE - 1), 'YYYY-MM-DD') GPS_DATE,
SYSDATE CREATION_DATE,
'1' CREATION_BY,
SYSDATE LAST_UPDATE_DATE,
'1' LAST_UPDATE_BY
FROM
(
-- PICK_DATE START_GPS_DATE， PICK_DATE END_GPS_DATE
SELECT DISTINCT
(
SELECT
MIN (PICK_DATE)
FROM
CMS_GPS
WHERE
VEHICLE_NO = x_VEHICLE_NO
AND TO_CHAR (PICK_DATE, 'YYYY-MM-DD') = TO_CHAR ((SYSDATE - 1), 'YYYY-MM-DD')
AND SPEED1 <> 0
AND KILOMETER <> 0
) START_GPS_DATE,
PICK_DATE END_GPS_DATE,
VEHICLE_NO,
0 START_GPS_KILOMETER,
KILOMETER END_GPS_KILOMETER
FROM
CMS_GPS
WHERE
VEHICLE_NO = x_VEHICLE_NO
AND TO_CHAR (PICK_DATE, 'YYYY-MM-DD') = TO_CHAR ((SYSDATE - 1), 'YYYY-MM-DD')
AND PICK_DATE = (
SELECT
MAX (PICK_DATE)
FROM
CMS_GPS
WHERE
VEHICLE_NO = x_VEHICLE_NO
AND TO_CHAR (PICK_DATE, 'YYYY-MM-DD') = TO_CHAR ((SYSDATE - 1), 'YYYY-MM-DD')
AND SPEED1 <> 0
AND KILOMETER <> 0
)
AND TO_NUMBER (LONGITUDE) >= 73
AND TO_NUMBER (LONGITUDE) <= 140
AND TO_NUMBER (LATITUDE) >= 3
AND TO_NUMBER (LATITUDE) <= 54
AND SPEED1 > = 0
AND SPEED1 <= 200
AND DIRECTION >= 0
AND DIRECTION <= 360
AND KILOMETER <> 0
UNION ALL
-- PICK_DATE START_GPS_DATE， PICK_DATE END_GPS_DATE
SELECT DISTINCT
PICK_DATE START_GPS_DATE,
(
SELECT
MAX (PICK_DATE)
FROM
CMS_GPS
WHERE
VEHICLE_NO = x_VEHICLE_NO
AND TO_CHAR (PICK_DATE, 'YYYY-MM-DD') = TO_CHAR ((SYSDATE - 1), 'YYYY-MM-DD')
AND SPEED1 <> 0
AND KILOMETER <> 0
) END_GPS_DATE,
VEHICLE_NO,
KILOMETER START_GPS_KILOMETER,
0 END_GPS_KILOMETER
FROM
CMS_GPS
WHERE
VEHICLE_NO = x_VEHICLE_NO
AND TO_CHAR (PICK_DATE, 'YYYY-MM-DD') = TO_CHAR ((SYSDATE - 1), 'YYYY-MM-DD')
AND PICK_DATE = (
SELECT
MIN (PICK_DATE)
FROM
CMS_GPS
WHERE
VEHICLE_NO = x_VEHICLE_NO
AND TO_CHAR (PICK_DATE, 'YYYY-MM-DD') = TO_CHAR ((SYSDATE - 1), 'YYYY-MM-DD')
AND SPEED1 <> 0
AND KILOMETER <> 0
)
AND TO_NUMBER (LONGITUDE) >= 73
AND TO_NUMBER (LONGITUDE) <= 140
AND TO_NUMBER (LATITUDE) >= 3
AND TO_NUMBER (LATITUDE) <= 54
AND SPEED1 <> 0
AND SPEED1 <= 200
AND DIRECTION >= 0
AND DIRECTION <= 360 -- AND KILOMETER<>0
)
GROUP BY
START_GPS_DATE,
END_GPS_DATE,
VEHICLE_NO ;
COMMIT ;
END loop ;
INSERT INTO SYS_USER (
USER_ID,
USERNAME,
PASSWORD,
NAME,
ROLE_ID,
STATUS,
BZ,
SKIN,
NUMBER_,
PHONE,
SMS_STATE,
WEIXIN_STATE,
CLOCKWEIXIN_STATE,
EXCLOCKWEIXIN_STATA
)(
SELECT
STAFF_ID USER_ID,
STAFF_ID USERNAME,
'' PASSWORD,
NAME,
'4bb06f4b69944c5cb9e9af9620e9006f' ROLE_ID,
'0' STATUS,
NAME BZ,
'default' SKIN,
STAFF_ID AS NUMBER_,
TEL PHONE,
0 SMS_STATE,
1 WEIXIN_STATE,
1 CLOCKWEIXIN_STATE,
1 EXCLOCKWEIXIN_STATA
FROM
OA_STAFF
WHERE
STAFF_ID NOT IN (SELECT NUMBER_ FROM SYS_USER)
) ;
COMMIT ;
END AY_GPS_VEHICLE_KILOMETER_SP ;対応トリガ
--CMS_EXPENSE_LIST_NUMBER_ID_TRG
CREATE OR REPLACE TRIGGER "ITSM20"."CMS_EXPENSE_LIST_NUMBER_ID_TRG" BEFORE INSERT ON cms_expense_list_number
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.ID IS NULL THEN
SELECT cms_expense_list_number_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT NVL(max(ID),0) INTO v_newVal FROM cms_expense_list_number;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT cms_expense_list_number_ID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
--used to emulate LAST_INSERT_ID()
--mysql_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.ID := v_newVal;
END IF;
END;
ALTER TRIGGER "ITSM20"."CMS_EXPENSE_LIST_NUMBER_ID_TRG" ENABLE
--CMS_GPS_MONITOR_ID_TRG
CREATE OR REPLACE TRIGGER "ITSM20"."CMS_GPS_MONITOR_ID_TRG" BEFORE INSERT ON cms_gps_monitor
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.ID IS NULL THEN
SELECT cms_gps_monitor_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT NVL(max(ID),0) INTO v_newVal FROM cms_gps_monitor;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT cms_gps_monitor_ID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
--used to emulate LAST_INSERT_ID()
--mysql_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.ID := v_newVal;
END IF;
END;
ALTER TRIGGER "ITSM20"."CMS_GPS_MONITOR_ID_TRG" ENABLE
--CMS_TASK_LIST_NUMBER_ID_TRG
CREATE OR REPLACE TRIGGER "ITSM20"."CMS_TASK_LIST_NUMBER_ID_TRG" BEFORE INSERT ON cms_task_list_number
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.ID IS NULL THEN
SELECT cms_task_list_number_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT NVL(max(ID),0) INTO v_newVal FROM cms_task_list_number;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT cms_task_list_number_ID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
--used to emulate LAST_INSERT_ID()
--mysql_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.ID := v_newVal;
END IF;
END;
ALTER TRIGGER "ITSM20"."CMS_TASK_LIST_NUMBER_ID_TRG" ENABLE