Oracle查询固定值查询

create PROCEDURE findCountryData(HeaderGuid IN VARCHAR2)
IS
v_found_it NUMBER;
BEGIN

DBMS_OUTPUT.put_line('start');
FOR x
    IN ( SELECT owner, table_name, column_name
         FROM all_tab_columns
         WHERE data_type not IN ('NUMBER', 'DATE', 'CLOB', 'NCLOB')
           AND owner IN ('IEMINT2')

– and TABLE_NAME = ‘CTCOUNTRYCODE’
and COLUMN_NAME not in (‘GUID’, ‘STCREATEDBY’, ‘STUPDATEDBY’, ‘STDATASTATUS’)
and COLUMN_NAME not like ‘GUID’
and TABLE_NAME not in
(‘COUNTRY_DATA’, ‘COUNTRY_DATA_TABLE_NAME’, ‘IFACELOG’, ‘STEXCELQUEUE’, ‘STEXPORTQUEUE’, ‘STJOB’,
‘STLOG’, ‘STLOGS’, ‘STNUMBERRANGE’, ‘STNUMBERRANGEMODELCONFIG’, ‘STPARAM’)
and TABLE_NAME not like ‘%BAK’
and TABLE_NAME not like 'QRTZ%’
and TABLE_NAME not like ‘R_%’
and TABLE_NAME not like ‘%_TEMP’
and TABLE_NAME not in (select distinct TABLE_NAME from COUNTRY_DATA_TABLE_NAME)

         ORDER BY owner, table_name, column_name)
    LOOP
        EXECUTE IMMEDIATE
                'select count(0) from "'
                || x.owner
                || '"."'
                || x.table_name
                || '" where "'
                || x.column_name
                || '" like ''%'
                || 'XXXXXXXX'
                || '%'' '
                || ' and rownum = 1'
            INTO v_found_it;

        --             merge into COUNTRY_DATA_TABLE_NAME dest using(

– select x.table_name “TABLE_NAME” from dual
– ) source on (dest.TABLE_NAME = source.TABLE_NAME)
– when not matched then
– insert (table_name, dtcreatedtime) values (x.table_name, sysdate);
insert into COUNTRY_DATA_TABLE_NAME values (x.TABLE_NAME, x.COLUMN_NAME, sysdate);

        IF v_found_it > 0
        THEN
            insert into COUNTRY_DATA values (x.table_name, x.column_name);

            DBMS_OUTPUT.put_line(
                        '"' || x.owner || '"."' || x.table_name || '"."' || x.column_name || '"'
                );
        END IF;

        commit;
    END LOOP;
DBMS_OUTPUT.put_line('end');

END;
/