Oracle存储过程中如何根据指定的参数判断该参数的值否存在数据表中:

悠悠 2024-02-17 15:16 33阅读 0赞

摘要：最近项目中用到了Oracle存储过程，所以就自己尝试着写了下，下面我把我遇到的问题描述一下：就是在我处理解析Clob字段中的xml字符串的时候，有个需求就是根据指定的主键参数，来判断该参数的值是否已经存在数据表里面，如果存在我就根据这个参数执行Update操作，如果不存在我就执行Save操作：

一：Oracle存储过程中如何根据指定的参数判断该参数的值否存在数据表中，以及通过Oracle存储过程解析Clob字段中的xml字符串到指定的数据表里面：

二：下面是具体的方法，主要是红色部分，是解决如果根据指定的参数判断该参数的值是否已经存在的数据表中：

CREATE OR REPLACE PROCEDURE MIP.MIP_PARSE (xmlStr IN CLOB)
    IS
       RENO     VARCHAR2 (100);
       AIRLINE  VARCHAR2 (100);
       FFID     VARCHAR2 (100);
       FFID_A   VARCHAR2 (100);
       FFID_D   VARCHAR2 (100);
    
       ABNS     VARCHAR2 (100);
       ACFT     VARCHAR2 (100);
       CHDT     VARCHAR2 (100);
       EIBT     VARCHAR2 (100);
       FATA     VARCHAR2 (100);
       FETA     VARCHAR2 (100);
       FSTA     VARCHAR2 (100);
       LMDT     VARCHAR2 (100);
       LMUR     VARCHAR2 (100);
       PSTM     VARCHAR2 (100);
       RWAY     VARCHAR2 (100);
       SPOT     VARCHAR2 (100);
       STND     VARCHAR2 (100);
    
       A_TOBT   VARCHAR2 (100);
       A_WEATHER     VARCHAR2 (100);
       ASAT     VARCHAR2 (100);
       BCTM     VARCHAR2 (100);
       BOTM     VARCHAR2 (100);
       BSTM     VARCHAR2 (100);
       C_TOBT   VARCHAR2 (100);
       COBT     VARCHAR2 (100);
       CTOT     VARCHAR2 (100);
    
       DINT     VARCHAR2 (100);
       DLAB     VARCHAR2 (100);
       DOUT     VARCHAR2 (100);
       EDDI     VARCHAR2 (100);
       EOBT     VARCHAR2 (100);
       EPGT     VARCHAR2 (100);
       EPOT     VARCHAR2 (100);
       FATD     VARCHAR2 (100);
       FSTD     VARCHAR2 (100);
       OFTM     VARCHAR2 (100);
       STDI     VARCHAR2 (100);
       TSAT     VARCHAR2 (100);
       
       --新增字段
       FLIGHTNUMBER VARCHAR2 (100);
       FLIGHTMARK   VARCHAR2 (100);
       
       --定义出港信息表要格式的时间字段
       A_TOBT_D VARCHAR2 (100);
       ASAT_D   VARCHAR2 (100);
       BCTM_D   VARCHAR2 (100);
       BOTM_D   VARCHAR2 (100);
       BSTM_D   VARCHAR2 (100);
       C_TOBT_D VARCHAR2 (100);
       COBT_D   VARCHAR2 (100);
       CTOT_D   VARCHAR2 (100);
       DINT_D   VARCHAR2 (100);
       DOUT_D   VARCHAR2 (100);
       EDDI_D   VARCHAR2 (100);
       EOBT_D   VARCHAR2 (100);
       EPGT_D   VARCHAR2 (100);
       EPOT_D   VARCHAR2 (100);
       FATD_D   VARCHAR2 (100);
       FSTD_D   VARCHAR2 (100);
       LMDT_D   VARCHAR2 (100);
       OFTM_D   VARCHAR2 (100);
       STDI_D   VARCHAR2 (100);
       TSAT_D   VARCHAR2 (100);
       
       --定义进港信息表要格式化的时间字段
       EIBT_A   VARCHAR2 (100);
       FATA_A   VARCHAR2 (100);
       FETA_A   VARCHAR2 (100);
       FSTA_A   VARCHAR2 (100);
       LMDT_A   VARCHAR2 (100);
       PSTM_A   VARCHAR2 (100);
       SPOT_A   VARCHAR2 (100);
       
       COUNTS   NUMBER(36);
       
       --出港信息要修改的除时间外的字段
       STND_D   VARCHAR2 (100);
       A_WEATHER_D VARCHAR2 (100);
       ABNS_D   VARCHAR2 (100);
       ACFT_D   VARCHAR2 (100);
       AIRLINE_D VARCHAR2 (100);
       DLAB_D   VARCHAR2 (100);
       LMUR_D   VARCHAR2 (100);
       RENO_D   VARCHAR2 (100);
       RWAY_D   VARCHAR2 (100);
       
       -- 进港信息要修改的除时间外的字段
       ABNS_A   VARCHAR2 (100);
       ACFT_A   VARCHAR2 (100);
       AIRLINE_A VARCHAR2 (100);
       CHDT_A   VARCHAR2 (100);
       RENO_A   VARCHAR2 (100);
       LMUR_A   VARCHAR2 (100);
       RWAY_A   VARCHAR2 (100);
       STND_A   VARCHAR2 (100);
       
       
    BEGIN
       RENO := GetXmlNodeValue (xmlStr, 'RENO');
       AIRLINE := GetXmlNodeValue (xmlStr, 'AIRLINE');
       FFID := GetXmlNodeValue (xmlStr, 'FFID');
    
       ABNS := GetXmlNodeValue (xmlStr, 'ABNS');
       ACFT := GetXmlNodeValue (xmlStr, 'ACFT');
       CHDT := GetXmlNodeValue (xmlStr, 'CHDT');
       EIBT := GetXmlNodeValue (xmlStr, 'EIBT');
       FATA := GetXmlNodeValue (xmlStr, 'FATA');
       FETA := GetXmlNodeValue (xmlStr, 'FETA');
       FFID := GetXmlNodeValue (xmlStr, 'FFID');
       FSTA := GetXmlNodeValue (xmlStr, 'FSTA');
       LMDT := GetXmlNodeValue (xmlStr, 'LMDT');
       LMUR := GetXmlNodeValue (xmlStr, 'LMUR');
       PSTM := GetXmlNodeValue (xmlStr, 'PSTM');
       RWAY := GetXmlNodeValue (xmlStr, 'RWAY');
       SPOT := GetXmlNodeValue (xmlStr, 'SPOT');
       STND := GetXmlNodeValue (xmlStr, 'STND');
    
       A_TOBT := GetXmlNodeValue (xmlStr, 'A_TOBT');
       A_WEATHER := GetXmlNodeValue (xmlStr, 'A_WEATHER');
       ASAT := GetXmlNodeValue (xmlStr, 'ASAT');
       BCTM := GetXmlNodeValue (xmlStr, 'BCTM');
       BOTM := GetXmlNodeValue (xmlStr, 'BOTM');
       BSTM := GetXmlNodeValue (xmlStr, 'BSTM');
       C_TOBT := GetXmlNodeValue (xmlStr, 'C_TOBT');
       COBT := GetXmlNodeValue (xmlStr, 'COBT');
       CTOT := GetXmlNodeValue (xmlStr, 'CTOT');
    
       DINT := GetXmlNodeValue (xmlStr, 'DINT');
       DLAB := GetXmlNodeValue (xmlStr, 'DLAB');
       DOUT := GetXmlNodeValue (xmlStr, 'DOUT');
       EDDI := GetXmlNodeValue (xmlStr, 'EDDI');
       EOBT := GetXmlNodeValue (xmlStr, 'EOBT');
       EPGT := GetXmlNodeValue (xmlStr, 'EPGT');
       EPOT := GetXmlNodeValue (xmlStr, 'EPOT');
       FATD := GetXmlNodeValue (xmlStr, 'FATD');
       FSTD := GetXmlNodeValue (xmlStr, 'FSTD');
       OFTM := GetXmlNodeValue (xmlStr, 'OFTM');
       STDI := GetXmlNodeValue (xmlStr, 'STDI');
       TSAT := GetXmlNodeValue (xmlStr, 'TSAT');
       
       
       --出港信息表中时间字段的时间格式函数的用法
       A_TOBT_D := FORMATDATEVALUE (A_TOBT, 'A_TOBT_D');
       ASAT_D := FORMATDATEVALUE (ASAT, 'ASAT_D');
       BCTM_D := FORMATDATEVALUE (BCTM, 'BCTM_D');
       BOTM_D := FORMATDATEVALUE (BOTM, 'BOTM_D');
       BSTM_D := FORMATDATEVALUE (BSTM, 'BSTM_D');
       C_TOBT_D := FORMATDATEVALUE (C_TOBT, 'C_TOBT_D');
       COBT_D := FORMATDATEVALUE (COBT, 'COBT_D');
       CTOT_D := FORMATDATEVALUE (CTOT, 'CTOT_D');
       DINT_D := FORMATDATEVALUE (DINT, 'DINT_D');
       DOUT_D := FORMATDATEVALUE (DOUT, 'DOUT_D');
       EDDI_D := FORMATDATEVALUE (EDDI, 'EDDI_D');
       EOBT_D := FORMATDATEVALUE (EOBT, 'EOBT_D');
       EPGT_D := FORMATDATEVALUE (EPGT, 'EPGT_D');
       EPOT_D := FORMATDATEVALUE (EPOT, 'EPOT_D');
       FATD_D := FORMATDATEVALUE (FATD, 'FATD_D');
       FSTD_D := FORMATDATEVALUE (FSTD, 'FSTD_D');
       LMDT_D := FORMATDATEVALUE (LMDT, 'LMDT_D');
       OFTM_D := FORMATDATEVALUE (OFTM, 'OFTM_D');
       STDI_D := FORMATDATEVALUE (STDI, 'STDI_D');
       TSAT_D := FORMATDATEVALUE (TSAT, 'TSAT_D');
       
       --进港信息表中时间字段的时间格式函数的用法
       EIBT_A := FORMATDATEVALUE (EIBT, 'EIBT_A');
       FATA_A := FORMATDATEVALUE (FATA, 'FATA_A');
       FETA_A := FORMATDATEVALUE (FETA, 'FETA_A');
       FSTA_A := FORMATDATEVALUE (FSTA, 'FSTA_A');
       LMDT_A := FORMATDATEVALUE (LMDT, 'LMDT_A');
       PSTM_A := FORMATDATEVALUE (PSTM, 'PSTM_A');
       SPOT_A := FORMATDATEVALUE (SPOT, 'SPOT_A');
       
       --出港信息要修改的除时间外的字段
       STND_D := GetXmlNodeValue (xmlStr, 'STND');
       A_WEATHER_D := GetXmlNodeValue (xmlStr, 'A_WEATHER');
       ABNS_D := GetXmlNodeValue (xmlStr, 'ABNS');
       ACFT_D := GetXmlNodeValue (xmlStr, 'ACFT');
       AIRLINE_D := GetXmlNodeValue (xmlStr, 'AIRLINE');
       DLAB_D := GetXmlNodeValue (xmlStr, 'DLAB');
       LMUR_D := GetXmlNodeValue (xmlStr, 'LMUR');
       RENO_D := GetXmlNodeValue (xmlStr, 'RENO');
       RWAY_D := GetXmlNodeValue (xmlStr, 'RWAY');
       
       --进港信息要修改的除时间外的字段
       ABNS_A := GetXmlNodeValue (xmlStr, 'ABNS');
       ACFT_A := GetXmlNodeValue (xmlStr, 'ACFT');
       AIRLINE_A := GetXmlNodeValue (xmlStr, 'AIRLINE');
       CHDT_A := GetXmlNodeValue (xmlStr, 'CHDT');
       RENO_A := GetXmlNodeValue (xmlStr, 'RENO');
       LMUR_A := GetXmlNodeValue (xmlStr, 'LMUR');
       RWAY_A := GetXmlNodeValue (xmlStr, 'RWAY');
       STND_A := GetXmlNodeValue (xmlStr, 'STND');
       
       
       IF INSTR(FFID,'-D-') > 0 THEN
       
        FFID_D := FFID;
        
        --截取航班号
        FLIGHTNUMBER := SUBSTR(FFID_D,INSTR(FFID_D,'-',1)+1,INSTR(FFID_D,'-',INSTR(FFID_D,'-',1)+1)-INSTR(FFID_D,'-',1)-1);
        
        --截取出港标志
        
        FLIGHTMARK := SUBSTR(FFID_D,INSTR(FFID_D,'-',2,2)+1,INSTR(FFID_D,'-',2,3)-1-INSTR(FFID_D,'-',2,2));
        
        --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE
        
        <span style="color:#cc0000;">SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_D where FFID = FFID_D;
        
        IF COUNTS > 0 THEN 
        </span>
        UPDATE TB_CMS_FLGTINFO_D
        
        SET 
        
        A_TOBT = A_TOBT_D,
        A_WEATHER = A_WEATHER_D,
        ABNS = ABNS_D,
        ACFT = ACFT_D,
        AIRLINE = AIRLINE_D,
        ASAT = ASAT_D,
        BCTM = BCTM_D,
        BOTM = BOTM_D,
        BSTM = BSTM_D,
        C_TOBT = C_TOBT_D,
        COBT = COBT_D,
        CTOT = CTOT_D,
        DINT = DINT_D,
        DLAB = DLAB_D,
        DOUT = DOUT_D,
        EDDI = EDDI_D,
        EOBT = EOBT_D,
        EPGT = EPGT_D,
        EPOT = EPOT_D,
        FATD = FATD_D,
        FSTD = FSTD_D,
        LMDT = LMDT_D,
        LMUR = LMUR_D,
        OFTM = OFTM_D,
        RENO = RENO_D,
        RWAY = RWAY_D,
        STDI = STDI_D,
        STND = STND_D,
        TSAT = TSAT_D
        
        WHERE FFID = FFID_D;
        
        ELSE
        
        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,FLIGHTNUMBER,
                   FLIGHTMARK,FSTD,LMDT,LMUR,OFTM,RENO,RWAY,STDI,STND,TSAT)
         VALUES   (FLGTINFO_D_SEQ.NEXTVAL,
                   A_TOBT_D,
                   A_WEATHER,
                   ABNS,
                   ACFT,
                   AIRLINE,
                   ASAT_D,
                   BCTM_D,
                   BOTM_D,
                   BSTM_D,
                   C_TOBT_D,
                   COBT_D,
                   CTOT_D,
                   DINT_D,
                   DLAB,
                   DOUT_D,
                   EDDI_D,
                   EOBT_D,
                   EPGT_D,
                   EPOT_D,
                   FATD_D,
                   FFID_D,
                   FLIGHTNUMBER,
                   FLIGHTMARK,
                   FSTD_D,
                   LMDT_D,
                   LMUR,
                   OFTM_D,
                   RENO,
                   RWAY,
                   STDI_D,
                   STND,
                   TSAT_D);
        
        END IF;
        
       
       ELSE
       
         FFID_A := FFID;
        
         --截取航班号
         FLIGHTNUMBER := SUBSTR(FFID_A,INSTR(FFID_A,'-',1)+1,INSTR(FFID_A,'-',INSTR(FFID_A,'-',1)+1)-INSTR(FFID_A,'-',1)-1);
        
         --截取出港标志
         FLIGHTMARK := SUBSTR(FFID_A,INSTR(FFID_A,'-',2,2)+1,INSTR(FFID_A,'-',2,3)-1-INSTR(FFID_A,'-',2,2));
         
         --UPDATE之前要先根据FFID查询一下看看数据表中是否已经存在该条数据，如果存在就UPDATE,如果不存在就SAVE
         
         <span style="color:#cc0000;">SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_A where FFID = FFID_A;
    
         IF COUNTS > 0 THEN</span>
         
         UPDATE TB_CMS_FLGTINFO_A
         
         SET 
         
         ABNS = ABNS_A,
         ACFT = ACFT_A,
         AIRLINE = AIRLINE_A,
         CHDT = CHDT_A,
         RENO = RENO_A,
         EIBT = EIBT_A,
         FATA = FATA_A,
         FETA = FETA_A,
         FSTA = FSTA_A,
         LMDT = LMDT_A,
         LMUR = LMUR_A,
         PSTM = PSTM_A,
         RWAY = RWAY_A,
         SPOT = SPOT_A,
         STND = STND_A
         
         WHERE 
         
         FFID = FFID_A;
         
         ELSE
         
         INSERT INTO TB_CMS_FLGTINFO_A (ID,ABNS,ACFT,AIRLINE,CHDT,FFID,FLIGHTNUMBER,FLIGHTMARK,RENO,EIBT,FATA,FETA,FSTA,LMDT,LMUR,PSTM,RWAY,SPOT,STND)
         
         VALUES   (FLGTINFO_A_SEQ.NEXTVAL,
                   ABNS,
                   ACFT,
                   AIRLINE,
                   CHDT,
                   FFID_A,
                   FLIGHTNUMBER,
                   FLIGHTMARK,
                   RENO,
                   EIBT_A,
                   FATA_A,
                   FETA_A,
                   FSTA_A,
                   LMDT_A,
                   LMUR,
                   PSTM_A,
                   RWAY,
                   SPOT_A,
                   STND);
         
         END IF;
         
       END IF;
    
       COMMIT;
       
    EXCEPTION
    
       WHEN OTHERS
       
       THEN
       
          DBMS_OUTPUT.PUT_LINE (SQLERRM);
          
    END MIP_PARSE;
    /

三：详情请看： [http://www.zuidaima.com/question/2041312860310528.htm][http_www.zuidaima.com_question_2041312860310528.htm]

[http_www.zuidaima.com_question_2041312860310528.htm]: http://www.zuidaima.com/question/2041312860310528.htm