OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » CURSORS

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CURSORS [message #205221]

Thu, 23 November 2006 23:43

cutsmartprem
Messages: 62
Registered: November 2006

Member

I have seen this example in a book

CURSOR EMP_CUR IS
SELECT EMP_NAME,EMP_SAL FROM EMP_DET WHERE EMP_ID=1;

It is used in a explicit cursor. What is the need to fetch a single row using explicit cursor, the function or a procedure can imply the logic.

Also is it possible to move through the rows in the cursor without using a loop.

Thanks in advance

Report message to a moderator

Re: CURSORS [message #205236 is a reply to message #205221]

Fri, 24 November 2006 00:46

moparthy99
Messages: 13
Registered: July 2006

Junior Member

Using the bulk collect into option to all rows from cursor with out any loop.for that we need define either index by tables or varrays.

If ur writing any pl/sql script means we reduced context swithes
reduced network traffic.if it is sql stmt it always hit DB.Only
performance issue

Report message to a moderator

Re: CURSORS [message #205243 is a reply to message #205236]

Fri, 24 November 2006 00:55

vgs2005
Messages: 123
Registered: April 2005

Senior Member

CURSOR EMP_CUR IS
SELECT EMP_NAME,EMP_SAL FROM EMP_DET WHERE EMP_ID=1;

Quote:

What is the need to fetch a single row using explicit cursor

That is not really a common practice. But the code you see does not imply that it will return only 1 row or emp_id is a primary key. The table is EMP_DET not EMPLOYEE.

Report message to a moderator

Re: CURSORS [message #205251 is a reply to message #205236]

Fri, 24 November 2006 01:12

cutsmartprem
Messages: 62
Registered: November 2006

Member

ya thanks for tat explanation

can u tel me how to spool the output of a query..

explain with example and tel the use of it

Report message to a moderator

Re: CURSORS [message #205252 is a reply to message #205243]

Fri, 24 November 2006 01:13

cutsmartprem
Messages: 62
Registered: November 2006

Member

this will get only one row because emp_id is a primary of the

table emp_det;

thanks for that explanation

Report message to a moderator

Re: CURSORS [message #205277 is a reply to message #205252]

Fri, 24 November 2006 02:39

vgs2005
Messages: 123
Registered: April 2005

Senior Member

Quote:

this will get only one row because emp_id is a primary of the

hmm.. frankly, I don't see any use putting it in a cursor. Simplest thing is to use INTO. To 'spool' (as in SQL term), you can try the 'SPOOL' command:

SQL>  spool my_log
SQL>  select * from Parent;

NAME                        AGE ADDRESS
-------------------- ---------- ----------
A                            19 here
B                            19 there
C                            19 near
D                            19 far

SQL> spool off;

To see the spool file: