another help for cursor

  • From: "xiaoyan" <xiaoyezi.xyz@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 27 Jun 2006 21:41:04 +0800

when I make this query in sql*plus:
 
sql> select  describe from categories  where name=courses;
 
I get the following data:
 
NAME                DESCRIBE                                                    
                
------------------------------ 
courses              计算机系                                                   
                          
courses               数学系                                                    
                          
courses               英语系                                                    
                              
courses               物理系                                                    
                         
 
Now in my preject,I need  to use cursor like this:
 
 
CREATE OR REPLACE PROCEDURE test(p_name VARCHAR2)  IS   
 
 CURSOR  categories_cur (p_str VARCHAR2) IS SELECT  describe FROM  categories 
      WHERE  name=p_str ;  
 v_describe categories.describe%TYPE;     
  
BEGIN      
      OPEN categories_cur(p_name);
      LOOP
            FETCH categories_cur INTOv_describe;            
           EXIT WHEN categories_cur%NOTFOUND;
           str:=str ||','||v_name;
           DBMS_OUTPUT.PUT_LINE(v_long_name);
     END LOOP;      
END test;
 
   The procedure 'test' is compiled successfully,
   then I creat a new test script in pl/sql developer, the developing tools 
,but there is no related  result printed!
   when I run the procedure step by step,I found when it come to the line' EXIT 
WHEN categories_cur%NOTFOUND;'
   the next step is line'END LOOP'   which indicate no records in 
categories_cur,who can tell me why?
     
   Thanks in advance!

Other related posts:

  • » another help for cursor