新手求助：将现有SQL查询转为支持SKU参数输入的PL/SQL程序

Hey there! Since you're new to PL/SQL and looking to turn that static SQL into a reusable program that accepts a SKU parameter (and replaces hardcoded SKU conditions), let's walk through this step by step with practical examples.

1. Create a Reusable Stored Procedure #

A stored procedure is ideal here—it encapsulates your SQL logic, accepts input parameters, and lets you run the query with different SKUs anytime. Here's a tailored version for your example:

CREATE OR REPLACE PROCEDURE get_sku_order_details (p_sku IN NUMBER)
IS
    -- Declare variables to match table column types (avoids type mismatches)
    v_column_id ORDERS_TABLE.COLUMN_ID%TYPE;
    v_sku ORDERS_TABLE.SKU%TYPE;
    v_orders ORDERS_TABLE.ORDERS%TYPE;
    v_customer_id CUSTOMER_TABLE.CUSTOMER_ID%TYPE;

    -- Cursor holding your parameterized query
    CURSOR c_sku_results IS
        SELECT A.*, CT.CUSTOMER_ID, CT.ORDERS 
        FROM CUSTOMER_TABLE CT 
        RIGHT JOIN ( 
            SELECT OT.COLUMN_ID, OT.SKU, OT.ORDERS 
            FROM ORDERS_TABLE OT 
            WHERE OT.SKU = p_sku  -- Replaced hardcoded 123 with input parameter
        ) A ON CT.ORDERS = A.ORDERS AND CT.SKU > 0;
BEGIN
    -- Loop through the cursor to process results (here we print them to the console)
    OPEN c_sku_results;
    LOOP
        FETCH c_sku_results INTO v_column_id, v_sku, v_orders, v_customer_id;
        EXIT WHEN c_sku_results%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(
            'Column ID: ' || v_column_id || 
            ' | SKU: ' || v_sku || 
            ' | Orders: ' || v_orders || 
            ' | Customer ID: ' || NVL(v_customer_id, 'No customer linked')
        );
    END LOOP;
    CLOSE c_sku_results;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No records found for SKU: ' || p_sku);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/

Key changes here:

• We swapped the hardcoded 123 in OT.SKU = 123 with the input parameter p_sku—this is how the procedure uses your specified SKU.
• Used %TYPE for variables to match the exact data types of your table columns (safer than hardcoding types like NUMBER(10)).
• Added an exception block to handle common errors like missing data or unexpected issues.

2. How to Call the Procedure #

Testing the procedure is simple. Use EXECUTE for quick one-off calls, or wrap it in a PL/SQL block if you need to chain multiple calls:

-- Call with SKU 456 as an example
EXECUTE get_sku_order_details(456);

-- Or in a PL/SQL block for more control
BEGIN
    get_sku_order_details(789);
    get_sku_order_details(101112);
END;
/

3. Optional: Return Results to External Apps (Using REF CURSOR) #

If you need to pass the result set to an external tool (like Java, .NET, or a reporting tool), use a REF CURSOR instead of printing to the console. This lets the caller handle the results:

CREATE OR REPLACE PROCEDURE get_sku_order_details_ref (
    p_sku IN NUMBER,
    p_result OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_result FOR
        SELECT A.*, CT.CUSTOMER_ID, CT.ORDERS 
        FROM CUSTOMER_TABLE CT 
        RIGHT JOIN ( 
            SELECT OT.COLUMN_ID, OT.SKU, OT.ORDERS 
            FROM ORDERS_TABLE OT 
            WHERE OT.SKU = p_sku
        ) A ON CT.ORDERS = A.ORDERS AND CT.SKU > 0;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;  -- Re-throw the error so the caller can handle it
END;
/

Test this version with a PL/SQL block:

DECLARE
    v_result SYS_REFCURSOR;
    v_column_id ORDERS_TABLE.COLUMN_ID%TYPE;
    v_sku ORDERS_TABLE.SKU%TYPE;
    v_orders ORDERS_TABLE.ORDERS%TYPE;
    v_customer_id CUSTOMER_TABLE.CUSTOMER_ID%TYPE;
BEGIN
    get_sku_order_details_ref(123, v_result);
    FETCH v_result INTO v_column_id, v_sku, v_orders, v_customer_id;
    
    WHILE v_result%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE('Column ID: ' || v_column_id || ' | SKU: ' || v_sku);
        FETCH v_result INTO v_column_id, v_sku, v_orders, v_customer_id;
    END LOOP;
    
    CLOSE v_result;
END;
/

4. Quick Tips for PL/SQL Beginners #

• Match parameter types: If your SKU is a string (e.g., 'ABC123'), update p_sku IN NUMBER to p_sku IN VARCHAR2(20) (adjust the length to match your table's SKU column).
• Dynamic conditions: If CT.SKU > 0 needs to be flexible, add another parameter like p_min_sku IN NUMBER DEFAULT 0 and change the condition to CT.SKU > p_min_sku. The DEFAULT value means it uses 0 if you don't pass a value.
• Test edge cases: Try calling the procedure with SKUs that don't exist to make sure the NO_DATA_FOUND exception works.
• Use DBMS_OUTPUT for debugging: Enable output in your SQL client (like SQL Developer) to see the printed results.

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