develog

/*여러 개의 row를한 개 row, column 으로 변환*/ SELECT WMSYS.WM_CONCAT(A.COL1) RET_VALFROM ( SELECT 'A' COL1 FROM DUAL UNION ALL SELECT 'B' COL1 FROM DUAL UNION ALL SELECT 'C' COL1 FROM DUAL UNION ALL SELECT 'D' COL1 FROM DUAL UNION ALL SELECT 'E' COL1 FROM DUAL) A; --RET_VAL => A,B,C,D,E

DECLARE FUNCTION FN_TEST ( P_PARAM1 IN NUMBER , P_PARAM2 IN NUMBER DEFAULT 0 ) RETURN NUMBER IS BEGIN RETURN P_PARAM1 + P_PARAM2; END; BEGIN DBMS_OUTPUT.PUT_LINE(FN_TEST(1)); DBMS_OUTPUT.PUT_LINE(FN_TEST(1, 1)); END;

SELECT ROWID , ROWNUM , ORA_ROWSCNFROM DUAL;

FUNCTION FN_SPLIT(P_STR VARCHAR2)RETURN T_LISTIS V_LIST T_LIST; V_PREV VARCHAR2(1024); V_NEXT VARCHAR2(1024); V_POS NUMBER;BEGIN V_NEXT := P_STR; V_LIST := T_LIST(); LOOP V_POS := INSTR(V_NEXT, ','); IF V_POS = 0 THEN V_LIST.EXTEND; V_LIST(V_LIST.COUNT) := V_NEXT; EXIT; END IF; V_PREV := SUBSTR(V_NEXT, 0, V_POS - 1); V_NEXT := SUBSTR(V_NEXT, V_POS + 1); V_LIST.EXTEND; V_LIST(V_LIST.COUNT) := V_P..

(X)SELECT *FROM A, BWHERE A.COL1 = B.COL1(+)AND B.COL2(+) IN ('C','D'); (O)SELECT *FROM A, BWHERE A.COL1 = B.COL1(+)AND DECODE(B.COL2(+), 'C', 1, 'D', 1, 0) = 1; (O)SELECT *FROM A, BWHERE A.COL1 = B.COL1(+)AND (B.COL2 IS NULL OR B.COL2 IN ('C','D'));

SELECT X.USER_SEQ, Y.SCORE1, Z.SCORE2FROM( SELECT 'A' USER_SEQ FROM DUAL UNION ALL SELECT 'B' USER_SEQ FROM DUAL UNION ALL SELECT 'C' USER_SEQ FROM DUAL UNION ALL SELECT 'D' USER_SEQ FROM DUAL UNION ALL SELECT 'E' USER_SEQ FROM DUAL) X, ( SELECT 'A' USER_SEQ, '10' SCORE1 FROM DUAL UNION ALL SELECT 'B' USER_SEQ, '20' SCORE1 FROM DUAL UNION ALL SELECT 'E' USER_SEQ, '30' SCORE1 FROM DUAL) Y, ( SELE..