Oracle:动态SQL [英] Oracle: Dynamic SQL

问题描述

我正在尝试编写Oracle动态SQL来进行行计数，以下是我的方法，请帮助我完成操作.

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

这是我用来生成单个count语句的选择:

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，因为这会排除您无法计数的表，但是您还需要指定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 (;).

这篇关于Oracle:动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！