1,首先是oracle的存储过程,利用了cursor
sql 代码
- CREATE OR REPLACE PROCEDURE OTAC_ADM.pro_gettasksubscriber_data (
- msg_overtime IN INTEGER,
- subscriber_total IN INTEGER,
- task_id IN INTEGER,
- retry_subscriber_percent IN NUMBER,
- retry_num IN INTEGER,
- sub_groupid IN INTEGER,
- p_cursor OUT tasksubscriber_package.task_cursor
- )
- IS
- retry_task INTEGER;
- db_retry INTEGER;
- db_new INTEGER;
- db_task_num INTEGER;
- BEGIN
- SELECT COUNT (1)
- INTO db_task_num
- FROM otatask t
- WHERE t.statusid = 3;
- IF db_task_num > 0
- THEN
- retry_task := subscriber_total / db_task_num * retry_subscriber_percent;
- ELSE
- RETURN;
- END IF;
- SELECT COUNT (msgid)
- INTO db_retry
- FROM otatasksubscribers
- WHERE subscriberstatusid = 2
- AND (SYSDATE - msg_overtime / (24 * 3600) - sendingdate) > 0
- AND retrynum < retry_num
- AND sendingstat = 0
- AND taskid = task_id
- AND subgroupid = sub_groupid
- AND iscanretry = 1;
- IF db_retry > retry_task
- THEN
- db_retry := retry_task;
- END IF;
- db_new := subscriber_total / db_task_num - db_retry;
- OPEN p_cursor FOR
- -- retry subscriber of a task
- SELECT *
- FROM (SELECT msisdn, currentseq, msgid, retrynum, sendingdate,
- subscriberstatusid, patchlastseq, patchlastcode,
- iscanretry
- FROM otatasksubscribers
- WHERE subscriberstatusid = 2
- AND (SYSDATE - msg_overtime / (24 * 3600) - sendingdate) >
- 0
- AND retrynum < retry_num
- AND sendingstat = 0
- AND taskid = task_id
- AND iscanretry = 1
- AND subgroupid = sub_groupid
- ORDER BY currentseq DESC)
- WHERE ROWNUM <= db_retry
- UNION ALL
- -- new subscriber of a task
- SELECT *
- FROM (SELECT msisdn, currentseq, msgid, retrynum, sendingdate,
- subscriberstatusid, patchlastseq, patchlastcode,
- iscanretry
- FROM otatasksubscribers
- WHERE subscriberstatusid = 0
- AND taskid = task_id
- AND subgroupid = sub_groupid
- AND iscanretry = 1)
- WHERE ROWNUM <= db_new;
- EXCEPTION
- WHEN NO_DATA_FOUND
- THEN
- NULL;
- WHEN OTHERS
- THEN
- -- Consider logging the error and then re-raise
- RAISE;
- END pro_gettasksubscriber_data;
- /
2.再看看java的调用
java 代码
- CallableStatement proc = null;
- proc = DBConn
- .prepareCall("{ call pro_gettasksubscriber_data(?,?,?,?,?,?,?) }");
- proc.setInt(1, Start.MessageOverTime);
- proc.setInt(2, Start.runnergroupsize);
- proc.setInt(3, taskID);
- proc.setDouble(4, Start.retry_subscriber_percent);
- proc.setInt(5, Start.RetryTimes);
- proc.setLong(6, subGroupId);
- proc.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
- proc.execute();
- rs = (ResultSet) proc.getObject(7);
3,小心oracle的classes12.jar的bug会造成代码的一些问题。。。
参考