主页 / user-216765

Igor Zhilin's questions

Martin Hope
Igor Zhilin
Asked: 2021-10-06 03:28:55 +0800 CST
  • 0

我正在尝试查找包含名为 的列的所有表，在这些表的该列中arg_column_name查找值arg_column_value，并将表的名称和找到的行数返回给用户。

我使用 dbeaver 作为 IDE。我知道 T-SQL 和 pl/pgsql - 但我没有 Oracle PL/SQL 的先验知识。

这就是问题很重的原因。

我的程序是：

CREATE OR REPLACE PROCEDURE FIND_TABLE_WITH_COLUMN_VALUE (arg_column_name IN VARCHAR2, arg_column_value IN VARCHAR2) 
IS 
    v_rowcount NUMBER;
    v_sql_statement VARCHAR2(4000);
BEGIN
    FOR L IN (
        SELECT 
            OWNER || '.' || TABLE_NAME AS OWNER_TABLE,
                'BEGIN 
                    SELECT 1 FROM ' || OWNER || '.' || TABLE_NAME || ' 
                    WHERE ' || COLUMN_NAME || '=''' || arg_column_value || ''';
                    :0:=SQL%ROWCOUNT;
                END;
                ' AS SQL_STATEMENT
        FROM ALL_TAB_COLUMNS 
        WHERE 1=1
            AND COLUMN_NAME = arg_column_name
    )
    LOOP 
        v_sql_statement := L.SQL_STATEMENT;
        EXECUTE IMMEDIATE v_sql_statement USING OUT v_rowcount;
        DBMS_OUTPUT.put_line(L.OWNER_TABLE);
        DBMS_OUTPUT.put_line(v_rowcount);
    END LOOP;
END;
/

和错误：

SQL Error [6550] [65000]: ORA-06550: line 14, column 22:
PL/SQL: ORA-00904: "ARG_COLUMN_NAME": invalid identifier
ORA-06550: line 3, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 3:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 17, column 3:
PL/SQL: Statement ignored
ORA-06550: line 18, column 21:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 18, column 3:
PL/SQL: Statement ignored
ORA-06550: line 19, column 24:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 19, column 3:
PL/SQL: Statement ignored
ORA-06550: line 20, column 24:
PLS-00201: identifier 'V_ROWCOUNT' must be declared
ORA-06550: line 20, column 3:
PL/SQL: Statement ignored

这怎么可能？

  1. “ARG_COLUMN_NAME”：无效标识符- 这不是我指定的函数参数吗？
  2. 必须声明标识符“V_SQL_STATEMENT” - 它是否尚未在存储过程的 IS 部分中指定？

更新：@Littlefoot 的答案已经奏效。我认为我的错误的原因可能是双重的：

  1. dbeaver 存在 oracle 语法问题，或者
  2. 我对 oracle 语法有疑问（尤其是语句终止符\ ）

我还下载了 Oracle SQL Developer。它比 dbeaver 更有效地完成 Oracle SQL 开发工作。

这是我根据@Littlefoot 的输入调整的最终代码：

create or replace procedure find_table_with_column_value
  (arg_column_name in varchar2, arg_column_value in varchar2, row_count_limit in number default 100000)
is
    v_rowcount      number;
    v_sql_statement varchar2(4000);
    v_owner_table varchar2(400);
    v_prefix varchar2(10) := '';
    err_msg varchar2(4000);
BEGIN
    FOR l IN (
        SELECT
            c.owner || '.' || c.table_name AS owner_table,
            c.column_name,
            t.num_rows
        FROM
            all_tab_columns c
        INNER JOIN all_tables t ON
            t.table_name = c.table_name
            AND t.owner = c.owner
        WHERE
            1 = 1
            AND upper(c.column_name) = upper(dbms_assert.qualified_sql_name(arg_column_name))
            AND t.NUM_ROWS <= row_count_limit
            AND c.DATA_TYPE = 'VARCHAR2' -- safeguard against NUMERIC columns
           )
    LOOP
        v_sql_statement :=
          'select count(*) from ' || l.owner_table ||
          ' where ' || l.column_name || ' = ' ||
            chr(39) || arg_column_value || chr(39);

        v_owner_table := l.owner_table;
        
        EXECUTE IMMEDIATE v_sql_statement INTO v_rowcount;
        
        IF v_rowcount > 0 
            THEN v_prefix := '> ';
            ELSE v_prefix := '';
        END IF;
        
        dbms_output.put_line(v_prefix || l.owner_table || ': ' || v_rowcount);
    END LOOP;

EXCEPTION
WHEN OTHERS THEN
    err_msg := SQLERRM;
    dbms_output.put_line('Error with ' || v_owner_table);
    dbms_output.put_line('Error message = ' || err_msg);
END;
oracle plsql