473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

oracle

1
Explain about cursor in oracle.
Mar 15 '07 #1
2 2144
Dave44
153 100+
Explain about cursor in oracle.
well ill try to show a couple things here but i would read the oracle documentation if i were you :)

there are 2 forms of cursors, explicit and implicit.
explicit is the kind that you declare, cursor my_cur is select * from my_table;
implicit is of the form for rec in (select * from my_table) loop

in both cases the data within the cursor is a snapshot of a certain point and time. what i mean is when the cursor is opened it will contain the data that the query would have returned had you just run it in sqlplus. if your session adds more data to the table queried by the cursor, it will not be in the cursor itself unless that cursor is closed and opened again.

thought of another way, a cursor is a result set in memory, that result set doesnt ever change. if the cursor is closed and opened again, it is a new result set in memory... a new cursor.

Expand|Select|Wrap|Line Numbers
  1. [138]dave@ORADB> CREATE TABLE t (col  NUMBER);
  2.  
  3. Table created.
  4.  
  5. [138]dave@ORADB> INSERT INTO t
  6.   2       SELECT     DBMS_RANDOM.VALUE(0, 20)
  7.   3       FROM       DUAL
  8.   4       CONNECT BY ROWNUM <= 4;
  9.  
  10. 4 rows created.
  11.  
  12. [138]dave@ORADB> DECLARE
  13.   2       CURSOR c_rows
  14.   3       IS
  15.   4            SELECT col
  16.   5            FROM   t;
  17.   6  
  18.   7       var         NUMBER;
  19.   8       v_count     NUMBER := 0;
  20.   9  BEGIN
  21.  10       OPEN c_rows;
  22.  11  
  23.  12       LOOP
  24.  13            FETCH c_rows
  25.  14            INTO  var;
  26.  15  
  27.  16            EXIT WHEN c_rows%NOTFOUND;
  28.  17  
  29.  18            INSERT INTO t
  30.  19                 VALUES (DBMS_RANDOM.VALUE(0, 20) );
  31.  20  
  32.  21            v_count      := v_count + 1;
  33.  22  
  34.  23       END LOOP;
  35.  24  
  36.  25       DBMS_OUTPUT.put_line('initial cursor count is ' || v_count);
  37.  26       DBMS_OUTPUT.put_line('but we inserted a new row for each existing row');
  38.  27  
  39.  28       CLOSE c_rows;
  40.  29  
  41.  30       v_count      := 0;
  42.  31  
  43.  32       OPEN c_rows;
  44.  33  
  45.  34       LOOP
  46.  35            FETCH c_rows
  47.  36            INTO  var;
  48.  37  
  49.  38            EXIT WHEN c_rows%NOTFOUND;
  50.  39            v_count      := v_count + 1;
  51.  40       END LOOP;
  52.  41  
  53.  42       DBMS_OUTPUT.put_line('new cursor count is ' || v_count);
  54.  43  
  55.  44       CLOSE c_rows;
  56.  45  
  57.  46       v_count      := 0;
  58.  47  
  59.  48       FOR rec IN (SELECT col
  60.  49                   FROM   t) LOOP
  61.  50            v_count      := v_count + 1;
  62.  51       END LOOP;
  63.  52  
  64.  53       DBMS_OUTPUT.put_line('and the for loop implicit cursor count is ' || v_count);
  65.  54  END;
  66.  55  /
  67. initial cursor count is 4
  68. but we inserted a new row for each existing row
  69. new cursor count is 8
  70. and the for loop implicit cursor count is 8
  71.  
  72. PL/SQL procedure successfully completed.
  73.  
  74.  
  75.  
Mar 19 '07 #2
debasisdas
8,127 Expert 4TB
Basically a cursor is a pointer to the context area.
or u can say it a recordset/resultset in memory.

following is CURSOR LIFE CYCLE
----------
DECLARE-->OPEN-->FETCH-->CHECK LAST RECORD-->CLOSE

syntax
------
DECLARE
CURSOR CURSORNAME [(PARAM1,PARAM2,....)]
IS SELECT STATMENT [FOR UPDATE [OF COL1,COL2,..];
BEGIN
OPEN CURSORNAME[(INPUT ARGUMENTS)];
LOOP
FETCH CURSORNAME INTO V1,V2,.....;
END LOOP;
CLOSE CURSORNAME;
END;
----CURSOR ATTRIBUTES---
%FOUND-----BOOLEAN
%NOTFOUND-----BOOLEAN
%ISOPEN-----BOOLEAN
%ROWCOUNT------INTEGER

all the cursor attributes are valid within the cursor life cycle
i.e.-once the cursor is closed these are no longer valid.


Hope u get somthing

good luck
Mar 20 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Jan Bols | last post by:
I've been trying to install Oracle 8.1.7 on a fresh Mandrake 9.1 O.S for days, but I'm still not able to get it running. I've tried several install instructions that I found on the internet but no...
4
by: susmita_ganguly | last post by:
Hi I am trying to upgrade from oracle 8i to oracle 9i on the same server ..I don't know much abt migration . Can anyone help me out. Thanks. Susmita
63
by: Nick Palmer | last post by:
Hi all, Is there a DB2 equivilant to Oracle's DB Link functionality ? I have two DB2 databases and I need to get access to the tables in one from the other. In Oracle I would just create a DB...
3
by: Jon Ole Hedne | last post by:
My Access 2002-application need to work with tables from both Oracle and Access. To solve this, I want to run some querys on three views in Oracle and import the results into temporary...
13
by: Chris Botha | last post by:
The machine is running XP Pro with all the latest service packs, etc. I must access an Oracle database so I installed the Oracle client stuff. I can query Oracle from a Windows app, no problem....
2
by: Vinod Sadanandan | last post by:
All, Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features . Precompilers:...
2
by: Ruslan A Dautkhanov | last post by:
Hello ! I'm about to install O9i on FreeBSD box. uname -a: FreeBSD stat2.scn.ru 5.2.1-RELEASE-p3 FreeBSD 5.2.1-RELEASE-p3 #2: Fri Apr 23 19:19:43 KRAST 2004...
0
by: Jack | last post by:
Training Classes for Oracle10g, 9i, 8i Certification training in Oracle10g and 9i: DBA, Developer, Discoverer. training conducted at your location worldwide. Courseware licensing also available....
0
by: Winder | last post by:
Training Classes for Oracle10g, 9i, 8i Certification training in Oracle10g and 9i: DBA, Developer, Discoverer. training conducted at your location worldwide. Courseware licensing also available....
0
by: sathyguy | last post by:
when i type the below in my RHEL AS 4's Firefox 1.5 http://appsworld.ncc.com:7777/forms/...&form=test.fmx iam getting the below error... The requested URL /forms/frmservlet was not found on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.