Oracle横竖转换通用函数

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'));