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;