| |||||
МЕНЮ
| Межбанковский клиринг-- Возвращает текущее дебетовое сальдо банка-участника FUNCTION GetCurrentDebitSaldo(aBankID IN INT) RETURN INT IS Res INT; BEGIN BEGIN SELECT DebitPos INTO Res FROM TmpRes WHERE BankID = aBankID; EXCEPTION WHEN No_Data_Found THEN Res := 0; END; RETURN Res; END GetCurrentDebitSaldo; -- Возвращает текущее кредитовое сальдо банка-участника FUNCTION GetCurrentCreditSaldo(aBankID IN INT) RETURN INT IS Res INT; BEGIN BEGIN SELECT CreditPos INTO Res FROM TmpRes WHERE BankID = aBankID; EXCEPTION WHEN No_Data_Found THEN Res := 0; END; RETURN Res; END GetCurrentCreditSaldo; END CommonUtils; / SHOW ERROR; -------------------------------------------------------- -- Модуль содержит глобальные константы -------------------------------------------------------- PROMPT Creating package GlobalConst CREATE OR REPLACE PACKAGE GlobalConst AS cStatusClass CONSTANT INT := -1; cBankWork CONSTANT INT := 1; cBankStopWork CONSTANT INT := 2; cBankNotWork CONSTANT INT := 3; cMsgNotProcessed CONSTANT INT := 1; cMsgAccepted CONSTANT INT := 2; cMsgSourceError CONSTANT INT := 3; cMsgDebitError CONSTANT INT := 4; cMsgCreditError CONSTANT INT := 5; cMsgProcessed CONSTANT INT := 6; cSessionUnActive CONSTANT INT := 1; cSessionActive CONSTANT INT := 2; END GlobalConst; / SHOW ERROR; ------------------------------------------------------ -- Модуль инициализации системы в СУБД Oracle 7.3.3 ------------------------------------------------------ CONNECT INTERNAL SPOOL createdb.log SET ECHO ON CREATE TABLESPACE "CLEARING" DATAFILE 'C:\ORAWIN95\DATABASE\clearing.dbf' SIZE 5M REUSE DEFAULT STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 120 PCTINCREASE 0); CREATE USER "ALEX" IDENTIFIED BY "ALEX" DEFAULT TABLESPACE "CLEARING"; GRANT "DBA" TO "ALEX" WITH ADMIN OPTION; GRANT UNLIMITED TABLESPACE to "ALEX" WITH ADMIN OPTION; GRANT SELECT ANY TABLE to "ALEX" WITH ADMIN OPTION; GRANT GRANT ANY ROLE to "ALEX" WITH ADMIN OPTION; GRANT CREATE ANY SYNONYM to "ALEX" WITH ADMIN OPTION; GRANT DROP ANY SYNONYM to "ALEX" WITH ADMIN OPTION; GRANT EXECUTE ANY PROCEDURE to "ALEX"; GRANT CREATE USER to "ALEX"; GRANT ALTER USER to "ALEX"; GRANT DROP USER to "ALEX"; GRANT GRANT ANY PRIVILEGE to "ALEX"; SPOOL OFF EXIT -------------------------------------------------- -- Начальное создание объектов системы -------------------------------------------------- PROMPT Creating databases and sequences... @@consts.pkg @@banks @@messages @@status @@statinit @@bnkstate @@results @@tmpres @@statist PROMPT Creating packages... @@srvrutil.pkg @@srvrutil.bdy @@clntutil.pkg @@clntutil.bdy @@commutil.pkg @@commutil.bdy ------------------------------------------------------------- -- Таблица и последовательность для обработки сообщений ------------------------------------------------------------- PROMPT Creating sequence Msg_Seq... CREATE SEQUENCE Msg_Seq INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 MINVALUE 1 NOCACHE CYCLE; PROMPT Creating table Messages... CREATE TABLE Messages ( MessageID NUMBER(8) -- Номер сообщения CONSTRAINT message_pk PRIMARY KEY, SourceBankID NUMBER(8) -- Банк-источник сообщения CONSTRAINT sourcebank_fk REFERENCES Banks ON DELETE CASCADE, DebitBankID NUMBER(8) -- Банк-получатель CONSTRAINT debitbank_fk REFERENCES Banks ON DELETE CASCADE, CreditBankID NUMBER(8) -- Банк-плательщик CONSTRAINT creditbank_fk REFERENCES Banks ON DELETE CASCADE, Amount INT NOT NULL, -- Сумма MsgStatus INT NOT NULL -- Статус сообщения ); -------------------------------------------------------------- -- Таблица окончательных результатов клирингового сеанса -------------------------------------------------------------- PROMPT Creating table Results... CREATE TABLE RESULTS ( BankID NUMBER(8) CONSTRAINT resultbankid_fk REFERENCES Banks ON DELETE CASCADE, DebitPos INT, CreditPos INT ); ------------------------------------------------------- -- Интерфейс модуля "АРМ оператора клиринговой палаты" ------------------------------------------------------- PROMPT Creating package ServerUtils... CREATE OR REPLACE PACKAGE ServerUtils AS SessionState INT DEFAULT GlobalConst.cSessionUnActive; PROCEDURE StartClearingSession; PROCEDURE StopClearingSession; FUNCTION GetActiveBanksCountInSession RETURN INT; -- Процедура регистрации банка-участника клиринговой системы FUNCTION RegisterBank(aBankName IN VARCHAR2, aUserName IN VARCHAR2, aPassword IN VARCHAR2) RETURN INT; PROCEDURE UnRegisterBank(aBankName IN VARCHAR2); -- Процедура выхода банка из клиринговой системы PROCEDURE UnRegisterBank(aBankID IN INT); FUNCTION CheckMessage(aMessageID IN INT) RETURN INT; PROCEDURE ConfirmMessage(aMessageID IN INT); END ServerUtils; / SHOW ERROR; ----------------------------------------------- -- Процедуры и функции сервера КП ----------------------------------------------- PROMPT Creating package body ServerUtils... CREATE OR REPLACE PACKAGE BODY ServerUtils AS -- Процедура инициализации клирингового сенса в клиринговой палате -- Выполняется каждый день в определенное время PROCEDURE StartClearingSession IS BEGIN DELETE FROM Messages; DELETE FROM BankState; DELETE FROM Results; DELETE FROM TmpRes; FOR x IN ( SELECT BankID FROM Banks ) LOOP INSERT INTO BankState (BankID, BankStatus) VALUES (x.BankID, GlobalConst.cBankNotWork); END LOOP; SessionState := GlobalConst.cSessionActive; END StartClearingSession; -- Процедура завершения клирингового сеанса -- Выполняется каждый день PROCEDURE StopClearingSession IS aDebitSum INT; aCreditSum INT; BEGIN SessionState := GlobalConst.cSessionUnActive; UPDATE BankState SET BankStatus = GlobalConst.cBankStopWork WHERE BankStatus = GlobalConst.cBankWork; FOR x IN ( SELECT BankID FROM BankState WHERE BankStatus = GlobalConst.cBankStopWork ) LOOP BEGIN SELECT Sum(Amount) INTO aDebitSum FROM Messages WHERE MsgStatus = GlobalConst.cMsgAccepted AND DebitBankID = x.BankID; EXCEPTION WHEN No_Data_Found THEN aDebitSum := 0; END; BEGIN SELECT Sum(Amount) INTO aCreditSum FROM Messages WHERE MsgStatus = GlobalConst.cMsgAccepted AND CreditBankID = x.BankID; EXCEPTION WHEN No_Data_Found THEN aCreditSum := 0; END; INSERT INTO Results(BankID, DebitPos, CreditPos) VALUES(x.BankID, aDebitSum, aCreditSum); END LOOP; INSERT INTO Statistics(ItemNo, WorkDay, BankID, DebitPos, CreditPos) SELECT Stat_Seq.NextVal, SYSDATE, BankID, DebitPos, CreditPos FROM Results; DELETE FROM Messages; DELETE FROM BankState; DELETE FROM Results; DELETE FROM TmpRes; END StopClearingSession; -- Возвращает количество активных участников текущего сеанса FUNCTION GetActiveBanksCountInSession RETURN INT IS Res INT; BEGIN BEGIN SELECT Count(*) INTO Res FROM BankState WHERE BankStatus = GlobalConst.cBankWork; EXCEPTION WHEN No_Data_Found THEN Res :=0; END; RETURN Res; END GetActiveBanksCountInSession; -- Регистрирует новый банк в клиринговой системе FUNCTION RegisterBank(aBankName IN VARCHAR2, aUserName IN VARCHAR2, aPassword IN VARCHAR2) RETURN INT IS Res INT; Cur INT; Col INT; BEGIN BEGIN SELECT BankID INTO Res FROM Banks WHERE BankName = aBankName; EXCEPTION WHEN No_Data_Found THEN SELECT BankID_Seq.NextVal INTO Res FROM Dual; INSERT INTO Banks(BankID, BankName, UserName) VALUES (Res, aBankName, aUserName); cur:=dbms_sql.open_cursor; dbms_sql.parse(cur, 'CREATE USER '||aUserName||' IDENTIFIED BY '||aPassword, dbms_sql.v7); Col := dbms_sql.execute(Cur); dbms_sql.parse(cur, 'GRANT CREATE SESSION to '||aUserName, dbms_sql.v7); Col := dbms_sql.execute(Cur); dbms_sql.close_cursor(cur); END; RETURN Res; END RegisterBank; -- Удаляет банк из числа участников по его номеру PROCEDURE UnRegisterBank(aBankID IN INT) IS BEGIN DELETE FROM Banks WHERE BankID = aBankID; END UnRegisterBank; -- Удаляет банк из числа участников по его имени PROCEDURE UnRegisterBank(aBankName IN VARCHAR2) IS BEGIN DELETE FROM Banks WHERE BankName = aBankName; END UnRegisterBank; -- Проверяет допустимость поступившего платежного документа FUNCTION CheckMessage(aMessageID IN INT) RETURN INT IS aMsgStatus INT; aBankStatus INT; aSourceBank INT; aDebitBank INT; aCreditBank INT; BEGIN SELECT SourceBankID INTO aSourceBank FROM Messages WHERE MessageID = aMessageID; SELECT DebitBankID INTO aDebitBank FROM Messages WHERE MessageID = aMessageID; SELECT CreditBankID INTO aCreditBank FROM Messages WHERE MessageID = aMessageID; BEGIN SELECT BankStatus INTO aBankStatus FROM BankState WHERE BankID = aSourceBank; EXCEPTION WHEN No_Data_Found THEN UPDATE Messages SET MsgStatus = GlobalConst.cMsgSourceError WHERE MessageID = aMessageID; RETURN GlobalConst.cMsgSourceError; END; IF aBankStatus = GlobalConst.cBankNotWork THEN UPDATE Messages SET MsgStatus = GlobalConst.cMsgSourceError WHERE MessageID = aMessageID; RETURN GlobalConst.cMsgSourceError; END IF; BEGIN SELECT BankStatus INTO aBankStatus FROM BankState WHERE BankID = aDebitBank; EXCEPTION WHEN No_Data_Found THEN UPDATE Messages SET MsgStatus = GlobalConst.cMsgDebitError WHERE MessageID = aMessageID; RETURN GlobalConst.cMsgDebitError; END; IF aBankStatus = GlobalConst.cBankNotWork THEN UPDATE Messages SET MsgStatus = GlobalConst.cMsgDebitError WHERE MessageID = aMessageID; RETURN GlobalConst.cMsgDebitError; END IF; BEGIN SELECT BankStatus INTO aBankStatus FROM BankState WHERE BankID = aCreditBank; EXCEPTION WHEN No_Data_Found THEN UPDATE Messages SET MsgStatus = GlobalConst.cMsgCreditError WHERE MessageID = aMessageID; RETURN GlobalConst.cMsgCreditError; END; IF aBankStatus = GlobalConst.cBankNotWork THEN UPDATE Messages SET MsgStatus = GlobalConst.cMsgCreditError WHERE MessageID = aMessageID; RETURN GlobalConst.cMsgCreditError; END IF; UPDATE Messages SET MsgStatus = GlobalConst.cMsgProcessed WHERE MessageID = aMessageID; RETURN GlobalConst.cMsgProcessed; END CheckMessage; -- Подтверждает платежный документ, вызывается при наличии необходимых -- документов PROCEDURE ConfirmMessage(aMessageID IN INT) IS aMsgStatus INT; aTmpID INT; aAmount INT; aDebitBank INT; aCreditBank INT; BEGIN SELECT MsgStatus, DebitBankID, CreditBankID INTO aMsgStatus, aDebitBank, aCreditBank FROM Messages WHERE MessageID = aMessageID; IF aMessageID = GlobalConst.cMsgProcessed THEN UPDATE Messages SET MsgStatus = GlobalConst.cMsgAccepted WHERE MessageID = aMessageID; SELECT Amount INTO aAmount FROM Messages WHERE MessageID = aMessageID; BEGIN SELECT BankID INTO aTmpID FROM TmpRes WHERE BankID = aDebitBank; EXCEPTION WHEN No_Data_Found THEN INSERT INTO TmpRes (BankID, DebitPos, CreditPos) VALUES (aDebitBank, 0, 0); END; UPDATE TmpRes SET DebitPos = DebitPos + aAmount WHERE BankID = aDebitBank; BEGIN SELECT BankID INTO aTmpID FROM TmpRes WHERE BankID = aCreditBank; EXCEPTION WHEN No_Data_Found THEN INSERT INTO TmpRes (BankID, DebitPos, CreditPos) VALUES (aCreditBank, 0, 0); END; UPDATE TmpRes SET CreditPos = CreditPos - aAmount WHERE BankID = aCreditBank; END IF; END ConfirmMessage; END ServerUtils; / SHOW ERROR; --------------------------------------------------------- -- Таблица и последовательность для ведения статистики --------------------------------------------------------- PROMPT Create table Statistics... PROMPT Creating sequence Stat_Seq... CREATE SEQUENCE Stat_Seq INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 MINVALUE 1 NOCACHE CYCLE; CREATE TABLE Statistics ( ItemNo NUMBER(8) -- Номер элемента выборки CONSTRAINT itemno_pk PRIMARY KEY, WorkDay DATE, -- Дата и время платежа BankID NUMBER(8) -- Номер банка DebitPos INT, -- Дебетовая позиция CreditPos INT -- Кредитовая позиция ); ----------------------------------------------- -- Заполнение таблицы классификаторов ----------------------------------------------- PROMPT Inserting data into table Status ... BEGIN DELETE FROM Status WHERE StatusClass = 1; INSERT INTO Status VALUES (1, GlobalConst.cStatusClass, 'Статус банка в текущем сеансе'); INSERT INTO Status VALUES (1, GlobalConst.cBankWork, 'Банк участвует в текущем сеансе'); INSERT INTO Status VALUES (1, GlobalConst.cBankStopWork, 'Банк закончил текущий сенс'); INSERT INTO Status VALUES (1, GlobalConst.cBankNotWork, 'Банк не участвует в текущем сенсе'); COMMIT; DELETE FROM Status WHERE StatusClass = 2; INSERT INTO Status VALUES (2, GlobalConst.cStatusClass, 'Статус сообщения о платеже'); INSERT INTO Status VALUES (2, GlobalConst.cMsgNotProcessed, 'Сообщение не обработано'); INSERT INTO Status VALUES (2, GlobalConst.cMsgSourceError, 'Банк-источник не работает'); INSERT INTO Status VALUES (2, GlobalConst.cMsgDebitError, 'Банк по дебету счета не работает'); INSERT INTO Status VALUES (2, GlobalConst.cMsgCreditError, 'Банк по кредиту счета не работает'); INSERT INTO Status VALUES (2, GlobalConst.cMsgProcessed, 'Сообщение обработано и требует документального подтверждения'); INSERT INTO Status VALUES (2, GlobalConst.cMsgAccepted, 'Сообщение обработано и принято в систему взаимозачета'); COMMIT; END; / ----------------------------------------------- -- Таблица классификаторов ----------------------------------------------- PROMPT Create table Status ... CREATE TABLE Status ( StatusClass NUMBER(3), StatusNo NUMBER(3), StatusText VARCHAR2(70) NOT NULL, CONSTRAINT status_pk PRIMARY KEY (StatusClass, StatusNo) ); ------------------------------------------------------- -- Таблица текущих результатов клирингового сеанса ------------------------------------------------------- PROMPT Creating table TmpRes... CREATE TABLE TmpRes ( BankID NUMBER(8) CONSTRAINT tmpresbankid_fk REFERENCES Banks ON DELETE CASCADE, DebitPos INT, CreditPos INT ); --------------------------------------------------- -- Удаление объектов системы --------------------------------------------------- PROMPT Deleting databases... SET ECHO ON; DROP SEQUENCE Stat_Seq; DROP TABLE Statistics; DROP TABLE BankState; DROP TABLE Status; DROP SEQUENCE Msg_Seq; DROP TABLE Messages; DROP TABLE Results; DROP TABLE TmpRes; DROP SEQUENCE BankID_Seq; DROP TABLE Banks; PROMPT Deleting packages... DROP PACKAGE BODY ServerUtils; DROP PACKAGE ServerUtils; DROP PACKAGE BODY ClientUtils; DROP PACKAGE ClientUtils; DROP PACKAGE BODY CommonUtils; DROP PACKAGE CommonUtils; SET ECHO OFF; ---------------------------------------------------------------- ----------------------- [pic] [pic] [pic] |
ИНТЕРЕСНОЕ | |||
|