Oracle存储过程批量调用咨询：获取所有人员ID的多列多行数据集

Great question—this is such a common pain point when working with row-returning stored procedures in Oracle. The approach you’re using right now has two critical flaws: repeated procedure calls that kill performance, and an inability to handle multi-row results from the procedure. Here’s how to fix both without touching the existing package.proc:

Use a CROSS JOIN with the TABLE() Function #

The simplest and most efficient fix is to pair your persons table with the procedure’s output using a CROSS JOIN. This will call the procedure once per person ID (not once per column) and return all rows/columns from the procedure alongside the matching person ID.

Example SQL: #

SELECT 
    p.ID,
    proc_results.col1,
    proc_results.col2,
    proc_results.col3 -- Add all columns returned by package.proc here
FROM 
    persons p
CROSS JOIN 
    TABLE(package.proc(p.ID)) proc_results;

Why This Works: #

• No data loss: Unlike your subquery approach, this will return every row that package.proc outputs for each ID—no more errors when the procedure returns multiple rows.
• Dramatically better performance: Instead of calling package.proc (number of persons × number of columns) times, you only call it number of persons times. This cuts down on overhead drastically, especially with large datasets and should eliminate those server timeouts.
• Clean, readable syntax: You’re joining the person data directly with the procedure’s results, making the query logic easy to follow and maintain.

Additional Optimization Tips #

• Ensure the ID column in the persons table has an index. This speeds up the scan of the persons table and reduces the time spent looking up each ID to pass to the procedure.
• If package.proc has internal performance bottlenecks (like unindexed queries inside it), you might still see delays—but this approach fixes the calling overhead which is almost certainly the main cause of your current timeouts.

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