Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    13

    Unanswered: Problems using Oracle Cursor

    I have defined a cursor in PL/SQL which selects the sum of two values. My question is how do I reference the field in my SQL program ? I tried using an alias but it didn't like it.

    Thanks in advance to all who reply !

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: Problems using Oracle Cursor

    Here's an example :

    procedure myproc is
    cursor curs_val is
    select segm_code
    from segment
    where segm_type = 'PVC'
    and xnat_code = 1
    and prcl_code = 1620;

    cpt number;
    compteur varchar2(8);
    v_curs_val curs_val%rowtype;

    begin
    select xnat_vallast into cpt
    from xnature
    where xnat_code = 11;

    open curs_val;
    loop
    fetch curs_val into v_curs_val;
    exit when curs_val%notfound;
    cpt := cpt + 1;
    compteur := to_char(cpt);
    update segment
    set segm_nom = 'CNGS_'||compteur, xnat_code = 11
    where segm_code = v_curs_val.segm_code;
    end loop;
    close curs_val;


    END myproc;
    /

    Hope this help you

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I'm not sure if this is what you are looking for:

    Code:
    PROCEDURE my_sum
    IS
       CURSOR date_cur IS
         select sum( month ) m, sum( week ) w from date_summary;
    	    
    BEGIN
    
       FOR date_rec IN date_cur LOOP
          dbms_output.put_line( date_rec.m );
       END LOOP;
       
    END;
    However, there is an easier way:

    Code:
    BEGIN
       ...
       SELECT sum( month ) INTO my_var FROM date_summary;
       ...
    END;
    JoeB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •