轨道交通数据库
数据查询，太慢了。恳请援助，20分还不出来一个查询结果。
大家好，恳请大家的帮助。
我编写好的查询结果，查询一下竟然20分钟还不一定出来，有时候能出结果，有时候出不来，显示数据中断。
迫切请大家帮忙。
我是北京的。
如方便上门的话，也可提供一定报酬。我的Q：7478592
电话：65730198
我编写好的查询结果，查询一下竟然20分钟还不一定出来，有时候能出结果，有时候出不来，显示数据中断。
迫切请大家帮忙。
我是北京的。
如方便上门的话，也可提供一定报酬。我的Q：7478592
电话：65730198
13同行回答
发重了，不好意思！收起
浏览759
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_PLAZA
(
PLAZAID INT,
PLAZANO INT,
PLAZANAME VARCHAR(40)
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
--从上下班记录中，获取这个分中心没有封过帐的数据
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_DUTYONOFF
(
PLAZAID INT,
LANE INT,
OPERATOR_ID INT,
DUTYON_TIME TIMESTAMP,
DUTYOFF_TIME TIMESTAMP,
HASDUTYOFF INT,
ROWCOUNT INT,
SHIFT_ID INT,
SHIFT_DATE_A DATE,
SHIFT_DATE_B DATE,
ISDEALED INT DEFAULT 0
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
--记录的明细
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_DETAIL
(
MID VARCHAR(30) not null,
PLAZAID INT,
LANE INT,
OPERATOR_ID INT,
WORK_MODE INT,
PASSED_TYPE INT,
CARD_NET_ID VARCHAR(10),
VEHCLASS INT,
UP_DOWN INT,
ENTRY_EXIT INT,
SHIFT_ID INT,
SHIFT_DATE DATE,
CASH DECIMAL(15,2),
DUE_FARE DECIMAL(15,2),
IC_TRANS_TIME TIMESTAMP,
LANE_CREATED TIMESTAMP,
LINE_OF_DUTY INT,
LOW_VALUE INT,
TIME_OUT INT,
NOT_REACHABLE INT,
NO_ENTRY INT,
UNPACKING_OBU INT,
RE_DEAL INT,
CHECK_TICK INT,
NEWSHIFT_ID INT,
NEWSHIFT_DATE DATE,
CLOSED INT DEFAULT 0,
PROCESS_RESULT INT
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
--预进行封帐的数据临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_BRANSHIFTS
(
PLAZAID INT,
PLAZANO INT,
PLAZANAME VARCHAR(40),
OPERATOR_ID INT,
USER_NUM INT,
USER_NAME VARCHAR(40),
DUTYON_TIME TIMESTAMP,
DUTYOFF_TIME TIMESTAMP,
SHIFT_ID INT,
SHIFT_DATE DATE,
SHIFT_NAME VARCHAR(40),
ROWCOUNT INT,
DBROWCOUNT INT,
NMLROWCOUNT INT,
SHIFT_DATE_MAX DATE,
HASDUTYOFF INT,
ISDEALED INT DEFAULT 0
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_DUTYOFF
(
PLAZAID INT not null,
LANE INT not null,
OPERATOR_ID INT not null,
DUTY_TIME TIMESTAMP not null,
DUTY_NO INT not null,
SHIFT_ID INT not null,
SHIFT_DATE DATE,
ROWCOUNT INT
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
SET SCHEMA ECDBA;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","ECDBA";
CREATE PROCEDURE "ECDBA"."UP_CLOSE_SHIFTS"
(IN "P_ROAD_NO" INTEGER,
IN "P_NEWSHIFT_ID" INTEGER,
IN "P_NEWSHIFTDATE" DATE,
IN "P_OP_ID" INTEGER,
IN "P_ORGAN_DUTY" INTEGER
)
SPECIFIC "ECDBA"."SQL101010163622754"
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
P1:BEGIN ATOMIC
DECLARE V_FOUND INT DEFAULT 0;
DECLARE V_PLAZAID INT;
DECLARE V_OPERATOR_ID INT;
DECLARE V_SHIFT_ID INT;
DECLARE V_SHIFT_DATE DATE;
DECLARE V_SHIFT_DATE_MAX DATE;
DECLARE V_ACTUAL_ROWS INT;
DECLARE V_NML_ROWS INT;
DECLARE V_DUTYON_TIME TIMESTAMP;
DECLARE V_DUTYOFF_TIME TIMESTAMP;
DECLARE V_ROW_COUNT INT;
-- DECLARE V_MID VARCHAR(30);
--从临时的上下班记录表中，取出要进行封帐的数据
DECLARE CUR_DATATODEAL CURSOR FOR
SELECT PLAZAID , SHIFT_DATE , SHIFT_DATE_MAX , OPERATOR_ID , SHIFT_ID , DBROWCOUNT , NMLROWCOUNT , DUTYON_TIME , DUTYOFF_TIME , ROWCOUNT
FROM SESSION.VT_BRANSHIFTS
WHERE ISDEALED = 1;
-- DECLARE CURSOR2 CURSOR WITH HOLD FOR SELECT MID FROM SESSION.VT_DETAIL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_FOUND = 1;
-- DECLARE EXIT HANDLER FOR SQLEXCEPTION;
INSERT INTO SPM_FOR_BRANCH_SHIFTS_SIMPLE (ROAD_NO,NEWSHIFT_ID,NEWSHIFT_DATE,OP_ID,ORGAN_DUTY)
SELECT P_ROAD_NO,P_NEWSHIFT_ID,P_NEWSHIFTDATE,P_OP_ID,P_ORGAN_DUTY
FROM SYSIBM.SYSDUMMY1;
OPEN CUR_DATATODEAL;
SET V_FOUND = 0;
WHILE V_FOUND = 0 DO
FETCH CUR_DATATODEAL INTO V_PLAZAID,V_SHIFT_DATE,V_SHIFT_DATE_MAX,V_OPERATOR_ID,V_SHIFT_ID,V_ACTUAL_ROWS,V_NML_ROWS,V_DUTYON_TIME,V_DUTYOFF_TIME,V_ROW_COUNT;
IF V_FOUND = 0 THEN
--更新原始交易记录的时间戳
UPDATE SESSION.VT_DETAIL
SET NEWSHIFT_ID = P_NEWSHIFT_ID,
NEWSHIFT_DATE = P_NEWSHIFTDATE,
CLOSED = 1
WHERE SHIFT_ID = V_SHIFT_ID
AND PLAZAID = V_PLAZAID
AND LANE_CREATED BETWEEN V_DUTYON_TIME AND V_DUTYOFF_TIME
AND OPERATOR_ID = V_OPERATOR_ID;
--新的交易入库
INSERT INTO SPM_FOR_BRANCH_SHIFTS (ROAD_NO,PLAZAID,SHIFT_DATE,OPERATOR_ID,SHIFT_ID,NEWSHIFT_ID,NEWSHIFT_DATE,OP_ID,DUTYON_TIME,DUTYOFF_TIME,ACTUAL_ROWS,ROW_COUNT,NML_ROWS)
VALUES(P_ROAD_NO,V_PLAZAID,V_SHIFT_DATE,V_OPERATOR_ID,V_SHIFT_ID,P_NEWSHIFT_ID,P_NEWSHIFTDATE,P_OP_ID,V_DUTYON_TIME,V_DUTYOFF_TIME,V_ACTUAL_ROWS,V_ROW_COUNT,V_NML_ROWS);
--更新上班信息
UPDATE SPM_FOR_DUTYON
SET LANEFLAG = 100,
NEWSHIFT_ID = P_NEWSHIFT_ID,
NEWSHIFT_DATE = P_NEWSHIFTDATE
WHERE PLAZAID = V_PLAZAID
AND SHIFT_DATE = V_SHIFT_DATE
AND OPERATOR_ID = V_OPERATOR_ID
AND SHIFT_ID = V_SHIFT_ID;
SET V_FOUND = 0;
END IF;
END WHILE;
CLOSE CUR_DATATODEAL;
--OPEN CURSOR2;
--SET V_FOUND = 0;
--WHILE V_FOUND = 0 DO
-- FETCH CURSOR2 INTO V_MID;
-- IF V_FOUND = 0 THEN
-- UPDATE SPM_FOR_PASSING_N SET
-- NEWSHIFT_ID = P_NEWSHIFT_ID,
-- NEWSHIFT_DATE = P_NEWSHIFTDATE
-- WHERE MID=V_MID;
-- SET V_FOUND = 0;
-- END IF;
--END WHILE;
--CLOSE CURSOR2;
DECLARE GLOBAL TEMPORARY TABLE VT_STATIC
(
MID VARCHAR(30) not null,
PLAZAID INT,
LANE INT,
OPERATOR_ID INT,
WORK_MODE INT,
PASSED_TYPE INT,
CARD_NET_ID VARCHAR(10),
VEHCLASS INT,
UP_DOWN INT,
ENTRY_EXIT INT,
SHIFT_ID INT,
SHIFT_DATE DATE,
CASH DECIMAL(15,2),
DUE_FARE DECIMAL(15,2),
IC_TRANS_TIME TIMESTAMP,
LANE_CREATED TIMESTAMP,
LINE_OF_DUTY INT,
LOW_VALUE INT,
TIME_OUT INT,
NOT_REACHABLE INT,
NO_ENTRY INT,
UNPACKING_OBU INT,
RE_DEAL INT,
CHECK_TICK INT,
NEWSHIFT_ID INT,
NEWSHIFT_DATE DATE,
CLOSED INT DEFAULT 0,
PROCESS_RESULT INT
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
INSERT INTO SESSION.VT_STATIC
SELECT "MID", "PLAZAID", "LANE", "OPERATOR_ID", "WORK_MODE", "PASSED_TYPE",
"CARD_NET_ID", "VEHCLASS", "UP_DOWN", "ENTRY_EXIT", "SHIFT_ID",
"SHIFT_DATE", "CASH", "DUE_FARE", "IC_TRANS_TIME", "LANE_CREATED",
"LINE_OF_DUTY", "LOW_VALUE", "TIME_OUT", "NOT_REACHABLE", "NO_ENTRY",
"UNPACKING_OBU", "RE_DEAL", "CHECK_TICK", P_NEWSHIFT_ID, P_NEWSHIFTDATE,
"CLOSED", "PROCESS_RESULT"
FROM SESSION.VT_DETAIL
WHERE WORK_MODE = 0
AND CLOSED = 1;
--加入数据检查表,核对差数用
INSERT INTO SESSION_VT_DETAIL_TMP_CHECK (SELECT * FROM SESSION.VT_STATIC);
--按新班次统计出口数据,按班次
INSERT INTO RPT_VEHICLE_PER_BRAN_SHIFT
(ROAD_NO,NEWSHIFT_ID,NEWSHIFT_DATE,OPERATOR_ID,PLAZAID,LANE,UP_DOWN,ENTRY_EXIT,CARD_NET_ID,PASSED_TYPE,PROCESS_RESULT,P0_V0,P0_V0_F,P0_V1,P0_V1_F,P0_V2,P0_V2_F,P0_V3,P0_V3_F,P0_V4,P0_V4_F,P0_V5,P0_V5_F,P0_V6,P0_V6_F,P6_V0,P6_V1,P6_V2,P6_V3,P6_V4,P6_V5,P6_V6,CHK_V0,CHK_V1,CHK_V2,CHK_V3,CHK_V4,CHK_V5,CHK_V6)
SELECT P_ROAD_NO,P_NEWSHIFT_ID,P_NEWSHIFTDATE,OPERATOR_ID,PLAZAID,LANE,MIN(UP_DOWN),MIN(ENTRY_EXIT),CARD_NET_ID,MIN(PASSED_TYPE),PROCESS_RESULT,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V0,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V0_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=1 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V1,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=1 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V1_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=2 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V2,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=2 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V2_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=3 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V3,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=3 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V3_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=4 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V4,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=4 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V4_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=5 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V5,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=5 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V5_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=6 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V6,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=6 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V6_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=0 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V0,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=1 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V1,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=2 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V2,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=3 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V3,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=4 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V4,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=5 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V5,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=6 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V6,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=0 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V0,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=1 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V1,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=2 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V2,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=3 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V3,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=4 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V4,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=5 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V5,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=6 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V6
FROM SESSION.VT_STATIC AS A
GROUP BY OPERATOR_ID,PLAZAID,LANE,CARD_NET_ID,PROCESS_RESULT;
--则将所有的已封帐的交易记录存入临时表中
INSERT INTO SPM_FOR_SHIFT_DETAIL
(
MID,
PLAZAID,
LANE,
OPERATOR_ID,
PASSED_TYPE,
CARD_NET_ID,
WORK_MODE,
VEHCLASS,
UP_DOWN,
ENTRY_EXIT,
SHIFT_ID,
SHIFT_DATE,
CASH,
IC_TRANS_TIME,
LANE_CREATED,
LINE_OF_DUTY,
CHECK_TICK,
NEWSHIFT_DATE,
NEWSHIFT_ID
)
SELECT
MID,
PLAZAID,
LANE,
OPERATOR_ID,
PASSED_TYPE,
CARD_NET_ID,
WORK_MODE,
VEHCLASS,
UP_DOWN,
ENTRY_EXIT,
SHIFT_ID,
SHIFT_DATE,
CASH,
IC_TRANS_TIME,
LANE_CREATED,
LINE_OF_DUTY,
CHECK_TICK,
NEWSHIFT_DATE,
NEWSHIFT_ID
FROM SESSION.VT_DETAIL
WHERE CLOSED = 1;
END P1;
有人给看看这个存储过程。有啥问题没有？收起
(
PLAZAID INT,
PLAZANO INT,
PLAZANAME VARCHAR(40)
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
--从上下班记录中，获取这个分中心没有封过帐的数据
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_DUTYONOFF
(
PLAZAID INT,
LANE INT,
OPERATOR_ID INT,
DUTYON_TIME TIMESTAMP,
DUTYOFF_TIME TIMESTAMP,
HASDUTYOFF INT,
ROWCOUNT INT,
SHIFT_ID INT,
SHIFT_DATE_A DATE,
SHIFT_DATE_B DATE,
ISDEALED INT DEFAULT 0
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
--记录的明细
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_DETAIL
(
MID VARCHAR(30) not null,
PLAZAID INT,
LANE INT,
OPERATOR_ID INT,
WORK_MODE INT,
PASSED_TYPE INT,
CARD_NET_ID VARCHAR(10),
VEHCLASS INT,
UP_DOWN INT,
ENTRY_EXIT INT,
SHIFT_ID INT,
SHIFT_DATE DATE,
CASH DECIMAL(15,2),
DUE_FARE DECIMAL(15,2),
IC_TRANS_TIME TIMESTAMP,
LANE_CREATED TIMESTAMP,
LINE_OF_DUTY INT,
LOW_VALUE INT,
TIME_OUT INT,
NOT_REACHABLE INT,
NO_ENTRY INT,
UNPACKING_OBU INT,
RE_DEAL INT,
CHECK_TICK INT,
NEWSHIFT_ID INT,
NEWSHIFT_DATE DATE,
CLOSED INT DEFAULT 0,
PROCESS_RESULT INT
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
--预进行封帐的数据临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_BRANSHIFTS
(
PLAZAID INT,
PLAZANO INT,
PLAZANAME VARCHAR(40),
OPERATOR_ID INT,
USER_NUM INT,
USER_NAME VARCHAR(40),
DUTYON_TIME TIMESTAMP,
DUTYOFF_TIME TIMESTAMP,
SHIFT_ID INT,
SHIFT_DATE DATE,
SHIFT_NAME VARCHAR(40),
ROWCOUNT INT,
DBROWCOUNT INT,
NMLROWCOUNT INT,
SHIFT_DATE_MAX DATE,
HASDUTYOFF INT,
ISDEALED INT DEFAULT 0
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
DECLARE GLOBAL TEMPORARY TABLE SESSION.VT_DUTYOFF
(
PLAZAID INT not null,
LANE INT not null,
OPERATOR_ID INT not null,
DUTY_TIME TIMESTAMP not null,
DUTY_NO INT not null,
SHIFT_ID INT not null,
SHIFT_DATE DATE,
ROWCOUNT INT
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
SET SCHEMA ECDBA;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","ECDBA";
CREATE PROCEDURE "ECDBA"."UP_CLOSE_SHIFTS"
(IN "P_ROAD_NO" INTEGER,
IN "P_NEWSHIFT_ID" INTEGER,
IN "P_NEWSHIFTDATE" DATE,
IN "P_OP_ID" INTEGER,
IN "P_ORGAN_DUTY" INTEGER
)
SPECIFIC "ECDBA"."SQL101010163622754"
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
P1:BEGIN ATOMIC
DECLARE V_FOUND INT DEFAULT 0;
DECLARE V_PLAZAID INT;
DECLARE V_OPERATOR_ID INT;
DECLARE V_SHIFT_ID INT;
DECLARE V_SHIFT_DATE DATE;
DECLARE V_SHIFT_DATE_MAX DATE;
DECLARE V_ACTUAL_ROWS INT;
DECLARE V_NML_ROWS INT;
DECLARE V_DUTYON_TIME TIMESTAMP;
DECLARE V_DUTYOFF_TIME TIMESTAMP;
DECLARE V_ROW_COUNT INT;
-- DECLARE V_MID VARCHAR(30);
--从临时的上下班记录表中，取出要进行封帐的数据
DECLARE CUR_DATATODEAL CURSOR FOR
SELECT PLAZAID , SHIFT_DATE , SHIFT_DATE_MAX , OPERATOR_ID , SHIFT_ID , DBROWCOUNT , NMLROWCOUNT , DUTYON_TIME , DUTYOFF_TIME , ROWCOUNT
FROM SESSION.VT_BRANSHIFTS
WHERE ISDEALED = 1;
-- DECLARE CURSOR2 CURSOR WITH HOLD FOR SELECT MID FROM SESSION.VT_DETAIL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_FOUND = 1;
-- DECLARE EXIT HANDLER FOR SQLEXCEPTION;
INSERT INTO SPM_FOR_BRANCH_SHIFTS_SIMPLE (ROAD_NO,NEWSHIFT_ID,NEWSHIFT_DATE,OP_ID,ORGAN_DUTY)
SELECT P_ROAD_NO,P_NEWSHIFT_ID,P_NEWSHIFTDATE,P_OP_ID,P_ORGAN_DUTY
FROM SYSIBM.SYSDUMMY1;
OPEN CUR_DATATODEAL;
SET V_FOUND = 0;
WHILE V_FOUND = 0 DO
FETCH CUR_DATATODEAL INTO V_PLAZAID,V_SHIFT_DATE,V_SHIFT_DATE_MAX,V_OPERATOR_ID,V_SHIFT_ID,V_ACTUAL_ROWS,V_NML_ROWS,V_DUTYON_TIME,V_DUTYOFF_TIME,V_ROW_COUNT;
IF V_FOUND = 0 THEN
--更新原始交易记录的时间戳
UPDATE SESSION.VT_DETAIL
SET NEWSHIFT_ID = P_NEWSHIFT_ID,
NEWSHIFT_DATE = P_NEWSHIFTDATE,
CLOSED = 1
WHERE SHIFT_ID = V_SHIFT_ID
AND PLAZAID = V_PLAZAID
AND LANE_CREATED BETWEEN V_DUTYON_TIME AND V_DUTYOFF_TIME
AND OPERATOR_ID = V_OPERATOR_ID;
--新的交易入库
INSERT INTO SPM_FOR_BRANCH_SHIFTS (ROAD_NO,PLAZAID,SHIFT_DATE,OPERATOR_ID,SHIFT_ID,NEWSHIFT_ID,NEWSHIFT_DATE,OP_ID,DUTYON_TIME,DUTYOFF_TIME,ACTUAL_ROWS,ROW_COUNT,NML_ROWS)
VALUES(P_ROAD_NO,V_PLAZAID,V_SHIFT_DATE,V_OPERATOR_ID,V_SHIFT_ID,P_NEWSHIFT_ID,P_NEWSHIFTDATE,P_OP_ID,V_DUTYON_TIME,V_DUTYOFF_TIME,V_ACTUAL_ROWS,V_ROW_COUNT,V_NML_ROWS);
--更新上班信息
UPDATE SPM_FOR_DUTYON
SET LANEFLAG = 100,
NEWSHIFT_ID = P_NEWSHIFT_ID,
NEWSHIFT_DATE = P_NEWSHIFTDATE
WHERE PLAZAID = V_PLAZAID
AND SHIFT_DATE = V_SHIFT_DATE
AND OPERATOR_ID = V_OPERATOR_ID
AND SHIFT_ID = V_SHIFT_ID;
SET V_FOUND = 0;
END IF;
END WHILE;
CLOSE CUR_DATATODEAL;
--OPEN CURSOR2;
--SET V_FOUND = 0;
--WHILE V_FOUND = 0 DO
-- FETCH CURSOR2 INTO V_MID;
-- IF V_FOUND = 0 THEN
-- UPDATE SPM_FOR_PASSING_N SET
-- NEWSHIFT_ID = P_NEWSHIFT_ID,
-- NEWSHIFT_DATE = P_NEWSHIFTDATE
-- WHERE MID=V_MID;
-- SET V_FOUND = 0;
-- END IF;
--END WHILE;
--CLOSE CURSOR2;
DECLARE GLOBAL TEMPORARY TABLE VT_STATIC
(
MID VARCHAR(30) not null,
PLAZAID INT,
LANE INT,
OPERATOR_ID INT,
WORK_MODE INT,
PASSED_TYPE INT,
CARD_NET_ID VARCHAR(10),
VEHCLASS INT,
UP_DOWN INT,
ENTRY_EXIT INT,
SHIFT_ID INT,
SHIFT_DATE DATE,
CASH DECIMAL(15,2),
DUE_FARE DECIMAL(15,2),
IC_TRANS_TIME TIMESTAMP,
LANE_CREATED TIMESTAMP,
LINE_OF_DUTY INT,
LOW_VALUE INT,
TIME_OUT INT,
NOT_REACHABLE INT,
NO_ENTRY INT,
UNPACKING_OBU INT,
RE_DEAL INT,
CHECK_TICK INT,
NEWSHIFT_ID INT,
NEWSHIFT_DATE DATE,
CLOSED INT DEFAULT 0,
PROCESS_RESULT INT
)
NOT LOGGED
ON COMMIT PRESERVE ROWS
WITH REPLACE;
INSERT INTO SESSION.VT_STATIC
SELECT "MID", "PLAZAID", "LANE", "OPERATOR_ID", "WORK_MODE", "PASSED_TYPE",
"CARD_NET_ID", "VEHCLASS", "UP_DOWN", "ENTRY_EXIT", "SHIFT_ID",
"SHIFT_DATE", "CASH", "DUE_FARE", "IC_TRANS_TIME", "LANE_CREATED",
"LINE_OF_DUTY", "LOW_VALUE", "TIME_OUT", "NOT_REACHABLE", "NO_ENTRY",
"UNPACKING_OBU", "RE_DEAL", "CHECK_TICK", P_NEWSHIFT_ID, P_NEWSHIFTDATE,
"CLOSED", "PROCESS_RESULT"
FROM SESSION.VT_DETAIL
WHERE WORK_MODE = 0
AND CLOSED = 1;
--加入数据检查表,核对差数用
INSERT INTO SESSION_VT_DETAIL_TMP_CHECK (SELECT * FROM SESSION.VT_STATIC);
--按新班次统计出口数据,按班次
INSERT INTO RPT_VEHICLE_PER_BRAN_SHIFT
(ROAD_NO,NEWSHIFT_ID,NEWSHIFT_DATE,OPERATOR_ID,PLAZAID,LANE,UP_DOWN,ENTRY_EXIT,CARD_NET_ID,PASSED_TYPE,PROCESS_RESULT,P0_V0,P0_V0_F,P0_V1,P0_V1_F,P0_V2,P0_V2_F,P0_V3,P0_V3_F,P0_V4,P0_V4_F,P0_V5,P0_V5_F,P0_V6,P0_V6_F,P6_V0,P6_V1,P6_V2,P6_V3,P6_V4,P6_V5,P6_V6,CHK_V0,CHK_V1,CHK_V2,CHK_V3,CHK_V4,CHK_V5,CHK_V6)
SELECT P_ROAD_NO,P_NEWSHIFT_ID,P_NEWSHIFTDATE,OPERATOR_ID,PLAZAID,LANE,MIN(UP_DOWN),MIN(ENTRY_EXIT),CARD_NET_ID,MIN(PASSED_TYPE),PROCESS_RESULT,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V0,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V0_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=1 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V1,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=1 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V1_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=2 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V2,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=2 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V2_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=3 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V3,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=3 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V3_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=4 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V4,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=4 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V4_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=5 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V5,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=5 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V5_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=6 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V6,
COALESCE((SELECT SUM(CASH) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 0 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=6 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P0_V6_F,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=0 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V0,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=1 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V1,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=2 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V2,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=3 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V3,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=4 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V4,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=5 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V5,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=1 AND VEHCLASS=6 AND OPERATOR_ID = A.OPERATOR_ID AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS P6_V6,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=0 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V0,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=1 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V1,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=2 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V2,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=3 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V3,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=4 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V4,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=5 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V5,
COALESCE((SELECT COUNT(1) FROM SESSION.VT_STATIC WHERE LINE_OF_DUTY=0 AND CHECK_TICK = 1 AND OPERATOR_ID = A.OPERATOR_ID AND VEHCLASS=6 AND WORK_MODE = 0 AND LANE = A.LANE AND PLAZAID = A.PLAZAID AND CARD_NET_ID = A.CARD_NET_ID AND PROCESS_RESULT = A.PROCESS_RESULT ),0) AS CHK_V6
FROM SESSION.VT_STATIC AS A
GROUP BY OPERATOR_ID,PLAZAID,LANE,CARD_NET_ID,PROCESS_RESULT;
--则将所有的已封帐的交易记录存入临时表中
INSERT INTO SPM_FOR_SHIFT_DETAIL
(
MID,
PLAZAID,
LANE,
OPERATOR_ID,
PASSED_TYPE,
CARD_NET_ID,
WORK_MODE,
VEHCLASS,
UP_DOWN,
ENTRY_EXIT,
SHIFT_ID,
SHIFT_DATE,
CASH,
IC_TRANS_TIME,
LANE_CREATED,
LINE_OF_DUTY,
CHECK_TICK,
NEWSHIFT_DATE,
NEWSHIFT_ID
)
SELECT
MID,
PLAZAID,
LANE,
OPERATOR_ID,
PASSED_TYPE,
CARD_NET_ID,
WORK_MODE,
VEHCLASS,
UP_DOWN,
ENTRY_EXIT,
SHIFT_ID,
SHIFT_DATE,
CASH,
IC_TRANS_TIME,
LANE_CREATED,
LINE_OF_DUTY,
CHECK_TICK,
NEWSHIFT_DATE,
NEWSHIFT_ID
FROM SESSION.VT_DETAIL
WHERE CLOSED = 1;
END P1;
有人给看看这个存储过程。有啥问题没有？收起
浏览763
谢谢楼上的朋友，已加群，还没通过。有这面能力的朋友请留下QQ啊！多谢！收起
浏览874
浏览916
这帖子发的。。。SQL优化论坛应该还是有不少案例的。收起
浏览885