一个实现行变列的存储过程

最近一个项目，需要实现动态的行变列的功能，写了一个存储过程，收录之：

---创建函数f_split_str，输入n个奶站的sid列表，依次输出第1到n个奶站的sid

CREATE OR REPLACE FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)

    RETURN VARCHAR2 IS

    v_first INT;

    v_last  INT;

  BEGIN

    IF p_seq < 1 THEN

      RETURN NULL;

    END IF;

    IF p_seq = 1 THEN

      IF instr(p_str, p_division, 1, p_seq) = 0 THEN

        RETURN p_str;

      ELSE

        RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);

      END IF;

    ELSE

      v_first := instr(p_str, p_division, 1, p_seq - 1);

      v_last  := instr(p_str, p_division, 1, p_seq);

      IF (v_last = 0) THEN

        IF (v_first > 0) THEN

          RETURN substr(p_str, v_first + 1);

        ELSE

          RETURN NULL;

        END IF;

      ELSE

        RETURN substr(p_str, v_first + 1, v_last - v_first - 1);

      END IF;

    END IF;

END;

/

---创建行变列的procedure

CREATE OR REPLACE PROCEDURE P_ROW_TO_COL (sid_group varchar2, date_group varchar2,table_name varchar2,c_row_to_col  out sys_refcursor)  

AS  

    TYPE v_sid_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

    v_sid v_sid_ind_by;

v_sid_cnt PLS_INTEGER;

v_station_cnt PLS_INTEGER;

v_stationsname varchar2(500);

V_SQLTEXT  VARCHAR2(32000);

BEGIN

    v_sid_cnt := length(sid_group) - length(REPLACE(sid_group, ',')) + 1;

FOR i IN 1 .. v_sid_cnt LOOP

      v_sid(i) := f_split_str(sid_group, ',', i);

    END LOOP;

V_SQLTEXT :='select '||date_group;

    FOR x IN 1 .. v_sid.COUNT LOOP

      V_SQLTEXT :=V_SQLTEXT||',max(decode(sid,'||v_sid(x)||',stationsname,null)) 奶站名称'||

                        ',max(decode(sid,'||v_sid(x)||',sum_weigh_'||date_group||',null)) 源奶总重量'||

',max(decode(sid,'||v_sid(x)||',avg_weigh_'||date_group||',null)) 源奶平均重量'||

',max(decode(sid,'||v_sid(x)||',avg_zhifang_'||date_group||',null)) 脂肪平均'||

',max(decode(sid,'||v_sid(x)||',avg_midu_'||date_group||',null)) 密度平均'||

',max(decode(sid,'||v_sid(x)||',avg_zonggan_'||date_group||',null)) 总干平均'||

',max(decode(sid,'||v_sid(x)||',avg_hanshui_'||date_group||',null)) 含水平均'||

',max(decode(sid,'||v_sid(x)||',avg_danbai_'||date_group||',null)) 蛋白平均'||

',max(decode(sid,'||v_sid(x)||',avg_bingdian_'||date_group||',null)) 冰点平均'||

',max(decode(sid,'||v_sid(x)||',avg_rutang_'||date_group||',null)) 乳糖平均'||

',max(decode(sid,'||v_sid(x)||',avg_diandaolv_'||date_group||',null)) 电导率平均'||

',max(decode(sid,'||v_sid(x)||',avg_wendu_'||date_group||',null)) 温度平均'||

',max(decode(sid,'||v_sid(x)||',avg_huifen_'||date_group||',null)) 灰分平均'||

',max(decode(sid,'||v_sid(x)||',avg_phzhi_'||date_group||',null)) PH值平均'||

',max(decode(sid,'||v_sid(x)||',zhifang_weighted_sum,null))    脂肪加权平均'||

',max(decode(sid,'||v_sid(x)||',midu_weighted_sum,null))       密度加权平均'||

',max(decode(sid,'||v_sid(x)||',zonggan_weighted_sum,null))    总干加权平均'||

',max(decode(sid,'||v_sid(x)||',hanshui_weighted_sum,null))    含水加权平均'||

',max(decode(sid,'||v_sid(x)||',danbai_weighted_sum,null))     蛋白加权平均'||

',max(decode(sid,'||v_sid(x)||',bingdian_weighted_sum,null))   冰点加权平均'||

',max(decode(sid,'||v_sid(x)||',rutang_weighted_sum,null))     乳糖加权平均'||

',max(decode(sid,'||v_sid(x)||',diandaolv_weighted_sum,null))  电导率加权平均'||

',max(decode(sid,'||v_sid(x)||',wendu_weighted_sum,null))      温度加权平均'||

',max(decode(sid,'||v_sid(x)||',huifen_weighted_sum,null))     灰分加权平均'||

',max(decode(sid,'||v_sid(x)||',phzhi_weighted_sum,null))      PH值加权平均'||

',max(decode(sid,'||v_sid(x)||',phzhi_weighted_sum,null))      指数';

    END LOOP;

V_SQLTEXT :=V_SQLTEXT||' from '||table_name||' where sid in('||sid_group

            ||') group by '||date_group||' order by 1';

    DBMS_OUTPUT.PUT_LINE(V_SQLTEXT);

OPEN c_row_to_col FOR V_SQLTEXT;

END;

/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-708097/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-708097/