Re: select inside a cursor problem

These codes do not look complete. You are missing a ';' here
> where uqid=i.emplid

In any case, try to improve your logic by removing the double select. Even better use bulk bind. Here is a comparison between the two:

SQL> create table t1 as select object_id, object_name, object_type from dba_objects;

Table created.

SQL> insert into t1 select * from t1 where rownum <11;

10 rows created.

SQL> delete from t1 where object_id is null;

6 rows deleted.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> select count(1) from t1;

  COUNT(1)

     30769

SQL> select count(distinct object_id) from t1;

COUNT(DISTINCTOBJECT_ID)

                   30759

• This is you logic right now SQL> set timing on SQL> declare 2 cursor c1 is select object_id from dba_objects; 3 cnt number; 4 oname varchar2(30); 5 icount number:=0; 6 begin 7 for i in c1 loop 8 select count(1) into cnt from t1 where t1.object_id=i.object_id; 9 if cnt=1 then 10 select object_name into oname from t1 where t1.object_id=i.object_id; 11 icount:=icount+1; 12 end if; 13 end loop; 14 dbms_output.put_line('number of objects without duplicates: '||icount); 15 end; 16 / number of objects without duplicates: 30749

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.01

• Use collection with bulk bind SQL> declare 2 cnt num_tab_type:=num_tab_type(); 3 icount number; 4 begin 5 select count(1) bulk collect into cnt from t1 where t1.object_id in 6 (select object_id from dba_objects) group by object_id; 7 select count(1) into icount from table(cast(cnt as num_tab_type)) t where t.column_value=1; 8 dbms_output.put_line('number of objects without duplicates: '||icount); 9 end; 10 / number of objects without duplicates: 30749

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
SQL> The response time went from 12.01 to just 0.06 seconds. Whenever you are doing loops, think bulk.

• Jusung Yang

markbathie_at_go.com (Mark Bathie) wrote in message news:<e55d17df.0305132146.3c59e7e4_at_posting.google.com>...
> Hi,
>
> I am trying to execute some PL/SQL. The basic idea is I have a cursor
> and wish to iterate through each row using a for loop. Within that
> loop I'm simply selecting a value from another table using "select
> into". The problem is that it should be returning 40,000odd rows,
> instead it stops at 350. I just can figure out why is stops way short
> of 40,000. Any ideas.
>
> create or replace procedure TEST
> is
> v_id people.id%TYPE;
> cursor c_select is
> select *
> from vw v
> where emplid > 34400000
> order by emplid;
>
> v_id_cnt integer;
> v_rowcnt integer := 0;
> BEGIN
> for i in c_select loop
> v_id_cnt := 0;
> select count(*) into v_id_cnt from people where uqid=i.emplid;
> if v_id_cnt = 1 then
> select id
> into v_id
> from people
> where uqid=i.emplid
> write_log('TESTING',v_id||' - '||v_id_cnt);
> end if;
> end loop;
> END;
>
> It should be calling the write_log procedure 40,000 times, but stops
> at 350 !
>
> Cheers,
>
> Mark.
Received on Wed May 14 2003 - 11:21:48 CDT