create table T_TEST ( TABLE_NAME VARCHAR2(200), DDD VARCHAR2(200) );create or replace procedure p_test_01 is -- Local variables here cursor cur_table(pi_colname in varchar2) is select distinct t.object_name from user_objects t , user_tab_cols c where t.object_type='TABLE' and c.table_name=t.object_name and c.column_name=upper(pi_colname) ; vc_colname varchar2(200); vr_table cur_table%rowtype; cursor cur_col(pi_tablename in varchar2) is select t.COLUMN_NAME from user_tab_columns t where t.TABLE_NAME=pi_tablename and t.DATA_TYPE like '%CHAR%'; vr_col cur_col%rowtype ; vc_sql varchar2(4000); vc_qry varchar2(100); vc_out varchar2(200); vn_count number; begin -- Test statements here vc_colname :='DDD'; vc_qry:='AAA'; open cur_table(vc_colname); loop fetch cur_table into vr_table; exit when cur_table%notfound; vn_count:=0; vc_sql:='insert into t_test select '''||vr_table.object_name||''', '||vc_colname||' from '||vr_table.object_name ||' where 1=0 '; open cur_col(vr_table.object_name); loop fetch cur_col into vr_col; exit when cur_col%notfound; vc_sql:=vc_sql||' or '||vr_col.column_name||'='''||vc_qry||''''; vn_count:=vn_count+1; end loop; if vn_count>0 then execute immediate vc_sql ; end if;
close cur_col; end loop; close cur_table; commit; end;
应该先判断列名再取数据比较: DECLARE L_TN VARCHAR2(30); L_C SYS_REFCURSOR; L_R VARCHAR2(4000); BEGIN FOR I IN (SELECT TABLE_NAME FROM DBA_TAB_COLS S WHERE S.COLUMN_NAME = 'DDD' AND S.OWNER = USER) LOOP DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME || ' have DDD column!'); OPEN L_C FOR 'select ddd from "' || I.TABLE_NAME || '" where ddd like ''%abc%'''; LOOP FETCH L_C INTO L_R; EXIT WHEN L_C%NOTFOUND; DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME||' ddd is:'||L_R); END LOOP; CLOSE L_C; DBMS_OUTPUT.PUT_LINE('***************'); END LOOP; END; /--输出: TX1 have DDD column! *************** TX2 have DDD column! TX2 ddd is:sssabcsf TX2 ddd is:xabc *************** TX3 have DDD column! TX3 ddd is:sssabcsf3 TX3 ddd is:xabc3 ***************
(
TABLE_NAME VARCHAR2(200),
DDD VARCHAR2(200)
);create or replace procedure p_test_01
is
-- Local variables here
cursor cur_table(pi_colname in varchar2) is
select distinct t.object_name
from user_objects t ,
user_tab_cols c
where t.object_type='TABLE'
and c.table_name=t.object_name
and c.column_name=upper(pi_colname)
;
vc_colname varchar2(200);
vr_table cur_table%rowtype;
cursor cur_col(pi_tablename in varchar2) is
select t.COLUMN_NAME
from user_tab_columns t
where t.TABLE_NAME=pi_tablename
and t.DATA_TYPE like '%CHAR%';
vr_col cur_col%rowtype ;
vc_sql varchar2(4000);
vc_qry varchar2(100);
vc_out varchar2(200);
vn_count number;
begin
-- Test statements here
vc_colname :='DDD';
vc_qry:='AAA';
open cur_table(vc_colname);
loop
fetch cur_table into vr_table;
exit when cur_table%notfound;
vn_count:=0;
vc_sql:='insert into t_test select '''||vr_table.object_name||''', '||vc_colname||' from '||vr_table.object_name ||' where 1=0 ';
open cur_col(vr_table.object_name);
loop
fetch cur_col into vr_col;
exit when cur_col%notfound;
vc_sql:=vc_sql||' or '||vr_col.column_name||'='''||vc_qry||'''';
vn_count:=vn_count+1;
end loop;
if vn_count>0 then
execute immediate vc_sql ;
end if;
close cur_col;
end loop;
close cur_table;
commit;
end;
应该先判断列名再取数据比较:
DECLARE
L_TN VARCHAR2(30);
L_C SYS_REFCURSOR;
L_R VARCHAR2(4000);
BEGIN
FOR I IN (SELECT TABLE_NAME
FROM DBA_TAB_COLS S
WHERE S.COLUMN_NAME = 'DDD'
AND S.OWNER = USER) LOOP
DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME || ' have DDD column!');
OPEN L_C FOR 'select ddd from "' || I.TABLE_NAME || '" where ddd like ''%abc%''';
LOOP
FETCH L_C
INTO L_R;
EXIT WHEN L_C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME||' ddd is:'||L_R);
END LOOP;
CLOSE L_C;
DBMS_OUTPUT.PUT_LINE('***************');
END LOOP;
END;
/--输出:
TX1 have DDD column!
***************
TX2 have DDD column!
TX2 ddd is:sssabcsf
TX2 ddd is:xabc
***************
TX3 have DDD column!
TX3 ddd is:sssabcsf3
TX3 ddd is:xabc3
***************