Oracle 11G插入无记录时抛出自定义异常的实现需求

Let me start by fixing a small issue in your original code first: using LOOP as a cursor variable name is invalid since it's a reserved keyword. Also, your original cursor only selects TIME_PERIOD from TABLEA, but you're referencing TABLEA.ITEM in the INSERT subquery—this would throw an error because the cursor doesn't include that column. We'll adjust the cursor to pull the necessary ITEM value (and keep TIME_PERIOD if you need it for other logic).

Here's the modified PL/SQL block that meets your requirements:

DECLARE
    -- Define custom exception for missing subitems
    NO_SUBITEM_FOUND EXCEPTION;
    -- Variables to hold cursor values
    v_item TABLEA.ITEM%TYPE;
    v_time_period TABLEA.TIME_PERIOD%TYPE;
    v_subitem_exists NUMBER;
BEGIN
    -- Cursor to fetch unique ITEM and TIME_PERIOD from TABLEA
    FOR rec IN (SELECT DISTINCT ITEM, TIME_PERIOD FROM TABLEA)
    LOOP
        v_item := rec.ITEM;
        v_time_period := rec.TIME_PERIOD;
        
        -- Check if any subitems exist for the current ITEM in TABLEC
        SELECT CASE 
                 WHEN EXISTS (SELECT 1 FROM TABLEC WHERE TABLEC.ITEM = v_item) 
                 THEN 1 
                 ELSE 0 
               END
        INTO v_subitem_exists
        FROM DUAL;
        
        IF v_subitem_exists = 1 THEN
            -- Insert into TABLEB if subitems exist
            INSERT INTO TABLEB (SUBITEM, LOC)
            SELECT SUBITEM, LOC
            FROM TABLEC
            WHERE TABLEC.ITEM = v_item;
        ELSE
            -- Throw custom exception when no subitems are found
            RAISE NO_SUBITEM_FOUND;
        END IF;
    END LOOP;
    
EXCEPTION
    WHEN NO_SUBITEM_FOUND THEN
        -- Insert error details into ER table (adjust columns to match your ER table structure)
        INSERT INTO ERROR_LOG (ITEM, ERROR_MESSAGE, ERROR_TIMESTAMP, TIME_PERIOD)
        VALUES (v_item, 'No subitems found for the specified item', SYSDATE, v_time_period);
        -- Optional: Re-raise the exception if you want it to propagate further to the caller
        -- RAISE;
END;
/

Key Details Explained: #

• Custom Exception: NO_SUBITEM_FOUND is declared to explicitly flag the scenario where an ITEM has no matching SUBITEM in TABLEC.
• Cursor Adjustment: The cursor now selects both ITEM and TIME_PERIOD from TABLEA to ensure we have the item we need to validate, while retaining the time period if it's relevant for your business logic.
• Validation Check: We use EXISTS for better performance—it stops searching as soon as it finds a matching subitem, instead of counting all matches like COUNT(*) would.
• Exception Handling: When the exception triggers, we log the problematic ITEM, error message, timestamp, and time period into your ER table. Adjust the table/column names to match your actual error logging setup.
• Optional Re-raise: If you want the exception to be caught by an outer block or reported to the application, uncomment the RAISE; line in the exception block.

Quick Notes: #

• Ensure your ER table (here named ERROR_LOG) has columns that can store all the details you want to log.
• If TIME_PERIOD isn't needed for logging or business logic, you can remove it from the cursor and variables entirely.

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