项目有个需求需要删除3个月之前的数据.我想写一个bat定时去调一个存储过程.过程我已经写好了,但是需要把时间作为入参传进去.bat中怎么取过去的时间大虾们知道么?还有bat中怎么传储存过程的入参啊!或者在储存过程中直接把时间取到也是个办法,请问大家有啥好办法么!储存过程如下
CREATE OR REPLACE PROCEDURE MBAPP.Delbigtab (
p_condition IN VARCHAR2,
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
n_delete NUMBER := 0;
BEGIN
WHILE 1 = 1
LOOP
EXECUTE IMMEDIATE 'delete /*+index(eai_audit EAI_AUDIT_CREAT_INDEX)*/ from '
|| audit
|| ' where '
|| p_condition
|| ' and rownum <= :rn'
USING 100; IF SQL%NOTFOUND
THEN
EXIT;
ELSE
n_delete := n_delete + SQL%ROWCOUNT;
END IF; COMMIT;
END LOOP; COMMIT;
DBMS_OUTPUT.PUT_LINE ('Finished!');
DBMS_OUTPUT.PUT_LINE ('Totally ' || TO_CHAR (n_delete)
|| ' records deleted!'
);
END;
/
CREATE OR REPLACE PROCEDURE MBAPP.Delbigtab (
p_condition IN VARCHAR2,
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
n_delete NUMBER := 0;
BEGIN
WHILE 1 = 1
LOOP
EXECUTE IMMEDIATE 'delete /*+index(eai_audit EAI_AUDIT_CREAT_INDEX)*/ from '
|| audit
|| ' where '
|| p_condition
|| ' and rownum <= :rn'
USING 100; IF SQL%NOTFOUND
THEN
EXIT;
ELSE
n_delete := n_delete + SQL%ROWCOUNT;
END IF; COMMIT;
END LOOP; COMMIT;
DBMS_OUTPUT.PUT_LINE ('Finished!');
DBMS_OUTPUT.PUT_LINE ('Totally ' || TO_CHAR (n_delete)
|| ' records deleted!'
);
END;
/
当前日期三个月前
7/9/2009 4:13:04 AMLAST_DAY(ADD_MONTHS(sysdate,-3))
7/31/2009 4:19:09 AMLAST_DAY(ADD_MONTHS (sysdate,-4))+1
7/1/2009 4:19:17 AM去掉时间:
TRUNC(sysdate)
10/9/2009
CREATE OR REPLACE PROCEDURE MBAPP.Delbigtab
AS
PRAGMA AUTONOMOUS_TRANSACTION;
n_delete NUMBER := 0;
BEGIN
WHILE 1 = 1
LOOP
EXECUTE IMMEDIATE 'delete /*+index(eai_audit EAI_AUDIT_REQUEST_TIME_IDX)*/ from '
|| 'EAI_AUDIT'
|| ' where '
|| ADD_MONTHS(sysdate,-1)
|| ' and rownum <= :rn'
USING 100; IF SQL%NOTFOUND
THEN
EXIT;
ELSE
n_delete := n_delete + SQL%ROWCOUNT;
END IF; COMMIT;
END LOOP; COMMIT;
DBMS_OUTPUT.PUT_LINE ('Finished!');
DBMS_OUTPUT.PUT_LINE ('Totally ' || TO_CHAR (n_delete)
|| ' records deleted!'
);
END;
/
直接调用call MBAPP.delBigTab();
但是报错啊:call MBAPP.delBigTab()
*
ERROR 位于第 1 行:
ORA-00920: invalid relational operator
ORA-06512: at "MBAPP.DELBIGTAB", line 8
请各位大虾帮忙看下哈
这个地方的时间取值是和表中哪个字段匹配呢?假设有一字段名为update_date
则 update_date = ADD_MONTHS(sysdate,-1)