Oracle之带参存储过程（存储过程中for循环调用存储过程）

--带参存储过程
create or replace procedure testdate(v in number) is
i number;
begin
i:=v;select EN_ID,EN_NAME,ASA_CODE,ASA_NAME,0 AS_YESTERDAY,i DATEKEEY
from testtable where EN_ID not in (select EN_ID from test2table where datekeey = i)
union
select EN_ID,EN_NAME,ASA_CODE,ASA_NAME,AS_YESTERDAY,DATEKEEY
from test2table
where datekeey = i;
commit;
end testdate;

--存储过程中for循环调用存储过程，
create or replace procedure testdate2 is
Cursor datekeey is select to_number(to_char(datekeey,'yyyyMMdd')) datekeey from (
          select date'2017-01-01' + (rownum - 1)  datekeey
          from  dual connect by rownum <= (date'2018-09-18' - date'2017-01-01' + 1)
); --遍历日期的起始
i number;
begin
for i in datekeey LOOP
  begin
    testdate(i.datekeey);
    --dbms_output.put_line(i.datekeey);
  end;
end LOOP;
commit;
end testdate2;

 

转载于:https://www.cnblogs.com/schoolbag/p/9674776.html