我们如何在特定时间后在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屋!