Oracle中用游标自动填充Date表is_holiday列的存储过程创建

Got it, let's walk through creating this stored procedure exactly as you need it—using a cursor to reference the holidays table and update the Date table's is_holiday column.

Step-by-Step Stored Procedure Code #

First, here's the complete PL/SQL code. Note that since Date is an Oracle reserved keyword, I've wrapped the table and column name in double quotes to avoid syntax errors:

CREATE OR REPLACE PROCEDURE update_holiday_flags
IS
    -- Define a cursor to fetch all holiday dates from the holidays table
    CURSOR cur_holidays IS
        SELECT "date" FROM holidays;
    v_hol_date DATE; -- Variable to hold each fetched holiday date
BEGIN
    -- Open the cursor to start retrieving data
    OPEN cur_holidays;
    
    -- Loop through each record in the cursor
    LOOP
        -- Fetch the next holiday date into our variable
        FETCH cur_holidays INTO v_hol_date;
        -- Exit the loop when there are no more records to fetch
        EXIT WHEN cur_holidays%NOTFOUND;
        
        -- Update the Date table: set is_holiday to 'Y' for matching dates
        UPDATE "Date"
        SET is_holiday = 'Y'
        WHERE "Date" = v_hol_date;
    END LOOP;
    
    -- Close the cursor once we're done with it
    CLOSE cur_holidays;
    
    -- Commit the changes (adjust this if you handle transactions elsewhere)
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Successfully updated holiday flags in Date table!');
EXCEPTION
    -- Handle any unexpected errors
    WHEN OTHERS THEN
        ROLLBACK; -- Undo changes if something goes wrong
        DBMS_OUTPUT.PUT_LINE('Error updating holiday flags: ' || SQLERRM);
        RAISE; -- Re-throw the exception if you want upstream code to handle it
END update_holiday_flags;
/

Key Notes to Keep in Mind #

• Reserved Keyword Handling: Date is a reserved word in Oracle, so wrapping it in double quotes ("Date") ensures the database recognizes it as your table/column name.
• Cursor Behavior: The cursor cur_holidays fetches every date from the holidays table. We loop through each date and run an update for that specific date.
• Transaction Control: The COMMIT is included here, but if you're running this as part of a larger transaction, you might want to remove it and handle commits outside the procedure.
• Date Type Matching: Make sure the date column in holidays and the Date column in your Date table are both DATE data types. If they're stored as strings, you'll need to use TO_DATE() to convert them (e.g., TO_DATE("date", 'DD-MM-YYYY')).

How to Run the Procedure #

Once you've created the procedure, you can execute it using:

EXEC update_holiday_flags;

Or within a PL/SQL block:

BEGIN
    update_holiday_flags;
END;
/

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