Oracle触发器转存储过程需求及跨表插入存储过程咨询

Got it, let's convert that INSTEAD OF trigger into a stored procedure (or two, depending on your use case). Here's how to do it properly in Oracle:

Batch Processing Version (Best for Bulk Sync) #

If you need to sync all new/unique records from LIVE_MATCHES to matches in one go (way more efficient than row-by-row processing), use this batch-focused procedure:

CREATE OR REPLACE PROCEDURE LIVE_MATCHES_TO_MATCHES_BATCH
IS
    p_priority NUMBER := 1;
    p_sport NUMBER := 0;
BEGIN
    -- Insert only records from LIVE_MATCHES that don't already exist in matches
    INSERT INTO matches (sub_list, priority, sport, created)
    SELECT lm.comp_name, p_priority, p_sport, SYSDATE
    FROM LIVE_MATCHES lm
    WHERE NOT EXISTS (
        SELECT 1 
        FROM matches m
        WHERE m.sub_list = lm.comp_name
    );
    
    -- Optional: Commit the transaction here, or let the caller handle it based on your workflow
    -- COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- Rollback on error to avoid partial inserts
        ROLLBACK;
        -- Re-throw the exception so the caller can handle it (or add custom error messaging here)
        RAISE;
END LIVE_MATCHES_TO_MATCHES_BATCH;
/

Single Record Version (Matches Trigger's Row-Level Logic) #

If you need to replicate the exact row-by-row behavior of your original trigger (e.g., for manual record insertion or integration with other code), use this parameterized procedure:

CREATE OR REPLACE PROCEDURE LIVE_MATCHES_TO_MATCHES_SINGLE(
    p_comp_name IN LIVE_MATCHES.comp_name%TYPE -- Use %TYPE to auto-match column data types
)
IS
    p_priority NUMBER := 1;
    p_sport NUMBER := 0;
BEGIN
    -- Exact same logic as the original trigger, but using an input parameter instead of :new
    INSERT INTO matches (sub_list, priority, sport, created)
    SELECT p_comp_name, p_priority, p_sport, SYSDATE
    FROM DUAL
    WHERE NOT EXISTS (
        SELECT 1 
        FROM matches m
        WHERE m.sub_list = p_comp_name
    );
    
    -- Optional commit
    -- COMMIT;
END LIVE_MATCHES_TO_MATCHES_SINGLE;
/

How to Call the Procedures #

• For the batch version (sync all unique records):

  EXEC LIVE_MATCHES_TO_MATCHES_BATCH;
  -- Or in a PL/SQL block:
  -- BEGIN LIVE_MATCHES_TO_MATCHES_BATCH; END;
  

• For the single record version:

  EXEC LIVE_MATCHES_TO_MATCHES_SINGLE('YOUR_COMP_NAME_HERE');
  

Key Differences from the Original Trigger #

• Manual Execution: Unlike the trigger (which runs automatically when inserting into LIVE_MATCHES), these procedures need explicit calls. If you want auto-sync, schedule the batch procedure with DBMS_SCHEDULER (e.g., run every 5 minutes).
• Efficiency: The batch version avoids row-by-row overhead, making it ideal for large datasets.
• Transaction Control: You have full control over commit/rollback timing, whereas triggers inherit the transaction context of the triggering statement.
• Flexibility: The single-record version can be called from other PL/SQL code, scripts, or application logic—something triggers can't do.

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