Oracle PL/SQL存储过程中校验表类型参数空值并处理

Got it, let's work through this problem to get your validation logic working correctly. The main gaps in your original code are handling the table-type parameter (it's a collection of objects, not a single object) and adding precise null checks for the IDs. Here's how to implement it properly:

First, let's fix the package body with complete validation and iteration logic:

CREATE OR REPLACE PACKAGE BODY order_mgr IS
    PROCEDURE ins_trees (p_emp_details_in IN trees_type_t, p_nrmployee_Id IN NUMBER)
    IS
        v_tree_obj tree_obj_type;
    BEGIN
        -- Guard clause: avoid processing empty/null input
        IF p_emp_details_in IS NULL OR p_emp_details_in.COUNT = 0 THEN
            RAISE_APPLICATION_ERROR(-20000, 'Input employee details collection cannot be empty or null.');
        END IF;

        -- Iterate over each object in the table-type parameter
        FOR i IN p_emp_details_in.FIRST .. p_emp_details_in.LAST LOOP
            v_tree_obj := p_emp_details_in(i);

            -- Case 1: Manager ID is null, Employee ID exists → insert into emp
            IF v_tree_obj.manager_id IS NULL AND v_tree_obj.employee_id IS NOT NULL THEN
                INSERT INTO emp (employee_id, address)
                VALUES (v_tree_obj.employee_id, v_tree_obj.address);
            -- Case 2: Employee ID is null, Manager ID exists → insert into manager
            ELSIF v_tree_obj.employee_id IS NULL AND v_tree_obj.manager_id IS NOT NULL THEN
                INSERT INTO manager (manager_id, address)
                VALUES (v_tree_obj.manager_id, v_tree_obj.address);
            -- Optional: Handle invalid records (both null or both non-null)
            ELSE
                RAISE_APPLICATION_ERROR(-20001, 
                    'Invalid record: Exactly one of employee_id or manager_id must be non-null. ' ||
                    'Employee ID: ' || v_tree_obj.employee_id || ', Manager ID: ' || v_tree_obj.manager_id);
            END IF;
        END LOOP;

        -- Note: Commit only if your procedure is responsible for transaction control;
        -- otherwise leave this to the caller
        -- COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- Add error logging here if needed, then re-raise the exception
            RAISE;
    END ins_trees;
END order_mgr;
/

Key Details Explained: #

• Iterating Over the Collection: Since trees_type_t is a table of tree_obj_type, we use a FOR loop with FIRST and LAST to loop through every object in the input parameter. You can't reference the collection directly like a single object—you need to access each element by index.
• Precise Null Checks: We add paired checks to ensure we only insert valid records:
  • Insert into emp only when manager_id is null and employee_id has a value (prevents inserting empty employee IDs)
  • Insert into manager only when employee_id is null and manager_id has a value (same logic for manager records)
• Edge Case Handling: The ELSE block catches invalid records where both IDs are null or both are present. You can adjust this to skip the record instead of raising an error if that's your requirement.
• Column Fix: Your original code referenced address_id, but your tree_obj_type defines an address attribute. I corrected this to match your object definition.

Confirm Your Object Types #

Your existing object definitions are already correct for this logic:

CREATE OR REPLACE TYPE tree_obj_type AS OBJECT (
    employee_id VARCHAR2(10),
    manager_id VARCHAR2(10),
    address VARCHAR2(100)
);
/

CREATE OR REPLACE TYPE trees_type_t AS TABLE OF tree_obj_type;
/

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