`

Oracle Stored Procedure Sample

 
阅读更多

 

CREATE OR REPLACE PROCEDURE TEST

(

  IN_BANK IN VARCHAR2,

  IN_BROKERTYPE IN VARCHAR2,

  IN_ISOESFORCELOGON IN VARCHAR2

)

AS

prodaltnum prod_alt_cde.prod_alt_num%TYPE;

var_midfsDownNo NUMBER(2):=-1;

var_fdsLinkStatusDownNo NUMBER(2):=-1;

var_fdsLinkStatusUpNo NUMBER(2):=-1;

var_oesLinkStatus VARCHAR2(1):='F';

var_timeout NUMBER(3):=300;

var_sleeptime NUMBER(2):=10;

var_sleeptimeTotal NUMBER(3):=0;

var_mktWatchStatusNoP NUMBER(2):=-1;

var_mktWatchStatusNoS NUMBER(2):=-1;

 

exp_invalidBank EXCEPTION;

exp_midfs EXCEPTION;

exp_fds EXCEPTION;

exp_oesForceLogon EXCEPTION;

exp_stopFDSFailed EXCEPTION;

BEGIN

 

---------------------------------------------

IF IN_BANK != 'HHHH' AND IN_BANK != 'SSSS' THEN

  RAISE exp_invalidBank;

  COMMIT;

END IF;

 

---- STEP 0, enable MIDFS link, full price download for later order preparation

-- UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'N', SESS_LA_SEQ_NUM = -1;

-- COMMIT;

 

-- var_sleeptimeTotal := 0;

-- WHILE var_midfsDownNo != 0 AND var_sleeptimeTotal < var_timeout LOOP

--   DBMS_LOCK.SLEEP(var_sleeptime);

--   var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

--   SELECT Count(*) INTO var_midfsDownNo FROM MKT_DATA_FEED_STAT WHERE SESS_LA_SEQ_NUM = -1;

-- END LOOP;

 

-- IF var_sleeptimeTotal >= var_timeout THEN

--   RAISE exp_midfs;

-- END IF;

 

---- STEP 0, disable MIDFS link,

-- UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'Y';

-- COMMIT;

 

---- STEP 0, reset Location

IF IN_BANK = 'HHHH' THEN

  update MKT_DATA_FEED_STAT set LOC_PRC_SERVR_CDE=1 WHERE isnt_name in ('HKHHHHMF01','HKHHHHMF11','HKHHHHMF21','HKHHHHMF31','HKHHHHMF41','HKHHHHMF51');

  update MKT_DATA_FEED_STAT set LOC_PRC_SERVR_CDE=2 WHERE isnt_name in ('HKHHHHMF02','HKHHHHMF12','HKHHHHMF22','HKHHHHMF32','HKHHHHMF42','HKHHHHMF52');

  COMMIT;

ELSIF IN_BANK = 'SSSS' THEN

  update MKT_DATA_FEED_STAT set LOC_PRC_SERVR_CDE=1 WHERE isnt_name in ('HKSSSSMF01','HKSSSSMF11','HKSSSSMF21','HKSSSSMF31','HKSSSSMF41','HKSSSSMF51');

  update MKT_DATA_FEED_STAT set LOC_PRC_SERVR_CDE=2 WHERE isnt_name in ('HKSSSSMF02','HKSSSSMF12','HKSSSSMF22','HKSSSSMF32','HKSSSSMF42','HKSSSSMF52');

  COMMIT;

END IF;

 

-- Patch for both FDS/OES --

UPDATE RTP_SYS_PARM SET SYS_PARM_VALUE_TEXT = (SELECT TO_CHAR(SYS_AUTO_GENR_CURR_DT,'yyyyMMdd')

FROM RTP_EXCHG_CTRY_DT WHERE CTRY_PROD_EXCHG_MKT_CDE = 'HK')

WHERE SYS_PARM_NAME IN ('BATCH_COND_ORDER_RESEND_DATE','BATCH_UNCOND_ORDER_RESEND_DATE','LAST_ORDER_REPOSTING_DATE') AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

COMMIT;

 

---------------------------------------------

---- STEP 1, update SYS_PARM (update this if require OES. Reminded that TIG is also required in MR mode - send heartbeat to OES in order to maintain link status) <--- even if FDS only, should run this step also!!!!

UPDATE TRD_INTF_CTL

SET trd_ifc_ctl_prm_text = (SELECT to_char(sys_auto_genr_curr_dt,'yyyyMMdd') FROM RTP_EXCHG_CTRY_DT where CTRY_PROD_EXCHG_MKT_CDE = 'HK')

WHERE trd_ifc_ctl_prm_type = 'tryOnDate' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

 

UPDATE TRD_INTF_CTL

SET trd_ifc_ctl_prm_text = (SELECT to_char(sys_auto_genr_curr_dt - 10,'yyyyMMdd') FROM RTP_EXCHG_CTRY_DT where CTRY_PROD_EXCHG_MKT_CDE = 'HK')

WHERE trd_ifc_ctl_prm_type = 'tryOffDate' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

COMMIT;

 

---------------------------------------------

---- STEP 2, SQL for toggling percentage (HHHH ONLY)

-- verify

-- select * from DEAL_PARTY_REC_LOG_CNTL WHERE BROKER_CNTNG_MODE_CDE = 'N' AND GRP_SYS_PARM_CUST_CDE = 'PFS';

 

IF IN_BANK = 'HHHH' THEN

  -- update OES

  UPDATE DEAL_PARTY_REC_LOG_CNTL SET DEAL_BROKER_SYS_PROC_PCT = 0

  WHERE DEAL_PARTY_CDE = 'B01089'

  AND BROKER_CNTNG_MODE_CDE = 'N'

  AND GRP_SYS_PARM_CUST_CDE = 'PFS';

 

  -- update FDS

  UPDATE DEAL_PARTY_REC_LOG_CNTL SET DEAL_BROKER_SYS_PROC_PCT = 100

  WHERE DEAL_PARTY_CDE = 'B01490'

  AND BROKER_CNTNG_MODE_CDE = 'N'

  AND GRP_SYS_PARM_CUST_CDE = 'PFS';

 

  COMMIT;

END IF;

 

---------------------------------------------

---- STEP 3, update Trading session on SUNDAY

UPDATE TRD_SESS SET TRD_SESS_START_TM = '50000', TRD_SESS_END_TM = '85959'

WHERE PROD_TYPE='LEQU' and TRD_SB_SESS_CDE='SESS01' and TRD_SESS_CDE='NORMAL' AND WKDY_TDY_CDE = 7;

 

UPDATE TRD_SESS SET TRD_SESS_START_TM = '90000', TRD_SESS_END_TM = '230159'

WHERE PROD_TYPE='LEQU' and TRD_SB_SESS_CDE='SESS02' and TRD_SESS_CDE='NORMAL' AND WKDY_TDY_CDE = 7;

 

UPDATE TRD_SESS SET TRD_SESS_START_TM = '230200', TRD_SESS_END_TM = '235959'

WHERE PROD_TYPE='LEQU' and TRD_SB_SESS_CDE='SESS03' and TRD_SESS_CDE='NORMAL' AND WKDY_TDY_CDE = 7;

 

COMMIT;

 

-- STEP3, Health check

-- select * from TRD_SESS where PROD_TYPE='LEQU' and TRD_SESS_CDE='NORMAL' AND WKDY_TDY_CDE = 7;

 

---------------------------------------------

---- STEP 4, Enable OES Link status (update this if require OES.)

---- Reminded that TIG is also required in MR mode - send heartbeat to OES in order to maintain link status)

---- STEP 4: Method 1

IF IN_BROKERTYPE = 'OES' AND IN_ISOESFORCELOGON = 'NO' THEN

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'N' WHERE SYS_PROD_SUPLY_NUM='OES' AND TRD_IFC_CTL_PRM_TYPE='ctgIdc';

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'false' WHERE SYS_PROD_SUPLY_NUM='OES' AND TRD_IFC_CTL_PRM_TYPE='autoSwtOff';

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = '20130312160000' WHERE TRD_IFC_CTL_PRM_TYPE in ('shbDatTime','rhbDatTime') AND SYS_PROD_SUPLY_NUM = 'OES';

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'L' where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='linkStatus';

  COMMIT;

 

  DBMS_OUTPUT.PUT_LINE('Enabled OES Link.');

  ---- STEP 4, Enable OES Link status HEALTH CHECK

  -- select * from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='linkStatus';

  -- select * from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='autoSwtOff';

  -- select * from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE in ('shbDatTime','rhbDatTime');

  -- SELECT * FROM TRD_INTF_CTL WHERE SYS_PROD_SUPLY_NUM = 'OES' AND TRD_IFC_CTL_PRM_TYPE ='ctgIdc';

 

---- STEP 4: Method 2

---- LOGON OES using ForceLogon

---- **(don't apply to automated script)**

ELSIF IN_BROKERTYPE = 'OES' AND IN_ISOESFORCELOGON = 'YES' THEN

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'true' WHERE SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='forceLogon';

  COMMIT;

 

  ---- After patching to 'true', it triggered logon to OES automatically

  var_sleeptimeTotal := 0;

  WHILE var_oesLinkStatus != 'L' AND var_sleeptimeTotal < var_timeout LOOP

    DBMS_LOCK.SLEEP(var_sleeptime);

    var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

    SELECT TRD_IFC_CTL_PRM_TEXT INTO var_oesLinkStatus from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='linkStatus';

  END LOOP;

 

  IF var_sleeptimeTotal >= var_timeout THEN

    RAISE exp_oesForceLogon;

  END IF;

 

  DBMS_OUTPUT.PUT_LINE('Enabled OES Link with ForceLogon.');

END IF;

 

---------------------------------------------

---- STEP 5, Enable GFIX Link status (HHHH only)

IF IN_BANK = 'HHHH' AND IN_BROKERTYPE = 'FDS' THEN

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'L'

  WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='linkStatus' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

 

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'N'

  WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='ctgIdc' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

 

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'Y'

  WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='ctgUpdSup' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

 

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'L'

  WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='shdLnkStat' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

 

  -- start socket

  -- will become 'STARTED'

  UPDATE TRD_INTF_GRP_CTL SET TRD_INTF_GRP_CTL_PRM_TEXT='START'

  WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_INTF_GRP_CTL_PRM_NAME='appiaInstruction'

  AND APP_INSTC_RUN_NAME LIKE 'HKHHHHMF%' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

 

  COMMIT;

 

  var_sleeptimeTotal := 0;

  WHILE var_fdsLinkStatusDownNo != 0 AND var_sleeptimeTotal < var_timeout LOOP

    DBMS_LOCK.SLEEP(var_sleeptime);

    var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

    SELECT Count(*) INTO var_fdsLinkStatusDownNo FROM TRD_INTF_GRP_CTL

    WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_INTF_GRP_CTL_PRM_NAME='appiaInstruction' AND

    APP_INSTC_RUN_NAME LIKE 'HKHHHHMF%' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK' AND TRD_INTF_GRP_CTL_PRM_TEXT!='STARTED';

  END LOOP;

 

  IF var_sleeptimeTotal >= var_timeout THEN

    RAISE exp_fds;

  END IF;

 

  DBMS_OUTPUT.PUT_LINE('GFIX are all started');

END IF;

-- HEALTH CHECK

-- Select * from TRD_INTF_GRP_CTL  where sys_prod_suply_num='FDS' and trd_intf_grp_ctl_prm_name='appiaInstruction' and APP_INSTC_RUN_NAME like 'HKHHHHMF%' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';

-- select * from trd_intf_ctl where sys_prod_suply_num='FDS' and trd_ifc_ctl_prm_type in ('linkStatus', 'ctgIdc', 'shdLnkStat','ctgUpdSup') and ctry_prod_exchg_mkt_cde='HK';

 

---- STEP 6, Order preparation

prepareOrder(IN_BANK);

 

---- STEP 7, Primary MDR full price download, will send out order to GFIX

UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'N', SESS_LA_SEQ_NUM = -1;

COMMIT;

 

var_sleeptimeTotal := 0;

WHILE var_midfsDownNo != 0 AND var_sleeptimeTotal < var_timeout LOOP

  DBMS_LOCK.SLEEP(var_sleeptime);

  var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

  SELECT Count(*) INTO var_midfsDownNo FROM MKT_DATA_FEED_STAT WHERE SESS_LA_SEQ_NUM = -1;

END LOOP;

 

IF var_sleeptimeTotal >= var_timeout THEN

  RAISE exp_midfs;

END IF;

 

 

-- STEP 7, HEALTH CHECK (Expected result: RD_MKT_WTCH_STAT_CDE P -> S)

-- SELECT pa.prod_alt_num, tp.PROD_NUM, TRD_ORD_REFER_NUM, TRD_ORD_REFER_TYPE_CDE, PORTF_ORD_REFER_TYPE_CDE, PORTF_ORD_REFER_NUM, tp.REC_UPDT_LA_DT_TM, DEAL_PARTY_CDE, CTPT_RSULT_INPUT_CDE, ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatus

-- FROM trd_proc tp, prod_alt_cde pa

-- WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P'

-- ORDER BY  tp.REC_UPDT_LA_DT_TM DESC;

 

var_sleeptimeTotal := 0;

WHILE var_mktWatchStatusNoP != 0 AND var_sleeptimeTotal < var_timeout LOOP

  DBMS_LOCK.SLEEP(var_sleeptime);

  var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

  SELECT Count(1) ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatusNoP FROM trd_proc tp, prod_alt_cde pa

    WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P' AND ORD_MKT_WTCH_STAT_CDE = 'P';

END LOOP;

 

SELECT Count(1) ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatusNoS FROM trd_proc tp, prod_alt_cde pa

WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P' AND ORD_MKT_WTCH_STAT_CDE = 'P';

IF var_sleeptimeTotal >= var_timeout THEN

  DBMS_OUTPUT.PUT_LINE('Sent orders seccessfully: ' || var_mktWatchStatusNoS);

  DBMS_OUTPUT.PUT_LINE('Sent orders failed: ' || var_mktWatchStatusNoP);

ELSE

  DBMS_OUTPUT.PUT_LINE('All orders have been sent out. Totally: ' || var_mktWatchStatusNoS);

END IF;

 

-- STEP 7, STOP Primary MDR MIDFS

UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'Y';

COMMIT;

 

---- STEP 8, Order preparation

prepareOrder(IN_BANK);

 

--- STEP 9 MDR failover

IF IN_BANK = 'HHHH' THEN

  UPDATE MKT_DATA_FEED_STAT SET LOC_PRC_SERVR_CDE=1 WHERE ISNT_NAME IN ('HKHHHHMF02','HKHHHHMF12','HKHHHHMF22','HKHHHHMF32','HKHHHHMF42','HKHHHHMF52');

  UPDATE MKT_DATA_FEED_STAT SET LOC_PRC_SERVR_CDE=2 WHERE ISNT_NAME IN ('HKHHHHMF01','HKHHHHMF11','HKHHHHMF21','HKHHHHMF31','HKHHHHMF41','HKHHHHMF51');

  COMMIT;

ELSIF IN_BANK = 'SSSS' THEN

  UPDATE MKT_DATA_FEED_STAT SET LOC_PRC_SERVR_CDE=1 WHERE ISNT_NAME IN ('HKSSSSMF02','HKSSSSMF12','HKSSSSMF22','HKSSSSMF32','HKSSSSMF42','HKSSSSMF52');

  UPDATE MKT_DATA_FEED_STAT SET LOC_PRC_SERVR_CDE=2 WHERE ISNT_NAME IN ('HKSSSSMF01','HKSSSSMF11','HKSSSSMF21','HKSSSSMF31','HKSSSSMF41','HKSSSSMF51');

  COMMIT;

END IF;

 

---- STEP 10, Secondary MDR full price download, will send out order to GFIX

UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'N', SESS_LA_SEQ_NUM = -1 ;

COMMIT;

 

var_sleeptimeTotal := 0;

WHILE var_midfsDownNo != 0 AND var_sleeptimeTotal < var_timeout LOOP

  DBMS_LOCK.SLEEP(var_sleeptime);

  var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

  SELECT Count(*) INTO var_midfsDownNo FROM MKT_DATA_FEED_STAT WHERE SESS_LA_SEQ_NUM = -1;

END LOOP;

 

IF var_sleeptimeTotal >= var_timeout THEN

  RAISE exp_midfs;

END IF;

 

var_sleeptimeTotal := 0;

WHILE var_mktWatchStatusNoP != 0 AND var_sleeptimeTotal < var_timeout LOOP

  DBMS_LOCK.SLEEP(var_sleeptime);

  var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

  SELECT Count(1) ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatusNoP FROM trd_proc tp, prod_alt_cde pa

    WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P' AND ORD_MKT_WTCH_STAT_CDE = 'P';

END LOOP;

 

SELECT Count(1) ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatusNoS FROM trd_proc tp, prod_alt_cde pa

WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P' AND ORD_MKT_WTCH_STAT_CDE = 'P';

IF var_sleeptimeTotal >= var_timeout THEN

  DBMS_OUTPUT.PUT_LINE('Sent orders seccessfully: ' || var_mktWatchStatusNoS);

  DBMS_OUTPUT.PUT_LINE('Sent orders failed: ' || var_mktWatchStatusNoP);

ELSE

  DBMS_OUTPUT.PUT_LINE('All orders have been sent out. Totally: ' || var_mktWatchStatusNoS);

END IF;

 

-- STEP 10, STOP Seconary MDR MIDFS

UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'Y';

COMMIT;

 

---- STEP 11 stop OES/GFIX

-- Stop OES (if enabled)

IF IN_BROKERTYPE = 'OES' AND IN_ISOESFORCELOGON = 'NO' THEN

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'F' WHERE SYS_PROD_SUPLY_NUM = 'OES' AND TRD_IFC_CTL_PRM_TYPE ='linkStatus';

  COMMIT;

  -- Better method is ask OES to trigger log-off

 

---- LOGOFF OES using ForceLogon

ELSIF IN_BROKERTYPE = 'OES' AND IN_ISOESFORCELOGON = 'YES' THEN

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'false' WHERE SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='forceLogon';

  COMMIT;

 

  ---- After patching to 'true', it triggered logon to OES automatically

  var_sleeptimeTotal := 0;

  WHILE var_oesLinkStatus = 'L' AND var_sleeptimeTotal < var_timeout LOOP

    DBMS_LOCK.SLEEP(var_sleeptime);

    var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

    SELECT TRD_IFC_CTL_PRM_TEXT INTO var_oesLinkStatus from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='linkStatus';

  END LOOP;

END IF;

 

-- Stop GFIX (HHHH only)

-- will become 'STOPPED'

IF IN_BANK = 'HHHH' AND IN_BROKERTYPE = 'FDS' THEN

  UPDATE TRD_INTF_GRP_CTL SET TRD_INTF_GRP_CTL_PRM_TEXT='STOP'

  WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_INTF_GRP_CTL_PRM_NAME='appiaInstruction' AND APP_INSTC_RUN_NAME LIKE 'HKHHHHMF%';

  COMMIT;

 

  var_sleeptimeTotal := 0;

  WHILE var_fdsLinkStatusUpNo != 0 AND var_sleeptimeTotal < var_timeout LOOP

    DBMS_LOCK.SLEEP(var_sleeptime);

    var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;

    SELECT Count(*) INTO var_fdsLinkStatusUpNo FROM TRD_INTF_GRP_CTL

    WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_INTF_GRP_CTL_PRM_NAME='appiaInstruction' AND

    APP_INSTC_RUN_NAME LIKE 'HKHHHHMF%' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK' AND TRD_INTF_GRP_CTL_PRM_TEXT!='STOPPED';

  END LOOP;

 

  UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'F' WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='linkStatus';

  COMMIT;

 

  IF var_sleeptimeTotal >= var_timeout THEN

    RAISE exp_stopFDSFailed;

  END IF;

END IF;

 

EXCEPTION

  WHEN exp_invalidBank THEN

  DBMS_OUTPUT.PUT_LINE('Invalid bank: ' ||  IN_BANK);

  WHEN exp_midfs THEN

  DBMS_OUTPUT.PUT_LINE('Cannot connect to MIDFS server.');

  WHEN exp_fds THEN

  DBMS_OUTPUT.PUT_LINE('Cannot connect to gfix server.');

  WHEN exp_oesForceLogon THEN

  DBMS_OUTPUT.PUT_LINE('Cannot connect to OES with forceLogon mode.');

  WHEN exp_stopFDSFailed THEN

  DBMS_OUTPUT.PUT_LINE('Cannot stop FDS socket.');

  when others then

  DBMS_OUTPUT.PUT_LINE('Exception');

END;

分享到：
| awk sample
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    存储过程（Stored Procedure）

    ### 存储过程（Stored Procedure）详解 #### 一、存储过程的概念与作用 存储过程是一种预先编写并编译好的SQL语句集合，通常用于实现特定的数据库操作或逻辑处理。存储过程存储在数据库服务器中，用户可以通过指定...

    学习使用存储过程(Stored Procedure)

    ### 学习使用存储过程(Stored Procedure) 在IT领域中，存储过程（Stored Procedure）是一项重要的技术，尤其对于从事Web开发尤其是ASP编程的开发者来说，掌握如何使用存储过程至关重要。存储过程是一种预先编译并...

    Using Informatica Stored Procedure Transformation

    在数据集成领域，Informatica是一个强大的企业级ETL（提取、转换、加载）工具，而“使用Informatica存储过程转换”是它的一项重要功能。存储过程转换允许用户通过Informatica执行预先在数据库中构建的存储过程，从而...

    解决OracleRAC集群下创建SDE时报Stored procedures错误问题.docx

    在Oracle RAC（Real Application Clusters）环境下，安装和配置ArcGIS Desktop的SDE（Spatial Database Extensions）数据库连接时，可能会遇到"Stored procedures"错误。这个问题通常与Oracle RAC的特性有关，即其...

    一个基于ADO.NET+COBOL+Stored Procedure的程序例子代码

    Stored Procedure是SQL Server数据库中预编译的SQL语句集合，它可以执行一系列复杂的数据库操作，如查询、插入、更新和删除数据。使用Stored Procedure有诸多优点，包括提高性能、减少网络流量、增强安全性以及提供...

    mysql存储过程编程 MySQL.Stored.Procedure.Programming

    mysql存储过程方面的圣经，以通俗的示例方法讲述mysql存储过程的深奥内容，In MySQL Stored Procedure Programming, they put that hard-won experience to good use. Packed with code examples and covering ...

    MySQL Stored Procedure Programming

    The implementation of stored procedures in MySQL 5.0 ... This book, destined to be the bible of stored procedure development, is a resource that no real MySQL programmer can afford to do without.

    一个基于C#实现的后台运行 any stored procedure 的类库源码程序

    标题中的“一个基于C#实现的后台运行 any stored procedure 的类库源码程序”表明这是一个C#编程项目，它的核心功能是能够在后台执行SQL Server的存储过程（stored procedure）。存储过程是预编译的SQL语句集合，...

    sybase stored procedure

    在 Sybase 中，存储过程分为两种类型：用户定义的存储过程（User-Defined Stored Procedures）和系统存储过程（System Stored Procedures）。用户定义的存储过程是由开发者创建的，可以根据业务需求进行定制。系统...

    DB2 Stored Procedure 存储过程教程

    DB2存储过程是数据库管理中的一个重要概念，它是一组为了完成特定功能的SQL语句集，可以在数据库中预先编译并存储。这个教程是专为初学者设计的，旨在帮助快速掌握DB2存储过程的创建、调用以及相关概念。...

    mysql stored procedure programming PDF

    涉及的关键字包括CREATE PROCEDURE、CALL、ALTER PROCEDURE、DROP PROCEDURE等。 3. 参数和变量：学习存储过程中的输入参数、输出参数以及局部变量的使用和声明。参数允许存储过程接收外部的输入值，而局部变量则...

    MySql存储过程编程.chm

    MySQL Stored Procedure Programming Advance Praise for MySQL Stored Procedure Programming Preface Objectives of This Book Structure of This Book What This Book Does Not Cover Conventions ...

    A tool to generate class files to implement stored procedure

    标题中的"A tool to generate class files to implement stored procedure"指的是一个软件工具，它的主要功能是自动生成Java类文件，这些类文件被设计用来实现数据库中的存储过程。存储过程是在数据库中预编译的SQL...

    存储过程讲解存储过程（Stored Procedure）， 是一组为了完成特定功能的SQL 语句，集经编译后

    存储过程（Stored Procedure）， 是一组为了完成特定功能的SQL 语句，集经编译后 存储在数据库中，用户通过指定存储过程的名字并给出参数，如果该存储过程带有参数来执行它， 在SQL Server 的系列版本中，存储过程...

    TERADATA SQL Reference:UDF, UDM, and External Stored Procedure Programming

    在IT领域，特别是数据库管理与开发中，Teradata作为一个领先的数据仓库解决方案提供商，其SQL参考文档中的UDF（用户自定义函数）、UDM（用户自定义宏）以及外部存储过程编程，是高级数据库管理和应用程序开发的重要...

    oracle练习题

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统，它在企业级数据存储、管理和处理方面具有卓越性能。"Oracle练习题"通常包含了一系列针对初学者和专业人员设计的问题，旨在帮助他们掌握Oracle数据库的基础...

    stored procudure

    stored procedure 学习代码

    Oracle PL/SQL从入门到精通 配书教学视频 第11章

    Oracle PL/SQL从入门到精通 配书教学视频 第11章

Magicbox
Global site tag (gtag.js) - Google Analytics