当前位置：Gxlcms > mysql > Oracle中通过Function，存储过程，触发器，调用实现解析Clob字段

Oracle中通过Function，存储过程，触发器，调用实现解析Clob字段

时间：2021-07-01 10:21:17 帮助过：53人阅读

http://blog.csdn.net/sxdtzhaoxinguo/article/details/40052783 一：Function.SQL: CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2) RETURN VARCHAR2IS --定义几个变量，出来解析过来的时间字符串 --日月年时分(11OCT14102

http://blog.csdn.net/sxdtzhaoxinguo/article/details/40052783


一：Function.SQL:

  1. CREATE OR REPLACE FUNCTION MIP.FormatDateValue (key VARCHAR2, value VARCHAR2)
  2.    RETURN VARCHAR2
  3. IS
  4.    --定义几个变量，出来解析过来的时间字符串
  5.    --日月年时分(11OCT141024)
  6.    AA       VARCHAR2(32);
  7.    
  8.    DAY      VARCHAR2(32);
  9.    
  10.    MOUNTH   VARCHAR2(32);
  11.    
  12.    YEAR     VARCHAR2(32);
  13.    
  14.    HOUR     VARCHAR2(32);
  15.    
  16.    MINUTE   VARCHAR2(32);
  17.    
  18.    ValueReturn   VARCHAR2 (100);
  19.    
  20. BEGIN
  21.    IF key IS NULL THEN 
  22.    
  23.    ValueReturn := NULL;
  24.    
  25.    RETURN ValueReturn;
  26.    
  27.    ELSE
  28.    
  29.    DAY := SUBSTR(key,0,2);
  30.    MOUNTH := SUBSTR(key,3,3);
  31.    IF INSTR (MOUNTH,'JAN') > 0 THEN
  32.    MOUNTH := 01;
  33.    END IF;
  34.    IF INSTR (MOUNTH,'FEB') > 0 THEN
  35.    MOUNTH := 02;
  36.    END IF;
  37.    IF INSTR (MOUNTH,'MAR') > 0 THEN
  38.    MOUNTH := 03;
  39.    END IF;
  40.    IF INSTR (MOUNTH,'APR') > 0 THEN
  41.    MOUNTH := 04;
  42.    END IF;
  43.    IF INSTR (MOUNTH,'MAY') > 0 THEN
  44.    MOUNTH := 05;
  45.    END IF;
  46.    IF INSTR (MOUNTH,'JUN') > 0 THEN
  47.    MOUNTH := 06;
  48.    END IF;
  49.    IF INSTR (MOUNTH,'JUL') > 0 THEN
  50.    MOUNTH := 07;
  51.    END IF;
  52.    IF INSTR (MOUNTH,'AUG') > 0 THEN
  53.    MOUNTH := 08;
  54.    END IF;
  55.    IF INSTR (MOUNTH,'SEP') > 0 THEN
  56.    MOUNTH := 09;
  57.    END IF;
  58.    IF INSTR (MOUNTH,'OCT') > 0 THEN
  59.    MOUNTH := 10;
  60.    END IF;
  61.    IF INSTR (MOUNTH,'NOV') > 0 THEN
  62.    MOUNTH := 11;
  63.    END IF;
  64.    IF INSTR (MOUNTH,'DEC') > 0 THEN
  65.    MOUNTH := 12;
  66.    END IF;
  67.    YEAR := SUBSTR(key,6,2);
  68.    HOUR := SUBSTR(key,8,2);
  69.    MINUTE := SUBSTR(key,-2);
  70.    AA := 20;
  71.    
  72.    --日月年时分(11OCT141017)
  73.    ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE;
  74.    
  75.    RETURN ValueReturn;
  76.    
  77.    END IF;
  78.    
  79. END FormatDateValue;
  80. /

