具有日期条件的 Oracle Proc 运行但不更新

Question

我有一个探针通过 PHP 执行 Oracle 存储过程。我获得了成功的返回值，但该过程没有更新任何行。

这是过程定义。

CREATE OR REPLACE PACKAGE trans_data AS 
  PROCEDURE UPDATE_TRANS_BY_NAME(
    var_FName IN TRANSACTION.FIRST_NAME%type,
    var_LName IN TRANSACTION.LAST_NAME%type,
    var_DOB IN TRANSACTION.DOB%type,
    var_PolNum IN TRANSACTION.POLICY_NUMBER%type,
    var_TransStatus IN TRANSACTION.TRANS_STATUS%type,
    var_Comp IN TRANSACTION.COMPANY%type,
    var_LineOfBusiness IN TRANSACTION.LINE_OF_BUSINESS%type,
    var_PlanCode IN TRANSACTION.PLAN_CODE%type,
    var_AppDate IN TRANSACTION.APP_DATE%type,
    var_IssueDate IN TRANSACTION.ISSUE_DATE%type,
    var_FaceAmt IN TRANSACTION.FACE_AMT%type,
    var_PolicyStatus IN TRANSACTION.POLICY_STATUS%type,
    var_PaidAmt IN TRANSACTION.PAID_AMT%type,
    var_Return OUT VARCHAR2
  );  
END trans_data;

CREATE OR REPLACE PACKAGE BODY trans_data AS 
  PROCEDURE UPDATE_TRANS_BY_NAME(
    var_FName IN TRANSACTION.FIRST_NAME%type,
    var_LName IN TRANSACTION.LAST_NAME%type,
    var_DOB IN TRANSACTION.DOB%type,
    var_PolNum IN TRANSACTION.POLICY_NUMBER%type,
    var_TransStatus IN TRANSACTION.TRANS_STATUS%type,
    var_Comp IN TRANSACTION.COMPANY%type,
    var_LineOfBusiness IN TRANSACTION.LINE_OF_BUSINESS%type,
    var_PlanCode IN TRANSACTION.PLAN_CODE%type,
    var_AppDate IN TRANSACTION.APP_DATE%type,
    var_IssueDate IN TRANSACTION.ISSUE_DATE%type,
    var_FaceAmt IN TRANSACTION.FACE_AMT%type,
    var_PolicyStatus IN TRANSACTION.POLICY_STATUS%type,
    var_PaidAmt IN TRANSACTION.PAID_AMT%type,
    var_Return OUT VARCHAR2)
  IS
  BEGIN
    UPDATE TRANSACTION
    SET
      POLICY_NUMBER = var_PolNum,
      TRANS_STATUS = var_TransStatus,
      COMPANY = var_Comp,
      LINE_OF_BUSINESS = var_LineOfBusiness,
      PLAN_CODE = var_PlanCode,
      APP_DATE = var_AppDate,
      ISSUE_DATE = var_IssueDate,
      FACE_AMT = var_FaceAmt,
      POLICY_STATUS = var_PolicyStatus,
      PAID_AMT = var_PaidAmt
    WHERE FIRST_NAME = var_FName
      AND LAST_NAME = var_LName
      AND DOB = var_DOB
      AND TRANS_STATUS = 'R'
      AND REASON_FOR_REVIEW = 'No Policy Match';
      commit;
      var_Return := 'PASS';
    EXCEPTION
    WHEN OTHERS THEN
      var_Return := 'FAIL';
  END UPDATE_TRANS_BY_NAME;
END trans_data;

当我在过程中删除行“AND DOB = var_DOB”时，该行会更新。我尝试将变量定义更改为 VARCHAR 并使用“AND DOB = to_date(var_DOB, 'DD-MON-YY')”但没有成功。我也尝试过“AND TRUNC(DOB = var_DOB)”，但它也不起作用。

这是 PHP 代码。我已经验证了传递的所有参数都有值，并且格式对于数据库数据类型正确。我删除了执行先前查询的代码，该查询填充一些绑定变量以删除正在运行的内容。

function matchWSPolicy($policy, $trans, $status, $amount){

    include("../includes/DBConn.php");


    if ($Policy == ""){
        $message = 'Policy number does not match!';
        return $message;
    }

    $stmt = OCI_Parse($c,"begin ucs.trans_data.UPDATE_TRANS_BY_NAME(:var_FName, :var_LName, :var_DOB, :var_PolNum, 
        :var_TransStatus, :var_Comp, :var_LineOfBusiness, :var_PlanCode, :var_AppDate, :var_IssueDate, :var_FaceAmt, 
        :var_PolicyStatus, :var_PaidAmt, :var_Return); end;"); 
    OCI_BIND_BY_NAME($stmt,":var_FName",$FName);
    OCI_BIND_BY_NAME($stmt,":var_LName",$LName);
    OCI_BIND_BY_NAME($stmt,":var_DOB",$DOB);
    }
    else {
        $message = 'Policy matched, but a valid SSN or (first name, last name, date of birth) was not returned from the WS database';
}   

    OCI_Bind_By_Name($stmt,":var_PolNum",$Policy);
    OCI_Bind_By_Name($stmt,":var_TransStatus",$status); 
    OCI_Bind_By_Name($stmt,":var_Comp",$ComCode); 
    OCI_Bind_By_Name($stmt,":var_LineOfBusiness",$LineOfBusiness); 
    OCI_Bind_By_Name($stmt,":var_PlanCode",$Plan); 
    OCI_Bind_By_Name($stmt,":var_AppDate",$AppDate); 
    OCI_Bind_By_Name($stmt,":var_IssueDate",$IssueDate); 
    OCI_Bind_By_Name($stmt,":var_FaceAmt",$FaceAmount); 
    OCI_Bind_By_Name($stmt,":var_PolicyStatus",$PolStatus); 
    OCI_Bind_By_Name($stmt,":var_PaidAmt",$amount); 
    OCI_Bind_By_Name($stmt,":var_Return",$return, 4); 

    if ($message == ''){
        oci_execute($stmt);
        oci_commit($c);
    }
    else{
        $message = 'Record not successfully updated.';
    }
    return $message;
}

任何建议将不胜感激。

谢谢。

Answer 1

影响零行的 UPDATE 语句仍然是成功的，因为它没有引发异常。因此，您可能需要测试 SQL%ROWCOUNT = 0 或 SQL%NOTFOUND 并抛出您自己的用户定义异常来模拟失败。

至于为什么没有行被更新，解释非常简单。你自己说：

'当我删除行“AND DOB = var_DOB”时，该行会更新
程序。'

显然，表中没有行与出生日期和其他条件相匹配。显然你认为情况并非如此，但恐怕事实确实如此。因此，要么数据库没有保存您认为的准确数据，要么您的 PHP 没有传递您认为的准确值。只有您才能发现哪个选项适用。

格式掩码可能掩盖了您的麻烦根源。例如，您测试了“DD-MON-YY”的日期格式。当您明确包含世纪（即“DD-MON-YYYY”）时会发生什么？您的表有哪些世纪值？

Answer 2

我知道已经过去一年多了，但我必须回到这个项目。答案是必须将两个值都转换为日期，即使数据库值已经是日期。

AND to_date(DOB, 'DD-MON-YY') = to_date(var_DOB, 'DD-MON-YY')