1、在本地?cái)?shù)據(jù)庫(kù)中創(chuàng)建如下表：
-- Create table
create table TMP_CR_PERSONS
(
SPECIALTY VARCHAR2(50),
USER_GROUP VARCHAR2(50),
USER_NAME VARCHAR2(50),
USER_CATEGORY VARCHAR2(50)
)
2、創(chuàng)建DBLINK：
create database link LK2PMS connect to XXX identified by XX
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.5.99)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb9)
)
)'
3、創(chuàng)建視圖，查詢變更清單：
CREATE OR REPLACE VIEW V_TMP_CR_LIST AS
SELECT D.CHANGE_REQUEST_CODE,
D.PROJECT_NAME,
D.PRODUCT_NAME,
D.MODULE_NAME,
D.TYPE,
D.PRIVILEGE,
D.PROPOSE_TIME,
WF.TRANSACTOR,
WF.TASKNAME,
P.SPECIALTY,
P.USER_GROUP,
P.USER_CATEGORY
FROM PMS_CHANGE@lk2pms D,
(SELECT MAX(ID) ID
FROM WFT_FLOWCONTROL@lk2pms WF
WHERE WF.WORKFLOWID = 'ChangeRequest'
GROUP BY WF.WORKID) MX,
WFT_FLOWCONTROL@lk2pms WF,
TMP_CR_PERSONS P
WHERE WF.ID = MX.ID
AND WF.WORKID = D.CHANGE_REQUEST_ID
AND D.CHANGE_REQUEST_CODE LIKE 'CR%'
AND WF.TRANSACTOR = P.USER_NAME
AND D.STATE NOT IN ('結(jié)束','終止','拒絕');
4、創(chuàng)建統(tǒng)計(jì)視圖：
CREATE VIEW V_TMP_CR_STAT AS
SELECT T.USER_GROUP, T.USER_NAME, COUNT(V.CHANGE_REQUEST_CODE) TOTAL
FROM V_TMP_CR_LIST V, TMP_CR_PERSONS T
WHERE V.TRANSACTOR(+) = T.USER_NAME
GROUP BY CUBE(T.USER_GROUP, T.USER_NAME);