oracle字符串合并

た入场券 2024-04-17 14:38 63阅读 0赞

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;

![20190827151616606.png][]

![20190827151632170.png][]

[20190827151616606.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8ab3404b97694a2bafe8f9dbc30ef3ed.png
[20190827151632170.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/a1c0c44150514508ad12d9011e3a807a.png