今天在CSDN论坛上看到一个网友提的问题，他将字段名和值均以字符串的形式保存在一个表中，这样他的这个表可以扩充无数的纪录，用不同纪录代表的字段来修饰不同的对象，但是他在写查询的时候遇到了难题，就是如何把字符串还原成对应的一个数据集。
问题链接请看：http://community.csdn.net/Expert/topic/4595/4595459.xml?temp=.9010736
/*
we have two table ,one is master table as follows:
APP_CODE AUD_TYPE AUD_ID AUD_LEVEL OP_TYPE operator
EMED35 LOGIN 149 HiGHT INSERT han
EMED35 LOGOUT 150 HiGHT INSERT han
EMED35 POLL 153 HiGHT INSERT han
EMED35 VOTE 154 HiGHT INSERT han
the other one is detail table as follows:
AUD_ID AUD_ITEM_CODE AUD_ITEM_VALUE
149 IP_ADDRESS 202.102.22.33
149 LOGIN_TIME 20060306121212
150 LOGOUT_TIME 20060306151212
153 DELER_CODE ABC
153 POINT 40
154 DELER_CODE AAA
master.aud_id = detail.aud_id
each aud_id have one or many item id detail table decided by the master table AUD_type
now we want build a sql ,when we input a AUD_TYPE ,for example 'LOGIN',
when can get the result like this
APP_CODE AUD_TYPE AUD_ID AUD_LEVEL OP_TYPE operator IP_ADDRESS LOGIN_TIME
EMED35 LOGIN 149 HiGHT INSERT han 202.102.22.33 20060306121212
when we input a AUD_TYPE ,for example POLL,
when can get the result like this
APP_CODE AUD_TYPE AUD_ID AUD_LEVEL OP_TYPE operator DELER_CODE POINT
EMED35 POLL 153 HiGHT INSERT han ABC 40
any one can help me?
*/
本人花了点时间想到一个拙笨的解决方法，我也不知道有没有更好的方法，暂且抛砖引玉，同时也希望这个方法能够给其它遇到问题的朋友予以启示.
--创建数据源
CREATE TABLE testa(
APP_CODE VARCHAR2(10),
AUD_TYPE VARCHAR2(10),
AUD_ID NUMBER,
AUD_LEVEL VARCHAR2(10),
OP_TYPE VARCHAR2(10),
OPERATOR VARCHAR2(10));
ALTER TABLE TESTA
ADD CONSTRAINT pk_testa PRIMARY KEY (AUD_ID);
CREATE TABLE testb(
AUD_ID NUMBER,
AUD_ITEM_CODE VARCHAR2(10),
AUD_ITEM_VALUE VARCHAR2(1000));
ALTER TABLE TESTB
ADD CONSTRAINT fk_testb FOREIGN KEY (AUD_ID)
REFERENCES testa (AUD_ID);
INSERT INTO testa
SELECT 'EMED35','LOGIN',149,'HiGHT','INSERT','han' FROM dual;
INSERT INTO testa
SELECT 'EMED35','LOGOUT',150,'HiGHT','INSERT','han' FROM dual;
INSERT INTO testa
SELECT 'EMED35','POLL',153,'HiGHT','INSERT','han' FROM dual;
INSERT INTO testa
SELECT 'EMED35','VOTE',154,'HiGHT','INSERT','han' FROM dual;
INSERT INTO testb
SELECT 149,'IP_ADDRESS','202.102.22.33' FROM dual;
INSERT INTO testb
SELECT 149,'LOGIN_TIME','20060306121212' FROM dual;
INSERT INTO testb
SELECT 150,'LOGOUT_TIME','20060306151212' FROM dual;
INSERT INTO testb
SELECT 153,'DELER_CODE','ABC' FROM dual;
INSERT INTO testb
SELECT 153,'POINT','40' FROM dual;
INSERT INTO testb
SELECT 154,'DELER_CODE','AAA' FROM dual;
COMMIT;
--创建自定义类型
create or replace type tab_str as table of varchar2(4000);
--创建包
CREATE OR REPLACE PACKAGE pkg_test IS
-- Author : ADMINISTRATOR
-- Created : 2006-3-7 11:45:04
-- Purpose : 测试而已,用完立即删除
function to_table(pn_aud_id number,pv_str VARCHAR2) return tab_str;
FUNCTION f_get_query ( pc_aud_id IN NUMBER ) RETURN VARCHAR2;
END pkg_test;
CREATE OR REPLACE PACKAGE BODY pkg_test IS
function to_table(pn_aud_id number,pv_str VARCHAR2) return tab_str
as
ltab tab_str := tab_str();
pv_value VARCHAR2(1000);
BEGIN
if pv_str is not null THEN
SELECT aud_item_value INTO pv_value
FROM testb
WHERE aud_item_code=pv_str AND rownum=1 AND aud_id=pn_aud_id;
ltab.extend;
ltab(ltab.COUNT) := pv_value;
end if;
return ltab;
END to_table;
FUNCTION f_get_query( pc_aud_id IN NUMBER ) RETURN VARCHAR2 IS
v_sqlstr VARCHAR2(4000) := '';
v_sql1 VARCHAR2(4000) := '';
v_sql1_all VARCHAR2(4000);
v_sql2 VARCHAR2(4000) := '';
v_sql2_all VARCHAR2(4000) := '';
n_row NUMBER;
v_str VARCHAR2(100);
TYPE curtype IS REF CURSOR;
cur_1 curtype;
BEGIN
v_sqlstr:='SELECT a.*,t1.COLUMN_VALUE IP_ADDRESS,t2.COLUMN_VALUE LOGIN_TIME FROM testa a,';
OPEN cur_1 FOR SELECT rownum,aud_item_code FROM testb WHERE aud_id=pc_aud_id;
LOOP
FETCH cur_1 INTO n_row,v_str;
EXIT WHEN cur_1%NOTFOUND;
v_sql1:=',t'||n_row||'.COLUMN_VALUE '||v_str;
v_sql1_all:=v_sql1_all||v_sql1;
v_sql2:=',TABLE(CAST(pkg_test.to_table(aud_id,'''||v_str||''') AS tab_str)) t'||n_row;
v_sql2_all:=v_sql2_all||v_sql2;
END LOOP;
v_sqlstr :='SELECT a.*'||v_sql1_all||' from testa a'||v_sql2_all||' where a.aud_id='||pc_aud_id;
CLOSE cur_1;
RETURN(v_sqlstr);
END f_get_query;
END pkg_test;
--测试1
SELECT pkg_test.f_get_query(153) FROM dual;
SQL> --执行函数返回的SQL语句得到结果集
SQL> SELECT a.*,t1.COLUMN_VALUE DELER_CODE,t2.COLUMN_VALUE POINT from testa a,TABLE(CAST(pkg_test.to_table(aud_id,'DELER_CODE') AS tab_str)) t1,TABLE(CAST(pkg_test.to_table(aud_id,'POINT') AS tab_str)) t2 where a.aud_id=153;
APP_CODE AUD_TYPE AUD_ID AUD_LEVEL OP_TYPE OPERATOR DELER_CODE POINT
---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
EMED35 POLL 153 HiGHT INSERT han ABC 40
--测试2
SELECT pkg_test.f_get_query(149) FROM dual;
SQL> --执行函数返回的SQL语句得到结果集
SQL> SELECT a.*,t1.COLUMN_VALUE IP_ADDRESS,t2.COLUMN_VALUE LOGIN_TIME from testa a,TABLE(CAST(pkg_test.to_table(aud_id,'IP_ADDRESS') AS tab_str)) t1,TABLE(CAST(pkg_test.to_table(aud_id,'LOGIN_TIME') AS tab_str)) t2 where a.aud_id=149;
APP_CODE AUD_TYPE AUD_ID AUD_LEVEL OP_TYPE OPERATOR IP_ADDRESS LOGIN_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
EMED35 LOGIN 149 HiGHT INSERT han 202.102.22.33 20060306121212