C++调用PL/SQL存储过程验证订单归属失败求助

问题根源分析 #

1. PL/SQL存储过程customer_order的异常处理漏洞 #

当前存储过程仅在NO_DATA_FOUND时将orderID设为0，但遇到TOO_MANY_ROWS或其他异常时，未重置orderID为0，导致C++端会收到原始输入的orderID，误判订单归属有效。

• 例如：当出现多行匹配（尽管order_id理论上是主键，但代码需兼容异常场景），存储过程仅打印错误日志，但orderID保持输入值，C++会认为验证通过并执行取消操作。
• 另外，OTHERS异常分支同样未设置orderID为0，后续C++逻辑会错误执行。

2. 存储过程输出与C++逻辑的匹配问题 #

存储过程中使用DBMS_OUTPUT打印错误，C++端无法捕获这些日志，只能通过orderID的返回值判断验证结果，因此所有验证失败场景（包括异常）必须统一将orderID设为0。

修复方案 #

修复后的customer_order存储过程 #

CREATE OR REPLACE PROCEDURE customer_order (
    customerID IN NUMBER,
    orderID IN OUT NUMBER
) AS
    temp_order_id NUMBER;
BEGIN
    SELECT order_id INTO temp_order_id
    FROM orders
    WHERE customer_id = customerID AND order_id = orderID;

    orderID := temp_order_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        orderID := 0;
    WHEN TOO_MANY_ROWS THEN
        orderID := 0;
        DBMS_OUTPUT.PUT_LINE('Error: Multiple orders found for the customer.');
    WHEN OTHERS THEN
        orderID := 0;
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

C++函数cancelOrder的优化建议 #

1. 改用PreparedStatement提升参数绑定安全性与复用性，同时及时释放资源：

void cancelOrder(Connection* conn, int orderId, int customerId) {
    try {
        PreparedStatement* pstmt = nullptr;
        int cancelStatus = 0;

        // 调用归属验证存储过程
        pstmt = conn->prepareStatement("BEGIN customer_order(:1, :2); END;");
        pstmt->setInt(1, customerId);         
        pstmt->setInt(2, orderId);           
        pstmt->registerOutParam(2, Type::OCCIINT); 
        pstmt->executeUpdate();

        int validatedOrderId = pstmt->getInt(2); 
        pstmt->close();

        if (validatedOrderId == 0) {
            cout << "Order does not belong to the customer." << endl;
            return; 
        }

        // 调用取消订单存储过程
        pstmt = conn->prepareStatement("BEGIN cancel_order(:1, :2); END;");
        pstmt->setInt(1, validatedOrderId);   
        pstmt->registerOutParam(2, Type::OCCIINT); 
        pstmt->executeUpdate();
        cancelStatus = pstmt->getInt(2); 

        switch (cancelStatus) {
        case 1:
            cout << "The order has been already canceled." << endl;
            break;
        case 2:
            cout << "The order is shipped and cannot be canceled." << endl;
            break;
        case 3:
            cout << "The order is canceled successfully." << endl;
            break;
        case 0:
            cout << "The order does not exist." << endl;
            break;
        default:
            cout << "Unknown cancel status." << endl;
            break;
        }

        pstmt->close();
    }
    catch (SQLException& sqlExcp) {
        cout << "Error: Unable to cancel the order." << endl;
        cout << sqlExcp.getErrorCode() << ": " << sqlExcp.getMessage() << endl;
    }
}

验证逻辑说明 #

修复后，无论存储过程遇到哪种异常场景，都会将orderID设为0，C++端通过判断validatedOrderId == 0即可准确识别归属验证失败的情况，确保后续取消操作仅在归属有效时执行。

内容的提问来源于stack exchange，提问作者Alyssa Manlosa