我可以编写一个从多个表中删除的过程吗？

all the procedure help I find is used for select purposes only.

我找到的所有程序帮助仅用于选择目的。

can I write a table to truncate several tables?

我可以写一个表来截断几个表吗?

similar to (but this does NOT work)

类似于(但这不起作用)

CREATE PROCEDURE clearall()
  BEGIN
    truncate tallgrrl.auth;
    truncate tallgrrl.factory;
    truncate tallgrrl.farm;
    truncate tallgrrl.player;
    truncate tallgrrl.timer;
  END;

7 个解决方案

#1

truncate might not work if you have relationships with that table in that case you need to either

如果您与该表有关系,truncate可能无效,在这种情况下您需要

use delete

drop the relationship truncate the table and recreate the relationship again

删除关系会截断表并再次重新创建关系

#2

Absolutely. One of the purposes of stored procedures is to encapsulate logic/multiple operations.

绝对。存储过程的目的之一是封装逻辑/多个操作。

#3

I don't know why your TRUNCATE isn't working. I have stored procs that TRUNCATE tables. What DB platform are you using?

我不知道为什么你的TRUNCATE不起作用。我已经存储了TRUNCATE表的过程。您使用的是什么数据库平台?

Does your TRUNCATE work outside of the SP? As SQLMenace mentions, you cannot use TRUNCATE on tables with FK dependencies.

你的TRUNCATE是否在SP之外工作?正如SQLMenace所提到的,您不能对具有FK依赖性的表使用TRUNCATE。

#4

Try switching to "delete from [table name]", because truncate might not work due to rights issue.

尝试切换到“从[表名]中删除”,因为由于权限问题,截断可能不起作用。

#5

You should be able to. Perhaps you are doing your truncations in the wrong order (and violating the integrity constraints. e.g., you can't delete a parent until there are no children hanging off of it.

你应该能够。也许你正在以错误的顺序进行截断(并且违反完整性约束。例如,在没有孩子悬挂它之前你不能删除父级。

#6

You may need to change the order of the truncations so that foreign keys are not truncated before the data that references them.

您可能需要更改截断的顺序,以便在引用它们的数据之前不截断外键。

#7

What error(s) do you get?

你得到了什么错误?

Besides the aforementioned FK potential issue, depending on the rights of the user executing the proc you might not have permissions to truncate.

除了前面提到的FK潜在问题,根据执行proc的用户的权限,您可能没有截断权限。

#1