查看t_tasks表中某日期的数据里 类型是order_view,状态是FINISH的内容,日期是输入参数。
CREATE OR REPLACE PROCEDURE put_task_id(sdate in date) IS taskid NUMBER(19); TYPE cur IS REF CURSOR; cur_01 cur; BEGIN DBMS_OUTPUT.ENABLE (buffer_size=>null);--设置缓冲区内存 OPEN cur_01 FOR SELECT t.id FROM t_tasks t WHERE t.type='order_view' AND t.status='FINISH' AND to_char(t.create_time)=sdate; LOOP FETCH cur_01 INTO taskid; EXIT WHEN cur_01%NOTFOUND; dbms_output.put_line(taskid); END LOOP; CLOSE cur_01; END; /调用存储过程
--调用存储过程 DECLARE m1 VARCHAR2(32):='2020-06-08'; BEGIN put_task_id(m1); END; /批量插入测试数据的代码块
declare i integer; begin i := 1; loop insert into t_tasks values(seq_t_task.nextval,'order_view','milldleTableManager.insertorderView','2681',to_timestamp('2018-12-25 23:23:23.112324233','yyyy-mm--dd hh24:mi:ss.ff'),sysdate,sysdate,sysdate,'FINISH',0); i := i + 1; exit when i > 100; end loop; commit; END;输入日期、类型、状态三个参数,删除thorn_tasks表里对应的数据
CREATE OR REPLACE PROCEDURE delete_thorn_task(IN1 IN VARCHAR2,IN2 IN VARCHAR2,IN3 IN VARCHAR2) IS taskid NUMBER(19); TYPE cur IS REF CURSOR; cur_01 cur; BEGIN OPEN cur_01 FOR SELECT t.id FROM t_tasks t WHERE t.type=IN1 AND t.status=IN2 AND to_char(t.create_time,'yyyy-mm-dd')=IN3; LOOP FETCH cur_01 INTO taskid; EXIT WHEN cur_01%NOTFOUND; DELETE FROM t_tasks t WHERE t.id=taskid; END LOOP; COMMIT; CLOSE cur_01; END;调用存储过程
--调用存储过程 DECLARE m1 VARCHAR2(32):='order_view'; m2 VARCHAR2(32):='FINISH'; m3 VARCHAR2(32):='2018-12-25'; BEGIN delete_thorn_task(m1,m2,m3); END; /