项目有个需求需要删除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;
/
解决方案 »
- 人事系统要查询某个时刻的部门人员数
- 三张表联查的结果分页?
- oracle锁的问题
- 创建表空间的问题
- 几个新手小问题,请教一下~
- "未找到 Oracle 客户端和网络组件..." 这个问题是什么原因造成的???100分急盼高手回答!!!
- 初学者:最近,做一个产品信息管理系统,asp+oracle,每个产品的参数信息有50多个,如何建表,1:把这些信息都放在一张表中2:以编号为主
- 如何在一个数据库中建立两个实例(ORACLE 8.17)
- 一个很初级的问题,我用sqlserver导入oracle的表为什么在sqlplus中查不到?
- 一个n难的sql,大虾来挑战
- PL/SQL问题
- SOS;ODP.NET 第二个引用游标没有行
当前日期三个月前
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)