화면에서 파라미터 1,2,3으로 넘어오는 변수를
mybatis에서 for each 문을 이용하여 in 절을 이용하여 쿼리를 보통 하는데
이번에는 자바단에서 list를 만들수 없는 상황.
그래서 오라클에서 사용하는 pipelined 를 이용한 table 형태를 반환하는 함수 생성.
티베로도 잘 되는 구만.
WITH tt AS ( SELECT '1,2,3' AS txt FROM dual )
SELECT TRIM ( REGEXP_SUBSTR(txt, '[^,]+',1, LEVEL )) AS txt
FROM tt
CONNECT BY INSTR(txt, ',', 1, LEVEL-1) > 0
;
CREATE PACKAGE PKG_TEST IS
TYPE rec IS RECORD(cd VARCHAR2(100), cd2 VARCHAR2(100));
TYPE tbl_rec IS TABLE OF rec;
END;
/
CREATE OR REPLACE FUNCTION pipelined_table_func
(
p_value IN varchar
)
RETURN PKG_TEST.TBL_REC PIPELINED
AS
x PKG_TEST.REC;
CURSOR c_sc111 IS
WITH tt AS ( SELECT p_value AS txt FROM dual )
SELECT TRIM ( REGEXP_SUBSTR(txt, '[^,]+',1, LEVEL )) AS txt
FROM tt
CONNECT BY INSTR(txt, ',', 1, LEVEL-1) > 0
;
c_sc111_r c_sc111%ROWTYPE;
BEGIN
OPEN c_sc111;
LOOP
FETCH c_sc111 INTO c_sc111_r;
EXIT WHEN c_sc111%NOTFOUND;
x.cd := c_sc111_r.txt;
PIPE ROW(x);
END LOOP;
CLOSE c_sc111;
END;
/
WITH data1 AS (
SELECT '1' AS code, '대한민국1' AS codename FROM dual
UNION ALL
SELECT '2', '대한민국2' FROM dual
UNION ALL
SELECT '3', '대한민국3' FROM dual
UNION ALL
SELECT '4', '대한민국4' FROM dual
UNION ALL
SELECT '5', '대한민국5' FROM dual
UNION ALL
SELECT '6', '대한민국6' FROM dual
UNION ALL
SELECT '7', '대한민국7' FROM dual
UNION ALL
SELECT '8', '대한민국8' FROM dual
UNION ALL
SELECT '9', '대한민국9' FROM dual
)
SELECT * FROM
data1 a,
TABLE(pipelined_table_func(',1,2,3,')) b
WHERE a.code = b.cd
;
