- 浏览: 229753 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
netwelfare:
文章的格式有点乱啊,看起来很费事啊。推荐看这个系列的json教 ...
java 操作JSON -
yangqingandjay:
ijoi刚好一个月[color=orange][align=c ...
spring 定时器配置 -
Jxdwuao:
不错, 很好用
spring 定时器配置 -
1870702720:
3楼说的可以,之前我一直以为想配置在一个定时器中,可是实现不了 ...
spring 定时器配置 -
taiwei.peng:
你可以配置两个定时器,这是我个人的想法,仅供参考。
spring 定时器配置
CREATE OR REPLACE PROCEDURE STP_INTERNATIONAL(P_DT DATE DEFAULT SYSDATE) IS
RECE_DT DATE; --收件端持久化日期
TRANSFER_DT DATE; --中转持久化日期
DELIVERY_DT DATE; --派件端持久化日期
CONVEYAN_DT DATE; --运力持久化日期
SEND_DT DATE; --寄件日期(国际件跨度比较大,所以用该变量把运单基础信息表的数据扩大)
MAX_RECE_DT DATE;
MAX_TRANSFER_DT DATE;
MAX_DELIVERY_DT DATE;
MAX_CONVEYAN_DT DATE;
begin
RECE_DT := TRUNC(P_DT) - 5; --收件端持久化5天前的数据
TRANSFER_DT := TRUNC(P_DT) - 3; --中转持久化3天前的数据
DELIVERY_DT := TRUNC(P_DT) - 5; --派件端持久化5天前的数据
CONVEYAN_DT := TRUNC(P_DT) - 4; --运力持久化4天前的数据
MAX_RECE_DT := RECE_DT + 1;
MAX_TRANSFER_DT := TRANSFER_DT + 1;
MAX_DELIVERY_DT := DELIVERY_DT + 1;
MAX_CONVEYAN_DT := CONVEYAN_DT + 1;
SEND_DT := TRUNC(P_DT) - 20; --寄件日期国际件跨度比较大
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
NULL,
'START国际件环节处理量日期' ||
TO_CHAR(TRUNC(P_DT), 'yyyy-mm-dd'));
COMMIT;
--删除过渡表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TT_PROCESS_RESULT_N';
----------------------------------------------仓库发件晚点-------------------------------------------------
--收件端监控-实发票数明细
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT A.REPORT_DT, A.ZONE_CODE, COUNT(1) WAYBILL_CNT, 1, 1
FROM (SELECT /*+ USE_HASH(B,P) */
TRUNC(P.PLAN_BEGIN_TM) REPORT_DT, P.ZONE_CODE
FROM TT_WAYBILL_BASIS B, TT_PKP_DEPOT_DETAIL P
WHERE B.WAYBILL_NO = P.BILL_NO
AND B.IS_NATIONAL = 'Y'
AND B.SEND_TIME >= SEND_DT
--实发标志
AND P.ACTUAL_SEND_FLAG = 1
AND P.PLAN_BEGIN_TM >= RECE_DT
AND P.PLAN_BEGIN_TM < MAX_RECE_DT) A
GROUP BY A.ZONE_CODE, A.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
'INSERT INTO TT_PROCESS_RESULT_N 1');
COMMIT;
--------------------------------------------中转发件晚点------------------------------------------------
--中转班次监控-实发票数明细
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT B.REPORT_DT, B.ZONE_CODE, COUNT(1) WAYBILL_CNT, 2, 1
FROM (SELECT /* +USE_HASH(B,T) */
TRUNC(T.REPORT_DT) REPORT_DT, T.ZONE_CODE
FROM TT_WAYBILL_BASIS B, TT_TRANSFER_DEPART_DETAIL T
WHERE B.WAYBILL_NO = T.WAYBILL_NO
AND B.IS_NATIONAL = 'Y'
AND B.SEND_TIME >= SEND_DT
AND T.REPORT_DT >= TRANSFER_DT
AND T.REPORT_DT < MAX_TRANSFER_DT) B
GROUP BY B.ZONE_CODE, B.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
'INSERT INTO TT_PROCESS_RESULT_N 2');
COMMIT;
----------------------------------------------仓库出仓晚点-----------------------------------------------
--派件端监控-应发票数明细
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT C.REPORT_DT, C.ZONE_CODE, COUNT(1) WAYBILL_CNT, 3, 1
FROM (SELECT /* +USE_HASH(B,D) */
TRUNC(D.BATCH_BEGIN_DATE) REPORT_DT, D.ZONE_CODE
FROM TT_WAYBILL_BASIS B, TT_DELIVERY_BILL_DETAIL D
WHERE B.WAYBILL_NO = D.WAYBILLNO
AND B.IS_NATIONAL = 'Y'
AND B.SEND_TIME >= SEND_DT
--应发标志
AND D.DIM_PLAN_SEND_FLAG LIKE '1-%-%'
AND D.BATCH_BEGIN_DATE >= DELIVERY_DT
AND D.BATCH_BEGIN_DATE < MAX_DELIVERY_DT) C
GROUP BY C.ZONE_CODE, C.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
'INSERT INTO TT_PROCESS_RESULT_N 3');
COMMIT;
----------------------------------------------延误派送---------------------------------------------------
--派件端监控-出仓量明细
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT D.REPORT_DT, D.ZONE_CODE, COUNT(1) WAYBILL_CNT, 4, 1
FROM (SELECT /*+ USE_HASH(B,DE) */
TRUNC(DE.QUERY_DATE) REPORT_DT, DE.ZONE_CODE
FROM TT_WAYBILL_BASIS B, TT_DELIVERY_DETAIL DE
WHERE B.WAYBILL_NO = DE.WAY_BILL_NO
AND B.IS_NATIONAL = 'Y'
AND B.SEND_TIME >= SEND_DT
AND DE.QUERY_DATE >= DELIVERY_DT
AND DE.QUERY_DATE < MAX_DELIVERY_DT) D
GROUP BY D.ZONE_CODE, D.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
'INSERT INTO TT_PROCESS_RESULT_N 4');
COMMIT;
---------------------------------------------------临时表包含运力key,运单号,日期-------------------------
--删除临时表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_UNLOAD_DETAIL_NATIONAL';
--先插入卸车数据
INSERT INTO TEMP_UNLOAD_DETAIL_NATIONAL
(PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL_UNLOAD,B) FULL(TT_CONVEYANCE_DETAIL_UNLOAD)*/
PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
FROM TT_CONVEYANCE_DETAIL_UNLOAD
WHERE PLAN_DATE >= CONVEYAN_DT
AND PLAN_DATE < MAX_CONVEYAN_DT
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = BILL_NO);
COMMIT;
--再插入漏卸数据
INSERT INTO TEMP_UNLOAD_DETAIL_NATIONAL
(PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL,B) FULL(TT_CONVEYANCE_DETAIL)*/
PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
FROM TT_CONVEYANCE_DETAIL
WHERE DATA_TYPE = 4
AND PLAN_DATE >= CONVEYAN_DT
AND PLAN_DATE < MAX_CONVEYAN_DT
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = BILL_NO);
COMMIT;
---------------------------------------------车辆到达晚点-------------------------------------------------
--运力监控中运力类型为干支线的实到票数明细数据 (无车辆管理区,则记在始发方网点) 实到(卸车+漏卸)
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT H.REPORT_DT, H.ZONE_CODE, COUNT(1) WAYBILL_CNT, 5, 1
FROM (SELECT /*+ USE_HASH(T,S)*/
TRUNC(T.PLAN_DATE) REPORT_DT,
NVL(S.VEHICLE_AREA, S.SRC_ZONE_CODE) ZONE_CODE
FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = S.PLAN_DATE
--干支线
AND S.CONVEYANCE_TYPE = 4
AND S.PLAN_DATE >= CONVEYAN_DT
AND S.PLAN_DATE < MAX_CONVEYAN_DT) H
GROUP BY H.ZONE_CODE, H.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
' INSERT INTO TT_PROCESS_RESULT_N 5 ');
COMMIT;
---------------------------------------------运力管理处处理量-------------------------------------------
--航管处理量=提货票数+实发票数+应发票数
--应发票数(中转应发)
--删除运力中转应发临时表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_DEPART_INTERNATIONAL';
INSERT INTO TEMP_DEPART_INTERNATIONAL
(PLAN_DATE, ZONE_CODE, WAYBILL_NO, SN)
SELECT /*+ FULL(TD) FULL(TA) USE_HASH(TD,TA) */
TA.PLAN_DATE_TYPE_DATE,
TD.ZONE_CODE,
TD.WAYBILL_NO,
ROW_NUMBER() OVER(PARTITION BY TD.WAYBILL_NO ORDER BY TD.REPORT_DT) SN
FROM TT_TRANSFER_PLAN_DEPART_DETAIL TD, TT_CONVEYANCE_ATTATCH TA
WHERE TA.PLAN_DATE_TYPE = 2
AND TA.PLAN_DATE_TYPE_DATE >= CONVEYAN_DT
AND TA.PLAN_DATE_TYPE_DATE < MAX_CONVEYAN_DT
AND TD.REPORT_DT >= TRANSFER_DT
AND TD.REPORT_DT < MAX_TRANSFER_DT
AND TD.ZONE_CODE = TA.SRC_ZONE_CODE
AND TD.CONVEYANCE_NAME = TA.CONVEYANCE_NAME
AND TD.LINE_CODE = TA.LINE_CODE
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = TD.WAYBILL_NO);
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_LOAD_INTERNATIONAL';
--实发票数(实发就是装车)
--先插入运力明细数据
INSERT INTO TEMP_LOAD_INTERNATIONAL
(PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL,B) FULL(TT_CONVEYANCE_DETAIL)*/
PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
FROM TT_CONVEYANCE_DETAIL
WHERE DATA_TYPE = 1
AND PLAN_DATE >= CONVEYAN_DT
AND PLAN_DATE < MAX_CONVEYAN_DT
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = BILL_NO);
COMMIT;
--再插入装车明细数据
INSERT INTO TEMP_LOAD_INTERNATIONAL
(PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL_LOAD,B) FULL(TT_CONVEYANCE_DETAIL_LOAD)*/
PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
FROM TT_CONVEYANCE_DETAIL_LOAD
WHERE PLAN_DATE >= CONVEYAN_DT
AND PLAN_DATE < MAX_CONVEYAN_DT
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = BILL_NO);
COMMIT;
--先删除临时表的数据再向临时表中插入数据
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_INTERNATIONAL';
--实发票数(实发就是装车)
--实发票数与应发票数去重
INSERT INTO TEMP_CONVEYANCE_INTERNATIONAL
(PLAN_DATE, WAYBILL_NO, ZONE_CODE)
SELECT NVL(AAA.PLAN_DATE, BBB.PLAN_DATE) PLAN_DATE,
NVL(AAA.WAYBILL_NO, BBB.WAYBILL_NO) WAYBILL_NO,
NVL(AAA.SRC_ZONE_CODE, BBB.ZONE_CODE) ZONE_CODE
FROM (SELECT /*+ USE_HASH(T,A)*/
A.PLAN_DATE, A.SRC_ZONE_CODE, T.WAYBILL_NO
FROM TEMP_LOAD_INTERNATIONAL T, TT_CONVEYANCE_SHOW A
WHERE T.CONVEYANCE_KEY = A.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = A.PLAN_DATE
AND A.PLAN_DATE >= CONVEYAN_DT
AND A.PLAN_DATE < MAX_CONVEYAN_DT) AAA
FULL JOIN (SELECT PLAN_DATE, ZONE_CODE, WAYBILL_NO
FROM TEMP_DEPART_INTERNATIONAL
WHERE SN = 1) BBB
ON AAA.WAYBILL_NO = BBB.WAYBILL_NO;
COMMIT;
--先删除票数临时表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_CNT_NATIONAL';
--提货票数(卸车票数+漏卸票数)
INSERT INTO TEMP_CONVEYANCE_CNT_NATIONAL
(REPORT_DT, ZONE_CODE, WAYBILL_CNT)
SELECT F.REPORT_DT, F.ZONE_CODE, COUNT(1) WAYBILL_CNT
FROM (SELECT /*+USE_HASH(T,S)*/
TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = S.PLAN_DATE
AND S.PLAN_DATE >= CONVEYAN_DT
AND S.PLAN_DATE < MAX_CONVEYAN_DT) F
GROUP BY F.ZONE_CODE, F.REPORT_DT;
COMMIT;
--实发+应发
INSERT INTO TEMP_CONVEYANCE_CNT_NATIONAL
(REPORT_DT, ZONE_CODE, WAYBILL_CNT)
SELECT TRUNC(F.PLAN_DATE) PLAN_DATE, F.ZONE_CODE, COUNT(1) WAYBILL_CNT
FROM TEMP_CONVEYANCE_INTERNATIONAL F, TT_WAYBILL_BASIS B
WHERE B.WAYBILL_NO = F.WAYBILL_NO
AND B.IS_NATIONAL = ' Y '
AND B.SEND_TIME >= SEND_DT
GROUP BY F.PLAN_DATE, F.ZONE_CODE;
COMMIT;
--插入数据
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT H.REPORT_DT, H.ZONE_CODE, SUM(H.WAYBILL_CNT), 6, 1
FROM TEMP_CONVEYANCE_CNT_NATIONAL H
GROUP BY H.REPORT_DT, H.ZONE_CODE;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
' INSERT INTO TT_PROCESS_RESULT_N 6 ');
COMMIT;
--删除临时表的数据
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_DEPART_INTERNATIONAL';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_INTERNATIONAL';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_CNT_NATIONAL';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_LOAD_INTERNATIONAL';
----------------------------------------关务处理量--------------------------------------------------------
--所有网点运力监控中为跨境运输运力的实到票数
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT G.REPORT_DT, G.ZONE_CODE, COUNT(1) WAYBILL_CNT, 7, 1
FROM (SELECT /*+USE_HASH(T,S)*/
TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = S.PLAN_DATE
--跨境标志
AND S.CROSS_BORDER = 1
AND S.PLAN_DATE >= CONVEYAN_DT
AND S.PLAN_DATE < MAX_CONVEYAN_DT) G
GROUP BY G.ZONE_CODE, G.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
' INSERT INTO TT_PROCESS_RESULT_N 7 ');
COMMIT;
---------------------------------------运输处理量--------------------------------------------------------
--运力监控实到票数(卸车+漏卸)
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT E.REPORT_DT, E.ZONE_CODE, COUNT(1) WAYBILL_CNT, 9, 1
FROM (SELECT /*+USE_HASH(T,S)*/
TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = S.PLAN_DATE
AND S.PLAN_DATE >= CONVEYAN_DT
AND S.PLAN_DATE < MAX_CONVEYAN_DT) E
GROUP BY E.ZONE_CODE, E.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
' INSERT INTO TT_PROCESS_RESULT_N 9 ');
COMMIT;
--删除存放运力key,运单号,日期的临时表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_UNLOAD_DETAIL_NATIONAL';
--把过渡表中的结果插入到结果表
INSERT INTO TT_PROCESS_RESULT
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE
FROM TT_PROCESS_RESULT_N;
COMMIT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL ',
SYSDATE,
SYSDATE,
NULL,
NULL,
' END国际件环节处理量日期 ' ||
TO_CHAR(TRUNC(P_DT), ' yyyy - mm - dd '));
EXCEPTION
WHEN OTHERS THEN
PKG_SYS_LOG.ERROR_LOG(P_PACKAGE_NAME => '',
P_PROC_NAME => ' STP_INTERNATIONAL ',
P_EXCEP_DT => SYSDATE,
P_EXCEP_CODE => SQLCODE,
P_EXCEP_DESC => SQLERRM,
P_EXCEP_REMK => '',
P_LINE_NO => NULL);
end STP_INTERNATIONAL;
RECE_DT DATE; --收件端持久化日期
TRANSFER_DT DATE; --中转持久化日期
DELIVERY_DT DATE; --派件端持久化日期
CONVEYAN_DT DATE; --运力持久化日期
SEND_DT DATE; --寄件日期(国际件跨度比较大,所以用该变量把运单基础信息表的数据扩大)
MAX_RECE_DT DATE;
MAX_TRANSFER_DT DATE;
MAX_DELIVERY_DT DATE;
MAX_CONVEYAN_DT DATE;
begin
RECE_DT := TRUNC(P_DT) - 5; --收件端持久化5天前的数据
TRANSFER_DT := TRUNC(P_DT) - 3; --中转持久化3天前的数据
DELIVERY_DT := TRUNC(P_DT) - 5; --派件端持久化5天前的数据
CONVEYAN_DT := TRUNC(P_DT) - 4; --运力持久化4天前的数据
MAX_RECE_DT := RECE_DT + 1;
MAX_TRANSFER_DT := TRANSFER_DT + 1;
MAX_DELIVERY_DT := DELIVERY_DT + 1;
MAX_CONVEYAN_DT := CONVEYAN_DT + 1;
SEND_DT := TRUNC(P_DT) - 20; --寄件日期国际件跨度比较大
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
NULL,
'START国际件环节处理量日期' ||
TO_CHAR(TRUNC(P_DT), 'yyyy-mm-dd'));
COMMIT;
--删除过渡表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TT_PROCESS_RESULT_N';
----------------------------------------------仓库发件晚点-------------------------------------------------
--收件端监控-实发票数明细
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT A.REPORT_DT, A.ZONE_CODE, COUNT(1) WAYBILL_CNT, 1, 1
FROM (SELECT /*+ USE_HASH(B,P) */
TRUNC(P.PLAN_BEGIN_TM) REPORT_DT, P.ZONE_CODE
FROM TT_WAYBILL_BASIS B, TT_PKP_DEPOT_DETAIL P
WHERE B.WAYBILL_NO = P.BILL_NO
AND B.IS_NATIONAL = 'Y'
AND B.SEND_TIME >= SEND_DT
--实发标志
AND P.ACTUAL_SEND_FLAG = 1
AND P.PLAN_BEGIN_TM >= RECE_DT
AND P.PLAN_BEGIN_TM < MAX_RECE_DT) A
GROUP BY A.ZONE_CODE, A.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
'INSERT INTO TT_PROCESS_RESULT_N 1');
COMMIT;
--------------------------------------------中转发件晚点------------------------------------------------
--中转班次监控-实发票数明细
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT B.REPORT_DT, B.ZONE_CODE, COUNT(1) WAYBILL_CNT, 2, 1
FROM (SELECT /* +USE_HASH(B,T) */
TRUNC(T.REPORT_DT) REPORT_DT, T.ZONE_CODE
FROM TT_WAYBILL_BASIS B, TT_TRANSFER_DEPART_DETAIL T
WHERE B.WAYBILL_NO = T.WAYBILL_NO
AND B.IS_NATIONAL = 'Y'
AND B.SEND_TIME >= SEND_DT
AND T.REPORT_DT >= TRANSFER_DT
AND T.REPORT_DT < MAX_TRANSFER_DT) B
GROUP BY B.ZONE_CODE, B.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
'INSERT INTO TT_PROCESS_RESULT_N 2');
COMMIT;
----------------------------------------------仓库出仓晚点-----------------------------------------------
--派件端监控-应发票数明细
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT C.REPORT_DT, C.ZONE_CODE, COUNT(1) WAYBILL_CNT, 3, 1
FROM (SELECT /* +USE_HASH(B,D) */
TRUNC(D.BATCH_BEGIN_DATE) REPORT_DT, D.ZONE_CODE
FROM TT_WAYBILL_BASIS B, TT_DELIVERY_BILL_DETAIL D
WHERE B.WAYBILL_NO = D.WAYBILLNO
AND B.IS_NATIONAL = 'Y'
AND B.SEND_TIME >= SEND_DT
--应发标志
AND D.DIM_PLAN_SEND_FLAG LIKE '1-%-%'
AND D.BATCH_BEGIN_DATE >= DELIVERY_DT
AND D.BATCH_BEGIN_DATE < MAX_DELIVERY_DT) C
GROUP BY C.ZONE_CODE, C.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
'INSERT INTO TT_PROCESS_RESULT_N 3');
COMMIT;
----------------------------------------------延误派送---------------------------------------------------
--派件端监控-出仓量明细
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT D.REPORT_DT, D.ZONE_CODE, COUNT(1) WAYBILL_CNT, 4, 1
FROM (SELECT /*+ USE_HASH(B,DE) */
TRUNC(DE.QUERY_DATE) REPORT_DT, DE.ZONE_CODE
FROM TT_WAYBILL_BASIS B, TT_DELIVERY_DETAIL DE
WHERE B.WAYBILL_NO = DE.WAY_BILL_NO
AND B.IS_NATIONAL = 'Y'
AND B.SEND_TIME >= SEND_DT
AND DE.QUERY_DATE >= DELIVERY_DT
AND DE.QUERY_DATE < MAX_DELIVERY_DT) D
GROUP BY D.ZONE_CODE, D.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
'INSERT INTO TT_PROCESS_RESULT_N 4');
COMMIT;
---------------------------------------------------临时表包含运力key,运单号,日期-------------------------
--删除临时表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_UNLOAD_DETAIL_NATIONAL';
--先插入卸车数据
INSERT INTO TEMP_UNLOAD_DETAIL_NATIONAL
(PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL_UNLOAD,B) FULL(TT_CONVEYANCE_DETAIL_UNLOAD)*/
PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
FROM TT_CONVEYANCE_DETAIL_UNLOAD
WHERE PLAN_DATE >= CONVEYAN_DT
AND PLAN_DATE < MAX_CONVEYAN_DT
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = BILL_NO);
COMMIT;
--再插入漏卸数据
INSERT INTO TEMP_UNLOAD_DETAIL_NATIONAL
(PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL,B) FULL(TT_CONVEYANCE_DETAIL)*/
PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
FROM TT_CONVEYANCE_DETAIL
WHERE DATA_TYPE = 4
AND PLAN_DATE >= CONVEYAN_DT
AND PLAN_DATE < MAX_CONVEYAN_DT
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = BILL_NO);
COMMIT;
---------------------------------------------车辆到达晚点-------------------------------------------------
--运力监控中运力类型为干支线的实到票数明细数据 (无车辆管理区,则记在始发方网点) 实到(卸车+漏卸)
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT H.REPORT_DT, H.ZONE_CODE, COUNT(1) WAYBILL_CNT, 5, 1
FROM (SELECT /*+ USE_HASH(T,S)*/
TRUNC(T.PLAN_DATE) REPORT_DT,
NVL(S.VEHICLE_AREA, S.SRC_ZONE_CODE) ZONE_CODE
FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = S.PLAN_DATE
--干支线
AND S.CONVEYANCE_TYPE = 4
AND S.PLAN_DATE >= CONVEYAN_DT
AND S.PLAN_DATE < MAX_CONVEYAN_DT) H
GROUP BY H.ZONE_CODE, H.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
' INSERT INTO TT_PROCESS_RESULT_N 5 ');
COMMIT;
---------------------------------------------运力管理处处理量-------------------------------------------
--航管处理量=提货票数+实发票数+应发票数
--应发票数(中转应发)
--删除运力中转应发临时表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_DEPART_INTERNATIONAL';
INSERT INTO TEMP_DEPART_INTERNATIONAL
(PLAN_DATE, ZONE_CODE, WAYBILL_NO, SN)
SELECT /*+ FULL(TD) FULL(TA) USE_HASH(TD,TA) */
TA.PLAN_DATE_TYPE_DATE,
TD.ZONE_CODE,
TD.WAYBILL_NO,
ROW_NUMBER() OVER(PARTITION BY TD.WAYBILL_NO ORDER BY TD.REPORT_DT) SN
FROM TT_TRANSFER_PLAN_DEPART_DETAIL TD, TT_CONVEYANCE_ATTATCH TA
WHERE TA.PLAN_DATE_TYPE = 2
AND TA.PLAN_DATE_TYPE_DATE >= CONVEYAN_DT
AND TA.PLAN_DATE_TYPE_DATE < MAX_CONVEYAN_DT
AND TD.REPORT_DT >= TRANSFER_DT
AND TD.REPORT_DT < MAX_TRANSFER_DT
AND TD.ZONE_CODE = TA.SRC_ZONE_CODE
AND TD.CONVEYANCE_NAME = TA.CONVEYANCE_NAME
AND TD.LINE_CODE = TA.LINE_CODE
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = TD.WAYBILL_NO);
COMMIT;
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_LOAD_INTERNATIONAL';
--实发票数(实发就是装车)
--先插入运力明细数据
INSERT INTO TEMP_LOAD_INTERNATIONAL
(PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL,B) FULL(TT_CONVEYANCE_DETAIL)*/
PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
FROM TT_CONVEYANCE_DETAIL
WHERE DATA_TYPE = 1
AND PLAN_DATE >= CONVEYAN_DT
AND PLAN_DATE < MAX_CONVEYAN_DT
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = BILL_NO);
COMMIT;
--再插入装车明细数据
INSERT INTO TEMP_LOAD_INTERNATIONAL
(PLAN_DATE, CONVEYANCE_KEY, WAYBILL_NO)
SELECT /*+ USE_HASH(TT_CONVEYANCE_DETAIL_LOAD,B) FULL(TT_CONVEYANCE_DETAIL_LOAD)*/
PLAN_DATE, SRC_CONVEYANCE_KEY, BILL_NO
FROM TT_CONVEYANCE_DETAIL_LOAD
WHERE PLAN_DATE >= CONVEYAN_DT
AND PLAN_DATE < MAX_CONVEYAN_DT
AND EXISTS (SELECT 1
FROM TT_WAYBILL_BASIS B
WHERE B.SEND_TIME >= SEND_DT
AND B.IS_NATIONAL = 'Y'
AND B.WAYBILL_NO = BILL_NO);
COMMIT;
--先删除临时表的数据再向临时表中插入数据
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_INTERNATIONAL';
--实发票数(实发就是装车)
--实发票数与应发票数去重
INSERT INTO TEMP_CONVEYANCE_INTERNATIONAL
(PLAN_DATE, WAYBILL_NO, ZONE_CODE)
SELECT NVL(AAA.PLAN_DATE, BBB.PLAN_DATE) PLAN_DATE,
NVL(AAA.WAYBILL_NO, BBB.WAYBILL_NO) WAYBILL_NO,
NVL(AAA.SRC_ZONE_CODE, BBB.ZONE_CODE) ZONE_CODE
FROM (SELECT /*+ USE_HASH(T,A)*/
A.PLAN_DATE, A.SRC_ZONE_CODE, T.WAYBILL_NO
FROM TEMP_LOAD_INTERNATIONAL T, TT_CONVEYANCE_SHOW A
WHERE T.CONVEYANCE_KEY = A.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = A.PLAN_DATE
AND A.PLAN_DATE >= CONVEYAN_DT
AND A.PLAN_DATE < MAX_CONVEYAN_DT) AAA
FULL JOIN (SELECT PLAN_DATE, ZONE_CODE, WAYBILL_NO
FROM TEMP_DEPART_INTERNATIONAL
WHERE SN = 1) BBB
ON AAA.WAYBILL_NO = BBB.WAYBILL_NO;
COMMIT;
--先删除票数临时表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_CNT_NATIONAL';
--提货票数(卸车票数+漏卸票数)
INSERT INTO TEMP_CONVEYANCE_CNT_NATIONAL
(REPORT_DT, ZONE_CODE, WAYBILL_CNT)
SELECT F.REPORT_DT, F.ZONE_CODE, COUNT(1) WAYBILL_CNT
FROM (SELECT /*+USE_HASH(T,S)*/
TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = S.PLAN_DATE
AND S.PLAN_DATE >= CONVEYAN_DT
AND S.PLAN_DATE < MAX_CONVEYAN_DT) F
GROUP BY F.ZONE_CODE, F.REPORT_DT;
COMMIT;
--实发+应发
INSERT INTO TEMP_CONVEYANCE_CNT_NATIONAL
(REPORT_DT, ZONE_CODE, WAYBILL_CNT)
SELECT TRUNC(F.PLAN_DATE) PLAN_DATE, F.ZONE_CODE, COUNT(1) WAYBILL_CNT
FROM TEMP_CONVEYANCE_INTERNATIONAL F, TT_WAYBILL_BASIS B
WHERE B.WAYBILL_NO = F.WAYBILL_NO
AND B.IS_NATIONAL = ' Y '
AND B.SEND_TIME >= SEND_DT
GROUP BY F.PLAN_DATE, F.ZONE_CODE;
COMMIT;
--插入数据
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT H.REPORT_DT, H.ZONE_CODE, SUM(H.WAYBILL_CNT), 6, 1
FROM TEMP_CONVEYANCE_CNT_NATIONAL H
GROUP BY H.REPORT_DT, H.ZONE_CODE;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
' INSERT INTO TT_PROCESS_RESULT_N 6 ');
COMMIT;
--删除临时表的数据
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_DEPART_INTERNATIONAL';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_INTERNATIONAL';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVEYANCE_CNT_NATIONAL';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_LOAD_INTERNATIONAL';
----------------------------------------关务处理量--------------------------------------------------------
--所有网点运力监控中为跨境运输运力的实到票数
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT G.REPORT_DT, G.ZONE_CODE, COUNT(1) WAYBILL_CNT, 7, 1
FROM (SELECT /*+USE_HASH(T,S)*/
TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = S.PLAN_DATE
--跨境标志
AND S.CROSS_BORDER = 1
AND S.PLAN_DATE >= CONVEYAN_DT
AND S.PLAN_DATE < MAX_CONVEYAN_DT) G
GROUP BY G.ZONE_CODE, G.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
' INSERT INTO TT_PROCESS_RESULT_N 7 ');
COMMIT;
---------------------------------------运输处理量--------------------------------------------------------
--运力监控实到票数(卸车+漏卸)
INSERT INTO TT_PROCESS_RESULT_N
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT E.REPORT_DT, E.ZONE_CODE, COUNT(1) WAYBILL_CNT, 9, 1
FROM (SELECT /*+USE_HASH(T,S)*/
TRUNC(T.PLAN_DATE) REPORT_DT, S.SRC_ZONE_CODE ZONE_CODE
FROM TEMP_UNLOAD_DETAIL_NATIONAL T, TT_CONVEYANCE_SHOW S
WHERE T.CONVEYANCE_KEY = S.SRC_CONVEYANCE_KEY
AND T.PLAN_DATE = S.PLAN_DATE
AND S.PLAN_DATE >= CONVEYAN_DT
AND S.PLAN_DATE < MAX_CONVEYAN_DT) E
GROUP BY E.ZONE_CODE, E.REPORT_DT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL',
SYSDATE,
SYSDATE,
NULL,
SQL%ROWCOUNT,
' INSERT INTO TT_PROCESS_RESULT_N 9 ');
COMMIT;
--删除存放运力key,运单号,日期的临时表
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_UNLOAD_DETAIL_NATIONAL';
--把过渡表中的结果插入到结果表
INSERT INTO TT_PROCESS_RESULT
(REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE)
SELECT REPORT_DT, ZONE_CODE, WAYBILL_CNT, DATA_TYPE, RESULT_TYPE
FROM TT_PROCESS_RESULT_N;
COMMIT;
PKG_SYS_LOG.EXECUTE_LOG(NULL,
'STP_INTERNATIONAL ',
SYSDATE,
SYSDATE,
NULL,
NULL,
' END国际件环节处理量日期 ' ||
TO_CHAR(TRUNC(P_DT), ' yyyy - mm - dd '));
EXCEPTION
WHEN OTHERS THEN
PKG_SYS_LOG.ERROR_LOG(P_PACKAGE_NAME => '',
P_PROC_NAME => ' STP_INTERNATIONAL ',
P_EXCEP_DT => SYSDATE,
P_EXCEP_CODE => SQLCODE,
P_EXCEP_DESC => SQLERRM,
P_EXCEP_REMK => '',
P_LINE_NO => NULL);
end STP_INTERNATIONAL;
发表评论
-
时效承诺明细产品流向存储过程
2016-05-04 14:30 0CREATE OR REPLACE PROCEDURE ST ... -
oracle 小知识点
2016-05-02 15:46 479oracle 默认排序空值在后面,显示在前面用nvl(co ... -
oracle 分析函数
2016-05-02 15:17 304row_number() over(partition by ... -
时效承诺明细行政区到城市
2016-04-28 13:18 0CREATE OR REPLACE PROCEDURE ST ... -
修改行政区ID的存储过程
2016-04-28 13:15 0CREATE OR REPLACE PROCEDURE S ... -
hive 资料
2016-04-11 20:07 0hive 培训资料 -
oracle 三种表连接方式
2016-04-10 18:04 892oracle 小知识 1.oracle 查看表大小 SEL ... -
sql 基本优化
2016-03-17 21:58 6251.select语句中避免使用*;2.ORACLE 采用从 ... -
临时取数脚本
2015-07-23 10:39 595--01 删除表中的现有数据 TRUNCATE TABLE T ... -
oracle 开窗函数使用,merge
2014-10-14 15:17 841select cast(last_operate_tm as ... -
oracle case when then 用法
2014-10-11 12:15 1134SELECT D.DEPT_CODE, ... -
oracle 创建无参存储过程
2014-09-23 15:26 1430create or replace procedure pro ... -
Oracle表分区与索引的创建
2013-10-22 11:00 1396create table TT_FLOW_TEST ( i ... -
PL SQL Develop中文乱码问题
2013-03-29 10:53 22001.准备软件 下载 oracle client客服端工具包 ... -
Oracle DB link
2012-11-05 22:29 971drop database link jc_zd cre ... -
Oracle 下的自定义函数
2012-10-21 15:25 884create or replace function MY_L ... -
Oracle 带游标的存储过程
2012-10-21 15:11 1094CREATE OR REPLACE PROCEDURE ST ... -
oracle 递归查询一个树形结构的菜单
2011-09-02 11:53 21190关键字:oracle connect by level 树状结 ... -
oracle 查询日期
2011-09-02 11:53 1034--查询一个季度的第一天select trunc(sysda ... -
返回自定义游标的存储过程
2011-09-02 11:51 978create or replace procedure pro ...
相关推荐
使用sqlserver查询最近修改过的存储过程,。。。。。。。。。。。。
存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程SQL存储过程
sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程 sql 高效分页存储过程
阅读实验教材《SQL Server 2012数据库管理与开发》第137页到155页的内容,掌握存储过程的概念、了解存储过程的类别(系统存储过程,用户自定义存储过程,扩展存储过程);掌握存储过程的建立;掌握存储过程的两种...
存储过程概述 创建存储过程 执行存储过程 查看、重命名和删除存储过程 创建带有参数的存储过程
Informatica调用存储过程图文流程 Informatica 是一款功能强大的数据集成工具,能够帮助用户快速、可靠地集成各种数据源。调用存储过程是 Informatica 中的一种常用功能,下面将详细介绍 Informatica 调用存储过程...
破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程破解存储过程
birt报表中调用存储过程的方法. 目录 1. 概述 3 2. BIRT支持的存储过程返回值类型 3 3. BIRT调用存储过程的语法 4 4. 创建存储过程数据源/集 4 5. 在BIRT中使用存储过程 5 调用返回单结果集的存储过程。 5 调用...
db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程
分页存储过程 分页存储过程 分页存储过程 sql代码
在了解这个问题之前,我们通常认为存储过程具有以下优点:首先,存储过程只在创造时进行编译即可,以后每次执行存储过程都不需再重新编译,而我们通常使用的 SQL 语句每执行一次就编译一次,所以使用存储过程可以...
能不能写个动态的业务,只输入存储过程名称,自动获取存储过程参数,并且参数的数据从前台传递过来,这个就通用了。只写一个通用方法,就可以调用所有的存储过程。只根据输入不同的存储过程名称、参数内容,自动调用...
存储过程的理念、存储过程的基本语法、常用发放的实现及简易的存储分页
内容概要:简单的C# winform调用存储过程实例,创建存储过程入参,通过SqlConnection对象和SqlCommand对象调用存储过程,获取存储过程的出参并显示出来,详细代码注释,希望对用到C#调用存储过程的小伙伴有帮助 ...
SqlServer存储过程的导出导入 近日在研究SQL的存储过程,这里我陆续将我在使用存储过程中碰到的问题及解决办法发布到网上,请各位大虾指正。 SqlServer存储过程的导出导入,网上相关资料特别少,经过摸索,这里写...
oracle 存储过程实例 oracle存储过程实例
高效分页存储过程高效分页存储过程高效分页存储过程高效分页存储过程高效分页存储过程高效分页存储过程高效分页存储过程高效分页存储过程高效分页存储过程高效分页存储过程
针对 SQL Server 2008 存储过程通过 With Encryption 加密方式的解密 SQL Server 2008 存储过程中使用 With Encryption 加密方式可以对存储过程进行加密保护,以防止未经授权的访问和修改。但是,在某些情况下,...
"实验八 存储过程的使用(学生)" 本实验的目的是让学生掌握创建存储过程的基本方法,并学会使用 Transact-SQL 编写存储过程的方法。实验中,学生需要创建多个存储过程来完成不同的数据库操作,例如检索学生基本...
db2 存储过程 db2 存储过程 db2 存储过程