Описание работы АС и заглушек

Системы, работающие через Sonic: Бисквит; SC "Наличность"; ЦОП; Way4; «ДБО «Telebank»; Profile на тестовом стенде будут заменены эмулятором. Архитектура эмулятора Sonic должна позволять изменять формат входящих/исходящих XML сообщений без перекомпиляции заглушки.

Эмулятор УСБС представляет из себя пару взаимодействующих веб-сервисов, разворачиваемых на сервере Apache Tomcat: back-ws-ts и from-ws-ts и предоставляется Заказчиком.

Эмулятор Delta BranchCash представляет из себя пару взаимодействующих веб-сервисов, разворачиваемых на сервере Glassfish.

Времена отклика эмулятора основываются на информации, полученной от специалистов Заказчика (требования к временам отклика операций со смежными системами) и задаются в конфигурационных файлах самих заглушек.

Замена шаблонов входящих и исходящих сообщений эмулятора не требует перекомпиляции самого эмулятора.

Описание работы эмулятора смежных систем Sonic приведено в Приложение 3. Эмулятор Sonic.

Для эмуляции взаимодействия Системы с ЕФР (Siebel-Spectrum), будет разработан набор скриптов Load Runner (UC23-UC39 из раздела п.9.4 Описание операций НТ), входящих в основной профиль тестирования «День» - операции рассчетно-кассового функционала.

В ходе выполнения операций в интерфейс Spectrum загружается XML с соответствующим кейсу контекстом операции, для создания XML будет разработан отдельный генератор. Согласно информации от Заказчика, данные операции могут выполняться в обход Siebel’я или его эмулятора путем загрузки соответствующих XML через интерфейс Spectrum вместо получения XML со стороны веб-сервиса Siebel.

Наполнение базы данных

Нагрузочное тестирование ИС «Спектрум» будет проводиться на тестовой БД, наполненной до объемов, планируемых на начало 2016 года.

Согласно прогнозу, предоставленному заказчиком, ожидаются следующие объемы наполнения основных таблиц БД ИС «Спектрум»:

 

Таблица 11.1 Количество строк в основных таблицах БД ИС «Спектрум»

Владелец	Имя таблицы	Кол-во строк
09.2013	01.06.2014 (текущее стостояние БД)	01.2016 (план)
SP	CLIENT		3 800 500	15 000 000
SP	DEPT
SP	OPER_JOURNAL_EVENT		9 840 000	330 000 000

 

Наполнение БД будет производиться по таблицам, имеющим на 01.06.2014 более одного миллиона записей.

Для генерации данных будут разработаны скрипты SQL, создающие строки в таблицах селективным методом (на основании уже имеющихся записей в таблицах). Для пулов скриптов НТ будут использованы только изначальные записи, имеющиеся на момент снятия дампа БД, сгенерированные записи будут создавать исключительно объем и в скриптах задействованы не будут.

При создании скриптов SQL-запросов будут учтены связи между таблицами для избежания конфликтов заполнения.

Прогнозируемые объемы таблиц БД, по которым будет производиться наполнение, представлены в Таблице 11.2. Прогноз рассчитан на основании данных, предоставленных Заказчиком (Таблица 11.1), связей между таблицами и трендом ежемесячного увеличения объема записей в каждой таблице отдельно. Подробные расчеты представлены в Приложении 2.

 

Таблица 11.2 Объемы наполнения остальных таблиц

Название таблицы	всего 01.06.2014	прогноз 01.01.2016
OPER_JOURNAL_EVENT	19 051 557	330 000 000
TRANS_POS	17 924 652	310 000 000
VALUABLE_FLOW	12 302 303	210 000 000
AN_ATTR_VAL	11 813 501	200 000 000
OPER_JOURNAL	11 756 945	200 000 000
PMT_DOC	10 197 988	175 000 000
OPER_JOURNAL_PRINT	9 881 766	170 000 000
OPER_JOURNAL_CONTENT	9 840 220	170 000 000
TRANS	9 043 874	155 000 000
AUDIT_LOG	5 652 433	95 000 000
OPER_JOURNAL_EXT	5 427 518	95 000 000
OBJ_ID_SYS_ROLE	4 960 367	85 000 000
PUBLIC_SERVICE_OJ_PAR	4 643 805	80 000 000
CLIENT	3 798 433	15 000 000
PMT_DOC_EXT	3 856 880	65 000 000
PERSON	3 608 766	14 000 000
IDENTITY_CARD	3 604 002	14 000 000
FCY_CASH_REGISTER_OPER	3 302 016	50 000 000
USER_SESSION	3 015 526	11 000 000
VALUABLE_BALANCE	2 820 980	11 000 000
ADDRESS	2 588 235	10 000 000
POS_REGISTER_OPER	2 576 310	10 000 000
LIM_REC_POSTING	1 662 806	6 000 000
LIM_REC	1 571 279	6 000 000
ACCOUNT	1 497 884	5 000 000
BALANCE	1 298 498	5 000 000

 

Наполнение таблиц в связи с имеющимися зависимостями будет проводиться в следующем порядке:

 

Таблица 11.3 Порядок наполнения таблиц в БД.

ПОРЯДОК НАПОЛНЕНИЯ	НАЗВАНИЕ ТАБЛИЦЫ
	PERSON
	DEPT
	CLIENT
	USER_SESSION
	WORK_SHIFT
	IDENTITY_CARD
7,8	OPER_JOURNAL
	ACCOUNT
	TRANS_POS
	VALUABLE_FLOW
	PMT_DOC
	PMT_DOC_EXT
	AN_ATTR_VAL
	OPEN_JOURNAL_PRINT
	OPEN_JOURNAL_CONTENT
	TRANS
	AUDIT_LOG
	OPER_JOURNAL_EXT
	OBJ_ID_SYS_ROLE
	PUBLIC_SERVICE_OJ_PAR
	FCY_CASH_REGISTER_OPER
	VALUABLE_BALANCE
	BALANCE
	LIM_REC
	LIM_REC_POSTING
	POS_REGISTER_OPER

 

Возможные риски со стороны тестирования:

1) В наполнении не участвуют таблицы, в которых на момент снятия дампа БД менее 1 млн. записей. При возникновении необходимости будет произведено наполнение связных таблиц, не участвующих в прогнозе.

2) Погрешность результатов реальной производительности системы Spectrum в зависимости от отклонений прогнозируемого объема БД с реальным объемом на 01.2016 не превысит 10%.

3) Наполнение производится селективными данными, в связи с чем может возникнуть погрешность в скорости выполнения некоторых запросов (например, уменьшение скорости индекс-сканов). При выявлении и анализе проблемных запросов в БД во время НТ данная погрешность будет учтена.

 

Скрипты наполнения

Данный раздел будет дополнен скриптами SQL по каждой наполняемой таблице в БД после их разработки.

Таблица 11.4 Скрипты наполнения таблиц в БД.

