oracle - PL-SQL PROCEDURE issue -

i'm trying simple procedure using oracle db , sql developer:

create or replace procedure test_prod    query_str varchar2(200);    integer := 0;     type cur ref cursor;    my_cur cur;  begin    query_str := 'select * table rownum<=1000';     open my_cur query_str;     dbms_output.put_line('start');     loop       := + 1;       dbms_output.put_line('i: ' || i);    end loop;     dbms_output.put_line('count: ' || i);     close my_cur; end; 

the last value 'i' 3014 (the expected 1 1000) , last 'put_line' not displayed.

moreover, if try put increment in loop , displaying final value, procedure doesn't end.

anyone suggest me problem?

thanks

aleksej has answer use implicit cursor possible--implicit cursors close automatically , succinct , readable. example, implicit cursor recommended way go.

i'll add here additional examples in line etsa's answer alternatives using explicit cursors in original post.

if want stop looping, you'll need exit condition on loop. explicit can exit when counter reaches specific value, or when cursor has been exausted, or other condition like.

example 1 explicit cursor - exiting loop when cursor runs out of data:

create test table:

create table my_table(my_table_data number); 

and load it:

insert my_table select rownum all_objects rownum < 100; 

then create procedure:

create or replace procedure test_prod   query_str varchar2(200);   integer := 0;   type cur ref cursor;   v_my_table_data my_table%rowtype;   my_cur cur;   begin     query_str := 'select * my_table rownum<=5';     open my_cur query_str;     dbms_output.put_line('start');     loop       fetch my_cur v_my_table_data;       exit when my_cur%notfound;        := + 1;       dbms_output.put_line('i: ' || i);        dbms_output.put_line('my-table-data: ' || v_my_table_data.my_table_data);     end loop;     dbms_output.put_line('count: ' || i);     close my_cur;   end; / 

and try it:

begin   test_prod(); end;   / start i: 1 my-table-data: 1 i: 2 my-table-data: 2 i: 3 my-table-data: 3 i: 4 my-table-data: 4 i: 5 my-table-data: 5 count: 5 

or can stop looping based on i:

create or replace procedure test_prod   query_str varchar2(200);   integer := 0;   type cur ref cursor;   v_my_table_data my_table%rowtype;   my_cur cur;   begin     query_str := 'select * my_table rownum<=10';     open my_cur query_str;     dbms_output.put_line('start');     loop       if >= 3         exit;       end if;       fetch my_cur v_my_table_data;       := + 1;       dbms_output.put_line('i: ' || i);       dbms_output.put_line('my-table-data: ' || v_my_table_data.my_table_data);     end loop;     dbms_output.put_line('count: ' || i);     close my_cur;   end; /  begin   test_prod(); end;   /  start i: 1 my-table-data: 661 i: 2 my-table-data: 662 i: 3 my-table-data: 663 count: 3