如何在动态 sql (ORACLE PLSQL) 中获取本地临时变量中的 count(*) 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11608203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get count(*) value in local temp variable in dynamic sql (ORACLE PLSQL)
提问by Vijay Kolte
I want to get count(*)value in dynamic plsql statement.
We can write static stmt as:
我想count(*)在动态 plsql 语句中获取值。我们可以将静态 stmt 写为：
select count(*) into tmp_cnt from table_info where nbr_entry='0123456789';
but how to get tmp_cntvalue while writing the dynamic sql stament?
or any other way to get count(*)value into tmp_cntvariable?
但是如何tmp_cnt在编写动态 sql 语句时获取价值呢？或任何其他方式将count(*)价值转化为tmp_cnt变量？
回答by Miguel
Maybe different oracle version, but what worked for me was:
也许不同的 oracle 版本，但对我有用的是：
...
execute immediate 'select count(*) from ' || p_table_name into l_count;
...
回答by Codo
You can achieve it with EXECUTE IMMEDIATE ... RETURNING INTO:
您可以通过 EXECUTE IMMEDIATE ... 返回到：
function count_rows(p_table_name varchar2)
return number
is
l_count number;
begin
execute immediate 'select count(*) from ' || p_table_name into l_count;
return l_count;
end count_rows;
