createorreplacepackage ciu_upload_pkg is
procedure upload_repetitive(tagnameIn invarchar2, subfieldnameIn invarchar2,
datefromIn indatedefaultnull);
procedure upload_single(tagnameIn invarchar2, subfieldnameIn invarchar2,
datefromIn indatedefaultnull);
procedure upload_repetitive_all(tagnameIn invarchar2, subfieldnameIn invarchar2,
datefromIn indatedefaultnull);
procedure append_lib_data(v_bib_id innumber,
v_tag invarchar2,
v_subfieldinvarchar2,
v_value invarchar2,
v_tagnum innumberdefault1,
v_subfieldnum innumberdefault1);
-- возвращает дату последнего изменения записи
function get_date_changed(bibIdIn innumber) returndate;
-- проверяет, удален ли ресурс
function is_deleted(bibIdIn innumber) returnnumber;
-- выгрузка списка баз
procedure upload_bases(dateFromIn indatedefaultnull);
procedure clear_data;
procedure upload_all_id;
-- выгрузка по условию для монографий
procedure upload_monograph(tagnameIn invarchar2, subfieldnameIn invarchar2,
datefromIn indatedefaultnull);
endciu_upload_pkg;
createorreplacepackagebody ciu_upload_pkg is
procedure upload_repetitive(tagnameIn invarchar2, subfieldnameIn invarchar2,
datefromIn indatedefaultnull)
is
b_tag number;
b_subfield number;
val varchar2(5000);
tagnum number;
subfieldnum number;
begin
for i in (selectdistinct f.bib_id
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = f.bib_id
and r.bib_id = t.bib_id
and r.bib_id = t.bib_id
and
(MARC.Get_Tag (f.bib_ID,'856', 1, 'z', 1, 0) isnotnull
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ertsv')
and (
MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%eru%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%erm%'
or MARC.Get_Tag (f.bib_ID,'900', 1, 'a', 1, 0) like'%учебн%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%ern%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erc'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ertm'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'eruz'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erul'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ers'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erco'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erts%'
)
and (datefromIn isnullor
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'a', 1, 0), 'yyyymmddhh24mi')
>datefromIn
or
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'y', 1, 0), 'yyyymmddhh24mi')
>datefromIn
)-- and t.bib_id=159565
)
loop
b_tag := 0;
tagnum := 1;
while b_tag = 0
loop
b_subfield:=0;
subfieldnum:=2;
selectdistinct MARC.Get_Tag (f.bib_ID,tagnameIn, tagnum,
subfieldnameIn, 1, 0) into val
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = i.bib_id
and r.bib_id = t.bib_id
and t.bib_id = f.bib_id;
if(val isnotnull) then
ciu_upload_pkg.append_lib_data(i.bib_id,tagnameIn,subfieldnameIn,val,
tagnum, 1);
while b_subfield = 0
loop
selectdistinct MARC.Get_Tag (f.bib_ID,tagnameIn, tagnum,
subfieldnameIn, subfieldnum, 0) into val
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = i.bib_id
and r.bib_id = t.bib_id
and t.bib_id = f.bib_id;
if val isnullthen
b_subfield := 1;
else
ciu_upload_pkg.append_lib_data(i.bib_id,tagnameIn,subfieldnameIn,val,
tagnum, subfieldnum);
subfieldnum := subfieldnum + 1;
endif;
endloop;
tagnum := tagnum + 1;
else b_tag := 1;
endif;
endloop;
endloop;
end;
procedure upload_single(tagnameIn invarchar2, subfieldnameIn invarchar2,
datefromIn indatedefaultnull)
is
val varchar2 (4000);
begin
for ii in (
selectdistinct f.bib_id
fromdbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = f.bib_id
and r.bib_id = t.bib_id
and
(MARC.Get_Tag (f.bib_ID,'856', 1, 'z', 1, 0) isnotnull
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ertsv')
and (
MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%eru%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%erm%'
or MARC.Get_Tag (f.bib_ID,'900', 1, 'a', 1, 0) like'%учебн%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%ern%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erc'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ertm'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'eruz'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erul'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ers'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erco'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erts%'
)
and (datefromIn isnullor
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'a', 1, 0), 'yyyymmddhh24mi')
>datefromIn
or
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'y', 1, 0), 'yyyymmddhh24mi')
>datefromIn
)-- and t.bib_id=159565
)
loop
selectdistinct MARC.Get_Tag (f.bib_ID,tagnameIn, 1,
subfieldnameIn, 1, 0) into val
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = ii.bib_id
and r.bib_id = t.bib_id
and t.bib_id = f.bib_id;
if(val isnotnull ) then
ciu_upload_pkg.append_lib_data(ii.bib_id,tagnameIn,subfieldnameIn,val, 1, 1);
endif;
endloop;
commit;
end;
procedure upload_repetitive_all(tagnameIn invarchar2, subfieldnameIn invarchar2,
datefromIn indatedefaultnull)
is
b_tag number;
b_subfield number;
val varchar2(4000);
tagnum number;
subfieldnum number;
begin
for i in (selectdistinct f.bib_id
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = f.bib_id
and r.bib_id = t.bib_id
and (datefromIn isnullor
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'a', 1, 0), 'yyyymmddhh24mi')
>datefromIn
or
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'y', 1, 0), 'yyyymmddhh24mi')
>datefromIn)
)
loop
b_tag := 0;
tagnum := 1;
while b_tag = 0
loop
b_subfield:=0;
subfieldnum:=2;
selectdistinct MARC.Get_Tag (f.bib_ID,tagnameIn, tagnum,
subfieldnameIn, 1, 0) into val
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = i.bib_id
and r.bib_id = t.bib_id
and t.bib_id = f.bib_id;
if(val isnotnull) then
ciu_upload_pkg.append_lib_data(i.bib_id,tagnameIn,subfieldnameIn,val,
tagnum, 1);
while b_subfield = 0
loop
selectdistinct MARC.Get_Tag (f.bib_ID,tagnameIn, tagnum,
subfieldnameIn, subfieldnum, 0) into val
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = i.bib_id
and r.bib_id = t.bib_id
and t.bib_id = f.bib_id;
if val isnullthen
b_subfield := 1;
else
ciu_upload_pkg.append_lib_data(i.bib_id,tagnameIn,subfieldnameIn,val,
tagnum, subfieldnum);
subfieldnum := subfieldnum + 1;
endif;
endloop;
tagnum := tagnum + 1;
else b_tag := 1;
endif;
endloop;
null;
endloop;
commit;
end;
procedure append_lib_data(v_bib_id innumber,
v_tag invarchar2,
v_subfield invarchar2,
v_value invarchar2,
v_tagnum innumberdefault1,
v_subfieldnum innumberdefault1)
is
begin
insertinto ciu_data
(bib_id, tag, subfield, value, tagnum, subfieldnum)
values
(v_bib_id, v_tag, v_subfield, v_value, v_tagnum, v_subfieldnum);
commit;
end append_lib_data;
-- возвращает дату последнего изменения записи
function get_date_changed(bibIdIn innumber) returndate
is
begin
return nvl(to_date(MARC.Get_Tag(bibIdIn,'039', 1, 'a', 1, 0), 'yyyymmddhh24mi'),
to_date(MARC.Get_Tag (bibIdIn,'039', 1, 'y', 1, 0), 'yyyymmddhh24mi'));
end;
-- проверяет, удален ли ресурс
function is_deleted(bibIdIn innumber) returnnumber
is
begin
returncasewhen lower(MARC.Get_Tag(bibidIn,952, 1, 'b', 1, 0)) like'%списан%'
then1else0end;
end;
-- выгрузка списка баз
procedure upload_bases(datefromIn indatedefaultnull)
is
title varchar2(2000);
is_deleted varchar2(2000);
typename varchar2(2000);
link varchar2(2000);
base_link varchar2(2000);
begin
for ii in (
selectdistinct f.bib_id
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = f.bib_id
and r.bib_id = t.bib_id
and
(MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ersu'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erss')
and (datefromIn isnullor
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'a', 1, 0), 'yyyymmddhh24mi')
>datefromIn
or
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'y', 1, 0), 'yyyymmddhh24mi')
>datefromIn)
/*or to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'a', 1, 0), 'yyyymmddhh24mi') is null*/
)
loop
select MARC.Get_Tag (f.bib_ID,'245', 1, 'a', 1, 0),
MARC.Get_Tag (f.bib_ID,'952', 1, 'b', 1, 0),
MARC.Get_Tag (f.bib_ID,'520', 1, 'u', 1, 0),
MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0),
MARC.Get_Tag (f.bib_ID,'856', 1, 'u', 1, 0)
into title, is_deleted, link, typename, base_link
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = ii.bib_id
and r.bib_id = t.bib_id
and t.bib_id = f.bib_id;
ciu_upload_pkg.append_lib_data(ii.bib_id,'245','a',title, 1, 1);
ciu_upload_pkg.append_lib_data(ii.bib_id,'952','b',is_deleted, 1, 1);
ciu_upload_pkg.append_lib_data(ii.bib_id,'520','u',link, 1, 1);
ciu_upload_pkg.append_lib_data(ii.bib_id,'856','x',typename, 1, 1);
ciu_upload_pkg.append_lib_data(ii.bib_id,'856','u',base_link, 1, 1);
endloop;
commit;
end;
procedure clear_data
is
begin
deletefrom ciu_data;
commit;
end;
procedure upload_all_id
is
begin
for ii in (
selectdistinct f.bib_id
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = f.bib_id
and r.bib_id = t.bib_id
and
(MARC.Get_Tag (f.bib_ID,'856', 1, 'z', 1, 0) isnotnull
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ertsv')
and (
MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%eru%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%erm%'
or MARC.Get_Tag (f.bib_ID,'900', 1, 'a', 1, 0) like'%учебн%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%ern%'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erc'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ertm'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'eruz'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erul'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'ers'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erco'
or MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'erts%'
)
)
loop
ciu_upload_pkg.append_lib_data(ii.bib_id,null,null,ii.bib_id, 1, 1);
endloop;
end;
-- выгрузка по условию для монографий
procedure upload_monograph(tagnameIn invarchar2, subfieldnameIn invarchar2,
datefromIn indatedefaultnull)
is
b_tag number;
b_subfield number;
val varchar2(5000);
tagnum number;
subfieldnum number;
begin
for i in (selectdistinct f.bib_id
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = f.bib_id
and r.bib_id = t.bib_id
and r.bib_id = t.bib_id
and
MARC.Get_Tag (f.bib_ID,'856', 1, 'u', 1, 0) isnotnull
and
(
/* MARC.Get_Tag (f.bib_ID,'961', 1, 'a', 1, 0) like '%внутривуз%'
and*/ (
MARC.Get_Tag (f.bib_ID,'856', 1, 'x', 1, 0) like'%ertm%'
or
lower(MARC.Get_Tag (f.bib_ID,'245', 1, 'b', 1, 0)) like'%монография%'
or
lower(MARC.Get_Tag (f.bib_ID,'440', 1, 'a', 1, 0)) like'%монограф%нгту%'
or
(
lower(MARC.Get_Tag (f.bib_ID,'951', 1, 'a', 1, 0)) like'%труды%нгту%'
and
lower(MARC.Get_Tag (f.bib_ID,'951', 1, 'b', 1, 0)) like'%монография%'
)
)
)
and (datefromIn isnullor
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'a', 1, 0), 'yyyymmddhh24mi')
>datefromIn
or
to_date(MARC.Get_Tag (f.bib_ID,'039', 1, 'y', 1, 0), 'yyyymmddhh24mi')
>datefromIn
)
)
loop
b_tag := 0;
tagnum := 1;
while b_tag = 0
loop
b_subfield:=0;
subfieldnum:=2;
selectdistinct MARC.Get_Tag (f.bib_ID,tagnameIn, tagnum,
subfieldnameIn, 1, 0) into val
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = i.bib_id
and r.bib_id = t.bib_id
and t.bib_id = f.bib_id;
if(val isnotnull) then
ciu_upload_pkg.append_lib_data(i.bib_id,tagnameIn,subfieldnameIn,val,
tagnum, 1);
while b_subfield = 0
loop
selectdistinct MARC.Get_Tag (f.bib_ID,tagnameIn, tagnum,
subfieldnameIn, subfieldnum, 0) into val
from dbadmin.bibliographic_url t,
dbadmin.bibliographic_fields f,
dbadmin.bibliographic_record r
where t.bib_id = i.bib_id
and r.bib_id = t.bib_id
and t.bib_id = f.bib_id;
if val isnullthen
b_subfield := 1;
else
ciu_upload_pkg.append_lib_data(i.bib_id,tagnameIn,subfieldnameIn,val,
tagnum, subfieldnum);
subfieldnum := subfieldnum + 1;
endif;
endloop;
tagnum := tagnum + 1;
else b_tag := 1;
endif;
endloop;
endloop;
end;
end ciu_upload_pkg;
-- Create table
createglobaltemporarytable CIU_DATA
(
BIB_ID NUMBER(10),
TAG VARCHAR2(10),
SUBFIELD VARCHAR2(10),
TAGNUM NUMBER(10),
SUBFIELDNUM NUMBER(10),
VALUE VARCHAR2(4000)
)
oncommitpreserverows;
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 217.71.131.209)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = vtls01)))