-
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
-
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 is
CURSOR cur1 IS
SELECT ....
FROM ....
WHERE ....
ORDER BY ....
v_record %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. */
/* 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.
