Lesson 10

You can now write cursors within a PL/SQL block. You have learned about implicit and explicit cursors that help you build the program logic. In this module, you also discovered the different attributes of a cursor. In this module, you learned how to:

1. Define a cursor
2. Review implicit and explicit cursors
3. Define the basic structure of a cursor block
4. Build a cursor block
5. Use a cursor FOR loop to process records

Implicit and explicit cursors in PL/SQL are tools that help developers interact with the database by fetching data from it in a controlled manner. Understanding the difference between the two and how they can be used is essential for building efficient and effective program logic.

An implicit cursor is automatically created by Oracle when an SQL DML statement (INSERT, UPDATE, DELETE, SELECT INTO) is executed. The developer doesn't need to write code to declare, open, fetch, or close an implicit cursor. Oracle manages all these operations behind the scenes.
Here's how implicit cursors help in PL/SQL programming:

• Simplicity: Implicit cursors are simpler to use because they require less code. This can make the program easier to write and read, especially when the operation is straightforward and only involves a single row.
• Performance: Implicit cursors are generally faster for single-row queries since they are optimized by Oracle.
• Automatic Error Handling: You can use SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, and SQL%ISOPEN to check the outcome of the last executed DML statement without the need for explicit cursor attribute checks.
• Less Resource Intensive: Since implicit cursors are managed by Oracle and are not left open, they tend to be less resource-intensive.

Explicit cursors are declared by the programmer and provide more control over the context of data retrieval. They are essential when you need to process multi-row queries.
Benefits of explicit cursors include:

• Control: They give the programmer control over when and how many rows are fetched, which is important for memory management and performance.
• Complex Data Retrieval: When the logic requires complex multi-row operations, explicit cursors can be used to fetch each row and apply specific logic, such as calculations or conditional processing.
• Reusability: Explicit cursors can be parameterized and reused within the code, which enhances modularity and reduces redundancy.
• Readability and Maintenance: They can make the code more readable and maintainable, especially when dealing with complex operations that require careful row-by-row processing.

• Conditional Logic: Cursors can be used in conjunction with conditional statements (IF-THEN-ELSE, CASE) to apply specific business logic to each row of data retrieved from the database.
• Loops: Cursors are often used within loops (FOR, WHILE) to process a result set row by row, allowing complex logic to be applied to each row in turn.
• Exception Handling: Explicit cursors are often used with exception handling to manage and respond to SQL errors during the fetch cycle.
• Transaction Control: Cursors allow developers to control transactions by committing or rolling back changes based on the data processed or the occurrence of exceptions.

In summary, implicit cursors are best for simple, single-row DML operations, where the overhead of managing a cursor manually isn't justified. Explicit cursors are ideal for more complex scenarios where you need to process multiple rows or have greater control over the cursor's lifecycle and the processing of its data. Using both types of cursors effectively allows a PL/SQL developer to build robust, efficient, and maintainable database applications.

In this module, you were introduced to the following glossary terms:

1. Context area:Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement. For example, the number of rows processed. A cursor is a pointer to this context area. The set of rows the cursor holds is referred to as the active set.
2. Cursor: A cursor is a server-side mechanism by which you can name that context area and manipulate information within it. The SQL in a cursor can be static or dynamic.
3. Data warehouse:In computing, a data warehouse , also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis, and is considered a core component of business intelligence.
4. Exception: The term exception means exceptional condition and is an occurrence that alters the normal program flow. Many factors can lead to exceptions.
5. Legacy System
6. (PGA) program global area: The PGA is a memory buffer that contains data, process information, session information, and cursors for a server process.
7. Package: A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.
8. Virtual table: A virtual table is an object that presents an SQL table interface but which is not stored in the database file, at least not directly.

In the next module, you will learn how to handle exceptions.

Click the Quiz link below to take a multiple-choice quiz about the material we covered in this module.
Fetch Statement - Quiz