Join a cursor or record set in oracle

I have good experience in sybase and have started looking into oracle in free time. Most of the sybase procedures that i have worked with have temp tables and it makes sense to join two or more temp tables get a result set.

Question: Is there a way to join two or more cursors like a logical table.

Somethi开发者_C百科ng like:

SELECT c1.id, 
       c2.name 
  FROM cursorEmp c1, 
       CursorDept c2 
 WHERE c1.DeptId = c2.DeptId

You cannot join two cursors, no.

You could, of course, combine the two underlying queries, i.e.

SELECT c1.id,
       c2.name
  FROM (SELECT * FROM emp WHERE ename = 'KING') c1,
       (SELECT * FROM dept WHERE dname = 'ACCOUNTING') c2
 WHERE c1.DeptID = c2.DeptID

In Oracle, since readers do not block writers (and vice versa), it is very rarely necessary to use temporary tables. You would normally just query the underlying tables using views as appropriate to provide appropriate levels of abstraction.

Oracle does have its own version of temporary tables (permanent structures with temporary data stored for the duration of either a transaction or a session) - you could consider using those, although (as Justin suggested) you could also combine the two underlying queries.