如何在Java中调用返回自定义对象类型的Oracle PL/SQL存储过程

Hey there! Let's get your Java code fully working to call that CUSTOMER_LIST_FOR_SUSPICIOUS_CHECK stored procedure, especially handling the custom CUSTOMER_LIST output parameter. Here's a step-by-step implementation:

First, Let's Clarify Assumptions #

Your stored procedure uses EMOB.PKG_FINGER.CUSTOMER_LIST which I assume is a custom collection type (like a TABLE OF or VARRAY OF a customer object, e.g., PKG_FINGER.CUSTOMER_OBJ). I'll base the example on a typical customer object structure, but you can adjust it to match your actual database object.

Step 1: Create a Java POJO for the Customer Object #

Map the Oracle custom object to a Java class. For example, if CUSTOMER_OBJ has fields like CUST_NO, CUST_NAME, CONTACT_EMAIL, your POJO would look like this:

public class Customer {
    private int custNo;
    private String custName;
    private String contactEmail;

    // Constructor, getters, setters
    public Customer(int custNo, String custName, String contactEmail) {
        this.custNo = custNo;
        this.custName = custName;
        this.contactEmail = contactEmail;
    }

    // Getters and setters here
    public int getCustNo() { return custNo; }
    public void setCustNo(int custNo) { this.custNo = custNo; }
    public String getCustName() { return custName; }
    public void setCustName(String custName) { this.custName = custName; }
    public String getContactEmail() { return contactEmail; }
    public void setContactEmail(String contactEmail) { this.contactEmail = contactEmail; }
}

Step 2: Full Java Implementation to Call the Procedure #

Use try-with-resources to handle JDBC resources safely, register all parameters correctly, and process the custom collection output:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.STRUCT;

public class CustomerProcedureCaller {

    // Replace this with your actual database connection logic
    private static Connection getDatabaseConnection() throws SQLException {
        // Example: return DriverManager.getConnection("jdbc:oracle:thin:@//host:port/service", "user", "pass");
        return null;
    }

    public void getSuspiciousCustomers(int custNo) {
        // The PL/SQL block (match your procedure's schema and package name)
        String plsql = "begin BIOTPL.PKG_FINGER.CUSTOMER_LIST_FOR_SUSPICIOUS_CHECK(?, ?, ?, ?); end;";

        // Use try-with-resources to auto-close connection, statement
        try (Connection conn = getDatabaseConnection();
             CallableStatement stmt = conn.prepareCall(plsql)) {

            // 1. Set input parameter (pCustNo)
            stmt.setInt(1, custNo);

            // 2. Register OUT parameters
            // Register custom collection: use OracleTypes.ARRAY with the full type name
            stmt.registerOutParameter(2, OracleTypes.ARRAY, "EMOB.PKG_FINGER.CUSTOMER_LIST");
            // Register error flag and message
            stmt.registerOutParameter(3, Types.VARCHAR);
            stmt.registerOutParameter(4, Types.VARCHAR);

            // 3. Execute the stored procedure
            stmt.execute();

            // 4. Check error status first
            String errorFlag = stmt.getString(3);
            String errorMessage = stmt.getString(4);

            if ("Y".equals(errorFlag)) {
                System.err.println("Procedure error encountered: " + errorMessage);
                return;
            }

            // 5. Process the custom collection output
            ARRAY customerArray = ((OracleCallableStatement) stmt).getARRAY(2);
            Object[] structArray = (Object[]) customerArray.getArray();

            // Convert each STRUCT to your Customer POJO
            for (Object obj : structArray) {
                STRUCT struct = (STRUCT) obj;
                Object[] attributes = struct.getAttributes();

                // Map attributes to Customer (adjust indices to match your object's field order)
                int customerNo = ((Number) attributes[0]).intValue();
                String customerName = (String) attributes[1];
                String email = (String) attributes[2];

                Customer customer = new Customer(customerNo, customerName, email);
                // Do something with the customer object (e.g., add to list, log, etc.)
                System.out.println("Retrieved customer: " + customer.getCustName() + " (" + customer.getCustNo() + ")");
            }

        } catch (SQLException e) {
            e.printStackTrace();
            // Handle JDBC exceptions appropriately (e.g., log, notify)
        }
    }
}

Key Notes to Remember #

• JDBC Driver: Make sure you're using Oracle's official JDBC driver (ojdbc8 or newer) to access OracleCallableStatement and oracle.sql classes.
• Type Name Exact Match: The third parameter in registerOutParameter for the array must be the exact full name of your Oracle collection type (including schema and package name).
• Object Field Order: When extracting attributes from the STRUCT, the order must match the order of fields in your Oracle CUSTOMER_OBJ type.
• Resource Management: Always use try-with-resources to avoid connection/statement leaks.

Troubleshooting Tips #

• If you get "invalid name pattern" errors, double-check the collection type name (case sensitivity matters if your Oracle schema uses quoted identifiers).
• If the array is null, verify that the stored procedure is actually returning data for the input pCustNo you're passing.

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