help with cursor
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: help with cursor

  1. #1
    Join Date
    Nov 2000
    Posts
    205
    I stink at PL/SQL and need your help badly.

    Does this look right? I just want to check for the rows and display them. I need to put them later in a function. Any help would appreciated?

    declare chldcur cat%row_type

    begin

    curc1 is
    SELECT C.id, c.name
    FROM cat c
    WHERE child_count != (select count(child_Cat_id)
    FROM cat_rel r
    WHERE r.parent_Cat_id=c.id
    GROUP BY parent_Cat_id);

    if curc1%ISOPEN
    then
    close curc1;
    end if;

    open curc1;
    fetch curc1 into chldcur;

    while curc1%FOUND loop
    dbms_output.put_line('id= 'chdcur.id);
    dbms_output.put_line('name= 'chdcur.name);
    fetch curc1 into chldcur;
    end loop

    close cur1
    end;

    or can I just say return curc1 instead of the dbms output lines?

    Also I used cat%rowtype since I saw it in some example.. Is this correct, whct should I define it as?

    Thanks so much,
    Nirasha

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    Hrm all you want to do is print rows if they exist? Can't you just spool the sql query in SQL*Plus?

    But either way, if all you want to do is load a query and print what is inside, this is a little easier.
    <font face="courier">
    declare
    &nbsp;&nbsp;cursor c1 is
    &nbsp;&nbsp;&nbsp;&nbsp;select c.id, c.name
    &nbsp;&nbsp;&nbsp;&nbsp;from catc
    &nbsp;&nbsp;&nbsp;&nbsp;where child_count != (select count(child_Cat_id)
    &nbsp;&nbsp;&nbsp;&nbsp;where r.parent_Cat_id = c.id
    &nbsp;&nbsp;&nbsp;&nbsp;group by parent_Cat_id;
    begin
    &nbsp;&nbsp;for rec in c1 loop
    &nbsp;&nbsp;&nbsp;&nbsp;dbms_output.put_line ('id = ' || rec.id);
    &nbsp;&nbsp;&nbsp;&nbsp;dbms_output.put_line ('name = ' || rec.name);
    &nbsp;&nbsp;end loop;
    end;

Posting Permissions

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


Click Here to Expand Forum to Full Width