表 A 中存放表B的字段 ,如何将查询A表的值 作为 B表的查询字段 从而打到查询B表的效果?
---------一个
表存放字段,另一个表的列就是这些字段 模拟 动态字段查询数据
建表语句如下:
create table gzstorefields(id number ,fieldname varchar(100),datatype varchar(100)
,datadesc varchar(100),orderfield number)
create table gztest(gzid number,temp1 varchar(10),temp2 varchar(10)
,temp3 varchar(10),temp4 varchar(10));
insert into GZSTOREFIELDS (ID, FIELDNAME, DATATYPE, DATADESC, ORDERFIELD)
values (1, 'temp1', 'varchar(10)', 'temp1', 1);insert into GZSTOREFIELDS (ID, FIELDNAME, DATATYPE, DATADESC, ORDERFIELD)
values (2, 'temp2', 'varchar(10)', 'temp2', 2);insert into GZSTOREFIELDS (ID, FIELDNAME, DATATYPE, DATADESC, ORDERFIELD)
values (3, 'temp3', 'varchar(10)', 'temp3', 3);insert into GZSTOREFIELDS (ID, FIELDNAME, DATATYPE, DATADESC, ORDERFIELD)
values (4, 'temp4', 'varchar(10)', 'temp4', 4);
insert into GZTEST (GZID, TEMP1, TEMP2, TEMP3, TEMP4)
values (1, '1', '11', '111', '1111');insert into GZTEST (GZID, TEMP1, TEMP2, TEMP3, TEMP4)
values (2, '2', '22', '222', '2222');insert into GZTEST (GZID, TEMP1, TEMP2, TEMP3, TEMP4)
values (3, '3', '33', '333', '3333');insert into GZTEST (GZID, TEMP1, TEMP2, TEMP3, TEMP4)
values (4, '4', '44', '444', '4444');
---------一个
表存放字段,另一个表的列就是这些字段 模拟 动态字段查询数据
建表语句如下:
create table gzstorefields(id number ,fieldname varchar(100),datatype varchar(100)
,datadesc varchar(100),orderfield number)
create table gztest(gzid number,temp1 varchar(10),temp2 varchar(10)
,temp3 varchar(10),temp4 varchar(10));
insert into GZSTOREFIELDS (ID, FIELDNAME, DATATYPE, DATADESC, ORDERFIELD)
values (1, 'temp1', 'varchar(10)', 'temp1', 1);insert into GZSTOREFIELDS (ID, FIELDNAME, DATATYPE, DATADESC, ORDERFIELD)
values (2, 'temp2', 'varchar(10)', 'temp2', 2);insert into GZSTOREFIELDS (ID, FIELDNAME, DATATYPE, DATADESC, ORDERFIELD)
values (3, 'temp3', 'varchar(10)', 'temp3', 3);insert into GZSTOREFIELDS (ID, FIELDNAME, DATATYPE, DATADESC, ORDERFIELD)
values (4, 'temp4', 'varchar(10)', 'temp4', 4);
insert into GZTEST (GZID, TEMP1, TEMP2, TEMP3, TEMP4)
values (1, '1', '11', '111', '1111');insert into GZTEST (GZID, TEMP1, TEMP2, TEMP3, TEMP4)
values (2, '2', '22', '222', '2222');insert into GZTEST (GZID, TEMP1, TEMP2, TEMP3, TEMP4)
values (3, '3', '33', '333', '3333');insert into GZTEST (GZID, TEMP1, TEMP2, TEMP3, TEMP4)
values (4, '4', '44', '444', '4444');
解决方案 »
- 如何解决oracle xe的ODBC彻底删除问题------找不到驱动程序的安装实例
- 逻辑方案求优化。请拍砖、求抛玉。
- oracle中如何一次删除多张表
- oracle查询结果直接导出为csv文件,给各位大神见笑了
- hibernate对blob的处理,急用
- Oracle错误问题(错误号:ORA-01578&ORA-01110)
- oracle 创建表
- 高分求解:OEM里登錄時出現錯誤提示: ORA-12154:TNS:無法處理服務名
- 定时启动进程??
- C#怎么通过odbc的方式操作oracle数据库
- 关于oracle开启DRCP后,server=pooled连接不上的问题,急急急!
- powerdesignerBPM/EAM怎么关联CDM/PDM
TYPE gztest_t1_type IS TABLE OF gztest.temp1%TYPE INDEX BY BINARY_INTEGER;
TYPE gztest_t2_type IS TABLE OF gztest.temp2%TYPE INDEX BY BINARY_INTEGER;
TYPE gztest_t3_type IS TABLE OF gztest.temp3%TYPE INDEX BY BINARY_INTEGER;
TYPE gztest_t4_type IS TABLE OF gztest.temp4%TYPE INDEX BY BINARY_INTEGER;
v_tmp1 gztest_t1_type;
v_tmp2 gztest_t2_type;
v_tmp3 gztest_t3_type;
v_tmp4 gztest_t4_type;
BEGIN
FOR rec IN (SELECT * FROM gzstorefields ORDER BY orderfield) LOOP
CASE REC.ID
WHEN 1 THEN
EXECUTE IMMEDIATE 'SELECT '||REC.FIELDNAME||' FROM gztest ' BULK COLLECT INTO v_tmp1;
WHEN 2 THEN
EXECUTE IMMEDIATE 'SELECT '||REC.FIELDNAME||' FROM gztest ' BULK COLLECT INTO v_tmp2;
WHEN 3 THEN
EXECUTE IMMEDIATE 'SELECT '||REC.FIELDNAME||' FROM gztest ' BULK COLLECT INTO v_tmp3;
WHEN 4 THEN
EXECUTE IMMEDIATE 'SELECT '||REC.FIELDNAME||' FROM gztest ' BULK COLLECT INTO v_tmp4;
ELSE NULL;
END CASE;
END LOOP;
FOR I IN v_tmp1.FIRST..v_tmp1.LAST LOOP
DBMS_OUTPUT.put_line(v_tmp1(I));
END LOOP;
DBMS_OUTPUT.put_line('');
FOR J IN v_tmp2.FIRST..v_tmp2.LAST LOOP
DBMS_OUTPUT.put_line(v_tmp2(J));
END LOOP;
DBMS_OUTPUT.put_line('');
FOR K IN v_tmp3.FIRST..v_tmp3.LAST LOOP
DBMS_OUTPUT.put_line(v_tmp3(K));
END LOOP;
DBMS_OUTPUT.put_line('');
FOR L IN v_tmp4.FIRST..v_tmp4.LAST LOOP
DBMS_OUTPUT.put_line(v_tmp4(L));
END LOOP;
DBMS_OUTPUT.put_line('');
END;
就是把从表A查询出来的字段的名,存储到一个变量中,然后用这个变量去拼接新的查询SQL语句,用这个SQL语句对B表进行查询。具体拼接就是字符串的连接:IMMEDIATE 'SELECT '||REC.FIELDNAME||' FROM gztest '