2011. 11. 14. 16:25

[Oracle] PL/SQL FUNCTION Sample

CREATE OR REPLACE FUNCTION NOSUN.GET_RELCAR
( V_STRDAT IN VARCHAR2,  V_SUNCOD IN VARCHAR2 )
RETURN VARCHAR2 IS

    O_RELCAR VARCHAR2(30);
    S_CARNO4 VARCHAR2(4);

    CURSOR C_1 IS
      SELECT DISTINCT CARNO4
          FROM INVLOG
        WHERE BADATE= V_STRDAT
            AND SUNCOD= V_SUNCOD;
BEGIN

    OPEN C_1;
    LOOP
        FETCH C_1 INTO S_CARNO4;
        EXIT WHEN C_1%NOTFOUND;

        BEGIN
            O_RELCAR := TRIM(O_RELCAR || ' ' || S_CARNO4);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
            O_RELCAR := '';
        END;
    END LOOP;
    CLOSE C_1;

RETURN O_RELCAR;

END GET_RELCAR;
/

'Database / Sql' 카테고리의 다른 글

[Oracle] RANK() 함수 Sample  (0) 2011.11.15
[Oracle] PL/SQL PROCEDURE Sample  (0) 2011.11.14
[Oracle] PL/SQL FUNCTION Sample  (0) 2011.11.14
[Oracle] SAVEPOINT & TRANSACTION  (0) 2011.09.02
[Oracle] FLASHBACK  (0) 2011.09.02
[Oracle] BIN$... 으로 시작하는 테이블  (0) 2011.08.31
Trackback 0 Comment 0