将特定数据同步SQL查询转换为定时执行的PL/SQL存储过程的技术问询

Hey there! Let's walk through converting your query into a proper PL/SQL stored procedure, plus set up the recurring schedule you need. First, let's tweak your original SQL to fix syntax issues and boost efficiency.

1. Fix & Optimize the Base Query #

Your original CREATE OR INSERT syntax isn't valid in Oracle—you probably meant INSERT INTO (since you're populating an existing temp table). Also, the nested to_char/to_date conversion is redundant and can prevent Oracle from using indexes on create_date. Here's the cleaned-up, more performant version:

INSERT INTO cst_cust_attributes_tmp (
    ORGANIZATION_ID, CUST_ID, ATTRIBUTE_ID,
    ATTRIBUTE_SEQ, ATTRIBUTE_VALUE, ACTIVE_FLAG,
    CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER
)
SELECT 
    ORGANIZATION_ID, CUST_ID, ATTRIBUTE_ID,
    ATTRIBUTE_SEQ, ATTRIBUTE_VALUE, ACTIVE_FLAG,
    CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER
FROM cst_cust_attributes
WHERE 
    create_date BETWEEN SYSDATE - 10 AND SYSDATE
    AND attribute_value = 'TOY_GIFT';

Note: If you need to avoid duplicate rows in the temp table (e.g., if the procedure runs multiple times before the table is cleared), you could swap this for INSERT ... SELECT ... WHERE NOT EXISTS or a MERGE statement—but I'll stick with your original intent for now.

2. Wrap It into a Robust PL/SQL Procedure #

Here's a full stored procedure with error handling (critical for production use) and optional cleanup logic:

CREATE OR REPLACE PROCEDURE populate_cust_attr_temp
IS
    v_error_msg VARCHAR2(2000);
BEGIN
    -- Optional: Uncomment below to clear the temp table before loading fresh data
    -- TRUNCATE TABLE cst_cust_attributes_tmp;

    -- Execute the insert
    INSERT INTO cst_cust_attributes_tmp (
        ORGANIZATION_ID, CUST_ID, ATTRIBUTE_ID,
        ATTRIBUTE_SEQ, ATTRIBUTE_VALUE, ACTIVE_FLAG,
        CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER
    )
    SELECT 
        ORGANIZATION_ID, CUST_ID, ATTRIBUTE_ID,
        ATTRIBUTE_SEQ, ATTRIBUTE_VALUE, ACTIVE_FLAG,
        CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER
    FROM cst_cust_attributes
    WHERE 
        create_date BETWEEN SYSDATE - 10 AND SYSDATE
        AND attribute_value = 'TOY_GIFT';

    -- Commit changes (skip this if you want to handle commits outside the procedure)
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Successfully inserted ' || SQL%ROWCOUNT || ' rows into cst_cust_attributes_tmp');

EXCEPTION
    WHEN OTHERS THEN
        v_error_msg := 'Error during procedure execution: ' || SQLERRM || ' (Error code: ' || SQLCODE || ')';
        DBMS_OUTPUT.PUT_LINE(v_error_msg);
        ROLLBACK; -- Undo partial changes if something fails
        RAISE; -- Re-throw the error so calling code can handle it if needed
END populate_cust_attr_temp;
/

• SQL%ROWCOUNT tracks how many rows were inserted, which is great for logging or verification.
• The exception block catches unexpected errors, rolls back changes, and prints details (in production, you might want to log this to an audit table instead of using DBMS_OUTPUT).

3. Schedule the Procedure to Run Every 10 Days #

Oracle's DBMS_SCHEDULER is the modern, reliable way to set up recurring jobs. Here's how to create a job that runs your procedure every 10 days:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'POPULATE_CUST_ATTR_RECURRING_JOB',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'populate_cust_attr_temp',
        start_date      => SYSDATE, -- Start immediately
        repeat_interval => 'FREQ=DAILY; INTERVAL=10', -- Run every 10 days
        enabled         => TRUE,
        comments        => 'Populates temp table with last 10 days of TOY_GIFT customer data'
    );
END;
/

• If you want to start on a specific date/time (e.g., 2 AM on May 20th), replace SYSDATE with TO_TIMESTAMP('2024-05-20 02:00:00', 'YYYY-MM-DD HH24:MI:SS').
• To modify or disable the job later, use DBMS_SCHEDULER.SET_ATTRIBUTE or DBMS_SCHEDULER.DISABLE.

4. Test the Procedure Manually #

Before trusting the scheduler, run the procedure manually to confirm it works:

-- Quick execution with EXEC
EXEC populate_cust_attr_temp;

-- Or using an anonymous block
BEGIN
    populate_cust_attr_temp;
END;
/

Check the cst_cust_attributes_tmp table to verify the correct rows were inserted.

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