我想从三个表中删除一个月以前的数据,可总是把数据全部删除。各位看看是哪里的问题
create or replace procedure del_tab as
begin
select max(clbh) into cc from xxfl where jgsj<=add_months(sysdate,-1);
execute immediate 'delete from xxfl where clbh<:x' using cc;
execute immediate 'delete from tpxx where tpid<:x' using cc;
execute immediate 'delete from bjxx where clbh<:x' using cc;
commit;
end;
create or replace procedure del_tab as
begin
select max(clbh) into cc from xxfl where jgsj<=add_months(sysdate,-1);
execute immediate 'delete from xxfl where clbh<:x' using cc;
execute immediate 'delete from tpxx where tpid<:x' using cc;
execute immediate 'delete from bjxx where clbh<:x' using cc;
commit;
end;
直接
delete from xxfl where clbh <cc;
delete from tpxx where tpid <cc;
delete from bjxx where clbh <cc;
存储过程里是自动绑定变量的
dtDate varchar2(20);
begin
select sysdate - 1 into dtDate from dual;
delete from xxfl where clbh < to_date(dtDate, 'yyyy-mm-dd hh24:mi:ss');
delete from tpxx where tpid < to_date(dtDate, 'yyyy-mm-dd hh24:mi:ss');
delete from bjxx where clbh < to_date(dtDate, 'yyyy-mm-dd hh24:mi:ss');
commit;
end;
如果一个月前的数据中有个clbh是表中最大的clbh,那么用你的方法自然会把所有的数据删除。
你应该这么删除:
CREATE OR REPLACE PROCEDURE DEL_TAB AS
BEGIN DELETE FROM XXFL
WHERE CLBH IN
(SELECT CLBH FROM XXFL WHERE JGSJ <= ADD_MONTHS(SYSDATE, -1)); DELETE FROM TPXX
WHERE TPID IN
(SELECT CLBH FROM XXFL WHERE JGSJ <= ADD_MONTHS(SYSDATE, -1)); DELETE FROM BJXX
WHERE CLBH IN
(SELECT CLBH FROM XXFL WHERE JGSJ <= ADD_MONTHS(SYSDATE, -1));
COMMIT;
END;