Question

1. Describe when to use cursor:

•     implicit cursor
•      explicit  cursor

2. Please give one example for each of below statement:

•    TCL -- Transaction Control statement
•    DML -- Data Manipulation statement
•    Implicit Cursor Attributes --  PL/SQL statement

Answer 1

1.

A cursor is a pointer to a context area, which is memory created when an SQL statement is processed.

Implicit cursors Usage:

They’re used to check the DML operations status, whether the row is returned, etc.

They’re generated when a statement is used

They’re mainly used for the DML statements

Attributes such as %found ,%notfound, %rowcount, %isopen, %bulk_rowcount, %bulk_exceptions are used

They’re used in pl/sql procedures and are checked for the conditions, or used in procedural statements

They’re used for select into statements as well

These are predefined

It’s used when we’re working on one row

Explicit cursors Usage:

It’s used explicitly by the programmers

Declaring, opening, fetching, and closing are the steps followed to work with explicit cursor

Used to retrieve data, process statements set at one time

Used when working on many rows

2.

TCL – Transaction Control Statement

It manages the transactions in database.

Commit :

Used as

Commit;

It marks the permanent changes.

Rollback:

Used as

Rollback To name;

It either restores to the last state which is commited or jumps to a savepoint.

Savepoint:

Used as

SAVEPOINT name;

It temporarily stores a transaction

DML – Data Manipulation Language

Used for data manipulation and retrieval. It modifies the database.

Insert:

Used as

Insert into table_name..

This is an sql query which inserts data into the row of a table.

Update:

It’s used to modify or update a column value

Delete:

It’s used to delete one or more rows from the table

Used as

Delete from name…

Merge:

It’s used to merge tables

Implicit cursors attributes:

1. %found

It returns true when DML statement affects one or more rows or select into returns. Or else it returns false

2. %notfound

It’s the opposite of %found, returns true if DML doesn’t affect any rows, false otherwise.

3. %isopen

Returns false

4. %rowcount

Returns the number of rows affected by DML statements or returned by select into

comment for any doubts!