Описание работы АС и заглушек
Системы, работающие через 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; |
Планируемые тесты