oracle 存储过程判断参数是否为空,Oracle存储过程中如何根据指定的参数判断该参数的值否存在数据表中？...

一：解决办法：如下，在这里还要感谢@遇见的热情帮助！

二：最后的的SQL如下,主要是看红色部分！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 := 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

SELECT COUNT(1) INTO COUNTS FROM TB_CMS_FLGTINFO_A where FFID = FFID_A;

IF COUNTS > 0 THEN

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;

/