Understanding Theory Concepts of Cursors, Indexes, and Views in Oracle(SQL/PLSQL)

Understanding Theory Concepts of Cursors, Indexes, and Views in Oracle(SQL/PLSQL):

Cursors in Oracle (PL/SQL):

1. Cursors process data row-by-row, useful for complex calculations.

2. PL/SQL cursors are supported by DML operations.

3. Control structures like if, else, while, and for can be used.

4. Multiple rows held by a cursor are called the ResultSet.

5. Types: a. Implicit cursors. b. Explicit cursors.

6. Explicit Cursor Attributes: a. %NOTFOUND b. %FOUND c. %ISOPEN d. %ROWCOUNT

7. Drawback: Cursors are time-consuming, affecting performance due to row-by-row fetching.

Indexes in Oracle (SQL):

1. An index is a database object used to speed up data retrieval.

2. Types of indexes:

a. B-Tree Indexes: Used for columns with a wide range of values.

b. Bitmap Indexes: Used for columns with few distinct values.

Views in Oracle (SQL):

1. A view is similar to a table but does not store data. It is a virtual table created using a SELECT statement.

2. Views can restrict access to specific rows or columns and provide security, reducing database hits and improving performance.

3. Types of views:

a. Simple View: Based on a single table.

b. Composite View: Uses multiple tables with joins.

c. Force View: Created without a table.

d. Materialized View: Stores query results for performance improvement.

e. Inline View: A subquery used within a larger query.

4. Views can be updatable or read-only. Updatable views allow DML operations, while read-only views restrict them.