PLSQL数据库整数值搜索脚本报错：临时表创建问题及优化咨询

问题背景 #

我此前精通MSSQL，现转用PL/SQL，希望编写脚本在无文档的数据库中搜索特定整数值（如ID）。当前思路是创建临时表存储数值列的Schema/Table/Column信息，通过动态SQL统计匹配行数，最终筛选匹配数大于0的结果。但在SQL Developer中执行以下脚本时出现错误：

原执行脚本 #

DECLARE cSQL VARCHAR2(1000);
        iSearchValue INT;

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_COL_MATCHES
(
    SCHEMA_NAME VARCHAR2(100),
    TABLE_NAME VARCHAR2(100),
    COLUMN_NAME VARCHAR2(100),
    DATA_TYPE VARCHAR2(100),
    MATCH_COUNT INT
)
ON COMMIT PRESERVE DEFINITION;

BEGIN
    iSearchValue := 237001;
    
    
    INSERT INTO ORA$PTT_COL_MATCHES
            (SCHEMA_NAME,
            TABLE_NAME,
            COLUMN_NAME,
            DATA_TYPE,
            MATCH_COUNT)
    SELECT  col.owner as schema_name,
            col.table_name, 
            col.column_name,
            col.data_type,
            0
    FROM    sys.all_tab_columns col
            INNER JOIN sys.all_tables t     ON      col.owner = t.owner 
                                            AND     col.table_name = t.table_name
    WHERE   col.owner NOT IN ('SYS', 'SYSTEM');
    --AND     col.DATA_TYPE IN ('INT', 'NUMBER', 'FLOAT', 'LONG')
            
            
    FOR rec IN
        (SELECT  *
        FROM    ORA$PTT_COL_MATCHES)
    LOOP
        cSQL = 'UPDATE  ' || ORA$PTT_COL_MATCHES || '
                SET     MATCH_COUNT = (SELECT COUNT(*) FROM ' || rec.SCHEMA_NAME || '.' || rec.TABLE_NAME || ' WHERE ' || rec.COLUMN_NAME || ' = ' || TO_CHAR(iSearchValue) || ')
                WHERE   SCHEMA_NAME = ''' || rec.SCHEMA_NAME || '''
                AND     TABLE_NAME = ''' || rec.TABLE_NAME || '''';
        EXECUTE IMMEDIATE cSQL;
    END LOOP;
    
    
    SELECT  *
    FROM    ORA$PTT_COL_MATCHES
    WHERE   MATCH_COUNT > 0;
    
    
END;

错误信息 #

Error report -
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
06550. 00000 -  "line %s, column %s:
%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

问题解答 #

1. 为何此处不能创建临时表？ #

PL/SQL块的结构严格分为声明段（DECLARE后、BEGIN前）和执行段（BEGIN...END内部）。声明段仅允许定义变量、常量、游标等声明类语句，而CREATE TABLE属于DDL执行语句，不能放在声明段中，因此触发编译错误。

2. 可在何处创建临时表？ #

有两种合法位置：

• PL/SQL块外部：作为独立的SQL语句执行，与PL/SQL块分离；
• PL/SQL块执行段内部：通过EXECUTE IMMEDIATE执行动态DDL，因为静态DDL语句无法直接写在PL/SQL执行段中。示例代码：

BEGIN
  EXECUTE IMMEDIATE 'CREATE PRIVATE TEMPORARY TABLE ORA$PTT_COL_MATCHES
    (
        SCHEMA_NAME VARCHAR2(100),
        TABLE_NAME VARCHAR2(100),
        COLUMN_NAME VARCHAR2(100),
        DATA_TYPE VARCHAR2(100),
        MATCH_COUNT INT
    )
    ON COMMIT PRESERVE DEFINITION';
END;
/

3. 实现该需求的更优方式？ #

可以从效率、安全性、简洁性三个维度优化，核心思路是跳过临时表，直接动态统计输出：

1. 过滤数值类型列：打开原脚本中注释的数值类型过滤逻辑，避免对字符串等非数值列执行无效查询；
2. 使用绑定变量：替代字符串拼接传递搜索值，避免SQL注入风险，同时提升执行效率；
3. 直接输出结果：遍历符合条件的列后直接统计并输出，省去临时表的写入与更新操作；
4. 异常捕获：处理权限不足、列类型不匹配等异常，保证脚本完整执行。

优化后的示例脚本：

DECLARE
    v_search_value INT := 237001;
    v_count        INT;
BEGIN
    FOR rec IN (
        SELECT col.owner AS schema_name,
               col.table_name,
               col.column_name,
               col.data_type
        FROM sys.all_tab_columns col
        JOIN sys.all_tables t 
            ON col.owner = t.owner AND col.table_name = t.table_name
        WHERE col.owner NOT IN ('SYS', 'SYSTEM')
          AND col.data_type IN ('NUMBER', 'INTEGER', 'FLOAT') -- 精准过滤数值类型
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || rec.schema_name || '.' || rec.table_name || 
                              ' WHERE ' || rec.column_name || ' = :1'
                INTO v_count USING v_search_value;
            
            IF v_count > 0 THEN
                DBMS_OUTPUT.PUT_LINE('Schema: ' || rec.schema_name || ', Table: ' || rec.table_name || 
                                     ', Column: ' || rec.column_name || ', Matches: ' || v_count);
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                -- 捕获异常，避免脚本中断
                DBMS_OUTPUT.PUT_LINE('Error accessing ' || rec.schema_name || '.' || rec.table_name || '.' || rec.column_name || ': ' || SQLERRM);
        END;
    END LOOP;
END;
/

内容的提问来源于stack exchange，提问作者Mark Roworth