使用pl/sql简化动态删除多个表中的数据

发布于 2024-12-13 07:31:06 字数 2964 浏览 0 评论 0原文

我需要编写一个oracle pl/sql 过程来从多个表中删除数据。评选标准每个表要删除的数据都不同。

为了简单起见，假设我有 3 个表，每个表都会有一个选择查询。即

select id from table_a where product_type='A';
select name from table_b where category='student';
select phone_name from table_c where name='nokia';

几个规则

- I can only delete 1000 records at a time for each table. 
- I should issue a commit after every 1000 records
- Each row is processed first before deleting (in all tables)

最简单的解决方案是有这样的东西

commit_limit:=1000;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,id from table_a where product_type='A') loop

    Delete from table_a where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued. 
    end if;
End loop;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,name from table_b where category='student') loop

    Delete from table_b where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued. 
    end if;
End loop;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,phone_name from table_c where name='nokia') loop

    Delete from table_c where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued and log value of 'recordsDeleted'. 
    end if;
End loop;

> log to file total records deleted.

正如你所看到的，有很多重复。我想使用一个我可以的程序告诉它表名和用作选择标准的查询。

我认为，如果没有选择标准，并且在删除之前没有对每一行进行处理，那么构建动态 sql 语句并使用立即执行来执行删除就是一个简单的情况。我想要一个像下面这样的过程/函数，并为每个表调用它。（即上述示例的 3 次）

function delete_by_colid (table_name in varchar(35), column_name in varchar(150), select_criteria in varchar(200)) return number
Is
begin
    counter:=0;
    recordsDeleted:=0;

    For i in (<<<select_criteria>>>) loop

        Delete from <<<table_name>>> where <<<column_name>>>=i.rowid;
        recordsDeleted:=SQL%rowcount;
        counter:=counter++;

        if(counter>=commit_limit) then
            commit;
            counter:=0;
            >log to file that commit has been issued and log value of 'recordsDeleted'. 
        end if;
    End loop;
    return recordsDeleted;
End;

我遇到的问题是不确定以下内容

如何在 for 循环中包含 select_criteria？显式光标可以代替吗？我将如何动态定义它？
当它们传递给函数时，如何确定哪些大小适合 table_name，尤其是 select_criteria？
如果我使用立即执行来执行动态构建的sql语句。如何获取 sql%rowcount 的值？

提前致谢

编辑

我正在处理的流程将是夜间运行的批处理作业的一部分。批处理作业将处理数百万行，这意味着可能需要几个小时才能完成。

还有其他更重要的守护进程将同时运行。这意味着需要防止删除行的过程因删除而锁定表/行。为此，我们打算在每次提交后让进程休眠几秒钟，以允许其他进程继续。这意味着我不能只使用单个“DELETE”语句进行删除。

原文

I need to write an oracle pl/sql procedure to delete data from multiple tables. The selection criteria
for data to be deleted is different for each table.

To keep it simple, assuming i have 3 tables, there will be one selection query for each table. i.e.

select id from table_a where product_type='A';
select name from table_b where category='student';
select phone_name from table_c where name='nokia';

A couple of rules

- I can only delete 1000 records at a time for each table. 
- I should issue a commit after every 1000 records
- Each row is processed first before deleting (in all tables)

The easiest solution is to have something like this

commit_limit:=1000;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,id from table_a where product_type='A') loop

    Delete from table_a where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued. 
    end if;
End loop;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,name from table_b where category='student') loop

    Delete from table_b where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued. 
    end if;
End loop;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,phone_name from table_c where name='nokia') loop

    Delete from table_c where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued and log value of 'recordsDeleted'. 
    end if;
End loop;

> log to file total records deleted.

As you can see, there is a lot of repetition. I would like to use a procedure where i can
tell it the table name and the query to use as the selection criteria.

I think if there was no selection criteria and there was no processing for each row before the deletion it would be a simple case of building a dynamic sql statement and use execute immediate to perform the deletion.
I want to have a procedure/function like this one below and call it for each table. (i.e. 3 times for the above examples)

