oracle中的存储过程

使用场景：

1.使用存储过程来定时抽取数据（有传入参数）

create or replace procedure my_procedure_test(
  startDate in varchar2,--传入时间('2019-05-05')
  stopDate in varchar2,
  v_salerate_temp in varchar2,
  v_maintainrate_temp in varchar2
)
as
  v_salerate float := to_number(v_salerate_temp);--转为float类型
  v_maintainrate float := to_number(v_maintainrate_temp);
begin
insert into my_table_test(a1,a2,a3)
select b1,b2,b3 from table;
end;

2.使用存储过程来初始化数据（无传入参数）

create or replace procedure dg_initbusinesspersonAmount
  as
  o_count int;
  beginDate date;
  begin
  o_count := 0;
  --记录表是否有数据
  select COUNT(1) into o_count from dg_businesspersonAmountLog;
  --历史数据初始化
  if o_count = 0 then
begin
	  beginDate := to_date('2017-01-02 00:00:00','yyyy-mm-dd HH24:mi:ss');

    while beginDate+6 < sysdate loop
      dg_businesspersonAmount(to_char(beginDate,'yyyy-mm-dd'),to_char(beginDate+6,'yyyy-mm-dd'));
      --每周
      beginDate := beginDate + 7;
      --dbms_output.put_line(beginDate);
  end loop;

  beginDate := to_date('2017-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss');

  while add_months(beginDate,1)-1 < sysdate loop
      dg_businesspersonAmount(to_char(beginDate,'yyyy-mm-dd'),to_char(add_months(beginDate,1)-1,'yyyy-mm-dd'));
      --每月
      beginDate := add_months(beginDate,1);
      --dbms_output.put_line(beginDate);打印
  end loop;
end;

　　

转载于:https://www.cnblogs.com/TSHHENLIHAI/p/10825955.html