现有一查询出的结果集如下:
select xh AS "xh" from table1
xh
1 A
2 B
3 C
4 D
5 E
把此结果集当作表TABLE1。
第二张表TABLE2格式如下: 主键 A B C D E
1 01 x x x x x
2 02 x x x x x
3 03 x x x x x
4 04 x x x x x
5 05 x x x x x
即结果集TABLE1所对应的是TABLE2的字段名,且该字段名是动态的,现在想要根据TABLE1结果集动态的查询到其所对应的TABLE2里某主键所对应的数据。
注:项目是SSI,ibatis动态能实现的话也可以。
求大家帮助了,万分感激。 动态SQL sqlibatis
select xh AS "xh" from table1
xh
1 A
2 B
3 C
4 D
5 E
把此结果集当作表TABLE1。
第二张表TABLE2格式如下: 主键 A B C D E
1 01 x x x x x
2 02 x x x x x
3 03 x x x x x
4 04 x x x x x
5 05 x x x x x
即结果集TABLE1所对应的是TABLE2的字段名,且该字段名是动态的,现在想要根据TABLE1结果集动态的查询到其所对应的TABLE2里某主键所对应的数据。
注:项目是SSI,ibatis动态能实现的话也可以。
求大家帮助了,万分感激。 动态SQL sqlibatis
-- Created on 2013-1-10 by Thinylee
declare
-- Local variables here
i integer;
lc_column varchar(1024);
lc_sql varchar(1024);
lc_result varchar(1024);
begin
select xh into lc_column from table1 m where m.xxx = 'xxxxx';
lc_sql := 'select '|| lc_column ||' from test110';
EXECUTE IMMEDIATE lc_sql into lc_result;
dbms_output.put_line(lc_result);
end;
create or replace trigger tri_table1
after update or insert or delete on table1
for each row
declare
pragma autonomous_transaction;
begin
if inserting then
execute immediate'alter table table2 add '||:new.xh||' varchar2(30)';
elsif deleting then
execute immediate'alter table table2 drop column '||:old.xh;
end if;
end;
其中table1被UPDATE的情况还在思考中
create or replace trigger tri_table1
after update or insert or delete on table1
for each row
declare
pragma autonomous_transaction;
begin
if inserting then
execute immediate'alter table table2 add '||:new.xh||' varchar2(30)';
elsif deleting then
execute immediate'alter table table2 drop column '||:old.xh;
elsif updating then
execute immediate'alter table table2 drop column '||:old.xh;
execute immediate'alter table table2 add '||:new.xh||' varchar2(30)';
end if;
end;
procedure get_table2( v_pkey_val IN varchar2,
v_tbl2_cur OUT SYS_REFCURSOR
)
is
cursor c_table1 is select xh from table1;
r_xh VARCHAR2(100);
d_tbl_col_list_tmp VARCHAR2(1000);
d_tbl_col_list VARCHAR2(1000);
d_sel_tb2_str VARCHAR2(1100);
begin
--Step1: Fetch the list of records from table1 into d_tbl_col_list
open c_table1;
loop
fetch c_table1 into r_xh;
exit when c_table1%NOTFOUND;
d_tbl_col_list_tmp := d_tbl_col_list_tmp || ',' || r_xh;
end loop;
close c_table1;
select trim(leading ',' from d_tbl_col_list_tmp)
into d_tbl_col_list
from dual;
--Step 2: create the sql to return record from table2 based on d_tbl_col_list
d_sel_tb2_str := 'select ' || d_tbl_col_list || ' from table2 where pkey = ''' || v_pkey_val || '''';
DBMS_OUTPUT.PUT_LINE(d_sel_tb2_str);
OPEN v_tbl2_cur
FOR d_sel_tb2_str;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
测试成功了,试试吧