[TeraData] 커서 2개 열고 각각 조회,.
REPLACE PROCEDURE LDVIDB.UP_ROUNGE_USE_CUST
(
IN ST_CD CHAR(4) --관리점코드 --원 소스 ST_CD
, IN LOUNGE_CD CHAR(2) --라운지코드
, IN FAMY_GB CHAR(1) --가족구분
, IN LOUNGE_ST CHAR(8) --라운지이용기간 - 시작
, IN LOUNGE_ET CHAR(8) --라운지이용기간 - 종료
)
/*
수정일: 2014-1-27
작성자: 김우종
시스템명: VIP
업무명: 마이포지션
화면ID: RG2300M
메뉴: 마이포지션 -> 우수고객서비사관리 -> 라운지이용내역조회
SP설명: 라운지 이용내역 조회
SP실행: CALL LDVIDB.UP_ROUNGE_USE_CUST('0001' , '1111', '1', '20111111' , '20141212');
*/
DYNAMIC RESULT SETS 2
BEGIN
BEGIN
DECLARE cur1 CURSOR WITH RETURN ONLY FOR
LOCKING ROW FOR ACCESS
SELECT
SUBSTR(B.USE_DT,1, 4) || '-' || SUBSTR(B.USE_DT, 5,2) || '-' || SUBSTR(B.USE_DT, 7,2)
AS USE_DT--이용일자
, SUBSTR(B.ETR_TM, 1, 2) || ':' || SUBSTR(B.ETR_TM, 3, 2)
AS ETR_TM--입실시간
, A.KOR_NM AS CUST_NM --고객이름
, A.CUST_NO AS CUST_NUM --고객번호
--, A.RGST_NO AS RGST_NO
, (CASE A.FAMY_GB WHEN '1' THEN '본인'
WHEN '2' THEN '가족'
ELSE '' END) AS ME_FAMILY --가족구분
,(SELECT sel_lvl_nm
FROM LDVMDB.VI_SEL_LVL_CD_V
WHERE sel_flg_cd = '1'
AND use_yn = 'Y'
AND sel_lvl_cd = A.SEL_LVL_CD) AS RNK_NM --등급
, (SELECT b_code_nm
FROM LDVMDB.VI_BASE_CODE_V
WHERE p_code ='MY016'
AND b_code = A.INNER_GRD) AS INNER_RNK_NM --내부등급
,CASE B.MVG_ROOM_ST_CD WHEN '9999' THEN '전점'
ELSE COALESCE ((SELECT store_nm
FROM LDVMDB.PO_ST_V
WHERE store_cd = B.MVG_ROOM_ST_CD),'') END ST_CD --이용점
FROM LDVMDB.VI_MVG_CUST_V AS A
INNER JOIN LDVMDB.VI_MVG_ROOM_V AS B
ON A.CUST_NO = B.INTG_CUST_ID
WHERE B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간
AND (ST_CD = :ST_CD OR :ST_CD = '9999')
AND (B.LOUNGE_CD = :LOUNGE_CD OR :LOUNGE_CD= '99') --라운지코드
AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0')
ORDER BY B.USE_DT DESC;
OPEN cur1;
END;
BEGIN
DECLARE cur2 CURSOR WITH RETURN ONLY FOR
LOCKING ROW FOR ACCESS
SELECT COUNT(A.CUST_NO) AS CNT
, SUM(B.ACCMPNY_CNT) AS CNT2 --동반고객수
FROM LDVMDB.VI_MVG_CUST_V AS A
INNER JOIN LDVMDB.VI_MVG_ROOM_V AS B
ON A.CUST_NO = B.INTG_CUST_ID
WHERE B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간
AND (ST_CD = :ST_CD OR :ST_CD = '9999')
AND B.LOUNGE_CD = :LOUNGE_CD OR :LOUNGE_CD= '99' --라운지코드
AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0');
OPEN CUR2;
END;
END;