在ORACLE DB中执行upsert并且不起作用

Question

I am doing an insert condition in oracle that when the record based on job and subjob doesnt exists, it shall insert otherwise, if it exists then it should update the rest of the value.

this is my procedure,

CREATE OR REPLACE PROCEDURE WELTESADMIN.SP_JOB_INS
(
    JOB_V VARCHAR2,
    SUBJOB_V VARCHAR2,
    STARTDATE_V DATE,
    ENDDATE_V DATE,
    JOBWEIGHT_V NUMBER
)
AS BEGIN INSERT INTO PROJECT_SPAN (JOB, SUBJOB, STARTDATE, ENDDATE, WEIGHT) VALUES (JOB_V, SUBJOB_V, STARTDATE_V, ENDDATE_V, JOBWEIGHT_V);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
UPDATE PROJECT_SPAN SET STARTDATE = STARTDATE_V, ENDDATE = ENDDATE_V, WEIGHT = JOBWEIGHT_V WHERE JOB = JOB_V AND SUBJOB = SUBJOB_V;
END;
/

and this is from PHP Call,

$insertJobSpanSql = "BEGIN SP_JOB_INS(:JOB, :SUBJOB, :SDATE, :EDATE, :WT); END;";
            $insertJobSpanParse = oci_parse($conn, $insertJobSpanSql);
            oci_bind_by_name($insertJobSpanParse, ":JOB", $jobValue);
            oci_bind_by_name($insertJobSpanParse, ":SUBJOB", $subJobValue);
            oci_bind_by_name($insertJobSpanParse, ":SDATE", $startDateValue);
            oci_bind_by_name($insertJobSpanParse, ":EDATE", $endDateValue);
            oci_bind_by_name($insertJobSpanParse, ":WT", $jobWeightValue);
            $insertJobSpanRes = oci_execute($insertJobSpanParse);

            if ($insertJobSpanRes){
                oci_commit($conn);
            } else {
                oci_rollback($conn);
            }

problem is it keeps inserting new row with the same job and subjob value. it should be an update to the new value.

Answer 1

First of all, I recommend to use MERGE in such cases:

CREATE OR REPLACE PROCEDURE WELTESADMIN.SP_JOB_INS
(
    JOB_V VARCHAR2,
    SUBJOB_V VARCHAR2,
    STARTDATE_V DATE,
    ENDDATE_V DATE,
    JOBWEIGHT_V NUMBER
) AS 
BEGIN 
  merge into PROJECT_SPAN ps
  using (select JOB_V, SUBJOB_V, STARTDATE_V, ENDDATE_V, JOBWEIGHT_V 
           from dual) new_val
     on (ps.SUBJOB = new_val.SUBJOB_V and ps.JOB = new_val.JOB_V)
   when matched then update
    set STARTDATE = new_val.STARTDATE_V, 
        ENDDATE = new_val.ENDDATE_V, 
        WEIGHT = new_val.JOBWEIGHT_V
   when not matched then insert (JOB, SUBJOB, STARTDATE, ENDDATE, WEIGHT)
 values (new_val.JOB_V, new_val.SUBJOB_V, new_val.STARTDATE_V, 
         new_val.ENDDATE_V, new_val.JOBWEIGHT_V );
END;
/

If it still not updating values, use package DBMS_OUTPUT or logging into a table to make sure, that new and old JOB and SUBJOB really the same.