Oracle:动态 SQL

2021-12-24 00:00:00 oracle plsql

我正在尝试编写Oracle动态SQL来做行计数，下面是我的方法，请帮我完成.

I am trying to write Oracle dynamic SQL to do row count, below is my approach, please help me complete.

这是我选择生成单个计数语句:

This is my select to generate the individual count statements:

select 'select count(*) from '||table_name||';'
from dba_tables
where owner = 'URR';

我是这样开始的:

declare
  l_owner varchar2(30) := 'URR';
  l_table_name varchar2(30);
  l_columns varchar2(32000);
  l_sql varchar2(32000);
begin

推荐答案

以下是一个简单的示例，用于查看您自己架构中的表:

Here's a simple example that looks at tables in your own schema:

set serveroutput on
declare
    c number;
begin
    for r in (select table_name from user_tables) loop
        execute immediate 'select count(*) from ' || r.table_name
            into c;
        dbms_output.put_line(r.table_name ||': '|| c);
    end loop;
end;
/

要查看其他人的表格，您需要在开始尝试时使用 dba_tables，或者更有可能使用 all_tables，因为这应该排除您无法计算的表格from，但您还需要在 count 语句中指定所有者.

To look at someone else's tables you'll need to use dba_tables as you started to try, or more likely all_tables as that should exclude tables you can't count from, but you'll also need to specify the owner in the count statement.

通常您希望使用绑定变量来避免 SQL 注入，但您必须像这样使用连接来指定对象名称.

Normally you'd want to use bind variables to avoid SQL injection, but you have to specify object names with concatenation like this.

其他需要注意的是您在查询中的错误，但 Egor 现在已从问题中删除.您执行的动态 SQL 字符串不应以分号 (;) 结尾.

Something else to look out for is a mistake you had in your query, but which Egor has now removed from the question. The dynamic SQL string you execute should not be terminated by a semicolon (;).

相关文章