下面语句可以查出来,如果你的系统身份证号码定义的是number型的,把v_cert_no的定义改一下就行了。不过要查出来,是需要一定时间的:) DECLARE v_count NUMBER(10); v_sql_str VARCHAR2(500); v_data_type VARCHAR2(20); v_cert_no VARCHAR2(30); BEGIN v_cert_no:='''1234567890123456'''; FOR cert_no IN(SELECT table_name,column_name,data_type FROM User_Tab_Columns ORDER BY table_name) LOOP IF cert_no.data_type IN('VARCHAR2') THEN v_sql_str:='SELECT count(1) FROM ' || cert_no.table_name || ' WHERE ' || cert_no.column_name || '=' || v_cert_no; EXECUTE IMMEDIATE v_sql_str INTO v_count; IF v_count>1 THEN dbms_output.put_line('在表' || cert_no.table_name || '字段' || cert_no.column_name || '中查到身份证号:' || v_cert_no); END IF; END IF; END LOOP; END;
再加一个exit可以缩短时间: DECLARE v_count NUMBER(10); v_sql_str VARCHAR2(500); v_data_type VARCHAR2(20); v_cert_no VARCHAR2(30); BEGIN v_cert_no:='''1234567890123456'''; FOR cert_no IN(SELECT table_name,column_name,data_type FROM User_Tab_Columns ORDER BY table_name) LOOP IF cert_no.data_type IN('VARCHAR2') THEN v_sql_str:='SELECT count(1) FROM ' || cert_no.table_name || ' WHERE ' || cert_no.column_name || '=' || v_cert_no; EXECUTE IMMEDIATE v_sql_str INTO v_count; IF v_count>1 THEN dbms_output.put_line('在表' || cert_no.table_name || '字段' || cert_no.column_name || '中查到身份证号:' || v_cert_no); exit; END IF; END IF; END LOOP; END;
DECLARE
v_count NUMBER(10);
v_sql_str VARCHAR2(500);
v_data_type VARCHAR2(20);
v_cert_no VARCHAR2(30);
BEGIN
v_cert_no:='''1234567890123456''';
FOR cert_no IN(SELECT table_name,column_name,data_type FROM User_Tab_Columns ORDER BY table_name) LOOP
IF cert_no.data_type IN('VARCHAR2') THEN
v_sql_str:='SELECT count(1) FROM ' || cert_no.table_name || ' WHERE ' || cert_no.column_name || '=' || v_cert_no;
EXECUTE IMMEDIATE v_sql_str INTO v_count;
IF v_count>1 THEN
dbms_output.put_line('在表' || cert_no.table_name || '字段' || cert_no.column_name || '中查到身份证号:' || v_cert_no);
END IF;
END IF;
END LOOP;
END;
DECLARE
v_count NUMBER(10);
v_sql_str VARCHAR2(500);
v_data_type VARCHAR2(20);
v_cert_no VARCHAR2(30);
BEGIN
v_cert_no:='''1234567890123456''';
FOR cert_no IN(SELECT table_name,column_name,data_type FROM User_Tab_Columns ORDER BY table_name) LOOP
IF cert_no.data_type IN('VARCHAR2') THEN
v_sql_str:='SELECT count(1) FROM ' || cert_no.table_name || ' WHERE ' || cert_no.column_name || '=' || v_cert_no;
EXECUTE IMMEDIATE v_sql_str INTO v_count;
IF v_count>1 THEN
dbms_output.put_line('在表' || cert_no.table_name || '字段' || cert_no.column_name || '中查到身份证号:' || v_cert_no);
exit;
END IF;
END IF;
END LOOP;
END;