by InsideCore [PL/SQL] [2017.12.15 13:02:31]
안녕하세요.
고수님들의 도움이 필요하여 염치 불구하고
몇자 적어 봅닏다.
제가 원하는 것은
select * from table(test_pkg.GET_TYPE_CODE(1));
위와 같이 PIPELINED을 사용하고 싶은데요.
PACKAGE에는 DYNAMIC SQL로 만들고 싶은데..
쉽게 잘 안되네요...그래서 고수님들의 도움을 요청 드립니다.
아래 문장에서 뭐가 잘못된 것이며,
뭐가 누락 되었는지 고수님들 도와 주세요.
CREATE OR REPLACE PACKAGE TEST_PKG AS
FUNCTION GET_TYPE_CODE(
P_SEQ IN VARCHAR2
) RETURN REC_AMT PIPELINED;
END TEST_PKG;
CREATE OR REPLACE PACKAGE BODY TEST_PKG AS
FUNCTION GET_TYPE_CODE(
P_SEQ IN VARCHAR2
) RETURN REC_AMT PIPELINED IS
TYPEREF_CUR IS REF CURSOR;
TYPE OUT_AMT IS RECORD(
SEQ NUMBER ,
TYPE_CODE VARCHAR2(10)
);
TYPE REC_AMT IS TABLE OF OUT_AMT;
begin
L_SQL := 'WITH T AS (
SELECT 1 SEQ , ''M001'' TYPE_CODE
FROM DUAL
UNION ALL
SELECT 1 SEQ , ''M002'' TYPE_CODE
FROM DUAL
UNION ALL
SELECT 1 SEQ , ''M003'' TYPE_CODE
FROM DUAL
UNION ALL
SELECT 1 SEQ , ''M004'' TYPE_CODE
FROM DUAL
UNION ALL
SELECT 1 SEQ , ''M005'' TYPE_CODE
FROM DUAL
SELECT 2 SEQ , ''T001'' TYPE_CODE
FROM DUAL
UNION ALL
SELECT 2 SEQ , ''T002'' TYPE_CODE
FROM DUAL
UNION ALL
SELECT 2 SEQ , ''T003'' TYPE_CODE
FROM DUAL
UNION ALL
SELECT 2 SEQ , ''T004'' TYPE_CODE
FROM DUAL
UNION ALL
SELECT 2 SEQ , ''T005'' TYPE_CODE
FROM DUAL
)
SELECT SEQ,
TYPE_CODE
FROM T
WHERE SEQ = '||P_SEQ;
OPEN SIZ_CV FOR L_SQL ;
LOOP
FETCH SIZ_CV INTO ???;
EXIT WHEN SIZ_CV%NOTFOUND;
------------
-- PIPE ROW 에 위의 결과를 입력하고 싶어요
PIPE ROW(????);
END LOOP;
CLOSE SIZ_CV;
end;
END TEST_PKG;
CREATE OR REPLACE PACKAGE TEST_PKG AS FUNCTION GET_TYPE_CODE( P_SEQ IN VARCHAR2 ) RETURN REC_AMT PIPELINED; END TEST_PKG; CREATE OR REPLACE PACKAGE BODY TEST_PKG AS FUNCTION GET_TYPE_CODE( P_SEQ IN VARCHAR2 ) RETURN REC_AMT PIPELINED IS TYPE REF_CUR IS REF CURSOR; -- TYPE 후 띄어쓰기 안함 TYPE OUT_AMT IS RECORD( SEQ NUMBER , TYPE_CODE VARCHAR2(10) ); TYPE REC_AMT IS TABLE OF OUT_AMT; L_SQL VARCHAR2(2000); --L_SQL 변수 선언 begin L_SQL := 'WITH T AS ( SELECT 1 SEQ , ''M001'' TYPE_CODE FROM DUAL UNION ALL SELECT 1 SEQ , ''M002'' TYPE_CODE FROM DUAL UNION ALL SELECT 1 SEQ , ''M003'' TYPE_CODE FROM DUAL UNION ALL SELECT 1 SEQ , ''M004'' TYPE_CODE FROM DUAL UNION ALL SELECT 1 SEQ , ''M005'' TYPE_CODE FROM DUAL SELECT 2 SEQ , ''T001'' TYPE_CODE FROM DUAL UNION ALL SELECT 2 SEQ , ''T002'' TYPE_CODE FROM DUAL UNION ALL SELECT 2 SEQ , ''T003'' TYPE_CODE FROM DUAL UNION ALL SELECT 2 SEQ , ''T004'' TYPE_CODE FROM DUAL UNION ALL SELECT 2 SEQ , ''T005'' TYPE_CODE FROM DUAL ) SELECT SEQ, TYPE_CODE FROM T WHERE SEQ = :a '; --OPEN SIZ_CV FOR L_SQL ; --OPEN 하는 커서 가 SIZ_CV?? OPEN REF_CUR FOR L_SQL USING P_SEQ; --SIZ_CV 를 REF_CUR로 변경 (OPEN 커서변수 FOR 동적SQL문장) :a 에 P_SEQ값 바인딩 LOOP FETCH REF_CUR INTO REC_AMT; EXIT WHEN REF_CUR%NOTFOUND; ------------ -- PIPE ROW 에 위의 결과를 입력하고 싶어요 PIPE ROW(REC_AMT(REC_AMT.LAST)); END LOOP; CLOSE REF_CUR; end; END TEST_PKG;