|
효율적이 방법 부탁드립니다.
인라인쿼리를 사용하지 안커나, 효율적인 방법이 없을까요?
한방쿼리도 무방합니다.
http://database.sarang.net/?inc=read&aid=38859&criteria=oracle&subcrit=qna&id=&limit=20&keyword=&page=1
테이블 정보
t_recej 와 t_recek 는 1:M
t_recek 와 t_mosdk 는 1:M
t_recej 의 PK 는 grpno
t_recek 의 PK 는 grpno + key
t_mosdk 의 PK 는 key + mdrno
PROCEDURE Excute
(
in_deptcd IN CHAR,
in_endymd IN CHAR,
in_preymd IN CHAR
) IS
CURSOR cUsers(pDeptCd CHAR) IS
SELECT u.userid
FROM m_users u
WHERE u.deptcd = pDeptCd;
vUsers m_users%ROWTYPE;
BEGIN
OPEN cUsers(in_DeptCd);
LOOP
FETCH cUsers INTO vUsers;
EXIT WHEN cUsers%NOTFOUND;
vUserId := vUsers.userid;
vEndYmd := in_endymd;
vPreYmd := in_preymd;
cDataCount(vUserId,vEndYmd, vPreYmd);
END LOOP;
CLOSE cUsers;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END Excute;
PROCEDURE countData
(
in_userid IN CHAR,
in_endymd IN CHAR,
in_preymd IN CHAR
) IS
CURSOR cDataCount(pUserId CHAR, pEndYmd CHAR, pPreYmd CHAR) IS
SELECT COUNT(DISTINCT DECODE(k.endymd, pEndYmd, m.key,'')) as usercnt
,COUNT(DISTINCT SELECT m1.key
FROM t_mosdk m1
,t_recek k1
,t_recej j1
WHERE m1.key = m.key
AND m1.mdrno = m.mdrno
AND m1.key = k1.key
AND k1.grpno = j1.grpno
AND NOT EXISTS
(SELECT m2.key
FROM t_mosdk m2
,t_recek k2
WHERE m2.grpno = m1.groupno
AND m2.key = k2.key
AND m2.userid <> pUserId
)
AND k1.status IN ('185','195')
) AS managercnt
,COUNT(DISTINCT CASE WHEN k.status IN ('805''905') THEN DECODE(k.preymd, pPreYmd, m.key, '') END ) AS precnt
FROM t_mosdk m
,t_recek k
,t_recej j
WHERE k.grpno = j.grpno
AND m.key = k.key
AND m.grpno = k.grpno
AND m.userid = pUserId
AND(TRIM(k.endymd) IS NULL
OR k.endymd = pEndYmd
OR k.endymd = pPreYmd)
AND k.status IN ('185','190','195','220','230','300','310','315','320','705','805','905');
vDataCount cDataCount%ROWTYPE;
BEGIN
OPEN cDataCount(in_userid, in_endymd, in_preymd);
FETCH cDataCount INTO vDataCount;
EXIT WHEN cDataCount%NOTFOUND;
LOOP
vUserCnt := vDataCount.usercnt;
vManagerCnt := vDataCount.managercnt;
vPreCnt := vDataCount.precnt;
MERGE INTO t_sumcnt s
USING (SELECT in_userid as userid FROM DUAL) m
ON (s.userid = m.userid)
WHEN MATCHED THEN
UPDATE SET s.usercnt = vDataCount.usercnt
,s.managercnt = vDataCount.managercnt
,s.precnt = vDataCount.precnt
WHEN NOT MATCHAED THEN
INSERT (s.userid, s.usercnt, s.managercnt, s.precnt)
VALUES (pUserId, vUserCnt, vManagerCnt, vPreCnt );
END LOOP;
CLOSE cDataCount;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END countData;
|
