I have tried to solve your problem. Maybe it does not fit yet.
I have extended the table "config" by a column "column_type" to identify which entry represents numerator and which denominator.
CREATE OR REPLACE PACKAGE pkg_demo
AS
TYPE r_out IS RECORD
( table_name VARCHAR2(30),
customer VARCHAR2(30),
ratio VARCHAR2(30)
);
TYPE t_out IS TABLE OF r_out;
FUNCTION get_ratio ( i_table IN VARCHAR2 )
RETURN t_out PIPELINED;
END pkg_demo;
/
CREATE OR REPLACE PACKAGE BODY pkg_demo
AS
FUNCTION get_ratio ( i_table IN VARCHAR2 )
RETURN t_out PIPELINED
AS
l_num config.column_name%TYPE;
l_den config.column_name%TYPE;
l_sql VARCHAR2(32767);
TYPE cur_type IS REF CURSOR;
l_cur cur_type;
l_rec r_out;
BEGIN
SELECT config.column_name
INTO l_num
FROM config
WHERE table_name = i_table
AND column_type = 'N';
SELECT config.column_name
INTO l_den
FROM config
WHERE table_name = i_table
AND column_type = 'D';
l_sql := 'SELECT ''' || TRIM(LEADING 'A' FROM i_table) || ''', customer, TO_CHAR(' || l_num || ') ||'':''|| TO_CHAR('|| l_den || ', ''FM00'') FROM ' || i_table;
OPEN l_cur FOR l_sql;
LOOP
FETCH l_cur INTO l_rec;
EXIT WHEN l_cur%NOTFOUND;
PIPE ROW(l_rec);
END LOOP;
CLOSE l_cur;
END get_ratio;
END pkg_demo;
/
SELECT * FROM TABLE(pkg_demo.get_ratio('A100'));
Result:
TABLE_NAME CUSTOMER RATIO
----------- --------- ---------
100 Microsoft 10:01
100 Tesla 15:02
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…