Oracle存储过程迁移旧表数据及表列HTML内容处理技术咨询

Let’s walk through creating a stored procedure that migrates data from your old table to the new one, specifically handling that HTML-formatted comment column. I’ll assume your old table (let’s call it old_comments) has a column html_content containing the HTML snippet you shared, and your new table (new_comments) has structured columns like username, comment_timestamp, comment_text (adjust names to match your actual schema).

Step 1: Full Stored Procedure Code #

CREATE OR REPLACE PROCEDURE migrate_comments_data
IS
    CURSOR old_data_cursor IS
        SELECT html_content
        FROM old_comments;
    v_html_content CLOB;
    v_username VARCHAR2(100);
    v_comment_date VARCHAR2(50);
    v_comment_time VARCHAR2(20);
    v_comment_timestamp TIMESTAMP;
    v_comment_text VARCHAR2(1000);
BEGIN
    -- Iterate over records from the old table
    FOR rec IN old_data_cursor LOOP
        v_html_content := rec.html_content;
        
        -- Extract username from the first <b> tag
        v_username := REGEXP_SUBSTR(v_html_content, '<b style="font-size:15px;">\s*(.*?)\s*</b>', 1, 1, NULL, 1);
        
        -- Extract date (second <b> tag) and time
        v_comment_date := REGEXP_SUBSTR(v_html_content, '<b>\s*(.*?)\s*</b>', 1, 2, NULL, 1);
        v_comment_time := REGEXP_SUBSTR(v_html_content, '</b>\s*(.*?)\s*</div>', 1, 1, NULL, 1);
        
        -- Convert combined date/time string to a TIMESTAMP
        v_comment_timestamp := TO_TIMESTAMP(v_comment_date || ' ' || v_comment_time, 'MM/DD/YYYY HH24:MI:SS');
        
        -- Extract comment text from the <p> tag
        v_comment_text := REGEXP_SUBSTR(v_html_content, '<p>\s*(.*?)\s*</p>', 1, 1, NULL, 1);
        
        -- Insert parsed data into the new table
        INSERT INTO new_comments (username, comment_timestamp, comment_text)
        VALUES (v_username, v_comment_timestamp, v_comment_text);
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Data migration completed successfully!');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Migration failed with error: ' || SQLERRM);
        RAISE;
END;
/

Step 2: Key Details Explained #

• Cursor Iteration: The cursor fetches records one by one, which works well for small-to-medium datasets. For large datasets (millions of rows), use BULK COLLECT to fetch batches of records and speed up processing.
• Regex Parsing:
  • REGEXP_SUBSTR targets specific HTML tags to extract the data we need. The (.*?) captures text between tags, and the final 1 returns the first matched group. Adjust patterns if your HTML structure varies (e.g., different class names or tag order).
• Date Conversion: The TO_TIMESTAMP function converts the raw date/time string into a proper timestamp. If your dates use DD/MM/YYYY instead of MM/DD/YYYY, update the format mask to 'DD/MM/YYYY HH24:MI:SS'.
• Error Handling: The exception block rolls back changes on failure and logs the error message, so you can debug issues easily.

Step 3: Testing & Optimization Tips #

• Test with a Small Dataset: Modify the cursor to SELECT html_content FROM old_comments WHERE ROWNUM <= 10 to validate parsing logic before running on all data.
• Bulk Processing for Large Data: Replace the cursor loop with bulk collection to handle large volumes efficiently. Example snippet:

  TYPE html_tab IS TABLE OF old_comments.html_content%TYPE;
  v_html_tab html_tab;
  BEGIN
      OPEN old_data_cursor;
      LOOP
          FETCH old_data_cursor BULK COLLECT INTO v_html_tab LIMIT 1000;
          EXIT WHEN v_html_tab.COUNT = 0;
          
          FOR i IN 1..v_html_tab.COUNT LOOP
              -- Reuse parsing logic here with v_html_tab(i)
          END LOOP;
      END LOOP;
      CLOSE old_data_cursor;
  

• Handle NULLs: Add checks like IF v_username IS NULL THEN v_username := 'Unknown'; to avoid inserting invalid data if parsing fails for a record.

Step 4: Run the Procedure #

Execute the procedure using:

EXECUTE migrate_comments_data;

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