表 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批量插入数据方案
- oracle的表只能scott表的访问范围
- 求助一条sql语句。
- oracle 实际返回的行数超出请求的行数 如何让它返回多行?
- 数据库事务 锁和并发问题请教....
- SELECT id,nick FROM user WHERE username='test' AND password='1' or '1'='1'; 执行顺序
- 如何在只有plsql客户端的情况下备份Oracle中的某个数据库?
- 9i中,如何返回一个date类型字段加上59分59秒之后的结果(要返回的也是date类型字段)
- Form Builder里边怎么让搜索结果全部显示在一个列表里边啊?
- redhat linux 9下编译OCI,出现错误。
- 关于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 '