Oracle存储过程开发求助：按月份拆分获取一年预订数据

Hey there! Since you're new to Oracle, let's walk through building a stored procedure that handles your monthly interval queries—this will make it super easy to grab the data you need and export it to Excel later.

Your goal is to pull reservation data for 12 monthly intervals (from today back to one year ago). We'll create a stored procedure that loops through each month, calculates the correct date range for each interval, runs your query, and collects all results into a single table. This table will make exporting to Excel a breeze.

First, let's create a table to store all monthly results. This gives you a single source to export from, and we'll add a label to track which month each record belongs to:

CREATE TABLE monthly_reservations (
    -- Replace these columns with the actual columns from your reservations table
    reservation_id NUMBER,
    customer_name VARCHAR2(100),
    reservation_date DATE,
    -- This column tracks the month period (e.g., '2024-03' for March 2024)
    month_period VARCHAR2(7)
);

Note: Match the column names/data types to your actual reservations table—just add the month_period column to tag records.

Now let's build the stored procedure that handles the loop and data collection. We'll use Oracle's ADD_MONTHS function to easily calculate monthly date ranges, and a simple FOR loop to iterate 12 times (one for each month):

CREATE OR REPLACE PROCEDURE get_monthly_reservations
IS
    -- Variables to store our date range and month label
    v_start_date DATE;
    v_end_date DATE;
    v_month_label VARCHAR2(7);
BEGIN
    -- Clear existing data (optional, if you want fresh results each run)
    DELETE FROM monthly_reservations;
    
    -- Loop through 12 months (0 = current month, 11 = 11 months ago)
    FOR i IN 0..11 LOOP
        -- Calculate end date: Today, minus i months (truncated to remove time)
        v_end_date := TRUNC(ADD_MONTHS(SYSDATE, -i));
        -- Calculate start date: End date minus 1 month (so each interval is 1 month long)
        v_start_date := TRUNC(ADD_MONTHS(v_end_date, -1));
        
        -- Create a human-readable month label (e.g., '2024-03')
        v_month_label := TO_CHAR(v_end_date, 'YYYY-MM');
        
        -- Run your query and insert results into our monthly table
        INSERT INTO monthly_reservations
        SELECT 
            r.*,
            v_month_label AS month_period
        FROM reservations r
        WHERE r.date BETWEEN v_start_date AND v_end_date;
        
        -- Commit each month's data (or wait until the end—your call!)
        COMMIT;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Success! Collected 12 months of reservation data.');
EXCEPTION
    -- Catch any errors and roll back changes to avoid partial data
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Oops, an error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/

If you want to query full calendar months (e.g., March 1st to March 31st, instead of a rolling 30-day window), tweak the date calculations inside the loop:

-- For natural month: End date = last day of the target month
v_end_date := LAST_DAY(TRUNC(ADD_MONTHS(SYSDATE, -i)));
-- Start date = first day of the target month
v_start_date := TRUNC(v_end_date, 'MM');

This ensures you're grabbing all reservations from the first to last day of each calendar month.

1. Run the procedure: Open Oracle SQL Developer (or your preferred tool) and execute:

   EXEC get_monthly_reservations;
   -- Or use the block syntax:
   BEGIN
       get_monthly_reservations;
   END;
   /
   

2. Export to Excel: Once the procedure finishes, right-click the monthly_reservations table in your tool's navigator, select "Export", choose Excel (.xlsx) as the format, and follow the prompts to save the file.

• Test first: Before running the full procedure, test a single date range to make sure your query works. For example:

  SELECT * FROM reservations 
  WHERE date BETWEEN TRUNC(ADD_MONTHS(SYSDATE, -1)) AND TRUNC(SYSDATE);
  

• Index for speed: If your reservations table is large, add an index on the date column to speed up the queries:

  CREATE INDEX idx_reservations_date ON reservations(date);
  

• Check permissions: Make sure you have the right privileges to create tables, stored procedures, and insert data.

内容的提问来源于stack exchange，提问作者A.D