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
    Jan 2001
    Posts
    28
    I am not sure for what purpose you are going to use the Cursor but here are a few corrections.

    -- procedure declaration
    create or replace procedure <procedure name> is

    CURSOR cur1 IS
    SELECT ....
    FROM ....
    WHERE ....
    ORDER BY ....

    v_record <table_name>%ROWTYPE;
    /* this creates v_record as a single dimension array of the table_name structure. So you can assign values to the field names which can be accessed as v_record.<field_name> */

    /* Opening cursors through Ref cursors */
    TYPE cur_ref IS REF CURSOR;
    cursor_ref cur_ref

    BEGIN
    -- Opening Cursor
    FOR v_cursor IN cur1 LOOP
    ....
    END LOOP;

    -- Another way to open is if you have a ref cursor
    OPEN cursor_ref FOR
    SELECT ...
    FROM ...
    WHERE ...

    LOOP
    FETCH cursor_ref INTO v_record;
    EXIT WHEN cursor_ref%NOTFOUND;
    END LOOP;

    And in the dnms_output.put_line statement you will have to use the pipe... i mean the syntax would look like
    dbms_output.put_line('Id is '||cursor.value);

    And remember a procedure doesnot return a value unless you have an OUT parameter. I hope I have cleared the problem for you unless I have worsened it.
    - Mayur.

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