Oracle：如何将SYS_REFCURSOR转为表以关联并重构存储过程

Got it, let's fix this. The core problem is that Oracle doesn't support converting a SYS_REFCURSOR to a relational table with TABLE() out of the box (without pipelined functions, which you want to avoid due to double data traversal). Instead, we can handle each row from the cursor individually, look up the description from NOM_CODES on the fly, and write the output line immediately.

Revised Generic Procedure #

Here's the updated spool_file procedure that works exactly as you need—no cursor-to-table conversion required:

procedure spool_file (p_file_name varchar2, p_curr sys_refcursor) is
    l_File_Handle Utl_File.File_Type;
    -- Match these data types to your actual table columns!
    l_id          NUMBER;
    l_code        VARCHAR2(10);
    l_desc        VARCHAR2(100);
    l_file_line   VARCHAR2(200); -- Adjust length based on your data
begin
    -- Clean up any open file handles first
    IF Utl_File.Is_Open(l_File_Handle) THEN
        Utl_File.Fclose(l_File_Handle);
    END IF;
    l_File_Handle := Utl_File.Fopen(l_DIR, p_file_name , 'W', l_Len);

    -- Fetch and process each row from the input cursor
    LOOP
        FETCH p_curr INTO l_id, l_code;
        EXIT WHEN p_curr%NOTFOUND;

        -- Get the matching description from NOM_CODES
        SELECT c.desc INTO l_desc
        FROM nom_codes c
        WHERE c.code = l_code;

        -- Build the output line and write it to the file
        l_file_line := l_id || ',' || l_code || ',' || l_desc;
        Utl_File.Put_Line(l_File_Handle, Convert(l_file_line, 'CL8MSWIN1251') || l_cr);
    END LOOP;

    -- Cleanup resources
    CLOSE p_curr;
    Utl_File.Fclose(l_File_Handle);
end spool_file;

Your Table-Specific Procedures Stay Exactly As Designed #

You don't need to change any of your existing proc_tab1, proc_tab2, or proc_tab3—they'll work perfectly with the revised generic procedure:

procedure proc_tab1 is
    v_File_Name VARCHAR2(100);
begin
    v_File_Name := 'TAB_1.TXT';
    spool_file(v_File_Name, cursor(select t.id, t.code from tab1 t));
end proc_tab1;

procedure proc_tab2 is
    v_File_Name VARCHAR2(100);
begin
    v_File_Name := 'TAB_2.TXT';
    spool_file(v_File_Name, cursor(select t.id, t.code from tab2 t));
end proc_tab2;

procedure proc_tab3 is
    v_File_Name VARCHAR2(100);
begin
    v_File_Name := 'TAB_3.TXT';
    spool_file(v_File_Name, cursor(select t.id, t.code from tab3 t));
end proc_tab3;

Why This Works #

• No Double Traversal: We fetch each row once from the source table cursor, look up the description once, and write it to the file immediately—no redundant data processing.
• Full Code Reusability: All the repetitive file handling and join logic is centralized in spool_file, keeping your table-specific procedures clean and concise.
• Avoids Pipelined Functions: No need to implement a pipelined function that would require reading the cursor twice (once to generate rows, once to join with NOM_CODES).

Quick Notes #

• Data Type Matching: Make sure l_id, l_code, and l_desc match the exact data types of your table columns (e.g., if ID is VARCHAR2 instead of NUMBER, adjust the variable type).
• Error Handling: If there's a chance a CODE from your tables doesn't exist in NOM_CODES, add an exception handler (like EXCEPTION WHEN NO_DATA_FOUND THEN l_desc := 'N/A') to avoid runtime errors.

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