CREATE OR REPLACE FUNCTION get_data (
  params  IN  VARCHAR2 DEFAULT NULL
) RETURN ty_data_tab
PIPELINED
IS

  TYPE tt_param_list IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
  param_list    tt_param_list;
  param         VARCHAR2(100);
  data          ty_data := NEW ty_data(NULL, NULL, NULL, NULL, NULL);
  ref_cur       SYS_REFCURSOR;

BEGIN

  /* Zerlegung der Paramter */
  FOR n IN 1 .. REGEXP_COUNT(params, '[^|]+') LOOP
    param := REGEXP_SUBSTR(params, '[^|]+', 1, n);
    param_list(REGEXP_SUBSTR(param, '[^=]+', 1, 1)) := REGEXP_SUBSTR(param, '[^=]+', 1, 2);
  END LOOP;

  /* Zusammenbau der Selektion */
  IF    param_list('REPORT') = 'STD_REP_MA'
  AND   param_list('BEREICH') = 'DETAIL'
  THEN
    OPEN ref_cur FOR q'(
      SELECT    0 num_sort,
                NULL dat_sort,
                NULL var_sort,
                '<pre><b><u>' || RPAD('Nr', 4) || '  ' || RPAD('Mitarbeitername', 15) || '  ' ||
                RPAD('Beruf', 25) || '  ' || RPAD('Vorgesetzter', 20) || '  ' ||
                RPAD('Abteilung', 15) || '   ' || RPAD('Gehalt', 11) || '</u></b></pre>' data1,
                NULL data2
      FROM      dual
      UNION ALL
      SELECT    ma.mitarbeiternr,
                NULL,
                NULL,
                '<pre>' || LPAD(ma.mitarbeiternr, 4) || '  ' || RPAD(ma.mitarbeitername, 15) || '  ' ||
                RPAD(ma.beruf, 25) || '  ' || RPAD(NVL(vg.mitarbeitername, '- - -'), 20) || '  ' ||
                RPAD(abt.abteilungsname, 15) || '  ' ||
                '<font color=' ||
                CASE
                  WHEN ma.gehalt < 30000 THEN 'red'
                  WHEN ma.gehalt between 30000 AND 70000 THEN 'orange'
                  WHEN ma.gehalt > 70000 THEN 'green'
                END || '>' || LPAD(TO_CHAR(ma.gehalt, '999G999D99'), 11) || '  ' ||
                '</font>' || '</pre>',
                NULL
      FROM      mitarbeiter ma,
                mitarbeiter vg,
                abteilung abt
      WHERE     ma.vorgesetzter = vg.mitarbeiternr(+)
      AND       ma.abteilungsnr = abt.abteilungsnr
      ORDER BY  num_sort)';
  ELSIF param_list('REPORT') = 'STD_REP_MA'
  AND   param_list('BEREICH') = 'KOPF'
  THEN
  dbms_output.put_line(1);
    OPEN ref_cur FOR q'(
      SELECT    0 num_sort,
                NULL dat_sort,
                NULL var_sort,
                '<font size=2>Mitarbeiter <font color=red>inkl. Jahresgehalt</font>' data1,
                NULL data2
      FROM      dual)';
      dbms_output.put_line(2);
  ELSE
    OPEN ref_cur FOR '
      SELECT    CAST(NULL AS NUMBER),
                CAST(NULL AS DATE),
                CAST(NULL AS VARCHAR2(100)),
                CAST(NULL AS VARCHAR2(4000)),
                CAST(NULL AS VARCHAR2(4000))
      FROM      dual
      WHERE     ROWNUM < 1';
  END IF;

  /* Ausgabe */
  LOOP
    FETCH ref_cur INTO data.num_sort, data.dat_sort, data.var_sort, data.data1, data.data2;
    EXIT WHEN ref_cur%NOTFOUND;
    PIPE ROW(data);
  END LOOP;
  CLOSE ref_cur;

  RETURN;

EXCEPTION
  WHEN OTHERS THEN
  IF ref_cur%ISOPEN THEN CLOSE ref_cur; END IF;
END;