ziploc1010
Programmer
What is the difference between PL-SQL Cursors and Tables? I'd like to use a PL-SQL procedure that returns the result from a couple of queries. However, it is not working w/ using an out variable of ref cursor type.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
PL-SQL Cursors v. Tables
- Thread starter ziploc1010
- Start date
- Status
What you want to do is perfectly possible. A cursor is simply a program's pointer into the results of a SELECT. SELECT returns a result set from one or more tables, but most 3GL programming languages, including PL/SQL, can only work with one row at a time. So a cursor points at that row within that set.
You can perform three operations with a cursor, OPEN it, which executes the SELECT and sets the pointer before the first row, FETCH, which retrieves the next row, and CLOSE, which releases the cursor. A FOR LOOP with a SELECT or declared CURSOR is simply a shorthand for an OPEN, followed by a LOOP that FETCHes each row, followed by a CLOSE. A REF CURSOR is a variable that lets you use different selects with the same cursor name, and lets you pass a cursor as an argument. But it still must be OPENed, FETCHed, and CLOSEd. Until you OPEN it, you cannot FETCH, and once you CLOSE it, you must OPEN it again before you can FETCH.
You can perform three operations with a cursor, OPEN it, which executes the SELECT and sets the pointer before the first row, FETCH, which retrieves the next row, and CLOSE, which releases the cursor. A FOR LOOP with a SELECT or declared CURSOR is simply a shorthand for an OPEN, followed by a LOOP that FETCHes each row, followed by a CLOSE. A REF CURSOR is a variable that lets you use different selects with the same cursor name, and lets you pass a cursor as an argument. But it still must be OPENed, FETCHed, and CLOSEd. Until you OPEN it, you cannot FETCH, and once you CLOSE it, you must OPEN it again before you can FETCH.
- Status