CREATEORREPLACEFUNCTION 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);BEGINIF upper(l_where_clause)LIKE'WHERE'THEN
l_where_clause := SUBSTR(l_where_clause,6);ENDIF;FOR c IN(SELECT c.column_value FROMTABLE(str2varlist(p_columns)) c)LOOPEXECUTE 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);ENDLOOP;RETURN l_return_tbl;END test_conv;createorreplacetype fnd_horizontal_to_vertical_rec as object(
col_name varchar2(100),
COL_VALUE varchar2(100));createorreplacetype fnd_horizontal_to_vertical_tbl astableof fnd_horizontal_to_vertical_rec;
测试表格
createtable test
(
id varchar2(10),
name varchar2(10),
mfg_001 number,
mfg_002 number,
mfg_003 number,
mfg_004 number
)insertinto test (ID, NAME, MFG_001, MFG_002, MFG_003, MFG_004)values('1','²âÊÔ',12,324,324,24);commit;
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_horizon