如何创建Oracle函数汇总Fun_1返回的REF_CURSOR数据？

Got it, here's how you can build a new function that aggregates the numeric values from Fun_1's REF_CURSOR output. I'll share two common implementations depending on whether you want a structured record return or a REF_CURSOR (matching the original function's type).

Option 1: Return a Custom Record Type (Ideal for PL/SQL Calls) #

This returns a clean, reusable record with the total sums, which works great for internal PL/SQL workflows.

-- First, define a reusable record type for the summary result (can live in a package or standalone)
CREATE OR REPLACE TYPE t_summary_rec IS RECORD (
    total_cash_in  NUMBER,
    total_cash_out NUMBER,
    total_net_win  NUMBER,
    total_vaucher  NUMBER
);
/

-- Now create the summary function
CREATE OR REPLACE FUNCTION fun_summary
RETURN t_summary_rec
IS
    v_source_cursor SYS_REFCURSOR;
    v_summary       t_summary_rec;
    -- Match this record structure exactly to what Fun_1 returns
    v_fun1_row      RECORD (
        system_name VARCHAR2(100),
        cash_in     NUMBER,
        cash_out    NUMBER,
        net_win     NUMBER,
        vaucher     NUMBER
    );
BEGIN
    -- Initialize all totals to 0 to avoid null-related issues
    v_summary.total_cash_in := 0;
    v_summary.total_cash_out := 0;
    v_summary.total_net_win := 0;
    v_summary.total_vaucher := 0;

    -- Fetch the cursor from the original function
    v_source_cursor := fun_1();

    -- Loop through each row and accumulate sums
    LOOP
        FETCH v_source_cursor INTO v_fun1_row;
        EXIT WHEN v_source_cursor%NOTFOUND;

        -- Use NVL to handle null values (prevents sums from becoming null)
        v_summary.total_cash_in := v_summary.total_cash_in + NVL(v_fun1_row.cash_in, 0);
        v_summary.total_cash_out := v_summary.total_cash_out + NVL(v_fun1_row.cash_out, 0);
        v_summary.total_net_win := v_summary.total_net_win + NVL(v_fun1_row.net_win, 0);
        v_summary.total_vaucher := v_summary.total_vaucher + NVL(v_fun1_row.vaucher, 0);
    END LOOP;

    -- Clean up the cursor to avoid leaks
    CLOSE v_source_cursor;

    RETURN v_summary;
END;
/

Test This Function #

Use a PL/SQL block to retrieve and print the results:

DECLARE
    v_result t_summary_rec;
BEGIN
    v_result := fun_summary();
    DBMS_OUTPUT.PUT_LINE('Total Cash In: ' || v_result.total_cash_in);
    DBMS_OUTPUT.PUT_LINE('Total Cash Out: ' || v_result.total_cash_out);
    DBMS_OUTPUT.PUT_LINE('Total Net Win: ' || v_result.total_net_win);
    DBMS_OUTPUT.PUT_LINE('Total Vaucher: ' || v_result.total_vaucher);
END;
/

Option 2: Return a REF_CURSOR (Matches Original Function's Return Type) #

If you need consistency with Fun_1's return type (e.g., for application integration), use this version:

CREATE OR REPLACE FUNCTION fun_summary
RETURN SYS_REFCURSOR
IS
    v_source_cursor SYS_REFCURSOR;
    v_total_cash_in  NUMBER := 0;
    v_total_cash_out NUMBER := 0;
    v_total_net_win  NUMBER := 0;
    v_total_vaucher  NUMBER := 0;
    v_fun1_row      RECORD (
        system_name VARCHAR2(100),
        cash_in     NUMBER,
        cash_out    NUMBER,
        net_win     NUMBER,
        vaucher     NUMBER
    );
    v_result_cursor SYS_REFCURSOR;
BEGIN
    -- Fetch the cursor from Fun_1
    v_source_cursor := fun_1();

    -- Accumulate sums from each row
    LOOP
        FETCH v_source_cursor INTO v_fun1_row;
        EXIT WHEN v_source_cursor%NOTFOUND;

        v_total_cash_in := v_total_cash_in + NVL(v_fun1_row.cash_in, 0);
        v_total_cash_out := v_total_cash_out + NVL(v_fun1_row.cash_out, 0);
        v_total_net_win := v_total_net_win + NVL(v_fun1_row.net_win, 0);
        v_total_vaucher := v_total_vaucher + NVL(v_fun1_row.vaucher, 0);
    END LOOP;

    CLOSE v_source_cursor;

    -- Open a cursor to return the single summary row
    OPEN v_result_cursor FOR
        SELECT v_total_cash_in AS total_cash_in,
               v_total_cash_out AS total_cash_out,
               v_total_net_win AS total_net_win,
               v_total_vaucher AS total_vaucher
        FROM DUAL;

    RETURN v_result_cursor;
END;
/

Test This Function #

Retrieve results in a PL/SQL block or use your application's cursor handling:

DECLARE
    v_cursor SYS_REFCURSOR;
    v_ci NUMBER;
    v_co NUMBER;
    v_nw NUMBER;
    v_v NUMBER;
BEGIN
    v_cursor := fun_summary();
    FETCH v_cursor INTO v_ci, v_co, v_nw, v_v;
    DBMS_OUTPUT.PUT_LINE('Total Cash In: ' || v_ci);
    DBMS_OUTPUT.PUT_LINE('Total Cash Out: ' || v_co);
    DBMS_OUTPUT.PUT_LINE('Total Net Win: ' || v_nw);
    DBMS_OUTPUT.PUT_LINE('Total Vaucher: ' || v_v);
    CLOSE v_cursor;
END;
/

Key Notes #

• Field Matching: Ensure the v_fun1_row record exactly matches the column names and data types returned by Fun_1. If Fun_1 uses a custom object/record type, reuse that instead of declaring a new one to avoid errors.
• Null Handling: The NVL function is critical here—it ensures null values in the source data don't turn your totals into null.
• Permissions: Make sure the user creating fun_summary has EXECUTE permission on Fun_1.

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