Hi,

At the moment I am trying to grasp PL/SQL,
and I've read a few PL/SQL tutorials online -

Now I've got a few questions, I really didn't understand...

What I think I did grasp:

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?

thx,

Bernd

"Bernd Eichelsdorf" <gandor (AT) uni (DOT) de> wrote

Think of row by row as slow by slow. That said there are times when you
have to. If you want to process a billion row table you probably don't want
to make a pl/sql table out of it. It would create a huge in memory table
and probably cause a lot of swapping or exhaust RAM on the server. Think of
a pl/sql table as an array. There are times when you use arrays and time
you read from a file. These principals are SIMILAR (a table is NOT a file)
to when you use a pl/sql table and when you would use a cursor.
Jim

Bernd, to add to what Jim said technically every SQL statement results
in a cursor. But what you are obviously asking about are known as
explicit cursors, that is, a cursors explicitly defined in code. You
use an explicit cursor when you need access to individual rows in the
cursor such as when you need to insert a set of rows into a target
destination but some of the rows will error off and rather than treat
the entire operation as single all or nothing transaction you want the
valid rows to still be inserted while recording information about the
failed rows.

With versions 9 and 10 you can use bulk load operations to perform the
operation for the valid rows while capturing the errors for the bad or
non-qualifying rows. Prior to 9 you did not have this ability so you
used a cursor.

Explict cursors are useful where you need to perform conditional
processing on the rows treating rows differently based on complex rules
that you cannot easily or efficiently code into the SQL statement.

Never use an explicit curosr to do what you can do in a single SQL
statement.

HTH -- Mark D Powell --

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