Oracle之使用procedure及dbms_job进行定时批量造数据

1  环境说明

• Oracle：11g；
• PLSQL Developer ：12.0.4.1826；

2  存储过程基本语法

2.1  基本结构

CREATE OR REPLACE PROCEDURE  存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
    --在这里编写业务逻辑
END 存储过程名字
           

     注意：1、赋值使用【 :=】；

                2、每一个完整的语句后面一定要存在【;】。

2.2  select赋值

 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)；

赋值的时候，可以考虑min()、max()、count()等内置函数。

BEGIN
      SELECT min(col1),max(col2) into 变量1,变量2 FROM typestruct where xxx;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
           

 2.2  if 语句

IF i <=10 THEN
    BEGIN 
       --编写业务逻辑
       i := i+1 ;
    END;
  END IF;
           

 2.3  while 循环

WHILE i < 10  LOOP
  BEGIN
    --编写业务逻辑
    i := i+1;
  END;
END LOOP;
           

3、dbms_job基本语法

DECLARE
  X NUMBER; --自定义变量
BEGIN
  SYS.DBMS_JOB.SUBMIT( 
    job => X ,
    what => 'procedure(参数1，参数2);',  --执行的存储过程, ';'不能省略 
    next_date => to_date('22/12/2009 06:00:00','dd/mm/yyyy hh24:mi:ss'), --下次执行时间
   interval  => 'trunc(SYSDATE+1)+1/4',  --每次间隔时间，interval以天为单位
  );
COMMIT;
END;
           

 4、批量定时造数据的实例

• 编写存储过程：从当前表中获取最后（即id值最大）一条数据的id,然后顺序递增往里面添加制定条数rangeNum的数据：

create or replace procedure testproc (rangeNum in int) is
begin
  declare
  numb int;
  i int;
  begin
    select max(t.id) into numb from test_table t;
    i:= 1;
    while i<=rangeNum loop
    insert into system_log
      (id, timestamp, description, user)
    values
      (numb+i,to_timestamp(to_char(systimestamp,'DD-MON-RR HH.MI.SS.FF AM')),
      'this is a test','austin');
      i := i+1; 
     end loop;
  end;
end testproc;
           

     执行存储过程的语法如下：

begin  
    testproc(10);
end;
           

• 编写定时任务：每分钟执行一次上面的存储过程，每次向表中插入10条数据

begin
  sys.dbms_job.change(job => 23,
                      what => 'testproc(10);',
                      next_date => to_date('23-07-2018 17:01:18', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'sysdate+1/1440');
  commit;
end;
           

     实际上在PLSql中，可以直接可视化新建dbms_job，根据实际情况填写红框中的三个值，然后再点击应用，就可以开始自动执行存储过程了。 

     说明：本次在定时任务中本来想尝试自动让每次执行的变量进行自定义变化，但没有成功，最后选择了常量；也许只有系统函数sysdate，sys_guid()等可以，但还没有试过。