PLSQL GRP编号分配异常求助：按库存累计110000分组未实现需求

Let's walk through the issues in your current code and fix it to meet your requirement: assigning GRP numbers such that when the cumulative stock count reaches or exceeds 110000, the GRP increments and subsequent stores are assigned to the new group.

Key Issues in the Original Code #

• Invalid UPDATE syntax: You're missing the SET clause and proper table reference
• Incorrect exit condition: EXIT WHEN v_aggcount = 11; has no relation to your requirement and will prematurely stop processing
• Minor case inconsistency: Your cursor uses STOCK_COUNT as the alias, but you reference vll_use.stockcount (Oracle is case-insensitive, but consistency avoids confusion)
• Redundant inner exception block: The outer exception handler already covers errors

Corrected PL/SQL Code #

SET ECHO OFF
SET FEEDBACK ON
SET TIMING ON
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET PAGESIZE 0
SET VERIFY OFF
VARIABLE return_code NUMBER

DECLARE
    v_pg sku.LOCATION%TYPE; -- Match the column type correctly
    v_count sku.STOCK_COUNT%TYPE; -- Use the appropriate data type for stock count
    v_aggcount NUMBER := 0;
    v_value NUMBER := 1;
    CURSOR cur_upd_optinv_label IS
        SELECT /*+ Parallel (64) */ 
               LOCATION, 
               COUNT(*) AS STOCK_COUNT 
        FROM sku 
        GROUP BY LOCATION 
        ORDER BY COUNT(*) DESC;
BEGIN
    FOR vll_use IN cur_upd_optinv_label LOOP
        v_pg := vll_use.LOCATION;
        v_count := vll_use.STOCK_COUNT;
        
        -- Check if adding current store's count will exceed the threshold
        IF v_aggcount + v_count >= 110000 THEN
            -- Increment GRP and reset cumulative count to current store's count
            v_value := v_value + 1;
            v_aggcount := v_count;
        ELSE
            -- Add to cumulative count
            v_aggcount := v_aggcount + v_count;
        END IF;
        
        -- Update the GRP column for the current location
        UPDATE sku 
        SET GRP = v_value 
        WHERE LOCATION = v_pg;
        
        -- Optional: Print progress for debugging
        DBMS_OUTPUT.PUT_LINE('Assigned GRP ' || v_value || ' to location ' || v_pg || 
                            ' | Cumulative count: ' || v_aggcount);
    END LOOP;

    COMMIT;
    :return_code := 0;
EXCEPTION 
    WHEN OTHERS THEN
        :return_code := 3;
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error encountered: ' || SQLCODE || ' ' || SQLERRM);
END;
/

EXIT :return_code

Explanation of Changes #

1. Fixed UPDATE Syntax: Added SET clause and explicit table reference (sku) to properly update the GRP column
2. Adjusted Logic Flow: Now first checks if adding the current store's stock count will hit/exceed 110000. If yes, increment GRP and reset the cumulative count to the current store's value. Otherwise, just add to the cumulative total.
3. Removed Invalid Exit Condition: The EXIT WHEN clause that stopped processing at an arbitrary value is gone, ensuring all locations are processed
4. Type Consistency: Variables v_pg and v_count now explicitly match the column types from the sku table
5. Optional Debug Output: Added a DBMS_OUTPUT line to track which GRP is assigned to each location and the cumulative count (you can remove this if not needed)
6. Simplified Exception Handling: Removed the redundant inner exception block since the outer handler catches all errors and handles rollback appropriately

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