Re: PL/SQL

> Every SELECT query is stored internally in memory.
> A cursor is a pointer on it and you can use it to
> access each record of this "virtual table".
>
> 1. Is that correct?
> 2. When exactly do I need a cursor, and when don't I?
>
> Without a cursor, you have to use a select that returns exactly one
> row. But what if I put this SELECT statment into a loop and
> count up a variable for a variable in a WHERE condition of this
> statement - or what if I use a limit with a variable I count up?
> Wouldn't that do exactly the same?
>
> 3. What for do I need a PL/SQL table, and why does it only support
> one column - but on the other hand you can store a record in such a
> PL/SQL Table column, and so you get a full table again...
>
> 4. Where's the difference between a PL/SQL table and a cursor?
>

1. More or less. In a nutshell, cursor is a pointer to an area in memory where Oracle stores current row fetched by the query. So when you FETCH from a cursor, you cause Oracle to load new row into that memory area. Note that you can't freely move around the cursor, you can only fetch from it in one direction (you can get next one or several rows, but not previous.)
2. You use explicit cursors when you want to make your code more readable, especially for complex queries, or when you need to issue the same query several times with different arguments. There are other cases when they can be used, but these two in my experience are the most often.
3. PL/SQL table is analogous to an array in other languages (in fact, since 9i they are called associative arrays, which better describes what they are.) These arrays are stored entirely in memory and have single column by definition. However, by using records or object types and arrays of arrays you can have multicolumn multidimensional arrays since 9i. These arrays can be sparse (that is, have 'holes') and you can access their elements in random order. Since arrays are variables, you can assign them (unlike cursors, which are not variables.)
4. I believe my answers 1) and 3) described the difference.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Mon May 22 2006 - 00:40:15 CDT