How to combine the two SQL query results into one table?

Question

create or replace PROCEDURE SearchResult
(     V_ID IN VARCHAR2
      , V_REFCUR OUT SYS_REFCURSOR
)
IS

BEGIN
  DECLARE CNT INT;

  BEGIN

  SELECT COUNT(*) INTO CNT 
  FROM TABLE1 T1
  INNER JOIN TABLE2 T2 ON T1.CODE = T2.CODE
  WHERE T1.ID = V_ID;

  IF (CNT > 0) THEN
  OPEN V_REFCUR FOR  
  SELECT T1.TYPE
  FROM TABLE T1
  INNER JOIN TABLE2 T2 ON T1.CODE = T2.CODE
  WHERE T1.ID = V_ID AND T1.TYPE IS NOT NULL;


  ELSE 
  OPEN V_REFCUR FOR  
  SELECT 
     T3.NAME||T1.TYPE
     FROM TABLE3 T3
     INNER JOIN TABLE2 T2 ON T2.ID= T3.ID
     INNER JOIN TABLE1 T1 ON T1.CODE = T2.CODE
     AND TI.TYPE IS NOT NULL AND T1.ID = V_ID;

  END IF;
  END;

   BEGIN
   OPEN V_REFCUR FOR
    SELECT
    T1.NAME
    , T1.ADDRESS
    FROM TABLE1 T1
    WHERE T1.ID = V_ID

 END;
END SearchResult

Answer 1

To combine results of separate queries into the same table you have several options available to you:

• Combine both queries into one by using a Union or a Union all depending on whether you want to exclude duplicates.
• Use a temp table to break apart your queries into 2 separate Insert statements and then return the results of the temp table.