Название таблицы	Скрипт SQL
OPER_JOURNAL_EVENT	create or replace   procedure fill_oper_journal(num in number)   is   crtTime date;   tableSize number;   r number;   maxOper number;   begin   r:= num;   select max(oper_journal_id) into maxOper from oper_journal;   loop   select count(OPER_JOURNAL_ID) into tableSize from oper_journal;   insert into oper_journal(OPER_JOURNAL_ID, OPERATION_CODE,   CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID,   CLIENT_ID, USER_ID, TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID,   OJ_REG_TIME, OJ_DATE, OJ_STATUS_ENUM, OJ_NOTE, OJ_AMOUNT,   OJ_CONF_TIME, REQ_ID, OJ_EXT_ID,   OJ_SUSPECT_FLAG, OJ_REQUIRE_FM_FLAG, IDENTITY_CARD_ID)   select s_oper_journal.nextval, OPERATION_CODE, CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID, CLIENT_ID, USER_ID,   TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID,   trunc(sysdate - dbms_random.value(1,300)), '01.01.10', 1, dbms_random.string('x', 20), round(dbms_random.value(1,50000)), '01.01.10', null,null,   0, 0, IDENTITY_CARD_ID   from oper_journal s left join tmp t on s.OPER_JOURNAL_ID = t.tmp_id   where rownum <= r and tmp_id is null;   if tableSize <=0 then r:=-1;   else   r:= r - tableSize;   end if;   exit when r <= 0;   commit;   end loop;   update oper_journal set main_oper_journal_id = oper_journal_id   where oper_journal_id > maxOper;   commit;   update oper_journal set OJ_CONF_TIME = OJ_REG_TIME   where oper_journal_id > maxOper;   commit;   update oper_journal set OJ_DATE = OJ_REG_TIME   where oper_journal_id > maxOper;   commit;   insert into oper_journal_event   select s_oper_journal_event.nextval, o.oper_journal_id, oje.user_id, trunc(sysdate - dbms_random.value(1,300)), oje_after_status_enum   oje_elapsed_time_interval, oje_note, oje_before_status_enum, oje_after_status_enum, oje.work_shift_id   from oper_journal o join oper_journal_event oje on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = oje.oper_journal_id   where o.oper_journal_id > maxOper;   insert into oper_journal_print   select s_oper_journal_print.nextval, o.oper_journal_id, PMT_DOC_ID, PRINT_TMPL_TYPE_ID, OJP_QTY,   OJP_EVENT_ENUM, OJP_AFTER_FLAG, OJP_PRINT_FLAG, SIGN_USER_ID   from oper_journal o join oper_journal_print ojp on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojp.oper_journal_id   where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal);   commit;   insert into oper_journal_content   select s_oper_journal_content.nextval, o.oper_journal_id, OJC_CONTENT_STORE_ENUM,   OJC_CONTENT_TYPE_ENUM,OJC_PRINTABLE_ENUM,OJC_CONTENT_XML,OJC_CONTENT_BLOB,   OJC_CONTENT_CLOB,OJC_NAME,OJC_FILE_NAME   from oper_journal o join oper_journal_content ojc on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojc.oper_journal_id   where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal);   commit;   end fill_oper_journal;
TRANS_POS	create or replace procedure fill_trans_pos(cnt in number) is tDate date; rows_in_iter number; n number; intial_min_pd number; startSeq number; curr_trans_seq number; min_pmt_doc_id number; max_pmt_doc_id number; begin --на каждый аккаунт по 6 транзакций   rows_in_iter:= 2; n:= cnt; if (n - rows_in_iter < 0) then rows_in_iter:= n; end if;   while (n > 0) loop   tDate:= trunc(sysdate - dbms_random.value(1,200));   select coalesce(max(trans_num)+1, 1) into startSeq from trans where trans_date = tDate;   select coalesce(max(trans_num)+1,startSeq) into startSeq from trans_pos where trans_date = tDate;   reset_seq('trans_pos_seq', to_char(startSeq));   for i in 1..1000 loop curr_trans_seq:= trans_pos_seq.nextval;   insert into trans(TRANS_DATE, TRANS_NUM, REVERSE_TRANS_DATE, REVERSE_TRANS_NUM, DEPT_ID,PMT_DOC_ID,TRANS_REVERSE_FLAG) select trunc(tDate), curr_trans_seq, null,null,515, 35487,0 from dual where rownum < 2;     insert into trans_pos(TRANS_DATE, TRANS_NUM, TRANS_POS_NUM, CURRENCY_ID, CASH_SYMBOL_CODE, ACCOUNT_ID, TP_COR_NUM, TP_VALUE_DATE, TP_SIDE_ENUM, TP_AMOUNT, TP_AMOUNT_RUB, TP_NEED_ACT_BAL_FLAG) select trunc(tDate), curr_trans_seq, 1,126,37, 28155, 2, trunc(tDate), 'D',10000, 10000, null from dual where rownum < 2;   insert into trans_pos(TRANS_DATE, TRANS_NUM, TRANS_POS_NUM, CURRENCY_ID, CASH_SYMBOL_CODE, ACCOUNT_ID, TP_COR_NUM, TP_VALUE_DATE, TP_SIDE_ENUM, TP_AMOUNT, TP_AMOUNT_RUB, TP_NEED_ACT_BAL_FLAG) select trunc(tDate), curr_trans_seq, 2,126,37,28155, 2, trunc(tDate), 'D',10000,10000,null from dual where rownum < 2; end loop;   n:= n - rows_in_iter - 1000; end loop;   end fill_trans_pos;
VALUABLE_FLOW	create or replace procedure fill_val_flow(cnt in number) is tableSize number; r number; maxVFId number; minVFId number; rows_in_iter number; n number; m number; minVF number;   begin   select min(valuable_flow_id) into minVFId from valuable_flow; rows_in_iter:= 100000; n:= cnt; minVF:=minVFId; if (n - rows_in_iter < 0) then rows_in_iter:= n; end if;   while (n > 0) loop select max(valuable_flow_id) into maxVFId from valuable_flow; m:= round(dbms_random.value(minVFId,maxVFId));     insert into VALUABLE_FLOW (VALUABLE_FLOW_ID, RECEIVER_WORK_SHIFT_ID, SENDER_WORK_SHIFT_ID, OPER_JOURNAL_ID, VALUABLE_REGISTER_ID, PMT_DOC_ID, VF_SERIES, VF_FIRST_NUM, VF_LAST_NUM, VF_AMOUNT, VF_QUANTITY, SENDER_DEVICE_ID, SENDER_DEPT_ID, RECEIVER_DEPT_ID, RECEIVER_DEVICE_ID, VF_SORT_ENUM, VF_RECEIPT_DOC_QTY, VF_DISBURSEMT_DOC_QTY, MAIN_OPER_JOURNAL_ID, VN_SENDER_LOCATION_ENUM, VN_RECEIVER_LOCATION_ENUM, VF_HIST_COST, SENDER_USER_ID, RECEIVER_USER_ID)   select S_VALUABLE_FLOW.nextval, RECEIVER_WORK_SHIFT_ID, SENDER_WORK_SHIFT_ID, OPER_JOURNAL_ID, VALUABLE_REGISTER_ID, PMT_DOC_ID, VF_SERIES, VF_FIRST_NUM, VF_LAST_NUM, VF_AMOUNT, VF_QUANTITY, SENDER_DEVICE_ID, SENDER_DEPT_ID, RECEIVER_DEPT_ID, RECEIVER_DEVICE_ID, VF_SORT_ENUM, VF_RECEIPT_DOC_QTY, VF_DISBURSEMT_DOC_QTY, MAIN_OPER_JOURNAL_ID, VN_SENDER_LOCATION_ENUM, VN_RECEIVER_LOCATION_ENUM, VF_HIST_COST, SENDER_USER_ID, RECEIVER_USER_ID from valuable_flow v left join tmp t on v.valuable_flow_id = t.tmp_id where (valuable_flow_id between minVFId and m) and rownum < rows_in_iter;   maxVFId:= maxVFId + rows_in_iter; if (m >= rows_in_iter) then n:= n - rows_in_iter; else n:= n - m; end if;   minVFId:= minVFId + rows_in_iter; if (n > 0 and (minVFId + rows_in_iter) > maxVFId) then minVFId:= minVF; end if; commit; end loop;   end fill_val_flow;
AN_ATTR_VAL	create or replace procedure FILL_AN_ATTR_VAL(cnt in number) is rows_in_iter number; n number; min_acc_id number; max_acc_id number; init_min_acc_id number; tDate date; begin   rows_in_iter:= 300000; n:= cnt;   select min(account_id) into min_acc_id from account where account_id > 0; select max(account_id) into max_acc_id from account; init_min_acc_id:= min_acc_id;   if (n - rows_in_iter < 0) then rows_in_iter:= n; end if;   while (n >0) loop tDate:= trunc(sysdate - dbms_random.value(1,300));   SAVEPOINT start_transaction;   insert into an_attr_val (AN_ATTR_VAL_ID, AAV_OBJ_ID, AN_ATTR_ID, AAV_TIME, AAV_VALUE) select s_an_attr_val.nextval, a.account_id, ceil(dbms_random.value(11,19)), tDate, 'lt' || dbms_random.string('X',40) from account a where rownum < rows_in_iter and a.account_id between min_acc_id and max_acc_id;   n:= n - rows_in_iter; min_acc_id:= min_acc_id + rows_in_iter;   if (min_acc_id >= max_acc_id) then min_acc_id:= init_min_acc_id; end if; commit; end loop; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN rollback to start_transaction; tDate:= sysdate; min_acc_id:= min_acc_id + rows_in_iter; dbms_output.put_line('dups'); WHEN OTHERS THEN ROLLBACK; END;
OPER_JOURNAL, OPER_JOURNAL_PRINT, OPER_JOURNAL_CONTENT, OPER_JOURNAL_EXT	create or replace function func_fill_oper_journal(num in number) return number is crtTime date; tableSize number; r number; maxOper number; begin   r:= num; select max(oper_journal_id) into maxOper from oper_journal;   loop select count(OPER_JOURNAL_ID) into tableSize from oper_journal;   insert into oper_journal(OPER_JOURNAL_ID, OPERATION_CODE, CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID, CLIENT_ID, USER_ID, TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID, OJ_REG_TIME, OJ_DATE, OJ_STATUS_ENUM, OJ_NOTE, OJ_AMOUNT, OJ_CONF_TIME, REQ_ID, OJ_EXT_ID, OJ_SUSPECT_FLAG, OJ_REQUIRE_FM_FLAG, IDENTITY_CARD_ID) select s_oper_journal.nextval, OPERATION_CODE, CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID, CLIENT_ID, USER_ID, TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID, trunc(sysdate - dbms_random.value(1,300)), '01.01.10', 1, dbms_random.string('x', 20), round(dbms_random.value(1,50000)), '01.01.10', null, case when OJ_EXT_ID is null then null else OJ_EXT_ID || ', lt-' || dbms_random.string('x',5) end, 0, 0, IDENTITY_CARD_ID from oper_journal s left join tmp t on s.OPER_JOURNAL_ID = t.tmp_id where rownum <= r and tmp_id is null;   if tableSize <=0 then r:=-1; else r:= r - tableSize; end if; exit when r <= 0; commit; end loop;   update oper_journal set main_oper_journal_id = oper_journal_id where oper_journal_id > maxOper; commit; update oper_journal set OJ_CONF_TIME = OJ_REG_TIME where oper_journal_id > maxOper; commit; update oper_journal set OJ_DATE = OJ_REG_TIME where oper_journal_id > maxOper; commit;   insert into oper_journal_event select s_oper_journal_event.nextval, o.oper_journal_id, oje.user_id, trunc(sysdate - dbms_random.value(1,300)), oje_after_status_enum oje_elapsed_time_interval, oje_note, oje_before_status_enum, oje_after_status_enum, oje.work_shift_id from oper_journal o join oper_journal_event oje on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = oje.oper_journal_id where o.oper_journal_id > maxOper;   insert into oper_journal_print select s_oper_journal_print.nextval, o.oper_journal_id, PMT_DOC_ID, PRINT_TMPL_TYPE_ID, OJP_QTY, OJP_EVENT_ENUM, OJP_AFTER_FLAG, OJP_PRINT_FLAG, SIGN_USER_ID from oper_journal o join oper_journal_print ojp on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojp.oper_journal_id where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal); commit;   insert into oper_journal_content select s_oper_journal_content.nextval, o.oper_journal_id, OJC_CONTENT_STORE_ENUM, OJC_CONTENT_TYPE_ENUM,OJC_PRINTABLE_ENUM,OJC_CONTENT_XML,OJC_CONTENT_BLOB, OJC_CONTENT_CLOB,OJC_NAME,OJC_FILE_NAME from oper_journal o join oper_journal_content ojc on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojc.oper_journal_id where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal); commit;   insert into oper_journal_ext (oje_ext_id, oper_journal_id, id_sys_code) select distinct OJ_EXT_ID, oj.oper_journal_id, case when mod(oj.oper_journal_id,2)=0 then '00000' when mod(oj.oper_journal_id,3)=0 then 'SC_NAL' when mod(oj.oper_journal_id,5)=0 then 'Бисквит' else 'OW4' end from oper_journal oj where oj.oper_journal_id > maxOper and oj.OJ_EXT_ID is not null; commit;   return maxOper; end func_fill_oper_journal;       create or replace procedure fill_oper_journal(num in number) is crtTime date; tableSize number; r number; maxOper number; begin   r:= num; select max(oper_journal_id) into maxOper from oper_journal;   loop select count(OPER_JOURNAL_ID) into tableSize from oper_journal;   insert into oper_journal(OPER_JOURNAL_ID, OPERATION_CODE, CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID, CLIENT_ID, USER_ID, TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID, OJ_REG_TIME, OJ_DATE, OJ_STATUS_ENUM, OJ_NOTE, OJ_AMOUNT, OJ_CONF_TIME, REQ_ID, OJ_EXT_ID, OJ_SUSPECT_FLAG, OJ_REQUIRE_FM_FLAG, IDENTITY_CARD_ID) select s_oper_journal.nextval, OPERATION_CODE, CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID, CLIENT_ID, USER_ID, TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID, trunc(sysdate - dbms_random.value(1,300)), '01.01.10', 1, dbms_random.string('x', 20), round(dbms_random.value(1,50000)), '01.01.10', null, case when OJ_EXT_ID is null then null else OJ_EXT_ID || ', lt-' || dbms_random.string('x',5) end, 0, 0, IDENTITY_CARD_ID from oper_journal s left join tmp t on s.OPER_JOURNAL_ID = t.tmp_id where rownum <= r and tmp_id is null;   if tableSize <=0 then r:=-1; else r:= r - tableSize; end if; exit when r <= 0; commit; end loop;   update oper_journal set main_oper_journal_id = oper_journal_id where oper_journal_id > maxOper; commit; update oper_journal set OJ_CONF_TIME = OJ_REG_TIME where oper_journal_id > maxOper; commit; update oper_journal set OJ_DATE = OJ_REG_TIME where oper_journal_id > maxOper; commit;   insert into oper_journal_event select s_oper_journal_event.nextval, o.oper_journal_id, oje.user_id, trunc(sysdate - dbms_random.value(1,300)), oje_after_status_enum oje_elapsed_time_interval, oje_note, oje_before_status_enum, oje_after_status_enum, oje.work_shift_id from oper_journal o join oper_journal_event oje on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = oje.oper_journal_id where o.oper_journal_id > maxOper;   insert into oper_journal_print select s_oper_journal_print.nextval, o.oper_journal_id, PMT_DOC_ID, PRINT_TMPL_TYPE_ID, OJP_QTY, OJP_EVENT_ENUM, OJP_AFTER_FLAG, OJP_PRINT_FLAG, SIGN_USER_ID from oper_journal o join oper_journal_print ojp on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojp.oper_journal_id where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal); commit;   insert into oper_journal_content select s_oper_journal_content.nextval, o.oper_journal_id, OJC_CONTENT_STORE_ENUM, OJC_CONTENT_TYPE_ENUM,OJC_PRINTABLE_ENUM,OJC_CONTENT_XML,OJC_CONTENT_BLOB, OJC_CONTENT_CLOB,OJC_NAME,OJC_FILE_NAME from oper_journal o join oper_journal_content ojc on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojc.oper_journal_id where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal); commit;   insert into oper_journal_ext (oje_ext_id, oper_journal_id, id_sys_code) select distinct OJ_EXT_ID, oj.oper_journal_id, case when mod(oj.oper_journal_id,2)=0 then '00000' when mod(oj.oper_journal_id,3)=0 then 'SC_NAL' when mod(oj.oper_journal_id,5)=0 then 'Бисквит' else 'OW4' end from oper_journal oj where oj.oper_journal_id > maxOper and oj.OJ_EXT_ID is not null; commit;   insert into OBJ_ID_SYS_ROLE select 'OPER_JOURNAL', oje.oper_journal_id, 1, oje.id_sys_code from oper_journal_ext oje where oje.oper_journal_id > maxOper; commit; end fill_oper_journal;
PMT_DOC	procedure fill_pmt_doc(num in number)   is   tableSize number;   r number;   maxPmtId number;   begin   r:= num;   select max(pmt_doc_id) into maxPmtId from pmt_doc;   loop   select count(pmt_doc_id) into tableSize from pmt_doc;   insert into pmt_doc(PMT_DOC_ID,DEBIT_ACCOUNT_ID,CREDIT_ACCOUNT_ID,   CASH_OPERATION_SORT_CODE,PAR_PMT_DOC_ID,IDENTITY_CARD_ID,   OPER_JOURNAL_ID,PMT_TYPE_CODE,PMT_SORT_CODE,PMT_ORDER_TYPE_CODE,   PAYER_BIC, BEN_BIC, CREDIT_CASH_SYMBOL_CODE, DEBIT_CASH_SYMBOL_CODE,   PRINT_TMPL_TYPE_ID, PAYER_STATUS_CODE, PMT_GROUND_TYPE_CODE,   CURRENCY_ID, MARK_DOC_CODE, DEBIT_CURRENCY_ID, CREDIT_CURRENCY_ID,   PD_ORD_COMP_ENUM, PD_SIGN_ENUM, PD_NUM, PD_CASH_OPERATION_NUMBER,   PD_PRINT_QTY, PD_REG_DATE, PD_DATE, PD_VALUE_DATE, PD_PAYER_ACC_NUM,   PD_PAYER_NAME, PD_PAYER_INN, PD_PAYER_KPP, PD_PAYER_BANK_ACC_NUM,   PD_BEN_ACC_NUM, PD_BEN_NAME, PD_BEN_INN, PD_BEN_KPP,   PD_BEN_BANK_ACC_NUM, PD_AMOUNT, PD_PMT_DETAILS,   PD_CBC, PD_OKATO, PD_FISKAL_PERIOD, PD_FISKAL_DOC_NUM,   PD_FISKAL_DOC_DATE_C, PD_DEBIT_AMOUNT, PD_CREDIT_AMOUNT,   CURRENCY_OPER_SORT_CODE, PD_PAYER_RESIDENT_FLAG, PD_BEN_RESIDENT_FLAG,   PD_EXT_ID, PD_NUM_S, PD_CASH_OPERATION_NUMBER_S,   PD_CCY_EXCH_RATE, PD_PAYER_IDENTITY_CARD, PD_PRINCIPAL,   PD_PRINCIPAL_IDENTITY_CARD, PD_POA, PD_PRINT_FLAG,   PD_PRINT_CLIENT_INFO_FLAG, PD_CASH_RECEIPT_NUM, PD_CASH_DISBURSEMT_NUM,   PD_BEN_IDENTITY_CARD, BENEFICIARY_ID, MAIN_OPER_JOURNAL_ID,   PD_POA_EXP_DATE, PD_TAX_CHARGE_UID, PD_CB_CODE, DEBIT_DEPT_ID,   CREDIT_DEPT_ID, DEBIT_ID_SYS_CODE, CREDIT_ID_SYS_CODE,   DEBIT_SYNT_ACC_ID, CREDIT_SYNT_ACC_ID, PD_PAYER_ADDRESS,   DEBIT_AGR_ID, CREDIT_AGR_ID, PD_BENEFICIARY_SURNAME,   PD_BENEFICIARY_FIRST_NAME, PD_BENEFICIARY_PATRONYMIC)   select s_pmt_doc.nextval, DEBIT_ACCOUNT_ID,CREDIT_ACCOUNT_ID,   CASH_OPERATION_SORT_CODE,PAR_PMT_DOC_ID,IDENTITY_CARD_ID,   OPER_JOURNAL_ID,PMT_TYPE_CODE,PMT_SORT_CODE,PMT_ORDER_TYPE_CODE,   PAYER_BIC, BEN_BIC, CREDIT_CASH_SYMBOL_CODE, DEBIT_CASH_SYMBOL_CODE,   PRINT_TMPL_TYPE_ID, PAYER_STATUS_CODE, PMT_GROUND_TYPE_CODE,   CURRENCY_ID, MARK_DOC_CODE, DEBIT_CURRENCY_ID, CREDIT_CURRENCY_ID,   PD_ORD_COMP_ENUM, PD_SIGN_ENUM, PD_NUM, PD_CASH_OPERATION_NUMBER,   PD_PRINT_QTY, trunc(sysdate - dbms_random.value(1,300)), '01.01.2010', '01.01.2010', PD_PAYER_ACC_NUM,   PD_PAYER_NAME, PD_PAYER_INN, PD_PAYER_KPP, PD_PAYER_BANK_ACC_NUM,   PD_BEN_ACC_NUM, PD_BEN_NAME, PD_BEN_INN, PD_BEN_KPP,   PD_BEN_BANK_ACC_NUM, PD_AMOUNT, PD_PMT_DETAILS,   PD_CBC, PD_OKATO, PD_FISKAL_PERIOD, PD_FISKAL_DOC_NUM,   PD_FISKAL_DOC_DATE_C, PD_DEBIT_AMOUNT, PD_CREDIT_AMOUNT,   CURRENCY_OPER_SORT_CODE, PD_PAYER_RESIDENT_FLAG, PD_BEN_RESIDENT_FLAG,   PD_EXT_ID, PD_NUM_S, PD_CASH_OPERATION_NUMBER_S,   PD_CCY_EXCH_RATE, PD_PAYER_IDENTITY_CARD, PD_PRINCIPAL,   PD_PRINCIPAL_IDENTITY_CARD, PD_POA, PD_PRINT_FLAG,   PD_PRINT_CLIENT_INFO_FLAG, PD_CASH_RECEIPT_NUM, PD_CASH_DISBURSEMT_NUM,   PD_BEN_IDENTITY_CARD, BENEFICIARY_ID, MAIN_OPER_JOURNAL_ID,   PD_POA_EXP_DATE, PD_TAX_CHARGE_UID, PD_CB_CODE, DEBIT_DEPT_ID,   CREDIT_DEPT_ID, DEBIT_ID_SYS_CODE, CREDIT_ID_SYS_CODE,   DEBIT_SYNT_ACC_ID, CREDIT_SYNT_ACC_ID, PD_PAYER_ADDRESS,   DEBIT_AGR_ID, CREDIT_AGR_ID, PD_BENEFICIARY_SURNAME,   PD_BENEFICIARY_FIRST_NAME, PD_BENEFICIARY_PATRONYMIC   from pmt_doc s left join tmp t on s.PMT_DOC_ID = t.tmp_id   where rownum <= r and tmp_id is null;   if tableSize <=0 then r:=-1;   else   r:= r - tableSize;   end if;   exit when r <= 0;   commit;   end loop;   update pmt_doc set PD_DATE = PD_REG_DATE   where pmt_doc_id > maxPmtId;   commit;   update pmt_doc set PD_VALUE_DATE = PD_REG_DATE   where pmt_doc_id > maxPmtId;   commit;   end fill_pmt_doc;
TRANS	create or replace procedure fill_trans_pos(cnt in number) is tDate date; rows_in_iter number; n number; intial_min_pd number; startSeq number; curr_trans_seq number; min_pmt_doc_id number; max_pmt_doc_id number; begin --на каждый аккаунт по 6 транзакций   rows_in_iter:= 2; n:= cnt; if (n - rows_in_iter < 0) then rows_in_iter:= n; end if;   while (n > 0) loop   tDate:= trunc(sysdate - dbms_random.value(1,200));   select coalesce(max(trans_num)+1, 1) into startSeq from trans where trans_date = tDate;   select coalesce(max(trans_num)+1,startSeq) into startSeq from trans_pos where trans_date = tDate;   reset_seq('trans_pos_seq', to_char(startSeq));   for i in 1..1000 loop curr_trans_seq:= trans_pos_seq.nextval;   insert into trans(TRANS_DATE, TRANS_NUM, REVERSE_TRANS_DATE, REVERSE_TRANS_NUM, DEPT_ID,PMT_DOC_ID,TRANS_REVERSE_FLAG) select trunc(tDate), curr_trans_seq, null,null,515, 35487,0 from dual where rownum < 2;     insert into trans_pos(TRANS_DATE, TRANS_NUM, TRANS_POS_NUM, CURRENCY_ID, CASH_SYMBOL_CODE, ACCOUNT_ID, TP_COR_NUM, TP_VALUE_DATE, TP_SIDE_ENUM, TP_AMOUNT, TP_AMOUNT_RUB, TP_NEED_ACT_BAL_FLAG) select trunc(tDate), curr_trans_seq, 1,126,37, 28155, 2, trunc(tDate), 'D',10000, 10000, null from dual where rownum < 2;   insert into trans_pos(TRANS_DATE, TRANS_NUM, TRANS_POS_NUM, CURRENCY_ID, CASH_SYMBOL_CODE, ACCOUNT_ID, TP_COR_NUM, TP_VALUE_DATE, TP_SIDE_ENUM, TP_AMOUNT, TP_AMOUNT_RUB, TP_NEED_ACT_BAL_FLAG) select trunc(tDate), curr_trans_seq, 2,126,37,28155, 2, trunc(tDate), 'D',10000,10000,null from dual where rownum < 2; end loop;   n:= n - rows_in_iter - 1000; end loop;   end fill_trans_pos;
AUDIT_LOG	create or replace procedure fill_audit_log(min_us_id in number, cnt in number) is event numbertype; max_us number; rows_in_iter number; n number; m number; begin   select r bulk collect into event from (select event_sort_id r from event_sort);   select max(user_session_id) into max_us from user_session;   rows_in_iter:= 10000; n:= cnt; m:= min_us_id; if (n - rows_in_iter < 0) then rows_in_iter:= n; end if;   while (n > 0) loop insert into audit_log select s_audit_log.nextval, user_session_id, 330001, trunc(sysdate - dbms_random.value(1,300)), dbms_random.string('x', 10) from user_session where user_session_id > m and rownum < rows_in_iter;   update audit_log set event_sort_id = event(round(dbms_random.value(1,event.count))) where user_session_id > m;   m:= m + rows_in_iter; n:= n - rows_in_iter;   if (n > 0 and (m + rows_in_iter) > max_us) then m:= min_us_id; end if; commit; end loop;   end;
OBJ_ID_SYS_ROLE	create or replace procedure FILL_OBJ_ID_SYS_ROLE(cnt in number) is rows_in_iter number; n number; SYS_ROLE_CODE number; SYS_CODE varchar2(16 byte); cnt_av number; begin   rows_in_iter:= 300000;   n:= cnt; if (n - rows_in_iter < 0) then rows_in_iter:= n; end if; SYS_ROLE_CODE:=1; SYS_CODE:='99995'; while (n >0) loop   SAVEPOINT start_transaction;   select count(*) into cnt_av from FCY_CASH_REGISTER_OPER f left join obj_id_sys_role ob on to_char(f.fcy_cash_register_id) = ob.OISR_OBJ_ID where rownum < rows_in_iter and ob.OISR_OBJ_ID is null and f.fcy_cash_register_id is not null;   insert into obj_id_sys_role (OISR_OBJ_ID, OISR_TABLE, ID_SYS_ROLE_CODE, ID_SYS_CODE) select distinct f.fcy_cash_register_id, 'OPER_JOURNAL', SYS_ROLE_CODE, SYS_CODE from FCY_CASH_REGISTER_OPER f left join obj_id_sys_role ob on to_char(f.fcy_cash_register_id) = ob.OISR_OBJ_ID where rownum < rows_in_iter and ob.OISR_OBJ_ID is null and f.fcy_cash_register_id is not null;   if (cnt_av < rows_in_iter) then n:= n- cnt_av; select id_sys_code into SYS_CODE from id_sys where id_sys_code not like '99995' order by dbms_random.value; else n:= n - rows_in_iter; SYS_ROLE_CODE:=1; SYS_CODE:='99995'; end if; commit; end loop; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN rollback to start_transaction; SYS_ROLE_CODE:=2; select id_sys_code into SYS_CODE from id_sys where id_sys_code not like '99995' order by dbms_random.value; dbms_output.put_line('dups'); WHEN OTHERS THEN ROLLBACK; end;
PUBLIC_SERVICE_OJ_PAR	create or replace procedure fill_public_service_oj_par(min_oj_id in number, cnt in number) is min_psop_id number; oj numbertype; psrp numbertype; j number; begin   select max(public_service_repmt_par_code) into min_psop_id from public_service_oj_par;   select r bulk collect into oj from ( select oper_journal_id r from oper_journal where oper_journal_id > min_oj_id and rownum < cnt )rndm;   select r bulk collect into psrp from ( select public_service_repmt_par_code r from public_service_repmt_par order by dbms_random.value )rndm;   j:= 1; for i in 1..oj.count loop   insert into public_service_oj_par(OPER_JOURNAL_ID, PUBLIC_SERVICE_REPMT_PAR_CODE, PUBLIC_SERVICE_OJ_PAR_SN, PSOP_VALUE, PSOP_MOMENT_ENUM, ID_SYS_CODE) values(oj(i), psrp(j), round(dbms_random.value(1,7)), dbms_random.string('x', 10),1, null);   j:=j+1; if j > psrp.count then j:= 1; end if; end loop;   end;
CLIENT	create or replace procedure fill_client_tmp(num in number) is clients_cnt number; i number; dept number; type rowid_nt is table of rowid; rowids rowid_nt; begin --заполняем темповую таблицу клиентами и рандомными департаментами. Количество записей = количеству создаваемых аккаунтов. --берутся клиенты без аккаутов --delete from tmp_client;     insert into tmp_client (id, cl_id) select tsec.nextval, c.client_id from client c left join account a on c.client_id = a.client_id where a.client_id is null and rownum < i;   commit;   dbms_output.put_line('filled clliet_id');   select r bulk collect into rowids from ( select rowid r from tmp_client order by dbms_random.value ) rndm;   forall j in 1..rowids.count update tmp_client set d_id = (select dept_id from (select dept_id from dept order by dbms_random.value) t where rownum = 1) where rowid=rowids(j) and d_id is null; commit; dbms_output.put_line('updated dept_id'); forall j in 1..rowids.count update tmp_client set s_acc_id = (select SYNT_ACC_ID from (select SYNT_ACC_ID from synt_acc order by dbms_random.value) t where rownum = 1) where rowid=rowids(j) and s_acc_id is null;   dbms_output.put_line('updated s_acc_id');   commit; end fill_client_tmp;
PMT_DOC_EXT	create or replace PROCEDURE fill_pmt_doc_ext(num in number) is minJoinedId number; max_pmt_doc_id number; begin   select max(pmt_doc_id) into max_pmt_doc_id from pmt_doc; select max(pd.pmt_doc_id) into minJoinedId from pmt_doc pd join pmt_doc_ext pde on pde.pmt_doc_id = pd.pmt_doc_id; --10294362 --11294569 insert into pmt_doc_ext (PMT_DOC_ID,ID_SYS_CODE,PDE_EXT_ID) select p.PMT_DOC_ID, ID_SYS_CODE, tmp_pde_doc_ext_id.nextval from pmt_doc p join pmt_doc_ext pde on p.PMT_DOC_ID- 4*(max_pmt_doc_id - minJoinedId)/3 = pde.pmt_doc_id where p.PMT_DOC_ID > minJoinedId and rownum < num;   end;     --create sequence tmp_pde_doc_ext_id increment by 960241436 minvalue 1 --select tmp_pde_doc_ext_id.nextval from dual --alter sequence tmp_pde_doc_ext_id increment by 1 minvalue 1
PMT_DOC	create or replace procedure fill_pmt_doc(cnt in number) is tableSize number; r number; maxPmtId number; minPmtId number; rows_in_iter number; n number; mAcc number; m number; minPD number;   begin   select max(pmt_doc_id) into minPmtId from pmt_doc; rows_in_iter:= 100000; n:= cnt; minPD:=minPmtId; if (n - rows_in_iter < 0) then rows_in_iter:= n; end if;   while (n > 0) loop select max(pmt_doc_id) into maxPmtId from pmt_doc; m:= round(dbms_random.value(minPmtId,maxPmtId));     insert into pmt_doc(PMT_DOC_ID,DEBIT_ACCOUNT_ID,CREDIT_ACCOUNT_ID, CASH_OPERATION_SORT_CODE,PAR_PMT_DOC_ID,IDENTITY_CARD_ID, OPER_JOURNAL_ID,PMT_TYPE_CODE,PMT_SORT_CODE,PMT_ORDER_TYPE_CODE, PAYER_BIC, BEN_BIC, CREDIT_CASH_SYMBOL_CODE, DEBIT_CASH_SYMBOL_CODE, PRINT_TMPL_TYPE_ID, PAYER_STATUS_CODE, PMT_GROUND_TYPE_CODE, CURRENCY_ID, MARK_DOC_CODE, DEBIT_CURRENCY_ID, CREDIT_CURRENCY_ID, PD_ORD_COMP_ENUM, PD_SIGN_ENUM, PD_NUM, PD_CASH_OPERATION_NUMBER, PD_PRINT_QTY, PD_REG_DATE, PD_DATE, PD_VALUE_DATE, PD_PAYER_ACC_NUM, PD_PAYER_NAME, PD_PAYER_INN, PD_PAYER_KPP, PD_PAYER_BANK_ACC_NUM, PD_BEN_ACC_NUM, PD_BEN_NAME, PD_BEN_INN, PD_BEN_KPP, PD_BEN_BANK_ACC_NUM, PD_AMOUNT, PD_PMT_DETAILS, PD_CBC, PD_OKATO, PD_FISKAL_PERIOD, PD_FISKAL_DOC_NUM, PD_FISKAL_DOC_DATE_C, PD_DEBIT_AMOUNT, PD_CREDIT_AMOUNT, CURRENCY_OPER_SORT_CODE, PD_PAYER_RESIDENT_FLAG, PD_BEN_RESIDENT_FLAG, PD_EXT_ID, PD_NUM_S, PD_CASH_OPERATION_NUMBER_S, PD_CCY_EXCH_RATE, PD_PAYER_IDENTITY_CARD, PD_PRINCIPAL, PD_PRINCIPAL_IDENTITY_CARD, PD_POA, PD_PRINT_FLAG, PD_PRINT_CLIENT_INFO_FLAG, PD_CASH_RECEIPT_NUM, PD_CASH_DISBURSEMT_NUM, PD_BEN_IDENTITY_CARD, BENEFICIARY_ID, MAIN_OPER_JOURNAL_ID, PD_POA_EXP_DATE, PD_TAX_CHARGE_UID, PD_CB_CODE, DEBIT_DEPT_ID, CREDIT_DEPT_ID, DEBIT_ID_SYS_CODE, CREDIT_ID_SYS_CODE, DEBIT_SYNT_ACC_ID, CREDIT_SYNT_ACC_ID, PD_PAYER_ADDRESS, DEBIT_AGR_ID, CREDIT_AGR_ID, PD_BENEFICIARY_SURNAME, PD_BENEFICIARY_FIRST_NAME, PD_BENEFICIARY_PATRONYMIC)   select s_pmt_doc.nextval, DEBIT_ACCOUNT_ID,CREDIT_ACCOUNT_ID, CASH_OPERATION_SORT_CODE,PAR_PMT_DOC_ID,IDENTITY_CARD_ID, OPER_JOURNAL_ID,PMT_TYPE_CODE,PMT_SORT_CODE,PMT_ORDER_TYPE_CODE, PAYER_BIC, BEN_BIC, CREDIT_CASH_SYMBOL_CODE, DEBIT_CASH_SYMBOL_CODE, PRINT_TMPL_TYPE_ID, PAYER_STATUS_CODE, PMT_GROUND_TYPE_CODE, CURRENCY_ID, MARK_DOC_CODE, DEBIT_CURRENCY_ID, CREDIT_CURRENCY_ID, PD_ORD_COMP_ENUM, PD_SIGN_ENUM, PD_NUM, PD_CASH_OPERATION_NUMBER, PD_PRINT_QTY, trunc(sysdate - dbms_random.value(1,300)), '01.01.2010', '01.01.2010', PD_PAYER_ACC_NUM, PD_PAYER_NAME, PD_PAYER_INN, PD_PAYER_KPP, PD_PAYER_BANK_ACC_NUM, PD_BEN_ACC_NUM, PD_BEN_NAME, PD_BEN_INN, PD_BEN_KPP, PD_BEN_BANK_ACC_NUM, PD_AMOUNT, PD_PMT_DETAILS, PD_CBC, PD_OKATO, PD_FISKAL_PERIOD, PD_FISKAL_DOC_NUM, PD_FISKAL_DOC_DATE_C, PD_DEBIT_AMOUNT, PD_CREDIT_AMOUNT, CURRENCY_OPER_SORT_CODE, PD_PAYER_RESIDENT_FLAG, PD_BEN_RESIDENT_FLAG, PD_EXT_ID, PD_NUM_S, PD_CASH_OPERATION_NUMBER_S, PD_CCY_EXCH_RATE, PD_PAYER_IDENTITY_CARD, PD_PRINCIPAL, PD_PRINCIPAL_IDENTITY_CARD, PD_POA, PD_PRINT_FLAG, PD_PRINT_CLIENT_INFO_FLAG, PD_CASH_RECEIPT_NUM, PD_CASH_DISBURSEMT_NUM, PD_BEN_IDENTITY_CARD, BENEFICIARY_ID, MAIN_OPER_JOURNAL_ID, PD_POA_EXP_DATE, PD_TAX_CHARGE_UID, PD_CB_CODE, DEBIT_DEPT_ID, CREDIT_DEPT_ID, DEBIT_ID_SYS_CODE, CREDIT_ID_SYS_CODE, DEBIT_SYNT_ACC_ID, CREDIT_SYNT_ACC_ID, PD_PAYER_ADDRESS, DEBIT_AGR_ID, CREDIT_AGR_ID, PD_BENEFICIARY_SURNAME, PD_BENEFICIARY_FIRST_NAME, PD_BENEFICIARY_PATRONYMIC from pmt_doc s left join tmp t on s.PMT_DOC_ID = t.tmp_id where (pmt_doc_id between minPmtId and m) and rownum < rows_in_iter;   maxPmtId:= maxPmtId + rows_in_iter; if (m >= rows_in_iter) then n:= n - rows_in_iter; else n:= n - m; end if;   minPmtId:= minPmtId + rows_in_iter; if (n > 0 and (minPmtId + rows_in_iter) > maxPmtId) then minPmtId:= minPD; end if; commit; end loop;   update pmt_doc set PD_DATE = PD_REG_DATE where pmt_doc_id > maxPmtId; commit; update pmt_doc set PD_VALUE_DATE = PD_REG_DATE where pmt_doc_id > maxPmtId; commit;   end fill_pmt_doc;
PERSON, IDENTITY_CARD, ADDRESS	create or replace procedure fill_persons(num in number) is addr VARCHAR2(20); pers_id number; curr_ident_type number; type_cnt integer; begin   select count(IDENTITY_CARD_TYPE_ID) into type_cnt from identity_card_type;     for cnt in 1..num loop addr:= concat(concat(dbms_random.string('x', 10), ', '),dbms_random.string('x', 5)); insert into ADDRESS (address_id, address_full) values(S_ADDRESS.nextval, addr);   pers_id:= s_person.nextval; insert into PERSON(person_id, person_reg_state_type_code, person_post_state_type_code, person_surname, person_first_name, person_patronymic, person_sex_enum, person_birth_date, person_reg_address, person_post_address, person_illiterate_flag, person_resident_flag, person_verified_flag, person_citizen_country_code, person_code_word, birth_country_code, person_refugee_flag, person_act_address) values(pers_id, 643, 643, dbms_random.string('x',7), dbms_random.string('x',7), dbms_random.string('x',8), 'M', '01.05.83', addr,addr,0,1,1,643,'МЕРКУРИЙ',643,0, addr);   select IDENTITY_CARD_TYPE_ID into curr_ident_type from (select IDENTITY_CARD_TYPE_ID from identity_card_type order by dbms_random.value) t where rownum = 1;   insert into identity_card(IDENTITY_CARD_ID, IDENTITY_CARD_TYPE_ID, PERSON_ID, COUNTRY_CODE,IC_SERIES,IC_NUM,IC_ISSUE_DATE,IC_ISSUE_AUTHORITY,IC_VALID_THRU_DATE, IC_ISSUE_AUTHORITY_CODE,IC_ISSUE_AUTHORITY_EN,IC_PRIMARY_FLAG,IC_DEL_FLAG) values (S_IDENTITY_CARD.nextval, curr_ident_type, pers_id, null, 4004, 189745, '12.12.12', 'ОВД', '31.12.29', '782-031', null, 1, 0);   end loop; end fill_persons;
FCY_CASH_REGISTER_OPER	create or replace procedure FILL_FCY_CASH_REGISTER_OPER(cnt in number) is rows_in_iter number; n number; begin   rows_in_iter:= 100000;   n:= cnt; if (n - rows_in_iter < 0) then rows_in_iter:= n; end if;   while (n >0) loop insert into fcy_cash_register_oper (PMT_DOC_ID, OPER_JOURNAL_ID, FCY_CASH_REGISTER_ID, FCRO_SN, FCRO_RATE, RECEIVED_CURRENCY_ID, FCRO_RECEIVED_AMOUNT, PAYOUT_CURRENCY_ID, FCRO_PAYOUT_AMOUNT, FCRO_USE_CARD_FLAG, FCRO_CHEQUE_QTY, CHEQUE_CURRENCY_ID, FCRO_CHEQUE_AMOUNT, ACCOUNT_ID, FCRO_DISPONENT_SORT_ENUM, FCY_IN_CASH_N_CHK_SORT_CODE, FCRO_ACCOUNT_NUM) select pd.pmt_doc_id, main_oper_journal_id, S_FCY_CASH_REGISTER.nextval, 5, 40.55, 130, round(dbms_random.value(1,200000)),126, null,1,null,null, null,null,null,'P15',null from pmt_doc pd left join fcy_cash_register_oper f on pd.pmt_doc_id= f.pmt_doc_id where rownum < rows_in_iter and f.pmt_doc_id is null;   n:= n - rows_in_iter; end loop;   end;
USER_SESSION	create or replace function fill_user_session(cnt in number) return number is beginDate date; rows_in_iter number; max_us_id number; min_us_id number; n number; begin   beginDate:= sysdate - dbms_random.value(1,250); dbms_output.put_line(beginDate);   n:= cnt;   rows_in_iter:= 10000; if (cnt <= 0) then n:=1; rows_in_iter:= 1; else if (cnt - rows_in_iter) < 0 then rows_in_iter:= cnt; else rows_in_iter:= 10000; end if; end if;   select max(user_session_id) into max_us_id from user_session; select max(user_session_id) into min_us_id from user_session;   while (n > 0) loop beginDate:= sysdate - dbms_random.value(1,250); insert into user_session (user_session_id, us_begin_time, us_end_time, us_status_enum, us_operday_date, dept_id, computer_id, user_id, us_ip, us_user_agent, us_server_name, us_http_session_id, real_user_id) select s_user_session.nextval, beginDate, beginDate, us_status_enum, null, dept_id, computer_id,user_id, us_ip, us_user_agent, us_server_name, us_http_session_id, real_user_id from user_session where user_session_id > min_us_id and rownum <= rows_in_iter;   commit; min_us_id:= min_us_id + rows_in_iter; n:= n - rows_in_iter; end loop;   return max_us_id; end fill_user_session;
VALUABLE_BALANCE	create or replace   procedure fill_val_balance(cnt in number)   is   cntToInsert number;   currReg number;   nextId number;   currBal number;   accountId number;   vSortId number;   deptID number;   tdate date;   begin   select count(*) into currReg from valuable_register;   select count(*) into currBal from valuable_balance;   cntToInsert:= cnt * currReg / currBal;   if (cntToInsert < 1) then cntToInsert:= 1;   end if;   for i in 1..cntToInsert   loop   select account_id into accountId from account sample (0.1) where rownum < 2 order by dbms_random.value;   select dept_id into deptID from dept sample (10) where rownum < 2 order by dbms_random.value;   select vs.valuable_sort_id into vSortId from valuable_sort vs left join valuable_register vr on vs.valuable_sort_id = vr.valuable_sort_id   where (vr.valuable_sort_id is null or dept_id is null) and rownum < 2 order by dbms_random.value;   select s_valuable_register.nextval into nextId from dual;   --valuable register;   insert into valuable_register(VALUABLE_REGISTER_ID, DEPT_ID, VALUABLE_SORT_ID, ACCOUNT_ID)   values (nextId, deptID, vsortid, accountid);   commit;   --valueable_balance   for i in 1..round(currBal/currReg)   loop   tDate:= sysdate - i;   insert into valuable_balance(VALUABLE_REGISTER_ID, VB_DATE, VB_QUANTITY,   VB_BALANCE, VB_RESERVED_BALANCE, VB_RESERVED_QTY)   values(nextId, trunc(tDate), round(dbms_random.value(0,100)),   round(dbms_random.value(1,100000)), 0, 0);   commit;   end loop;   end loop;   end;
POS_REGISTER_OPER	create or replace procedure FILL_POS_REGISTER_OPER(cnt in number) is rows_in_iter number; n number; min_oj_id number; max_oj_id number; init_min_oj_id number; begin   rows_in_iter:= 300000; n:= cnt;   select min(oj.oper_journal_id)into min_oj_id from FCY_CASH_REGISTER_OPER oj left join pos_register_oper p on oj.oper_journal_id = p.oper_journal_id where oj.oper_journal_id > 1000000 and p.oper_journal_id is null;   select max(oper_journal_id) into max_oj_id from FCY_CASH_REGISTER_OPER; init_min_oj_id:= min_oj_id;   if (n - rows_in_iter < 0) then rows_in_iter:= n; end if;   while (n >0) loop   insert into pos_register_oper (OPER_JOURNAL_ID, PRO_CARD_OPER_TYPE_ENUM, POS_OPER_REGISTER_ID, PRO_SN, PRO_CARD_NUM, PRO_AUTH_CODE, PRO_TERMINAL_CODE, PRO_REF_CODE, PRO_RESPONSE_CODE, PRO_SALES_SLIP_NUM, PRO_BATCH_NUM, PRO_RRN, PRO_EXP_DATE, PRO_OWN_CARD_FLAG, PRO_ACCOUNT_NUM, PRO_PARAM1, PRO_PARAM2, PRO_PARAM3, CARD_DEPT_ID, PRO_MULTI_CURRENCY_FLAG) select distinct oj.oper_journal_id, ceil(dbms_random.value(1,2)), 1199, ceil(dbms_random.value(1,5)), ceil(dbms_random.value(4272000000009795,4272999999999795)), '509216','55535101','000321','256', '787878787',0,'310901464561','30.09.17',1,'40702810500000101768', '266995',null,null,null, 0 from FCY_CASH_REGISTER_OPER oj left join pos_register_oper p on oj.oper_journal_id = p.oper_journal_id where rownum < rows_in_iter and oj.OPER_JOURNAL_ID between min_oj_id and max_oj_id and p.oper_journal_id is null;   n:= n - rows_in_iter;   min_oj_id:= min_oj_id + rows_in_iter; if (min_oj_id >= max_oj_id) then min_oj_id:= init_min_oj_id; end if; commit;   end loop;   end;
LIM_REC_POSTING	create or replace procedure fill_lim_rec_posting(min_lim_rec_id in number, min_oper_id in number, cnt in number) is n number; rows_in_iter number; m_LR_Id number; min_lrp_id number; type rowid_nt is table of rowid; lrp rowid_nt; oj numbertype; begin   n:=cnt; rows_in_iter:= 100000; m_LR_Id:= min_lim_rec_id;   select max(LIM_REC_POSTING_ID) into min_lrp_id from LIM_REC_POSTING; while (n > 0) loop   insert into lim_rec_posting(LIM_REC_POSTING_ID, LIM_REC_ID, LRP_ACTION_TIME, LRP_AMOUNT, OPER_JOURNAL_ID) select s_lim_rec_posting.nextval, lim_rec_id, trunc(sysdate-dbms_random.value(1,200)),1, null from lim_rec where lim_rec_id > m_LR_Id and rownum <= rows_in_iter; m_LR_Id:= m_LR_Id + rows_in_iter; n:= n - rows_in_iter; commit; end loop;   select r bulk collect into lrp from ( select rowid r from lim_rec_posting where LIM_REC_POSTING_ID > min_lim_rec_id ) rndm; select r bulk collect into oj from ( select oper_journal_id r from oper_journal where oper_journal_id > min_oper_id and rownum < (select max(LIM_REC_POSTING_ID) - min_lrp_id from LIM_REC_POSTING) )rndm;   forall j in 1..lrp.count update lim_rec_posting set oper_journal_id = oj(j) where rowid = lrp(j); commit; end;
LIM_REC	create or replace function fill_lim_rec(minaccId in number, cnt in number) return number is min_limrec_id number; n number; iter number; rows_in_iter number; mAccId number; maxAccId number; begin   select max(lim_rec_id) into min_limrec_id from lim_rec; select max(account_id) into maxAccId from account; n:=cnt; mAccId:= minaccId; rows_in_iter:= 100000; if (cnt - rows_in_iter) < 0 then rows_in_iter:= cnt; else rows_in_iter:= 100000; end if;   while (n > 0) loop   insert into lim_rec(LIM_REC_ID,LIM_ID,LR_START_TIME, LR_CATEGORY_ENUM,LR_OBJ_ID) select s_lim_rec.nextval, 2, trunc(sysdate-dbms_random.value(1,200)),1, account_id from account where account_id > mAccId and rownum <= rows_in_iter; mAccId:= mAccId + rows_in_iter; if (mAccId >= maxAccId) then mAccId:=minaccId; end if; n:=n-rows_in_iter; commit; end loop;   update lim_rec set lr_next_time = lr_start_time where lim_rec_id > min_limrec_id; commit;   return min_limrec_id; end fill_lim_rec;
ACCOUNT	create or replace   function fill_account(num in number) return number   is   i number;   cnt number;   mId number;   begin   select count(id) into cnt from tmp_client;   i:=num;   mId:= s_account.nextval;   loop   insert into account (ACCOUNT_ID, DEPT_ID,   CURRENCY_ID, CLIENT_ID, SYNT_ACC_ID,   ACCOUNT_NUM, ACCOUNT_REG_DATE, ACCOUNT_STATUS_ENUM,   ACCOUNT_NAME, EXECUTIVE_USER_ID)   select s_account.nextval, t.d_id, 126, t.cl_id, t.s_acc_id, round(dbms_random.value(40700000000000000000,40799999999999999999)),   '16.04.09',0,'ÚÂÍÛ˘ËÈ Ò˜ÂÚ',null   from tmp_client t where rownum <= i;   if (i = 0) then i:=-1;   else i:= i - cnt;   end if;   commit;   exit when i <= 0;   end loop;   return (mId + 1);   end fill_account;
BALANCE	reate or replace procedure fill_balance(num in number)   is   cnt number;   bDate date;   begin   if num = -1   then   select count(a.account_id) into cnt from account a left join balance b on a.account_id = b.account_id   where b.account_id is null;   else   cnt:= num;   end if;   insert into balance(ACCOUNT_ID,BALANCE_DATE,   BALANCE_VALUE, BALANCE_VALUE_DATED, BALANCE_DEBIT, BALANCE_CREDIT)   select a.account_id,trunc(sysdate - dbms_random.value(1,250)),1000000,1000000,0,1000000   from account a left join balance b on a.account_id = b.account_id   where b.account_id is null and rownum <= cnt;   end fill_balance;

 

Планируемые тесты