截断存储过程中的表

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?

Thanks, Klas Mellbourn

谢谢,Klas Mellbourn

4 个解决方案

#1

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';

#2

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.

该语句失败,因为存储的proc正在执行DDL,并且DDL的某些实例可能使存储的proc无效。通过使用execute immediate或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在执行之前和之后都发出隐式提交的事实。

#3

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块那样直接运行DDL语句,因为PL / SQL不支持后期绑定,它只支持编译时绑定,这对DML来说很好。因此,为了克服这类问题,oracle提供了一种动态SQL方法,可用于执行DDL语句。动态sql方法是在运行时解析和绑定sql字符串。此外,您应该记住DDL语句默认是自动提交因此,如果您在执行DDL之前有一些DML(需要使用TCL显式提交),则应该注意使用动态SQL方法的任何DDL语句。存储过程/功能。

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.

希望这能解释你的问题。

#4

try the below code

试试下面的代码

execute immediate 'truncate table tablename' ;

#1