oracle 在存储过程中截断表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/625743/
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
Truncating a table in a stored procedure
提问by Klas Mellbourn
When I run the following in an Oracle shell it works fine
当我在 Oracle shell 中运行以下命令时，它工作正常
truncate table table_name
But when I try to put it in a stored procedure
但是当我尝试将它放入存储过程时
CREATE OR REPLACE PROCEDURE test IS
BEGIN
truncate table table_name;
END test;
/
it fails with
它失败了
ERROR line 3, col 14, ending_line 3, ending_col 18, Found 'table', Expecting: @ ROW or ( or . or ; :=
Why?
为什么？
回答by Dheer
All DDL statements in Oracle PL/SQL should use Execute Immediate before the statement. Hence you should use:
Oracle PL/SQL 中的所有 DDL 语句都应该在语句之前使用 Execute Immediate。因此你应该使用：
execute immediate 'truncate table schema.tablename';
回答by stjohnroe
As well as execute immediate you can also use
除了立即执行之外，您还可以使用
DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE tablename;');
DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE tablename;');
The statement fails because the stored proc is executing DDL and some instances of DDL could invalidate the stored proc. By using the execute immediate or exec_ddl approaches the DDL is implemented through unparsed code.
该语句失败，因为存储过程正在执行 DDL，并且 DDL 的某些实例可能会使存储过程无效。通过使用立即执行或 exec_ddl 方法，DDL 是通过未解析的代码实现的。
When doing this you neeed to look out for the fact that DDL issues an implicit commit both before and after execution.
执行此操作时，您需要注意 DDL 在执行前后都会发出隐式提交的事实。
回答by serioys sam
try the below code
试试下面的代码
execute immediate 'truncate table tablename' ;
回答by Gourabp
You should know that it is not possible to directly run a DDL statement like you do for DML from a PL/SQL block because PL/SQL does not support late binding directly it only support compile time binding which is fine for DML. hence to overcome this type of problem oracle has provided a dynamic SQL approach which can be used to execute the DDL statements.The dynamic sql approach is about parsing and binding of sql string at the runtime. Also you should rememder that DDL statements are by default auto commit hence you should be careful about any of the DDL statement using the dynamic SQL approach incase if you have some DML (which needs to be commited explicitly using TCL) before executing the DDL in the stored proc/function.
您应该知道，不可能像从 PL/SQL 块对 DML 那样直接运行 DDL 语句，因为 PL/SQL 不直接支持后期绑定，它只支持编译时绑定，这对 DML 来说很好。因此为了克服这类问题，oracle 提供了一种动态 SQL 方法，可用于执行 DDL 语句。动态 sql 方法是关于在运行时解析和绑定 sql 字符串。此外，您应该记住 DDL 语句默认是自动提交的，因此您应该小心使用动态 SQL 方法的任何 DDL 语句，以防在执行 DDL 之前有一些 DML（需要使用 TCL 显式提交）存储过程/函数。
You can use any of the following dynamic sql approach to execute a DDL statement from a pl/sql block.
您可以使用以下任何一种动态 sql 方法从 pl/sql 块执行 DDL 语句。
1) Execute immediate
1) 立即执行
2) DBMS_SQL package
2) DBMS_SQL 包
3) DBMS_UTILITY.EXEC_DDL_STATEMENT (parse_string IN VARCHAR2);
3) DBMS_UTILITY.EXEC_DDL_STATEMENT (parse_string IN VARCHAR2);
Hope this answers your question with explanation.
希望这可以通过解释来回答您的问题。
