Lesson 5	Structure of a Cursor Block
Objective	Define the basic Structure of a Cursor Block

Structure of a PL/SQL Cursor Block

So far, you have learned about the different types of cursors, their advantages, and their disadvantages. An explicit cursor block has four basic PL/SQL sections that are necessary for processing data selected within the cursor:

Define a cursor
Open the cursor
Fetch the records into variables for processing
Close the cursor

Define Cursor in PL/SQL

The correct syntax for declaring an explicit cursor in Oracle PL/SQL is:

CURSOR cursor_name [(parameter_1 [,parameter_2, ...])]
   IS SELECT select_clause;

The "IS" keyword is mandatory in Oracle's "explicit cursor syntax" to define the cursor's scope and the query it represents.
Cursor Declaration:

CURSOR <cursor_name> [([parameter_1 [,parameter_2])]
   IS 
SELECT <select_clause>;

Key elements of the Declaration:

cursor_name: The name you assign to the cursor, used to refer to it later in your PL/SQL code.
(parameter_1 [,parameter_2, ...]): Optional parameters for the cursor, used to pass values into the query dynamically.
IS: Keyword indicating the start of the cursor's scope.
SELECT select_clause: The SQL query that defines the set of rows the cursor will fetch.

Additional syntax notes:
The `RETURN return_specification` clause is optional and allows for explicit declaration of the data types for the columns returned by the cursor.

Error in PL/SQL Cursor Declaration

Example: The following cursor declaration

CURSOR pets is 
 SELECT PRODUCT_NAME, CURRENT_INVENTORY_COUNT
 FROM PRODUCT WHERE PRODUCT_NAME LIST 'Fish%';

contains an error.
The `LIST` function is not suitable for string comparisons in Oracle. It's typically used with 1) collections or 2) for creating dynamic SQL statements. Here are two ways to fix the cursor declaration to filter for products containing "Fish":

Option 1: Using LIKE operator:
```
CURSOR pets IS
  SELECT PRODUCT_NAME, CURRENT_INVENTORY_COUNT
  FROM PRODUCT
  WHERE PRODUCT_NAME LIKE 'Fish%';
```
This approach uses the `LIKE` operator with a wildcard character "%". This will find products where the name starts with "Fish" followed by any characters.
Option 2: Using IN operator with a subquery:
```
CURSOR pets IS
  SELECT PRODUCT_NAME, CURRENT_INVENTORY_COUNT
  FROM PRODUCT
  WHERE PRODUCT_NAME IN (
    SELECT PRODUCT_NAME
    FROM PRODUCT
    WHERE PRODUCT_NAME LIKE 'Fish%'
  );
```
This approach uses a subquery to find all products containing "Fish" and then uses the `IN` operator to filter the main query based on the results of the subquery.

Both options achieve the same outcome of filtering for products containing "Fish" in the `PRODUCT_NAME` column. Choose the approach that best suits your specific needs and coding style.

SELECT PRODUCT_NAME, CURRENT_INVENTORY_COUNT
FROM PRODUCT WHERE PRODUCT_NAME LIKE 'Fish%'

You can pass parameters using the optional parameter list. When a cursor is defined, Oracle allocates memory for the cursor. The name of an explicit cursor is not a PL/SQL variable; instead, it's an "undeclared identifier" that identifies a query.

You 1) cannot assign values to a cursor name and 2) can use a cursor name within an expression within a specific context.

Cannot assign values to a cursor name: You cannot directly assign values to a cursor name like a variable. Cursors are not variables themselves; they act as pointers to query results.
Can use a cursor name within an expression: You can use a cursor name within an expression, but in a specific context. You cannot use it like a numerical value or a string variable in calculations. However, you can use it within control flow statements like `FOR` loops specifically designed to iterate through the rows retrieved by the cursor.

Here's a breakdown of how cursors are used in expressions:
Cursor with `FOR` loop:

CURSOR emp_cursor IS
 SELECT employee_id, salary
 FROM employees;

BEGIN
 FOR emp_rec IN emp_cursor LOOP
   -- Process each row retrieved by the cursor
   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.employee_id || ', Salary: ' || emp_rec.salary);
 END LOOP;
END;

In this example, the `emp_cursor` is used within the `FOR` loop construct. The loop iterates through each row returned by the cursor, assigning each row's data to the record variable `emp_rec`. You can then access the column values of each row using the record variable within the loop.
In summary:

Cursors act as pointers and cannot be directly assigned values.
Cursors are used within control flow statements like `FOR` loops to process the data retrieved from the database query defined by the cursor.

Incorrect Syntax for usage of Cursor Names

