英文:
Determine whether DROP TABLE will succeed
问题
Is it possible to determine whether DROP TABLE table_name operation will succeed without executing it?
我能否确定DROP TABLE table_name操作是否会成功而不执行它？
I tried to build function with autonomous transaction and rollback whether it is successful or not. It looked like this:
我尝试创建一个具有自主事务和回滚功能的函数，以确定它是否成功。它看起来像这样：
CREATE FUNCTION is_droppable RETURN BOOLEAN AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tablename';
ROLLBACK;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
This function was always dropping my table.
这个函数总是删除我的表格。
Indeed, Oracle notices that DROP is unROLLBACKable in their docs in several places.
确实，在Oracle的文档中有多个地方提到DROP是不可回滚的。
But I still want to be able to determine whether my table is droppable from inside of my code.
但我仍然希望能够从我的代码内部确定是否可以删除我的表格。
P.S. I think table is droppable when and only when other tables don't link to it. Maybe it is a possible way to solve my problem, but I'm here to avoid it. I look for an easier method.
附注：我认为只有当其他表格没有链接到它时，表格才能被删除。也许这是解决我的问题的可能方法，但我在这里是为了避免它。我正在寻找一种更简单的方法。
英文:
Is it possible to determine whether DROP TABLE table_name operation will succeed without executing it?
I tried to build function with autonomous transaction and rollback whether it is successfull or not. It looked like this:
CREATE FUNCTION is_droppable RETURN BOOLEAN AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tablename';
ROLLBACK;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
This function was always dropping my table.
Indeed, Oracle notices that DROP is unROLLBACKable in their docs in several places.
But I still want be able to determine whether my table is droppable from inside of my code.
P.S. I think table is droppable when and only when other tables don't link to it. Maybe it is a possible way to solve my problem, but I'm here to avoid it. I look for an easier method.
答案1
得分: 0
你可以使用以下内容进行翻译：
"您可以级联删除表，从而将表与引用它的所有约束一起删除。
或者，要查看引用表的外键：
选择cfk.*
从all_constraints cp
加入所有的约束cfk，cfk.r_constraint_name=cp.constraint_name
并且cfk.r_owner=cp.owner
where table_name=&table_name和constraint_type='P';
但一般来说，在存储过程的动态SQL中删除表不是一个好的做法。您有考虑到这些表可能会在其他存储过程中被引用，而这些存储过程会被反编译吗？"
英文:
You may drop table cascade constraints, thus the table will be dropped together with all the constraints referencing it.
Or, to see the foreign keys referencing a table:
select cfk.*
from all_constraints cp
join all_constraints cfk on cfk.r_constraint_name=cp.constraint_name
and cfk.r_owner=cp.owner
where table_name=&table_name and constraint_type='P';
But, generally, it's not good to drop tables in dynamic SQL in stored procedures. Have you thought that those tables may be referred in other stored procedures, which will decompile?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论