|
|
|
|
| Re: cursor logic [message #648957 is a reply to message #648954] |
Tue, 08 March 2016 13:33   |
 |
M123
Messages: 47
Registered: February 2016
Location: USA
|
Member |
|
|
as mentioned in sql processing "When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the PGA".
https://docs.oracle.com/database/121/CNCPT/sqllangu.htm#CNCPT1736
so for every sql program there is new cursor created in the PGA. then what is the use of the creating new cursor ?
[Updated on: Tue, 08 March 2016 13:44] Report message to a moderator |
|
|
|
|
|
| Re: cursor logic [message #648959 is a reply to message #648958] |
Tue, 08 March 2016 14:25 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
A cursor is only a pointer: it doesn't contain any data. It just points to where the data is.
Incidentally, terminology: try to avoid saying "record" when you mean "row". Also, "RAM" is not a useful term in the Oracle environment. Think of SGA and PGA: memory shared by all sessions, or private to one session. Your cursor is private in PGA, but points to information that is shared in SGA. What we call a "shared cursor" isn't shared at all - it is many cursors that all point to the same place.
|
|
|
|
| Re: cursor logic [message #648962 is a reply to message #648953] |
Tue, 08 March 2016 15:24 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
M123 wrote on Tue, 08 March 2016 09:50Hi 8)
I am trying to understand the concept of curosr.
When we declare cursor c1 is select empno,ename from emp and send values into v_empno and v_ename,
it creates a private area in RAM, so thats why it is fast when fetching , where the values for 2 new variables are stored,
and also it stores multiple records, where as in normal case only one record is processed at time .
is my understanding correct on this part ?
What do you consider "normal case"? If you are trying to compare explicit cursors to implicit cursors, then implicit cursors are slightly faster and less code. Please provide an example of what you mean by "normal case".
|
|
|
|
|
|
| Re: cursor logic [message #648964 is a reply to message #648963] |
Tue, 08 March 2016 18:33 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your example of "normal case" bears little relation to your prior example. In the following examples, the first uses an explicit cursor as you initially described and the second uses an implicit cursor, which is usually faster. Neither of these does anything with the stored variables and each value overwrites the previous one, but is just doing what you seemed to be describing. In general, if you can do something in SQL without PL/SQL then that is faster. If not, then implicit cursors are usually faster than explicit cursors. There are also things like bulk collect for faster processing of large quantities of data and ref cursors and pipelined table functions for returning data sets. When in doubt, test and see. With small amounts of data you may not see any noticeable difference.
-- explicit cursor example:
SCOTT@orcl> declare
2 -- declaration of explicit cursor:
3 cursor c1 is select empno,ename from emp;
4 -- variables:
5 v_empno emp.empno%type;
6 v_ename emp.ename%type;
7 begin
8 -- open explicit cursor:
9 open c1;
10 -- loop through explicit cursor, fetching values into variables, then exiting:
11 loop
12 fetch c1 into v_empno, v_ename;
13 exit when c1%notfound;
14 end loop;
15 -- close explicit cursor:
16 close c1;
17 end;
18 /
PL/SQL procedure successfully completed.
-- implicit cursor examples:
SCOTT@orcl> declare
2 -- variables:
3 v_empno emp.empno%type;
4 v_ename emp.ename%type;
5 begin
6 -- loop through implicit cursor,
7 -- assigning values to variables,
8 -- without having to explicitly open, exit, and close:
9 for r1 in (select empno,ename from emp)
10 loop
11 v_empno := r1.empno;
12 v_ename := r1.ename;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
