Sql Question and answer 5

PL-SQL QUESTIONS

If you know pl-sql read it further otherwise better stop with sql alone and move to java at the bottom of the page (if you are a java guy), that should be sufficient.



1. What is the purpose of a cluster.


Oracle does not allow a user to specifically locate tables, since that is a part of the
function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing
data from different tables together for faster retrieval than if the table placement were
left to the RDBMS.


2. What is a cursor.

Oracle uses work area to execute SQL statements and store processing information
PL/SQL construct called a cursor lets you name a work area and access its stored
information A cursor is a mechanism used to fetch more than one row in a Pl/SQl
block.

3. Difference between an implicit & an explicit cursor.

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including
quries that return only one row. However,queries that return more than one row you
must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT
statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE,
DELETE and single row SELECT. .INTO statements.


4. What are cursor attributes

%ROWCOUNT
%NOTFOUND
%FOUND
%ISOPEN

5. What is a cursor for loop.

Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.

6. Difference between NO DATA FOUND and %NOTFOUND

NO DATA FOUND is an exception raised only for the SELECT....INTO statements
when the where clause of the querydoes not match any rows. When the where clause
of the explicit cursor does not match any rows the %NOTFOUND attribute is set to
TRUE instead.