执行删除操作的步骤 [英] Procedure to perform delete operation

问题描述

我想编写一个执行以下操作的程序

I want to write a procedure to perform a delete for the following

i want to delete from x_table and y_table where id=select id from z_table where obj_id="1234" also at the same time i want to delete 
from user_table id = select id from z_table where obj_id="1234" and user_id != in (select user id from main_table)

in short i want to delete the rows from 3 tables x_table,y_table and user_table for the given obj_id and for user_table i want to alsocheck if user_id is not in other main_table

推荐答案

尝试此过程.请注意，这不是一个经过记事本认证的版本:)我没有在Oracle上编译它，因为我没有在个人计算机上编译它.看看是否有效.

Try this proc. Please not that this is a notepad certified version :) I havent compiled it on Oracle as I dont have it on my personal machine. See if it works..

CREATE
OR REPLACE PROCEDURE testproc(inputVar IN number)

AS

BEGIN

EXECUTE IMMEDIATE 'DELETE FROM TABLE_X WHERE ID = (SELECT ID FROM TABLE_Z WHERE OBJ_ID=:num)' USING IN inputVar;

EXECUTE IMMEDIATE 'DELETE FROM TABLE_Y WHERE ID = (SELECT ID FROM TABLE_Z WHERE OBJ_ID=:num)' USING IN inputVar;

EXECUTE IMMEDIATE 'DELETE FROM TABLE_USER WHERE ID = (SELECT ID FROM TABLE_Z WHERE OBJ_ID=:num) AND USER_ID NOT IN (SELECT USER_ID FROM MAIN_TABLE)' USING IN inputVar;

COMMIT;

EXCEPTION

WHEN
OTHERS

THEN

dbms_output.put_line
(SQLERRM);

END;

这篇关于执行删除操作的步骤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！