여러행 반환은 펑션으론 못해보고 package로 해결했었습니다

http://www.gurubee.net/lecture/1843

입력 파라메터가 쿼리에 들어갈 때 뭔가 타입 불일치가 일어나는 것 같구요.

아마도 입력되는 날짜형 변경하면서 오류난 것 같네요.

전에 말씀 드렸듯이 굳이 동적쿼리로 안 해도 static 쿼리로도 가능합니다.

추후 관리할 때도 static 쿼리가 더 용이하실 거구요.

대충 변환해보았는데.. 일단 한번 해보시구요.

CREATE OR REPLACE FUNCTION FN_TEST(PV_START_DT IN VARCHAR2
                                 , PV_END_DT IN VARCHAR2
                                 , PV_CSID IN VARCHAR2
                                 , PV_CARDNO IN VARCHAR2
                                 , PV_CARD_TYPE IN VARCHAR2)
 RETURN TTAB_SPLIT
 PIPELINED
IS
 VTYP_SPLIT TTYP_SPLIT;
BEGIN
 FOR R IN (SELECT 'PAYMENT' TYPE, COUNT(CARDNO) CARDNO_MAX, SUM(FARERCV) SUM_PAY
             FROM PAYMENT
            WHERE WORKDATE BETWEEN PV_START_DT AND PV_END_DT
              AND CARDNO IN (SELECT DISTINCT A.CARDNO AS CARDNO
                               FROM CARDLEDGER A
                                  , REFUND B
                                  , LOSTAPPLY C
                                  , BLPRE D
                                  , CARDRETURN E
                              WHERE A.CARDNO = B.CARDNO(+)
                                AND A.CARDNO = C.CARDNO(+)
                                AND A.CARDNO = D.CARDNO(+)
                                AND A.CARDNO = E.CARDNO(+)
                                AND A.CSID = PV_CSID
                                AND C.LOSTSTAT(+) = '01'
                                AND ((C.CARDNO IS NULL
                                  AND E.CARDNO IS NULL)
                                  OR (C.REFUND_YN = 'N')
                                  OR (E.REFUND_YN = 'N')
                                  OR (C.REFUND_YN = 'Y'
                                  AND B.WORKDATE > TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYYMMDD'))
                                  OR (E.REFUND_YN = 'Y'
                                  AND B.WORKDATE > TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYYMMDD'))))
                AND CARDNO = DECODE(PV_CARDNO,'all',CARDNO,PV_CARDNO)--CARDNO가 null이 아니므로 이렇게 사용
--              AND ((PV_CARDNO != 'all'--CARDNO가 인덱스 컬럼인 것 같네요. 이렇게 하면 인덱스 활용 못함;
--                AND CARDNO = PV_CARDNO)
--                OR PV_CARDNO = 'all')
--              AND ((PV_CARD_TYPE != 'all'
--                AND '01' = PV_CARD_TYPE)
--                OR PV_CARD_TYPE = 'all')
           UNION ALL
           SELECT 'PAYMENT_TR' TYPE, COUNT(CARDNO) CARDNO_MAX, SUM(FARERCV) SUM_PAY
             FROM PAYMENT_TR
            WHERE WORKDATE BETWEEN PV_START_DT AND PV_END_DT
              AND CARDNO IN (SELECT DISTINCT A.CARDNO AS CARDNO
                               FROM CARDLEDGER A
                                  , REFUND B
                                  , LOSTAPPLY C
                                  , BLPRE D
                                  , CARDRETURN E
                              WHERE A.CARDNO = B.CARDNO(+)
                                AND A.CARDNO = C.CARDNO(+)
                                AND A.CARDNO = D.CARDNO(+)
                                AND A.CARDNO = E.CARDNO(+)
                                AND A.CSID = PV_CSID
                                AND C.LOSTSTAT(+) = '01'
                                AND ((C.CARDNO IS NULL
                                  AND E.CARDNO IS NULL)
                                  OR (C.REFUND_YN = 'N')
                                  OR (E.REFUND_YN = 'N')
                                  OR (C.REFUND_YN = 'Y'
                                  AND B.WORKDATE > TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYYMMDD'))
                                  OR (E.REFUND_YN = 'Y'
                                  AND B.WORKDATE > TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYYMMDD'))))
                AND CARDNO = DECODE(PV_CARDNO,'all',CARDNO,PV_CARDNO)
--              AND ((PV_CARDNO != 'all'
--                AND CARDNO = PV_CARDNO)
--                OR PV_CARDNO = 'all')
--              AND ((PV_CARD_TYPE != 'all'
--                AND '01' = PV_CARD_TYPE)
--                OR PV_CARD_TYPE = 'all')
                )
 LOOP
  PIPE ROW (TTYP_SPLIT(R.GROUPID, R.GROUPNAME, R.MSG));--커서에 없는 내용이네요. 적당히 수정
 END LOOP;
 
 RETURN;
END;

for문쪽 쿼리 뽑아다가 직접 파라메터 값 넣어도 오류 나는지 확인해보세요.

만약 오류 나면 안나게 수정하시면 됩니다.

글쎄요...
위 오류난 소스가 아닌 다른 소스를 올리신 듯 하네요.
위 오류(ORA-06502)는 버퍼 부족 오류 맞습니다.

1. END IF 에
   - 세미콜론 누락이 두군데 보이구요.
2. LOOP 구문에
   - EXIT 구문이 안보이네요.
   - FETCH 구문 바로 아래 EXIT 구문 필요합니다.
   - EXIT WHEN L_CUR%NOTFOUND;
3. PV_CARD_TYPE 에 대한 조건이
   - 테이블 컬럼과 비교하는게 아니라 상수값과 비교하네요?
   - 이게 어떤 의도로 사용된 것인지?
4. L_REC 는
   - L_REC    RECEIPT_PAY_MSG_20161213%ROWTYPE;
   - 3개 컬럼(GROUPID, GROUPNAME, MSG)으로만 이루어진 것 맞겠죠?

jkson, 마농 선배님. 답변 항상 감사드립니다.

일단 jkson님 스크립트를 이용하여 적용했을시 문제는 없이 함수 생성은 완료 되었습니다.

동적쿼리를 정적 쿼리로 가능하다라고 가이드를 해주신 쿼리문이 이해가 잘안되네요.

IF PV_CARDNO != 'all' THEN 인경우는 조건문을 전혀 걸어주면안되는데요. 위 작성해주신 부분을 보면

AND ((PV_CARDNO != 'all' AND CARDNO = PV_CARDNO) OR PV_CARDNO = 'all')

위와같이 되어있습니다. 함수에서 all이라는 인자값을 받게될경우 조건문이 들어가면 안되는데요..

해당 스크립트 이해를 잘 못하겠습니다.ㅜ.ㅜ..

마농님  varchar2 변수에 쿼리 버퍼 부족오류가 맞는건가요.

그렇다면 varchar2보다 큰 타입에 넣어서 할수 있는 방법은 없는걸까요..

...암울하네요......

마농님이 일단 말슴주신 세미콜론 에러 및 EXIT 문구는 해결했으나,,

버퍼 문제가 남아있네요...

일단 제가 질문글에 올린 스크립트에 마농님께서 주신 오류내용 해결하고,

union all 아래 쿼리부분을 지우고서, union all 윗부분 쿼리를 아래에 붙여넣기 후,

실행했을시는 해당 오류가 발생을 안하네요.

그런데 순수 쿼리를 토드에서 실행했을시는 이상없이 결과값이 나오고는 있습니다.

후==33......><

jkson 님 .ㅜ.ㅜ.. 정말 정말 감사합니다.

말씀주신 부분이 문제였습니다.. 진짜 눈물나네요....이걸로 몇일을 눈치보며..

시간끄는지..혼자 끙끙 앓고있었는데....><;;

항상 친절한 답글 가르침 감사합니다.

마농님, 우리집아찌, jkson 선배님들 오늘도 또 하나 배워갑니다.

^^;.....

순조롭게 잘 진행이 되는줄 아랐는데..

UNION 이 많은 쿼리라...VARCHAR2 버퍼 한계를 넘는 순간 오류가 발생하네요..

ORA-06502: PL/SQL: 수치 또는 값 오류: 문자열 버퍼가 너무 작습니다
ORA-06512: "HIPLUS.FN_TEST", 줄 296에서

아.....갑자기 이시간에 다시..멍해지고 눈물이납니다....ㅜ.ㅜ

늦은시간에도 답변을 달아주시네요.ㅜ.ㅜ.

감사합니다. !!! 좀 더 고민해보겠습니다. 할수 있을것 같습니다!!!!!

1. 쿼리 개선
  - IN 서브쿼리는 동일하고, 메인테이블만 바꿔가며 집계하는 모양새입니다.
  - IN 서브쿼리를 재사용할 수 있도록 해야 합니다.
  - WITH 구문을 활용하는 방법이 있구요.
  - 동적쿼리를 사용한다면 동일 쿼리를 테이블명만 바꿔가며 반복처리 하는 방법도 있습니다.
  - 그런데 IN 서브쿼리에 문제점이 있어 보입니다.

-- IN 서브쿼리 문제점 --
2. D 테이블은 불필요합니다.
  - D 가 아우터 조인되고 있으나 where, select 절 어디에도 사용이 되고 있지 않습니다.
  - 있으나 마나 한거구요
  - 혹시 이것 때문에 Distinct 를 사용하는 것일 수도 있습니다.
3. OR 조건절이 틀린게 아닐까 생각됩니다.
  - 검증이 필요할 듯.
4. pv_card_type 에대한 비교가
  - 상수값 '01' 과 비교하는게 이상하네요.