plsql - Is it advantageous using cursor in Oracle for frontend applications? -

plsql - Is it advantageous using cursor in Oracle for frontend applications? -

i have 2 tables in database. each table having 2000 records. have big number of records write optimal code retrieving records. user_details table is:

+---------+-----------+-----------+ | user_id | user_name | join_date | +---------+-----------+-----------+

the sec table refering user_details table is:

+---------+-----------+-----------+ | user_id | fav_color | fav_dish | +---------+-----------+-----------+

i have 2 approaches first one:

select ud.*,fav.fav_color, fav.fav_dish user_details ud, fav_details fav ud.user_id = fav.user_id;

second approach writing pl/sql procedure is:

declare cursor c1(x number) select * user_details user_id = x; cursor c2 select * user_fav; y number := &user_id; begin c in c1(y) loop dbms_output.put_line('user details'); dbms_output.put_line('----------------'); d in c2 loop if c.deptno = d.deptno dbms_output.put_line(rpad(c.user_id,10)||rpad(c.user_name,10)||rpad(d.fav_color,10)); end if; end loop; end loop; end;

which code give improve performance , why? want finish details of user.

if using cursor records form server sga? utilize database in jsp page accessed mobile devices only.

as net in mobile device slow of target users (around 10kb) hence concerned bandwidth. in point of view find performing bring together cartesian product , check matching result take 1 status out of 1000*1000 conditions checking conditions in pl/sql block 1000 + 1000. reduces number of conditions. per knowledge cursor create shadow page in client memory , create table. means fetch info form server , store in client. right @ point?

you can read here tom kyte's mantra:

you should in single sql statement if @ possible. if cannot in single sql statement, in pl/sql. if cannot in pl/sql, seek java stored procedure. if cannot in java, in c external procedure. if cannot in c external routine, might want think why need it…

basically using plsql stored procedure move sql engine plsql engine , forward. more that, if have right indexes , build query right, sql optimizer create things faster you.

here asktom post

oracle plsql cursor