截断存储过程中的表
当我在 Oracle shell 中运行以下命令时，它工作正常，
truncate table table_name
但是当我尝试将其放入存储过程时，
CREATE OR REPLACE PROCEDURE test IS
BEGIN
truncate table table_name;
END test;
/
它会失败，并显示
ERROR line 3, col 14, ending_line 3, ending_col 18, Found 'table', Expecting: @ ROW or ( or . or ; :=
Why?
When I run the following in an Oracle shell it works fine
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
Oracle PL/SQL 中的所有 DDL 语句都应在语句之前使用 Execute Immediate。 因此你应该使用：
All DDL statements in Oracle PL/SQL should use Execute Immediate before the statement. Hence you should use:
除了立即执行之外，您还可以使用
DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE tablename;');
该语句失败，因为存储过程正在执行 DDL，并且 DDL 的某些实例可能会使存储过程无效。 通过使用立即执行或 exec_ddl 方法，DDL 是通过未解析的代码实现的。
执行此操作时，您需要注意 DDL 在执行之前和之后都会发出隐式提交。
As well as execute immediate you can also use
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.
When doing this you neeed to look out for the fact that DDL issues an implicit commit both before and after execution.
尝试下面的代码
try the below code
您应该知道，不可能像从 PL/SQL 块运行 DML 那样直接运行 DDL 语句，因为 PL/SQL 不直接支持后期绑定，它只支持编译时绑定，这对于 DML 来说很好。 因此，为了克服这类问题，oracle提供了一种动态SQL方法，可以用来执行DDL语句。动态sql方法是在运行时解析和绑定sql字符串。
另外，您应该记住，DDL 语句默认情况下是自动提交的，因此您应该小心使用动态 SQL 方法的任何 DDL 语句，以防在执行 DDL 之前有一些 DML（需要使用 TCL 显式提交）。存储过程/函数。
您可以使用以下任何动态 sql 方法从 pl/sql 块执行 DDL 语句。
1) 立即执行
2) DBMS_SQL 包
3) DBMS_UTILITY.EXEC_DDL_STATEMENT (parse_string IN VARCHAR2);
希望这能通过解释回答您的问题。
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.
You can use any of the following dynamic sql approach to execute a DDL statement from a pl/sql block.
1) Execute immediate
2) DBMS_SQL package
3) DBMS_UTILITY.EXEC_DDL_STATEMENT (parse_string IN VARCHAR2);
Hope this answers your question with explanation.