Question

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

Answer 1

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




摘要：最近项目中用到了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

SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_D where FFID = FFID_D;

IF COUNTS > 0 THEN

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 := SUB