The following statements are incorrect syntax for usage of cursor names (pet_info is the cursor name):

pet_info := 15000;
If pet_info is not NULL then

The correct syntax is

DECLARE pet_info CURSOR IS -- Your SQL statement to fetch data here SELECT * FROM pets; BEGIN -- Open the cursor OPEN pet_info; -- Loop through the cursor and process data LOOP FETCH pet_info INTO -- variables to store fetched data EXIT WHEN pet_info%NOTFOUND; -- Exit loop when no more rows -- Process the fetched data here END LOOP; -- Close the cursor CLOSE pet_info; END; /

Keyword and Parameter Descriptions used with Cursors

cursor_name: The name of an open explicit cursor that was declared within the current scope.
cursor_variable_name: The name of an open cursor variable that was declared in the current scope.
host_cursor_variable_name: The name of an open cursor variable that was declared in a PL/SQL host environment and passed to PL/SQL as a bind argument.

Cursor Expressions: Oracle provides a powerful feature in the SQL language: the cursor expression. A cursor expression, denoted by the CURSOR operator, returns a nested cursor from within a query. Each row in the result set of this nested cursor can contain the usual range of values allowed in a SQL query; it can also contain other cursors as produced by subqueries. The CURSOR syntax, although first introduced in Oracle8i Database SQL, was not available from within PL/SQL programs. This deficiency was corrected in Oracle9i Database Release 1; since then, SQL statements within a PL/SQL procedure or function have been able to take advantage of the CURSOR expression.

You can therefore use "cursor expressions" to return a large and complex set of related values retrieved from one or more tables. You can then process the cursor expression result set using nested loops that fetch from the rows of the result set, and then additional rows from any nested cursors within those rows. Cursor expressions can get complicated, given how complex the queries and result sets can be. Nevertheless, it’s good to know all the possible ways to retrieve data from the Oracle database You can use cursor expressions in any of the following:

Explicit cursor declarations
Dynamic SQL queries
REF CURSOR declarations and variables

You cannot use a cursor expression in an implicit query. The syntax for a cursor expression is very simple:

CURSOR (subquery)

The database opens the nested cursor defined by a cursor expression implicitly as soon as it fetches the row containing the cursor expression from the parent or outer cursor. This nested cursor is closed when:

You explicitly close the cursor.
The outer, parent cursor is executed again, closed, or canceled.
An exception is raised while fetching from a parent cursor. The nested cursor is closed along with the parent cursor.

Passing Parameters into a Cursor

The rationale behind PL/SQL allowing you to pass parameters into a cursor is:

Parameters make a cursor more reusable.
Parameters avoid scoping problems.

The scope of a cursor parameter is confined to that cursor. You cannot refer to the cursor parameter outside the SELECT statement associated with that cursor.

Cursor Parameter Modes

The syntax for cursor parameters is very similar to that of procedures and functions, with the restriction that a cursor parameter can be an IN parameter only. You cannot specify 1) OUT or 2) IN OUT modes for cursor parameters. The IN and IN OUT modes are used to pass values out of a procedure through that parameter. This does not make sense for a cursor. Values cannot be passed back out of a cursor through the parameter list. Information is retrieved from a cursor only by fetching a record and copying values from the column list with an INTO clause.

In Oracle PL/SQL, cursors themselves do not have "IN" or "OUT" parameters like functions or procedures do. Instead, cursors can accept parameters, but these parameters essentially act like "IN" parameters because they are used to pass values into the cursor when it is opened. These parameters allow you to customize the cursor's result set each time you open it, by passing different values. Here's an example to illustrate how you can pass parameters to a cursor:

CURSOR emp_cursor (dept_id NUMBER) IS
SELECT * FROM employees WHERE department_id = dept_id;

In this example, `dept_id` is a parameter for the `emp_cursor`. When you open `emp_cursor`, you must provide a value for `dept_id`, which will be used in the cursor's `SELECT` statement to filter the employees by their department. Opening the cursor with a parameter might look like this in a PL/SQL block:

OPEN emp_cursor(10); -- Opens the cursor for employees in department 10

As for "OUT" parameters, these are typically used in PL/SQL procedures and functions to return values to the caller. Cursors do not return values in the same way; instead, they allow row-by-row processing of the result set. If you need to return a result set from a PL/SQL block to a calling application, you might use a REF CURSOR, which is a cursor variable that can be opened with a query and then passed back to the caller. However, the concept of "IN" and "OUT" parameters as it applies to procedures and functions doesn't directly apply to cursors in the same way.
In the next lesson, we will look at the rest of the structure of a cursor block.

Oracle PL/SQL Programming