Oracle横竖转换通用函数

深藏阁楼爱情的钟 2022-12-16 05:55 133阅读 0赞

# Sql函数 #

CREATE OR REPLACE FUNCTION test_conv(p_table_name   IN VARCHAR2,
    								    p_columns      IN VARCHAR2,
    								    p_where_clause IN VARCHAR2)
      RETURN fnd_horizontal_to_vertical_tbl IS
      l_return_tbl   fnd_horizontal_to_vertical_tbl := fnd_horizontal_to_vertical_tbl();
      l_where_clause VARCHAR2(2000) := ltrim(p_where_clause);
      l_char_value   VARCHAR2(400);
    
    BEGIN
      IF upper(l_where_clause) LIKE 'WHERE'
      THEN
        l_where_clause := SUBSTR(l_where_clause, 6);
      END IF;
    
      FOR c IN (SELECT c.column_value FROM TABLE(str2varlist(p_columns)) c)
      LOOP
        EXECUTE IMMEDIATE 'select ' || c.column_value || ' from ' || p_table_name ||
    			    ' where 1=1 and ' || l_where_clause
    	INTO l_char_value;
    
        l_return_tbl.EXTEND();
        l_return_tbl(l_return_tbl.COUNT) := fnd_horizontal_to_vertical_rec(c.column_value,
    											     l_char_value);
      END LOOP;
      RETURN l_return_tbl;
    END test_conv;
    
    
      
      create or replace type fnd_horizontal_to_vertical_rec as object(
      col_name varchar2(100),
      COL_VALUE varchar2(100)
      );
      create or replace type fnd_horizontal_to_vertical_tbl as table of fnd_horizontal_to_vertical_rec;

# 测试表格 #

create table test
    (
           id    varchar2(10),
    			 name  varchar2(10),
    			 mfg_001  number,
    			 mfg_002  number,
    			 mfg_003  number,
    			 mfg_004 number
    )
    
    insert into test (ID, NAME, MFG_001, MFG_002, MFG_003, MFG_004)
    values ('1', '²âÊÔ', 12, 324, 324, 24);
    commit;

# 调用 #

select  * from table(test_conv('test a', 'NAME,MFG_001,MFG_002,MFG_003,MFG_004','a.id=1'));