我们如何在特定时间后在PL SQL中自动删除表行 [英] How Can We Delete A Table Row Automatically After a Specific Time in PL SQL

问题描述

我的Tow表具有相同的列，如下所示:

I have Tow table with Identical Columns Given Below:

表1 PAYROLLFILE:

Table-1 PAYROLLFILE:

表2 TEMP_PAYROLLFILE:

Table-2 TEMP_PAYROLLFILE:

我已经编写了PL SQL函数，该函数将表PAYROLLFILE中的重复行插入到TEMP_PAYROLLFILE中，并从表PAYROLLFILE中删除原始行.因此，简而言之，表TEMP_PAYROLLFILE为从表PAYROLLFILE中删除的数据保留备份.

I have written a PL SQL Function, which inserts a duplicate row from Table PAYROLLFILE to TEMP_PAYROLLFILE and remove the original row from Table PAYROLLFILE.So in short, Table TEMP_PAYROLLFILE is keeping a backup for deleted data from Table PAYROLLFILE.

这是PL SQL功能代码:

Here is the PL SQL Function Code:

FUNCTION Remove_transaction_by_id(employee_id NUMBER)
RETURN CLOB
AS
  cnt INT;
BEGIN
    INSERT INTO temp_payrollfile
    SELECT *
    FROM   payrollfile
    WHERE  empid = employee_id;

    DELETE FROM payrollfile
    WHERE  empid = employee_id;

    SELECT Count(*)
    INTO   cnt
    FROM   payrollfile;

    COMMIT;

    RETURN '<result><status>success</status> <row>'
           ||cnt
           || '</row></result>';
EXCEPTION
  WHEN OTHERS THEN
             RETURN '<result><status>Error</status></result>';
END remove_transaction_by_id; 

但是，我想做更多的事情，我要在特定时间段结束后自动从表TEMP_PAYROLLFILE 中删除备份行 行.我是否需要触发器.我对此还很陌生，以前从未做过此类工作.如果有人知道这种技术，那么我将非常感谢您的帮助.请告诉我是否需要更多详细信息.谢谢

However, I want to do more than that, I want to Delete Backup rows Automatically from Table TEMP_PAYROLLFILE after a specific time period is over fort that specific row.Do i need triggers.I am new on that and never done this type of work before.If anyone know this kind of technique, then i would really appreciate your help.please let me know if further details required.Thanks

更新:

我已经写了这份工作来清理旧的行:行得通吗?

I have write this job to cleanup old rows: will it work?

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'oe.REMOVE_TEMP_PAYROLLFILE_JOB',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS('oe',
                            'TEMP_PAYROLLFILE'); 
                            delete from TEMP_PAYROLLFILE where RECORDDATE < sysdate-1;
                            END;',
   start_date           => '20-JAN-16 1.00.00AM US/Pacific',
   repeat_interval      => 'FREQ=DAILY', 
   end_date             => '25-JAN-16 1.00.00AM US/Pacific',
   enabled              =>  TRUE,
   comments             => 'Gather table statistics');
END;

推荐答案

您需要的是定期清理作业.使用

What you need here is a periodic cleanup job. Using DBMS_SCHEDULER you can configure a job that does this repeatedly:

delete from TEMP_PAYROLLFILE where recorddate < sysdate-60;

删除超过60天的记录.

to delete records that are more than 60 days old.

这些是有关如何创建的一些示例预定的工作.如下所示，job_action部分接受一个Pl/SQL脚本，您可以在其中放置清理逻辑.

These are some examples on how to create a scheduled job. As you see below, the job_action sections accepts a Pl/SQL script, in which you can place your cleanup logic.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'oe.my_job1',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                            ''sales''); END;',
   start_date           => '15-JUL-08 1.00.00AM US/Pacific',
   repeat_interval      => 'FREQ=DAILY', 
   end_date             => '15-SEP-08 1.00.00AM US/Pacific',
   enabled              =>  TRUE,
   comments             => 'Gather table statistics');
END;
/

这篇关于我们如何在特定时间后在PL SQL中自动删除表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！