做完了。这个只有你要查询的值是VARCHAR2 类型的时候才可以。其他类型,你自己稍微改一下就可以了。DECLARE CURSOR C_TABLE_LIST IS SELECT A.table_name FROM user_tables A; v_table_list C_TABLE_LIST%ROWTYPE; CURSOR C_COL_LIST (p_table_nm user_tab_columns.table_name%type := 'CURSOR param') IS SELECT A.column_name FROM user_tab_columns A WHERE DATA_TYPE='VARCHAR2' AND A.table_name=p_table_nm; v_col_list C_COL_LIST%ROWTYPE; TYPE my_cursor IS REF CURSOR; the_cursor my_cursor; v_into_value PLS_INTEGER:=0; v_result_value VARCHAR2(50):='CSDN'; BEGIN OPEN C_TABLE_LIST; <<TABLE_LOOP>> LOOP FETCH C_TABLE_LIST INTO v_table_list; EXIT TABLE_LOOP WHEN C_TABLE_LIST%NOTFOUND; OPEN C_COL_LIST(v_table_list.table_name); <<COL_LOOP>> LOOP FETCH C_COL_LIST INTO v_col_list; EXIT COL_LOOP WHEN C_COL_LIST%NOTFOUND; DBMS_OUTPUT.PUT_LINE('TBALE_NAME:'|| v_table_list.table_name); DBMS_OUTPUT.PUT_LINE('COL_NAME:'|| v_col_list.column_name); DBMS_OUTPUT.PUT_LINE('v_result_value:'|| v_result_value); OPEN the_cursor FOR 'SELECT COUNT(1) as val FROM ' || v_table_list.table_name || ' WHERE ' || v_col_list.column_name || '=''' || v_result_value || ''''; <<VAL_LOOP>> LOOP FETCH the_cursor INTO v_into_value; EXIT VAL_LOOP WHEN the_cursor%NOTFOUND; IF v_into_value > 0 THEN DBMS_OUTPUT.PUT_LINE('TBALE_NAME:'|| v_table_list.table_name); DBMS_OUTPUT.PUT_LINE('COL_NAME:'|| v_col_list.column_name); END IF; END LOOP; CLOSE the_cursor; END LOOP; CLOSE C_COL_LIST; END LOOP; CLOSE C_TABLE_LIST; END; /
估计会用到USER_TAB_COLUMNS;
select * from user_tab_columns t where t.column_name=upper('字段名');
这种事,哪是一个SQL能搞定的。
类型的时候才可以。其他类型,你自己稍微改一下就可以了。DECLARE
CURSOR C_TABLE_LIST
IS
SELECT A.table_name FROM user_tables A;
v_table_list C_TABLE_LIST%ROWTYPE;
CURSOR C_COL_LIST (p_table_nm user_tab_columns.table_name%type := 'CURSOR param')
IS
SELECT A.column_name FROM user_tab_columns A WHERE DATA_TYPE='VARCHAR2' AND A.table_name=p_table_nm;
v_col_list C_COL_LIST%ROWTYPE;
TYPE my_cursor IS REF CURSOR;
the_cursor my_cursor;
v_into_value PLS_INTEGER:=0;
v_result_value VARCHAR2(50):='CSDN';
BEGIN
OPEN C_TABLE_LIST;
<<TABLE_LOOP>>
LOOP
FETCH C_TABLE_LIST INTO v_table_list;
EXIT TABLE_LOOP WHEN C_TABLE_LIST%NOTFOUND;
OPEN C_COL_LIST(v_table_list.table_name);
<<COL_LOOP>>
LOOP
FETCH C_COL_LIST INTO v_col_list;
EXIT COL_LOOP WHEN C_COL_LIST%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TBALE_NAME:'|| v_table_list.table_name);
DBMS_OUTPUT.PUT_LINE('COL_NAME:'|| v_col_list.column_name);
DBMS_OUTPUT.PUT_LINE('v_result_value:'|| v_result_value);
OPEN the_cursor FOR 'SELECT COUNT(1) as val FROM '
|| v_table_list.table_name
|| ' WHERE ' || v_col_list.column_name
|| '=''' || v_result_value || '''';
<<VAL_LOOP>>
LOOP
FETCH the_cursor INTO v_into_value;
EXIT VAL_LOOP WHEN the_cursor%NOTFOUND;
IF v_into_value > 0 THEN
DBMS_OUTPUT.PUT_LINE('TBALE_NAME:'|| v_table_list.table_name);
DBMS_OUTPUT.PUT_LINE('COL_NAME:'|| v_col_list.column_name);
END IF;
END LOOP;
CLOSE the_cursor;
END LOOP;
CLOSE C_COL_LIST;
END LOOP;
CLOSE C_TABLE_LIST;
END;
/