Re: Nested Cursor

From: John Russell <johnrussell10_at_home.com>
Date: Sat, 21 Jul 2001 21:23:43 GMT
Message-ID: <cr23ktg8mf9aqohcdnidtgdjdm2074f8ln@4ax.com>

Sounds like a cursor expression. The 9i PL/SQL information for that feature is at:

http://tahiti.oracle.com/pls/db901/db901.drilldown?remark=&word=cursor+expression&book=a89856&preference=&method=LIKE

8i only has a little bit of information on this in the SQL Reference, so I presume it was more limited there. (E.g. supported in SQL but not PL/SQL.) Here's the relevant code 8i example:

SELECT d.deptno, CURSOR(SELECT e.empno, CURSOR(SELECT p.projnum,

                                                      p.projname
                                         FROM   projects p 
                                         WHERE  p.empno = e.empno)
                        FROM TABLE(d.employees) e)

  FROM dept d
  WHERE d.dno = 605;

I know that 9i also lets you do this sort of thing:

SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g()))));

In this case, g() is a function that returns a set of rows that get interpreted as a table. This is known as a table function; for information, see:

http://tahiti.oracle.com/pls/db901/db901.drilldown?remark=&word=table+function&book=a89856&preference=&method=LIKE

Table function lets you send large quantities of data from function to function to function without storing the entire result sets of each stage.

I know that 8i has the TABLE() keyword but I haven't used the feature in 8i so I'm not familiar with the capabilities and restrictions there.

John

>Bala <kmbase_at_usa.net> ñîîáùèë â íîâîñòÿõ
>ñëåäóþùåå:7d52c6a5.0106280138.d4f83a5_at_posting.google.com...
>> Hi,
>> Is it possible to have a nested cursor. That is
>> for instance, while declaring the first cursor i
>> will issue a select statement. And while looping
>> this cursor and based on the value of some columns
>> returned by this cursor is it possible to built
>> another cursor.If so,will u give me sample code.
>> Thanks in advance

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/

Received on Sat Jul 21 2001 - 16:23:43 CDT