Re: PL/SQL do processing on output cursor ?

On Sun, 8 Dec 2002 21:49:08 +0800, "Ka" <jackhui_at_hotmail.com> wrote:

>I create the following Stored Procedure, but it can't compile. I want to
>return a cursor
>from a "select", but after select, I want to update the fields too. Can I do
>it ??
>
> PROCEDURE GetTokenControlList(p_cursor OUT refcur) IS
> new_counter NUMBER;
> BEGIN
> OPEN p_cursor FOR
> SELECT TOK_KEY, TOK_COUNTER, TOK_BLOCKSIZE FROM TIF_TOKENCONTROL;
>
> FOR tok_rec in p_cursor LOOP
> new_counter := tok_rec.TOK_COUNTER + tok_rec.TOK_BLOCKSIZE;
> UPDATE TIF_TOKENCONTROL SET TOK_COUNTER = new_counter
> WHERE TOK_KEY = tok_rec.TOK_KEY;
> END LOOP;
>
> END GetTokenControlList;
>

Define 'can't compile', specify your version and don't have everyone fetch their crystall ball (which has worn out) You don't need to return a cursor, because you can easily reselect records. This won't constitute a performance issue, and least not more of a performance issue than using the above mechanism instead of sequences.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Dec 09 2002 - 01:49:15 CST