ストレージ・プロシージャ3月までのデータのバックアップ
詳細
注意:パラメータto_dateは‘’を付ける(引用符)
注意:パラメータto_dateは‘’を付ける(引用符)
create or replace procedure logBigDataTrasfor
is
queryEndDate Date:=to_date(to_char(sysdate - interval '3' month,'yyyy-MM-dd'),'yyyy-MM-dd');
queryStartDate Date:=to_date(to_char(sysdate - interval '6' month,'yyyy-MM-dd'),'yyyy-MM-dd');
idLog CSSP_USER_ACCESS_LOG_V2.ID_LOG%type;
idCsspUser CSSP_USER_ACCESS_LOG_V2.id_cssp_user%type;
idCsspOrg CSSP_USER_ACCESS_LOG_V2.Id_Cssp_Org%type;
accessDate CSSP_USER_ACCESS_LOG_V2.ACCESS_DATE%type;
url CSSP_USER_ACCESS_LOG_V2.Url%type;
systemFlag CSSP_USER_ACCESS_LOG_V2.system_flag%type;
type cur_type_ref is ref cursor;
cur_data cur_type_ref;
sqlStr varchar2(2000);
totalCount number;
totalPages number;
i number;
v_startRecord number;
v_endRecord number;
errorCode number;
errorMsg varchar2(1000);
Begin
sqlStr:= 'select count(1) from CSSP_USER_ACCESS_LOG_V2 v2 where v2.access_date<=to_date('''||queryEndDate||''') and v2.access_date>= to_date('''||queryStartDate||''')';
execute immediate sqlStr into totalCount;
IF MOD(totalCount,500) = 0 THEN
totalPages := totalCount / 500;
ELSE
totalPages := totalCount / 500 + 1;
END IF;
i:=1;
for i in 1..totalPages LOOP
v_startRecord := (i - 1) * 500 + 1;
v_endRecord := i * 500;
sqlStr:= 'select tempFData.id_log,tempFData.id_cssp_user,tempFData.id_cssp_org,tempFData.access_date,tempFData.url,tempFData.system_flag from (select tempData.*,ROWNUM as rn from (select * from CSSP_USER_ACCESS_LOG_V2 v2 where v2.access_date<=to_date('''||queryEndDate||''') and v2.access_date>= to_date('''||queryStartDate||''') order by access_date desc) tempData where ROWNUM<='||v_endRecord||') tempFData where tempFData.rn >= '||v_startRecord;
open cur_data for sqlStr;
loop
fetch cur_data into idLog,idCsspUser,idCsspOrg,accessDate,url,systemFlag;
exit when cur_data%notfound;
insert into test_log_bak(id_log,id_cssp_user,id_cssp_org,access_date,url,system_flag)
values(idLog,idCsspUser,idCsspOrg,accessDate,url,systemFlag);
end loop;
close cur_data;
commit;
end LOOP;
EXCEPTION
WHEN dup_val_on_index THEN
--
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line(errorCode || ',' || errorMsg);
END ;