function delete_by_colid (table_name in varchar(35), column_name in varchar(150), select_criteria in varchar(200)) return number
Is
begin
    counter:=0;
    recordsDeleted:=0;

    For i in (<<<select_criteria>>>) loop

        Delete from <<<table_name>>> where <<<column_name>>>=i.rowid;
        recordsDeleted:=SQL%rowcount;
        counter:=counter++;

        if(counter>=commit_limit) then
            commit;
            counter:=0;
            >log to file that commit has been issued and log value of 'recordsDeleted'. 
        end if;
    End loop;
    return recordsDeleted;
End;

The problem i have is am not sure of the following

How do i include the select_criteria in the for loop? Would an explicit cursor work instead? How would i define it dynamically?
How do i decide which sizes are suitable for table_name and especially the select_criteria when they are passed in to the function?
If i use execute immediate to execute a dynamically built sql statement. How do i get the value of sql%rowcount?

Thanks in advance

Edit

The process that i am working on will be part of a batch job that will run overnight. The batch job will be processing millions of rows which means it could take several hours to complete.

There are other more important deamon processes that will be running at the same time. This means the process that deletes the rows needs to be prevented from locking out tables/rows as a result of the deletion. To do this, we intend to send the process to sleep for a few seconds after every commit to allow the other processes to continue. This means i cant just delete using a single 'DELETE' statement.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄离 2024-12-20 07:31:06

你的伪代码的性能会非常糟糕。它还可能抛出 ORA-1555 错误。

更好的方法是：

delete from table_a
where product_type='A'
/
delete from table_b 
where category='student' 
/
delete from table_c 
where name='nokia'
/

我不明白为什么你认为需要简化。

“批处理作业将处理数百万行，这意味着它
可能需要几个小时才能完成。”

是的，但是在具有经过良好调整的访问路径的现代服务器上运行可能需要几分钟。基准测试就是全部。

" 这意味着需要阻止删除行的过程
避免因删除而锁定表/行。”

，只会锁定受影响的行。如果您有其他进程需要使用相关行，您可能不应该在首位。

“为此，我们打算让进程休眠几秒钟
每次提交后允许其他进程继续。”

最大限度地减少删除处理所消耗的资源的最佳方法是以高效的方式执行它。这意味着运行纯 SQL 语句而不是复杂的 PL/SQL 循环。基于的处理比 RBAR 进程要快得多、更轻，

但如果您确实想要一份占用桌面的工作，而不是一次完成任务，请执行以下操作：

delete from table_a
where product_type = 'A'
and rownum <= 1000;
log.write('records deleted from table_a = '||SQL%rowcount);
commit;

这是一个事实。轮询操作系统守护进程或cron 或其他东西，不要使用 DBMS_LOCK.SLEEP() 来挂起它：这只是无缘无故地占用 CPU

我的建议仍然是你应该对此进行基准测试。在开始过度设计之前，您需要证明最简单的方法实际上会导致系统资源的不可接受的消耗。

Your pseudo-code would perform really badly. It is also likely to hurl ORA-1555 errors.

A better approach would be:

delete from table_a
where product_type='A'
/
delete from table_b 
where category='student' 
/
delete from table_c 
where name='nokia'
/

I fail to see why you think that needs simplifying.

"The batch job will be processing millions of rows which means it
could take several hours to complete."

Yes but running on a modern server with well-tuned access paths it could take minutes. Benchmarking is all.

" This means the process that deletes the rows needs to be prevented
from locking out tables/rows as a result of the deletion."

DML in Oracle doesn't lock tables, only affected rows. If you have other processes which need to use the rows in question you probably shouldn't be deleting them in the first place.

"To do this, we intend to send the process to sleep for a few seconds
after every commit to allow the other processes to continue."

The best way to minimize the resources consumed by your deletion processing is to execute it in an efficient fashion. This means running a pure SQL statement rather than an elaborate PL/SQL loop. Set-based processing is considerably faster and lighter than RBAR processes. That is a fact.

But if you really want to have a job which nibbles away at the table rather than finishing the task in a single bite, do this:

delete from table_a
where product_type = 'A'
and rownum <= 1000;
log.write('records deleted from table_a = '||SQL%rowcount);
commit;

Have this run by a polling OS daemon or cron or something. Don't use DBMS_LOCK.SLEEP() to suspend it: that's juss tying up a CPU for no good reason.

My advice remains that you should benchmark this. You need to prove that the simplest approach will actually cause an unacceptable drain on system resources before you embark on an over-engineering exercise.

回复收藏 0 原文