PROSAGA码农传奇-plsql-PLSQL - 按组生成服务器列表

PLSQL - 按组生成服务器列表

作者: 冷月如霜·胡狼
发布时间: 2024-12-20 07:23:31 (28天前)
转自：

<div class =“post-text”itemprop =“text”>
  <P>
    这是一个缓慢的午餐时间：
  </p>
   <pre>
    <code>
      DECLARE 
  strLast_grp   VARCHAR2(20);
BEGIN
  -- Test statements here
  FOR aRow IN (WITH cteCsv_data AS (SELECT 'group1,serverA' AS CSV FROM DUAL UNION ALL
                                    SELECT 'group1,serverB' AS CSV FROM DUAL UNION ALL
                                    SELECT 'group2,serverA' AS CSV FROM DUAL UNION ALL
                                    SELECT 'group3,serverC' AS CSV FROM DUAL),
                    cteGrp_srv AS  (SELECT TRIM(REGEXP_REPLACE(c.CSV, '(.*),.*', '\1')) AS GRP,
                                           TRIM(REGEXP_REPLACE(c.CSV, '.*,(.*)', '\1')) AS SRV
                                      FROM cteCsv_data c)
               SELECT gs.GRP, gs.SRV
                 FROM cteGrp_srv gs
                 ORDER BY gs.GRP, gs.SRV)
  LOOP
    IF strLast_grp IS NULL OR strLast_grp <> aRow.GRP THEN
      strLast_grp := aRow.GRP;

IF strLast_grp IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(' ');
      END IF;

DBMS_OUTPUT.PUT_LINE(aRow.GRP);
    END IF;

DBMS_OUTPUT.PUT_LINE(aRow.SRV);
  END LOOP;  -- aRow
END;

</code>
  </pre>
</DIV>