declare v_sql varchar2(1000); data_count number; begin -- OWNER是模式名 tablespace_name是表空间 for cur_table in (select distinct table_name from ALL_ALL_TABLES WHERE OWNER='NEWJNCJEXT' and tablespace_name='JNCJ') loop for cur_column in (select distinct column_name,data_type from all_tab_columns a where a.OWNER='NEWJNCJEXT' and a.TABLE_NAME = cur_table.table_name) loop if cur_column.data_type = 'VARCHAR2' then -- SQL 拼接 v_sql := 'select count(1) from ' || cur_table.table_name || ' where ' || cur_column.column_name || '=''EEB10A60-7117-11DE-8A60-CAAB113F4FFE'''; -- 执行SQL execute immediate v_sql into data_count; if (data_count > 0) then DBMS_OUTPUT.PUT_LINE(v_sql); end if; end if; end loop; end loop; end;
以上SQL是在Oracle 11g下执行的
关于 DBMS_OUTPUT.PUT_LINE 的输出方式,你可以参考 https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/controlstatements.htm#LNPLS399 里的一些示例