Q-4

What is cursor?

For Oracle to process a SQL statement, it needs to create an area of memory known as the

context area;

A cursor is a handle, or pointer, to the context area.

Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed.

 

Cursors have two important features:

·         Cursors allow you to fetch and process rows returned by a SELECT statement one row at

a time.

·         A cursor is named so that it can be referenced.

How many types of cursors are available?

There are two types of cursors:

 

·         Oracle automatically declares an implicit cursor every time a SQL statement is executed.

The user is unaware of this and cannot control or process the information in an implicit

cursor.

 

·         The program defines an explicit cursor for any query that returns more than one row of

data. This means that the programmer has declared the cursor within the PL/SQL code

block. This declaration allows the application to sequentially process each row of data as

the cursor returns it.

FORMS60_PATH (REGEDIT) MAX LENGTH IS 255 characters. If it exceeds forms builder automatically close the application.

Implicit cursor

·         Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.

·         A cursor is automatically associated with every DML (data manipulation) statement

(UPDATE, DELETE, INSERT).

·         All UPDATE and DELETE statements have cursors that identify the set of rows that will

be affected by the operation.

·         An INSERT statement needs a place to receive the data that is to be inserted into the database;

the implicit cursor fulfills this need.

·         The most recently opened cursor is called the SQL cursor.

 

The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements.

During the processing of an implicit cursor, Oracle automatically performs the OPEN,

FETCH, and CLOSE operations.

 

EXPLICIT CURSOR

The only means of generating an explicit cursor is to name the cursor in the DECLARE sectionof the PL/SQL block.

The advantage of declaring an explicit cursor over an indirect implicit cursor is that the explicitcursor gives the programmer more programmatic control. Also, implicit cursors are less efficient than explicit cursors, so it is harder to trap data errors.

The process of working with an explicit cursor consists of the following steps:

1. Declaring the cursor. This initializes the cursor into memory.

2. Opening the cursor. The declared cursor is opened, and memory is allotted.

3. Fetching the cursor. The declared and opened cursor can now retrieve data.

4. Closing the cursor. The declared, opened, and fetched cursor must be closed to release the memory allocation.

RECORD TYPES

A record is a composite data structure, which means that it is composed of one or more

Elements.

Records are very much like a row of a database table, but each element of the record

does not stand on its own.

 PL/SQL supports three kinds of records:

·         table-based,

·         cursor-based,

·          Programmer-defined.

A table-based record is one whose structure is drawn from the list of columns in the table.

A cursor-based record is one whose structure matches the elements of a predefined cursor.

To create a table-based or cursor-based record, use the %ROWTYPE attribute:

 

record_name table_name or cursor_name%ROWTYPE

PL/SQL TOPICS

1)      CUSRSOR , CURSOR FOR UPDATE

2)      TRIGGER , MUTATING TRIGGER

3)      FUNCTION, PROCEDURE, PACKAGE

4)      ARRAYS (COOLECTION)

5)      RECORDS

6)      BULK SQL

7)      NATIVE DYNAMIC SQL

8)      EXCEPTION

9)      ORACLE SUPPLIED PACKAGES

10)  REF CURSOR

 

SQL

1)      VIEWS AND MATERIALIZED VIEWS

2)      CORRELATED SUBQUERIES

3)      ANALITICAL FUNCTIONS

4)      ANY , ALL

5)      CUBE , ROLLUP , HIERARICAL QUERIES

6)