Oracle -在删除其他表的行之前检查行是否存在

Question

我想删除几行在Oracle数据库中，如果一个主表的ID已经存在:我尝试先做一个SELECT检查ID是否存在于主表中，然后根据其他表中的ID删除行:

DECLARE
PROJECT_ID PLS_INTEGER;
PROJECT_ID_TO_DELETE PLS_INTEGER;
BEGIN
PROJECT_ID := PROJECTS_SEQ.nextval; 
SELECT PROJ_ID INTO PROJECT_ID_TO_DELETE FROM PROJECTS WHERE PROJ_NAME_EN LIKE 'FD Project - CSB - A.1 - CFT - Final Reject of an Increase Movement - Seb';    
IF PROJECT_ID_TO_DELETE IS NULL THEN
DELETE FROM PROJECTS_ORG WHERE PROJ_ID = PROJECT_ID_TO_DELETE;
DELETE FROM PROJECTS WHERE PROJ_ID  = PROJECT_ID_TO_DELETE;
DELETE FROM MVT_ONGOING WHERE MVT_ONGOING_PROJ_ID  = PROJECT_ID_TO_DELETE;
DELETE FROM MVT_HISTORY WHERE MVT_HISTORY_PROJ_ID  = PROJECT_ID_TO_DELETE;
END IF;
Insert into PROJECTS (PROJ_ID, ...) values (PROJECT_ID, ...);
Insert into PROJECTS_ORG (PROJ_ID, ...) values (PROJECT_ID, ...);
Insert into MVT_ONGOING (MVT_ONGOING_PROJ_ID, ...);
Insert into MVT_HISTORY (PROJ_ID, ...) values (PROJECT_ID, ...);
END;

我得到一个错误"no data found">

你能帮我找到最优的解决方案吗?

感谢

Answer 1

像这样:

DECLARE
project_id_new        PLS_INTEGER;
project_id_to_delete  PLS_INTEGER;
BEGIN
BEGIN
SELECT proj_id
INTO project_id_to_delete
FROM projects
WHERE proj_name_en LIKE 'blabla';
DELETE FROM projects_org WHERE proj_id = project_id_to_delete;
DELETE FROM projects     WHERE proj_id = project_id_to_delete;
DELETE FROM mvt_ongoing  WHERE mvt_ongoing_proj_id = project_id_to_delete;
DELETE FROM mvt_history  WHERE mvt_history_proj_id = project_id_to_delete;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
project_id_new := seq.nextval;

INSERT INTO projects (project_id)     VALUES (project_id_new);
INSERT INTO projects_org (project_id) VALUES (project_id_new);
END;

它是做什么的?

• 尝试查找PROJ_ID
• 如果找到，则执行DELETEs
  • 你不必使用IF;如果它被发现了，它就是已知的。如果没有找到，抛出异常
• ，引发NO_DATA_FOUND异常，处理
  • 我选择不做，你可能想做点别的

插入(如您所评论的，您总是想要这样做):将删除部分包含到自己的BEGIN-EXCEPTION-END块中，以便处理可能的no_data_found错误。然后，找到project_id_new值(例如，使用序列)并在INSERT语句中使用它。