oracle PL/SQL EXECUTE IMMEDIATE inside LOOP（截断模式中所有表的过程）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27258004/
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
PL/SQL EXECUTE IMMEDIATE inside LOOP (procedure to truncate all tables in schema)
提问by Karol Chudzik
I need to create procedure which will delete all data from tables in one schema. I try something like that
我需要创建一个过程，该过程将从一个模式中的表中删除所有数据。我尝试这样的事情
CREATE OR REPLACE PROCEDURE CLEAR_ALL
IS
sql_truncate VARCHAR2(50);
cursor c1 is
SELECT table_name
FROM all_tables
WHERE owner = 'KARCHUDZ_S';
BEGIN
sql_truncate := 'TRUNCATE TABLE :text_string';
FOR table_name in c1
LOOP
EXECUTE IMMEDIATE sql_truncate USING table_name;
END LOOP;
END CLEAR_ALL;
But it gives me two errors which i cannot understand and fix.
但它给了我两个我无法理解和修复的错误。
Error(13,7): PL/SQL: Statement ignored
Error(13,44): PLS-00457: Statment must be type of SQL <-- (This error i had to translate, cause i use University Oracle 11g base which have Polish lang)
错误（13,7）：PL/SQL：语句被忽略
错误（13,44）：PLS-00457：语句必须是 SQL <-- 类型（这个错误我不得不翻译，因为我使用具有波兰语语言的大学 Oracle 11g 基础）
回答by Ascalonian
Why not just generate the statement and call it, like this?
为什么不直接生成语句并调用它，像这样？
CREATE OR REPLACE PROCEDURE CLEAR_ALL
IS
vs_statement VARCHAR2(100);
cursor c1 is
SELECT table_name
FROM all_tables
WHERE owner = 'KARCHUDZ_S';
BEGIN
FOR table_rec in c1
LOOP
vs_statement := 'TRUNCATE TABLE ' || table_rec.table_name;
EXECUTE IMMEDIATE vs_statement;
END LOOP;
END CLEAR_ALL;
回答by Allan
You can't use bind variables (i.e. your usingclause) as a placeholder for an object name. If you could, you wouldn't need to use dynamic SQL in the first place. You'll have to use concatenation or substitution instead:
您不能使用绑定变量（即您的using子句）作为对象名称的占位符。如果可以，您一开始就不需要使用动态 SQL。您必须改用串联或替换：
CREATE OR REPLACE PROCEDURE CLEAR_ALL
IS
sql_truncate CONSTANT VARCHAR2(50) := 'TRUNCATE TABLE [text_string]';
cursor c1 is
SELECT table_name
FROM all_tables
WHERE owner = 'KARCHUDZ_S';
BEGIN
FOR row in c1
LOOP
EXECUTE IMMEDIATE replace(sql_truncate, '[text_string]', row.table_name);
END LOOP;
END CLEAR_ALL;