二：存储过程.SQL: 
  1. CREATE OR REPLACE PROCEDURE MIP.MIP_PARSE (xmlStr IN CLOB)
  2. IS
  3.    RENO     VARCHAR2 (100);
  4.    AIRLINE  VARCHAR2 (100);
  5.    FFID     VARCHAR2 (100);
  6.    FFID_A   VARCHAR2 (100);
  7.    FFID_D   VARCHAR2 (100);
  8.    ABNS     VARCHAR2 (100);
  9.    ACFT     VARCHAR2 (100);
  10.    CHDT     VARCHAR2 (100);
  11.    EIBT     VARCHAR2 (100);
  12.    FATA     VARCHAR2 (100);
  13.    FETA     VARCHAR2 (100);
  14.    --FFID     VARCHAR2 (100);
  15.    FSTA     VARCHAR2 (100);
  16.    LMDT     VARCHAR2 (100);
  17.    LMUR     VARCHAR2 (100);
  18.    PSTM     VARCHAR2 (100);
  19.    RWAY     VARCHAR2 (100);
  20.    SPOT     VARCHAR2 (100);
  21.    STND     VARCHAR2 (100);
  22.    A_TOBT   VARCHAR2 (100);
  23.    A_WEATHER     VARCHAR2 (100);
  24.    --ABNS     VARCHAR2 (100);
  25.    --ACFT     VARCHAR2 (100);
  26.    ASAT     VARCHAR2 (100);
  27.    BCTM     VARCHAR2 (100);
  28.    BOTM     VARCHAR2 (100);
  29.    BSTM     VARCHAR2 (100);
  30.    C_TOBT   VARCHAR2 (100);
  31.    COBT     VARCHAR2 (100);
  32.    CTOT     VARCHAR2 (100);
  33.    DINT     VARCHAR2 (100);
  34.    DLAB     VARCHAR2 (100);
  35.    DOUT     VARCHAR2 (100);
  36.    EDDI     VARCHAR2 (100);
  37.    EOBT     VARCHAR2 (100);
  38.    EPGT     VARCHAR2 (100);
  39.    EPOT     VARCHAR2 (100);
  40.    FATD     VARCHAR2 (100);
  41.    --FFID     VARCHAR2 (100);
  42.    FSTD     VARCHAR2 (100);
  43.    --LMDT     VARCHAR2 (100);
  44.    --LMUR     VARCHAR2 (100);
  45.    OFTM     VARCHAR2 (100);
  46.    --RENO     VARCHAR2 (100);
  47.    --RWAY     VARCHAR2 (100);
  48.    STDI     VARCHAR2 (100);
  49.    --STND     VARCHAR2 (100);
  50.    TSAT     VARCHAR2 (100);
  51.    
  52.    --定义出港信息表要格式的时间字段
  53.    A_TOBT_D VARCHAR2 (100);
  54.    ASAT_D   VARCHAR2 (100);
  55.    BCTM_D   VARCHAR2 (100);
  56.    BOTM_D   VARCHAR2 (100);
  57.    BSTM_D   VARCHAR2 (100);
  58.    C_TOBT_D VARCHAR2 (100);
  59.    COBT_D   VARCHAR2 (100);
  60.    CTOT_D   VARCHAR2 (100);
  61.    DINT_D   VARCHAR2 (100);
  62.    DOUT_D   VARCHAR2 (100);
  63.    EDDI_D   VARCHAR2 (100);
  64.    EOBT_D   VARCHAR2 (100);
  65.    EPGT_D   VARCHAR2 (100);
  66.    EPOT_D   VARCHAR2 (100);
  67.    FATD_D   VARCHAR2 (100);
  68.    FSTD_D   VARCHAR2 (100);
  69.    LMDT_D   VARCHAR2 (100);
  70.    OFTM_D   VARCHAR2 (100);
  71.    STDI_D   VARCHAR2 (100);
  72.    TSAT_D   VARCHAR2 (100);
  73.    
  74.    --定义进港信息表要格式化的时间字段
  75.    EIBT_A  VARCHAR2 (100);
  76.    FATA_A  VARCHAR2 (100);
  77.    FETA_A  VARCHAR2 (100);
  78.    FSTA_A  VARCHAR2 (100);
  79.    LMDT_A  VARCHAR2 (100);
  80.    PSTM_A  VARCHAR2 (100);
  81.    SPOT_A  VARCHAR2 (100);
  82.    
  83. BEGIN
  84.    RENO := GetXmlNodeValue (xmlStr, 'RENO');
  85.    AIRLINE := GetXmlNodeValue (xmlStr, 'AIRLINE');
  86.    FFID := GetXmlNodeValue (xmlStr, 'FFID');
  87.    ABNS := GetXmlNodeValue (xmlStr, 'ABNS');
  88.    ACFT := GetXmlNodeValue (xmlStr, 'ACFT');
  89.    CHDT := GetXmlNodeValue (xmlStr, 'CHDT');
  90.    EIBT := GetXmlNodeValue (xmlStr, 'EIBT');
  91.    FATA := GetXmlNodeValue (xmlStr, 'FATA');
  92.    FETA := GetXmlNodeValue (xmlStr, 'FETA');
  93.    FFID := GetXmlNodeValue (xmlStr, 'FFID');
  94.    FSTA := GetXmlNodeValue (xmlStr, 'FSTA');
  95.    LMDT := GetXmlNodeValue (xmlStr, 'LMDT');
  96.    LMUR := GetXmlNodeValue (xmlStr, 'LMUR');
  97.    PSTM := GetXmlNodeValue (xmlStr, 'PSTM');
  98.    RWAY := GetXmlNodeValue (xmlStr, 'RWAY');
  99.    SPOT := GetXmlNodeValue (xmlStr, 'SPOT');
  100.    STND := GetXmlNodeValue (xmlStr, 'STND');
  101.    A_TOBT := GetXmlNodeValue (xmlStr, 'A_TOBT');
  102.    A_WEATHER := GetXmlNodeValue (xmlStr, 'A_WEATHER');
  103.    --ABNS := GetXmlNodeValue (xmlStr, 'ABNS');
  104.    --ACFT := GetXmlNodeValue (xmlStr, 'ACFT');
  105.    ASAT := GetXmlNodeValue (xmlStr, 'ASAT');
  106.    BCTM := GetXmlNodeValue (xmlStr, 'BCTM');
  107.    BOTM := GetXmlNodeValue (xmlStr, 'BOTM');
  108.    BSTM := GetXmlNodeValue (xmlStr, 'BSTM');
  109.    C_TOBT := GetXmlNodeValue (xmlStr, 'C_TOBT');
  110.    COBT := GetXmlNodeValue (xmlStr, 'COBT');
  111.    CTOT := GetXmlNodeValue (xmlStr, 'CTOT');
  112.    DINT := GetXmlNodeValue (xmlStr, 'DINT');
  113.    DLAB := GetXmlNodeValue (xmlStr, 'DLAB');
  114.    DOUT := GetXmlNodeValue (xmlStr, 'DOUT');
  115.    EDDI := GetXmlNodeValue (xmlStr, 'EDDI');
  116.    EOBT := GetXmlNodeValue (xmlStr, 'EOBT');
  117.    EPGT := GetXmlNodeValue (xmlStr, 'EPGT');
  118.    EPOT := GetXmlNodeValue (xmlStr, 'EPOT');
  119.    FATD := GetXmlNodeValue (xmlStr, 'FATD');
  120.    FSTD := GetXmlNodeValue (xmlStr, 'FSTD');
  121.    --LMDT := GetXmlNodeValue (xmlStr, 'LMDT');
  122.    --LMUR := GetXmlNodeValue (xmlStr, 'LMUR');
  123.    OFTM := GetXmlNodeValue (xmlStr, 'OFTM');
  124.    STDI := GetXmlNodeValue (xmlStr, 'STDI');
  125.    TSAT := GetXmlNodeValue (xmlStr, 'TSAT');
  126.    
  127.    --出港信息表中时间字段的时间格式函数的用法
  128.    A_TOBT_D := FORMATDATEVALUE (A_TOBT, 'A_TOBT_D');
  129.    ASAT_D := FORMATDATEVALUE (ASAT, 'ASAT_D');
  130.    BCTM_D := FORMATDATEVALUE (BCTM, 'BCTM_D');
  131.    BOTM_D := FORMATDATEVALUE (BOTM, 'BOTM_D');
  132.    BSTM_D := FORMATDATEVALUE (BSTM, 'BSTM_D');
  133.    C_TOBT_D := FORMATDATEVALUE (C_TOBT, 'C_TOBT_D');
  134.    COBT_D := FORMATDATEVALUE (COBT, 'COBT_D');
  135.    CTOT_D := FORMATDATEVALUE (CTOT, 'CTOT_D');
  136.    DINT_D := FORMATDATEVALUE (DINT, 'DINT_D');
  137.    DOUT_D := FORMATDATEVALUE (DOUT, 'DOUT_D');
  138.    EDDI_D := FORMATDATEVALUE (EDDI, 'EDDI_D');
  139.    EOBT_D := FORMATDATEVALUE (EOBT, 'EOBT_D');
  140.    EPGT_D := FORMATDATEVALUE (EPGT, 'EPGT_D');
  141.    EPOT_D := FORMATDATEVALUE (EPOT, 'EPOT_D');
  142.    FATD_D := FORMATDATEVALUE (FATD, 'FATD_D');
  143.    FSTD_D := FORMATDATEVALUE (FSTD, 'FSTD_D');
  144.    LMDT_D := FORMATDATEVALUE (LMDT, 'LMDT_D');
  145.    OFTM_D := FORMATDATEVALUE (OFTM, 'OFTM_D');
  146.    STDI_D := FORMATDATEVALUE (STDI, 'STDI_D');
  147.    TSAT_D := FORMATDATEVALUE (TSAT, 'TSAT_D');
  148.    
  149.    --进港信息表中时间字段的时间格式函数的用法
  150.    EIBT_A := FORMATDATEVALUE (EIBT, 'EIBT_A');
  151.    FATA_A := FORMATDATEVALUE (FATA, 'FATA_A');
  152.    FETA_A := FORMATDATEVALUE (FETA, 'FETA_A');
  153.    FSTA_A := FORMATDATEVALUE (FSTA, 'FSTA_A');
  154.    LMDT_A := FORMATDATEVALUE (LMDT, 'LMDT_A');
  155.    PSTM_A := FORMATDATEVALUE (PSTM, 'PSTM_A');
  156.    SPOT_A := FORMATDATEVALUE (SPOT, 'SPOT_A');
  157.    
  158.    IF INSTR(FFID,'-D-') > 0 THEN
  159.    
  160.     --FFID_D := FFID;
  161.     
  162.     --截取航班号
  163.     FFID_D := SUBSTR(FFID,INSTR(FFID,'-',1)+1,INSTR(FFID,'-',INSTR(FFID,'-',1)+1)-INSTR(FFID,'-',1)-1);
  164.     
  165.     INSERT INTO TB_CMS_FLGTINFO_D (ID,A_TOBT,A_WEATHER,ABNS,ACFT,AIRLINE,ASAT,BCTM,BOTM,BSTM,C_TOBT,COBT,CTOT,DINT,DLAB,DOUT,EDDI,EOBT,EPGT,EPOT,FATD,FFID,FSTD,LMDT,LMUR,OFTM,RENO,RWAY,STDI,STND,TSAT)
  166.      VALUES   (FLGTINFO_D_SEQ.NEXTVAL,
  167.                A_TOBT_D,
  168.                A_WEATHER,
  169.                ABNS,
  170.                ACFT,
  171.                AIRLINE,
  172.                ASAT_D,
  173.                BCTM_D,
  174.                BOTM_D,
  175.                BSTM_D,
  176.                C_TOBT_D,
  177.                COBT_D,
  178.                CTOT_D,
  179.                DINT_D,
  180.                DLAB,
  181.                DOUT_D,
  182.                EDDI_D,
  183.                EOBT_D,
  184.                EPGT_D,
  185.                EPOT_D,
  186.                FATD_D,
  187.                FFID_D,
  188.                FSTD_D,
  189.                LMDT_D,
  190.                LMUR,
  191.                OFTM_D,
  192.                RENO,
  193.                RWAY,
  194.                STDI_D,
  195.                STND,
  196.                TSAT_D);
  197.    
  198.    ELSE
  199.    
  200.     --FFID_A := FFID;
  201.     
  202.      FFID_A := SUBSTR(FFID,INSTR(FFID,'-',1)+1,INSTR(FFID,'-',INSTR(FFID,'-',1)+1)-INSTR(FFID,'-',1)-1);
  203.     
  204.     INSERT INTO TB_CMS_FLGTINFO_A (ID,ABNS,ACFT,AIRLINE,CHDT,FFID,RENO,EIBT,FATA,FETA,FSTA,LMDT,LMUR,PSTM,RWAY,SPOT,STND)
  205.      VALUES   (FLGTINFO_A_SEQ.NEXTVAL,
  206.                ABNS,
  207.                ACFT,
  208.                AIRLINE,
  209.                CHDT,
  210.                FFID_A,
  211.                RENO,
  212.                EIBT_A,
  213.                FATA_A,
  214.                FETA_A,
  215.                FSTA_A,
  216.                LMDT_A,
  217.                LMUR,
  218.                PSTM_A,
  219.                RWAY,
  220.                SPOT_A,
  221.                STND);
  222.    
  223.    END IF;
  224.    
  225.     
  226.    
  227.    
  228.   
  229.    COMMIT;
  230. EXCEPTION
  231.    WHEN OTHERS
  232.    THEN
  233.       DBMS_OUTPUT.PUT_LINE (SQLERRM);
  234. END MIP_PARSE;
  235. /

三：以上就是改进后的sql语句;


四：详情请看：http://blog.csdn.net/sxdtzhaoxinguo/article/details/40052783

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！