Re: PL/SQL Question

Arthur Merar wrote

• Enable output. You might also need to enter
• set serveroutput on
• in SQL*Plus dbms_output.enable; open c1; loop fetch c1 into var_itemnum, var_closedate, var_ponum, var_vendorid, var_acctcode, var_costcenter;
  • jump to "A" below when no more data (or no
  • data at all) is found: exit when c1%NOTFOUND;
  • for each var_itemnum etc found in c1 fetch data
  • from c2. Could be any number of rows, so loop
  • over the result: open c2(var_itemnum, var_closedate, var_ponum, var_vendorid); loop fetch c2 into v_acctcode, v_costcenter;
    • jump to "B" below when no more data (or no
    • data at all) is found: exit when c2%NOTFOUND; -- jumps to "B"
    • otherwise, do something with the data here: dbms_output.put_line ('found: ' || var_itemnum || ', ' || v_costcenter);

    end loop;

• "B" close c2; end loop; -- "A" close c1;

>I do not understand what the parameters are for when the c2 cursor is
>defined.

Your example first loops over cursor c1. Each returned row of that cursor is used as input for cursor c2. So, you need parameters in c2.

>Also, I do not understand the parameters when the c2 cursor
>is opened.

I guess you do not understand the %type part? It is a good way to copy the declaration of of a table. Say, I have a column defined as number(2) and change that to a number(4,2) some time later. When using tablename.columnname%type I do not have to change all variables in my code that are supposed to store data of this column. Their definitions will be exactly as the table definition.

>Please respond via e-mail.

I don't see why. Would you ever you feel replies are missing, then check out http://www.dejanews.com

Arjan. Received on Sun Feb 28 1999 - 02:01:33 CST