oracle create function 行转列

CREATE OR REPLACE FUNCTION IMSC.f_ca_res_landmark_condition ( p_eesrbaseinfo_uuid  IN  VARCHAR2)
   RETURN VARCHAR2
IS
   RESULT   VARCHAR2 (200);

   --根据UUID关联的里程碑相关性
   CURSOR cs
   IS
      (SELECT d.eesrbaseinfo_uuid,
                 e.type_nm
              || '['
              || DECODE (d.landmark_value,
                         '0', '非',
                         '1', '关键',
                         '2', '重要',
                         '3', '一般'
                        )
              || ']' AS landmark_name
         FROM imsc.me_landmark d
              LEFT JOIN
              (SELECT d.type_nm, d.type_id
                 FROM ims.type_code_d d LEFT JOIN ims.type_code_m me
                      ON (d.type_code_m_uuid = me.type_code_m_uuid)
                WHERE me.type_cd = 'EESR_LANDMARK') e
              ON (d.landmark_type = e.type_id) where d.eesrbaseinfo_uuid=p_eesrbaseinfo_uuid
              );

   cstype   cs%ROWTYPE;
BEGIN
   OPEN cs;

   LOOP
      FETCH cs
       INTO cstype;

      EXIT WHEN cs%NOTFOUND;

      IF (p_eesrbaseinfo_uuid=cstype.eesrbaseinfo_uuid)
      THEN
         RESULT := RESULT || cstype.landmark_name || ',';
      END IF;
   END LOOP;

   CLOSE cs;

   IF (RESULT IS NOT NULL)
   THEN
      RESULT := SUBSTR (RESULT, 0, LENGTH (RESULT) - 1);
   END IF;

   RETURN (RESULT);
END f_ca_res_landmark_condition;
/
grant execute on   imsc.f_ca_res_landmark_condition to imsw;

grant execute on   imsc.f_ca_res_landmark_condition to imsr;