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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值