Oracle Function

CREATE OR REPLACE FUNCTION AC_NPUS.JJ_GET_STAFF(v_seq IN VARCHAR2)
RETURN VARCHAR IS
total_staff VARCHAR2(2000);
staff_nm VARCHAR2(100);
role VARCHAR2(100);
r_count number;
/******************************************************************************
NAME: JJ_GET_STAFF
PURPOSE: 수행원 조회

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2010-11-22 LeeJungSoo 1. Created this function.

NOTES: SEQ에 해당하는 모든 수행원 조회

******************************************************************************/
BEGIN

total_staff:='';
staff_nm:='';
DECLARE
CURSOR J1 IS
SELECT A.STAFF_NM AS staff_nm, B.COMM_NM as role
FROM MAN_SPECIAL_HISTORY_STAFF A, MAN_COMM_CODE B
WHERE A.ROLE=B.COMM_ID AND A.SEQ=v_seq ORDER BY A.SEQ2;
BEGIN
IF NOT J1%ISOPEN THEN
OPEN J1;
END IF;
r_count:= length(NVL(total_staff,''));
LOOP
FETCH J1 INTO staff_nm,role;
EXIT WHEN J1%NOTFOUND;

IF length(total_staff)>0 THEN
total_staff:=CONCAT(total_staff,' ');
total_staff:=CONCAT(total_staff,staff_nm);
total_staff:=CONCAT(total_staff,'[');
total_staff:=CONCAT(total_staff,role);
total_staff:=CONCAT(total_staff,']');


ELSE
total_staff:=CONCAT(total_staff,staff_nm);
total_staff:=CONCAT(total_staff,'[');
total_staff:=CONCAT(total_staff,role);
total_staff:=CONCAT(total_staff,']');

END IF;

END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
total_staff:=NULL;

END;

RETURN total_staff;

END JJ_GET_STAFF;
/