简单的含参存储过程的例子

简单的含参存储过程以及调用

查看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;
/