编写PL/SQL存储过程：表记录求和及逐行输出每行求和结果

Let's walk through practical, tested solutions for both of your requirements. I'll use sample table structures that you can easily adapt to your actual database schema.

1. Stored Procedure to Calculate Total Sum from a Table #

Suppose you have a table like sales_data with a numeric column sale_amount that you want to sum up. Here's a robust procedure that fetches the total and outputs the result:

CREATE OR REPLACE PROCEDURE calculate_total_sum
IS
    v_total NUMBER := 0;
BEGIN
    -- Fetch the total sum of the numeric column
    SELECT SUM(sale_amount)
    INTO v_total
    FROM sales_data;

    -- Print the final total
    DBMS_OUTPUT.PUT_LINE('Total sum of all records: ' || v_total);
    
    -- Handle edge cases
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No records exist in the table.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error encountered: ' || SQLERRM);
END;
/

Quick Notes: #

• Swap sales_data and sale_amount with your actual table name and numeric column.
• Run it with EXEC calculate_total_sum; (or wrap it in a PL/SQL block: BEGIN calculate_total_sum; END;).
• The exception handling covers empty tables and unexpected errors, so the procedure won't crash silently.

2. Stored Procedure to Calculate Row-Wise Sum and Output Each Result #

For this task, let's use a table like expense_details with multiple numeric columns (rent, utilities, groceries). The procedure will loop through each row, compute its total, and print the result line by line:

CREATE OR REPLACE PROCEDURE calculate_row_wise_sum
IS
    -- Cursor to fetch every row from the table
    CURSOR c_expenses IS
        SELECT id, rent, utilities, groceries
        FROM expense_details;
    
    -- Variables matching table column data types
    v_id expense_details.id%TYPE;
    v_rent expense_details.rent%TYPE;
    v_utilities expense_details.utilities%TYPE;
    v_groceries expense_details.groceries%TYPE;
    v_row_total NUMBER;
BEGIN
    -- Open the cursor and iterate through rows
    OPEN c_expenses;
    LOOP
        FETCH c_expenses INTO v_id, v_rent, v_utilities, v_groceries;
        EXIT WHEN c_expenses%NOTFOUND;
        
        -- Calculate sum for the current row
        v_row_total := v_rent + v_utilities + v_groceries;
        
        -- Print the row-specific total
        DBMS_OUTPUT.PUT_LINE('Row ID: ' || v_id || ' | Total Expenses: ' || v_row_total);
    END LOOP;
    CLOSE c_expenses;
    
    -- Handle errors and clean up cursor if needed
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No records found in the table.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error encountered: ' || SQLERRM);
            IF c_expenses%ISOPEN THEN
                CLOSE c_expenses;
            END IF;
END;
/

Quick Notes: #

• Adjust the cursor query to include all numeric columns from your table.
• Using %TYPE ensures variables match the column data types, so you don't have to hardcode types like NUMBER(10,2).
• Execute with EXEC calculate_row_wise_sum; to see each row's total printed individually.

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