oracle字符串合并

create or replace procedure PRO_PM_WORK_MAT_DEPT_SEL(V_V_ORDERGUID   IN VARCHAR2, --工单guid 
                                                     V_V_DEPTCODEREP VARCHAR2, --检修code dept
                                                     V_V_GXID        VARCHAR2, --工序序号
                                                     RET             OUT SYS_REFCURSOR) is
  DEPT_CODE     VARCHAR2(50);
  DEPT_REP_CODE VARCHAR2(50);
  NEW_DEPT      VARCHAR2(50);
  REPDEPTNUM    VARCHAR2(50);
begin
  SELECT COUNT(W.V_DEPTCODEREPARIR)
    INTO REPDEPTNUM
    FROM PM_WORKORDER W
   WHERE W.V_ORDERGUID = V_V_ORDERGUID
     AND W.V_DEPTCODEREPARIR IS NOT NULL;
  IF REPDEPTNUM = 0 THEN
    SELECT W.V_DEPTCODE
      INTO DEPT_CODE
      FROM PM_WORKORDER W
     WHERE W.V_ORDERGUID = V_V_ORDERGUID;
    NEW_DEPT := DEPT_CODE || ',' || V_V_DEPTCODEREP;
  ELSE
    SELECT W.V_DEPTCODEREPARIR
      INTO DEPT_REP_CODE
      FROM PM_WORKORDER W
     WHERE W.V_ORDERGUID = V_V_ORDERGUID;
    NEW_DEPT := DEPT_REP_CODE;
  END IF;
  --查找物料库房查找页面作业区
  OPEN RET FOR
    SELECT B.V_DEPTCODE, B.V_DEPTNAME
      FROM BASE_DEPT B
     WHERE B.V_SAP_JHGC IN
           (SELECT DISTINCT S.V_SAP_PLANDEPT
              FROM PM_WORKORDER_ET_OPERATIONS O
              LEFT OUTER JOIN SAP_PM_WORKCSAT S
                ON S.V_SAP_WORK = O.V_WORK_CENTER
             WHERE O.V_ACTIVITY = V_V_GXID
               AND O.V_ORDERGUID = V_V_ORDERGUID)
       AND B.V_DEPTCODE IN
           (SELECT REGEXP_SUBSTR(NEW_DEPT, '[^,]+', 1, LEVEL)
              FROM DUAL
            CONNECT BY REGEXP_SUBSTR(NEW_DEPT, '[^,]+', 1, LEVEL) IS NOT NULL);

end PRO_PM_WORK_MAT_DEPT_SEL;