Oracle存储过程优化：移除重复Count查询方案咨询

Got it, let's tackle this problem. You want to ditch that redundant COUNT query, skip temp tables (smart call to avoid unnecessary IO), and still determine if your REF cursor has records to return the appropriate WARN#/ERROR status—all while working within your framework's limitation of not being able to return both the string and cursor together. Here are three practical Oracle PL/SQL solutions that fit your requirements:

1. Use an In-Memory PL/SQL Collection (Best for Most Cases) #

This approach runs your query once, stores the results in an in-memory collection (no disk IO involved), checks if the collection has data, then feeds the collection back into your REF cursor. It’s clean, efficient, and avoids duplicate query execution.

CREATE OR REPLACE PROCEDURE your_procedure_name(
  p_cur_summary OUT SYS_REFCURSOR,
  p_status OUT VARCHAR2 -- Assuming your framework still lets you output this status param
) AS
  -- Define a collection type matching your view's row structure
  TYPE t_main_tab IS TABLE OF V_MAIN_T%ROWTYPE;
  v_main_records t_main_tab;
BEGIN
  -- Run your query ONCE and load all results into the collection
  SELECT * 
  BULK COLLECT INTO v_main_records 
  FROM V_MAIN_T 
  WHERE /* Your original query conditions */;

  -- Check if we have records to set the status
  IF v_main_records.COUNT > 0 THEN
    p_status := 'WARN#'; -- Adjust based on your actual status logic
    -- Open the cursor to return all collected records
    OPEN p_cur_summary FOR 
      SELECT * FROM TABLE(v_main_records);
  ELSE
    p_status := 'ERROR';
    -- Return an empty cursor to match your existing behavior
    OPEN p_cur_summary FOR 
      SELECT * FROM V_MAIN_T WHERE 1 = 0;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- Handle exceptions as needed (e.g., set error status, close cursor)
    p_status := 'ERROR';
    IF p_cur_summary%ISOPEN THEN
      CLOSE p_cur_summary;
    END IF;
    RAISE;
END;

Pros: Only one query execution, no disk IO, simple logic.
Cons: If your result set is extremely large (100k+ rows), the collection could consume significant memory. But if you were running a COUNT query before, it’s unlikely your dataset is that massive.

2. Pre-Fetch the First Record Then Reconstruct the Cursor #

This method fetches just the first record to check for existence, then reconstructs the cursor to include that first record plus the rest of the results. It’s useful if you want to avoid loading all data into memory at once.

CREATE OR REPLACE PROCEDURE your_procedure_name(
  p_cur_summary OUT SYS_REFCURSOR,
  p_status OUT VARCHAR2
) AS
  v_first_record V_MAIN_T%ROWTYPE;
BEGIN
  -- Try to fetch the first record from your query
  BEGIN
    SELECT * 
    INTO v_first_record 
    FROM V_MAIN_T 
    WHERE /* Your original query conditions */
    FETCH FIRST 1 ROW ONLY; -- Oracle 12c+ syntax; use ROWNUM=1 for older versions

    p_status := 'WARN#';
    -- Open the cursor to return the first record plus all other records
    OPEN p_cur_summary FOR
      SELECT * FROM (
        -- Return the pre-fetched first record
        SELECT v_first_record.* FROM DUAL
        UNION ALL
        -- Return the rest, excluding the first record (use ROWID or unique key if possible)
        SELECT * FROM V_MAIN_T 
        WHERE /* Your original query conditions */
        AND ROWID != v_first_record.ROWID
      );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_status := 'ERROR';
      OPEN p_cur_summary FOR SELECT * FROM V_MAIN_T WHERE 1 = 0;
  END;
EXCEPTION
  WHEN OTHERS THEN
    p_status := 'ERROR';
    IF p_cur_summary%ISOPEN THEN
      CLOSE p_cur_summary;
    END IF;
    RAISE;
END;

Pros: Minimal memory usage (only stores one record upfront).
Cons: Requires handling duplicate record exclusion (using ROWID works for single-table views; if your view joins multiple tables, use a unique composite key instead). Also, slightly more complex than the collection approach.

3. Use a Scrollable Cursor (Oracle 12c+) #

If you’re on Oracle 12c or newer, you can use a scrollable REF cursor to fetch the first record, check for existence, then scroll back to the start of the cursor to return all results.

CREATE OR REPLACE PROCEDURE your_procedure_name(
  p_cur_summary OUT SYS_REFCURSOR,
  p_status OUT VARCHAR2
) AS
  v_dummy_record V_MAIN_T%ROWTYPE;
BEGIN
  -- Open a SCROLLABLE cursor (supports moving backward/forward)
  OPEN p_cur_summary FOR
    SELECT * FROM V_MAIN_T WHERE /* Your original query conditions */
    WITH HOLD; -- Optional: keeps cursor open across commits if needed

  -- Fetch the first record to check existence
  FETCH p_cur_summary INTO v_dummy_record;
  IF p_cur_summary%FOUND THEN
    p_status := 'WARN#';
    -- Scroll back to the first record so the caller gets all results
    p_cur_summary%PRIOR;
  ELSE
    p_status := 'ERROR';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    p_status := 'ERROR';
    IF p_cur_summary%ISOPEN THEN
      CLOSE p_cur_summary;
    END IF;
    RAISE;
END;

Pros: No duplicate queries, no extra memory for collections.
Cons: Only works on Oracle 12c+, and scrollable cursors have minor overhead compared to regular cursors. Also, some client tools might have limited support for scrollable cursors.

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