title: PL/SQL Creating a procedure that contains result set joins
body: <p>I want to create a procedure in PL/SQL that has 5 steps. Step 1 and 2 execute first and return an <code>ID</code>. In step 3, we have a <code>SELECT</code> statement that has a condition with that returned <code>ID</code>. I want then to take all of the results of that <code>SELECT</code> statement and use them in a <code>JOIN</code> in another <code>SELECT</code> statement and use THOSE results in a 3rd <code>SELECT</code> statement again using <code>JOIN</code>. From what I've seen, I can't use <code>CURSOR</code> in <code>JOIN</code> statements. Some of my co-workers have suggested that I save the results in a <code>CURSOR</code> and then use a loop to iterate through each row and use that data for the next <code>SELECT</code>. However since I'm going to do 2 selects this will create a huge fork of inside loops and that's exactly what I'm trying to avoid.</p>
  
  <p>Another suggestion was to use Temprary Tables to store the data. However this procedure could be executed at the same time by many users and the table's data would conflict with each other. Right now I'm looking at LOCAL Temporary tables that supposedly filter the data according the the session but I'm not really sure I want to create dummy tables for my procedures since I want to avoid leaving trash in the schema (this procedure is for a custom part of the application). Is there a standard way of doing this? Any ideas?</p>
  
  <p>Sample:</p>
  
  <pre><code>DECLARE
  USERID INT := 1000000;
  TEXT1 VARCHAR(100);
  TEXT_INDEX INT;
  CURSOR NODES IS SELECT * FROM NODE_TABLE WHERE DESCRIPTION LIKE TEXT || '%';
  CURSOR USERS IS SELECT * FROM USERGROUPS JOIN NODES ON NODES.ID = USERGROUPS.ID;
  BEGIN
    SELECT TEXT INTO TEXT1 FROM TABLE_1 WHERE ID = USERID;
    TEXT_INDEX = INSTR(TEXT, '-');
    TEXT = SUBSTR(TEXT, 0, TEXT_INDEX);
    OPEN NODES;
    OPEN USERS;
  END;
  </code></pre>
  
  <p>NOTE: This does NOT work. Oracle doesn't support joins between cursors.
  NOTE2: This CAN be done in a single query but for the sake of argument (and in my real use case) I want to break those steps down in a procedure. The sample code is a depiction of what I'm trying to achieve IF joins between cursors worked. But they don't and I'm looking for an alternative.</p>