A cursor is a name for private SQL area.It is in private SQL area the parsed statement and other information for processing the statement are kept.
Execution of a cursor puts the results of the query into a set of rows called the result set, which can be fetched sequentially or non sequentially.
When you write generic cursor you can either use FETCH..,OPEN… and CLOSE cursor statements as give below.
CREATE OR REPLACE PROCEDURE Generic_Cursor IS <pre>CURSOR FETCH_INSERT ISSELECT * from PERSON; new_Rec FETCH_INSERT%ROWTYPE; BEGIN OPEN FETCH_INSERT; LOOP FETCH FETCH_INSERT INTO new_Rec; EXIT when FETCH_INSERT%NOTFOUND; DBMS_OUTPUT.put_line(new_Rec.FIRSTNAME); END LOOP; CLOSE FETCH_INSERT; END Generic_Cursor;
Or you can use the FOR LOOP for iterating through the cursor. When you use FOR LOOP for iteration no need of Explicit use of FETCH..,OPEN… and CLOSE cursor statements.
The cursor will open automatically when entering FOR LOOP and will close the cursor once the loop ends.
Example given below
<pre>CURSOR PERSON_CUR(pAge NUMBER) isSelect * from Person p where p.age = pAge; BEGIN for i in PERSON_CUR(13) loop dbms_output.put_line(i.firstname); end loop; END Param_Cursor;
- Oracle ‘ALTER TABLE’ to ADD columns
- CREATE INDEX as part of CREATE TABLE statement.
- Oracle Tables: Create Table as Select
- Oracle Tables: Create table with foreign key constraint
- ‘CREATE TABLE’ : create table with composite primary key