PACKAGE SPAC 부분은 아래 부분을 보면서 유추하길 바란다.
동적 QUERY로 데이터를 SELECT 할때 부분인데
동적 QUERY 자체를 사용하는 것 보다 어떤 때에 동적쿼리를 사용해야 하는지
본인이 정확히 파악하고 있어야 한다.
expert one on one 참조
PROCEDURE GET_DATA_LIST(
V_BOARDCD IN BBS_DATA_INFO.BOARDCD%TYPE
, V_DATACD IN BBS_DATA_INFO.DATACD%TYPE
, V_PAGE_NO IN NUMBER := 1
, V_PAGE_SIZE IN NUMBER := 10
, V_ISACTIVE IN CHAR := 'Y'
, V_SEARCHTYPE IN VARCHAR2 := ''
, V_SEARCHTEXT IN VARCHAR2 := ''
, V_RS OUT RS)
IS
V_SQL VARCHAR2(2000);
V_QUERY VARCHAR2(2000);
BEGIN
V_SQL:='SELECT DATACD, BOARDCD, CATETYPECD, CPCD, CATETYPENAME, PARENTCD,
BRENCH, NOTETYPE, SUBJECT, NID, USERID, NICKNAME,
POINTTOTAL, READNUM, MEMOCNT, REPCNT, REGDATE,
NUM, ROWNUM RNUM
FROM (
SELECT DATACD, BOARDCD, CATETYPECD, CPCD, CATETYPENAME, PARENTCD,
BRENCH, NOTETYPE, SUBJECT, NID, USERID, NICKNAME,
POINTTOTAL, READNUM, MEMOCNT, REPCNT, REGDATE,
ROW_NUMBER() OVER ( ORDER BY NOTICESORT DESC, SORTCODE DESC) AS NUM
FROM JAPANDEV.VI_BBS_LIST
WHERE DATACD = '||V_DATACD||' AND BOARDCD = '||V_BOARDCD||' AND ISACTIVE = '''||V_ISACTIVE||'''';
IF LENGTH(V_SEARCHTYPE) > 0 THEN
V_SQL := V_SQL || ' AND '|| V_SEARCHTYPE ||' LIKE '''||V_SEARCHTEXT||'%''';
END IF;
V_SQL := V_SQL || ' ) ORDER BY RNUM DESC ';
V_QUERY := 'SELECT * FROM (
SELECT ROWNUM AS RN, A.*, COUNT(*) OVER() AS TOTAL_RECORD FROM
( ' || V_SQL || ') A
) WHERE RN BETWEEN (('||V_PAGE_NO||' - 1) * '||V_PAGE_SIZE||') + 1 AND ('||V_PAGE_NO * V_PAGE_SIZE||')';
OPEN V_RS FOR V_QUERY;
END GET_DATA_LIST;
