oracle 将表名作为 plsql 参数传入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27592366/
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
passing in table name as plsql parameter
提问by Serge
I want to write a function to return the row count of a table whose name is passed in as a variable. Here's my code:
我想编写一个函数来返回名称作为变量传入的表的行数。这是我的代码：
create or replace function get_table_count (table_name IN varchar2)
return number
is
tbl_nm varchar(100) := table_name;
table_count number;
begin
select count(*)
into table_count
from tbl_nm;
dbms_output.put_line(table_count);
return table_count;
end;
I get this error:
我收到此错误：
FUNCTION GET_TABLE_COUNT compiled
Errors: check compiler log
Error(7,5): PL/SQL: SQL Statement ignored
Error(9,8): PL/SQL: ORA-00942: table or view does not exist
I understand that tbl_nmis being interpreted as a value and not a reference and I'm not sure how to escape that.
我知道这tbl_nm被解释为一个值而不是一个参考，我不知道如何逃避它。
回答by Dmitriy
You can use dynamic SQL:
您可以使用动态 SQL：
create or replace function get_table_count (table_name IN varchar2)
return number
is
table_count number;
begin
execute immediate 'select count(*) from ' || table_name into table_count;
dbms_output.put_line(table_count);
return table_count;
end;
There is also an indirect way to get number of rows (using system views):
还有一种获取行数的间接方法（使用系统视图）：
create or replace function get_table_count (table_name IN varchar2)
return number
is
table_count number;
begin
select num_rows
into table_count
from user_tables
where table_name = table_name;
return table_count;
end;
The second way works only if you had gathered statistics on table before invoking this function.
仅当您在调用此函数之前收集了表的统计信息时，第二种方法才有效。
