CREATE OR REPLACE FUNCTION F_SD_ROWCOL_CHANGE
(
iv_table IN VARCHAR2, --表名
iv_col IN VARCHAR2, --预转换的列
iv_condition IN VARCHAR2 --decode后的条件
) RETURN VARCHAR2
-----------------------------------------------------------------------
-- Function: F_SD_ROWCOL_CHANGE
-- Description: 行列转换
-- Author: ZhqoQG
-- Date: 2004/11/29
-----------------------------------------------------------------------
IS
v_outchar VARCHAR2(1000);
v_str VARCHAR2(500);
v_colvalue VARCHAR2(500);
TYPE mycursor IS REF CURSOR ;
v_cursor mycursor;
BEGIN
v_str := 'select distinct '||iv_col||' from '||iv_table ;
OPEN v_cursor FOR v_str ;
LOOP
FETCH v_cursor INTO v_colvalue;
EXIT WHEN v_cursor%NOTFOUND;
v_outchar := v_outchar||',sum(decode('||iv_col||','''||v_colvalue||''','||iv_condition||')) "'||v_colvalue||'"';
END LOOP;
CLOSE v_cursor;
RETURN v_outchar;
EXCEPTION
WHEN OTHERS THEN
RETURN '发生未知错误@'||SQLERRM;
END;