Cursors and Joins in Oracle

Cursors and Joins in Oracle

1.What is a cursor?

A.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.

2.How many types are availble?

A.They are two types of cursors.
    1. Implicit cursor
    2. Explicit cursor

3.Implicit cursor

A.All the activities related to cursors like opening the cursor, processing the cursor and closing the cursor are done automatically. Hence these cursor are called as implicit cursor

4.Explicit cursor

A.All the activities related to cursor like.
1.Opening the cursor
2.Processing the cursor
3.Closing the cursor

Act should be done by the developer. Hence this cursor is called explicit cursors.
We should use explicit cursors to run a select statement.

5.What are the attributes of  implicit cursors?

A.Implicit cursor attributes: They are 4 implicit cursor attributes.

i.SQL%ISOPEN
ii.SQL%FOUND
iii.SQL%NOTFOUND
iv.SQL%ROWCOUNT

6.Difference between an implicit & an explicit cursor.?

A.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.

7.What are the steps to write a explicit cursor?

A.Steps to use explicit cursors:
Step 1: declare the cursor
Step 2: open the cursor
Step 3: fetch data from cursor to local variables
Step 4: close the cursor

8.Attributes of explicit cursor?

A.EXPLICIT CURSOR ATTRIBUTES: These are four explicit cursor attributes.
1. %ISOPEN
2. %FOUND
3. %NORFOUND
4. %ROWCOUNT

9.What are various joins used while writing SUBQUERIES?

A.Self join-Its a join foreign key of a table references the same table. Outer Join--Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.
Equi-join--Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

10.What is a OUTER JOIN?

A.Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don?t satisfy the join condition.

11.Inner join

A.An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

0 comments:

Post a Comment