What is a Cursor in PL/SQL?

A cursor is used to process multiple records one record at a time. You may use cursors to:

1. Migrate data from a legacy system
2. Load data into a data warehouse
3. Process data from multiple tables in a database

There are several advantages to using cursors within your applications. Where and when you use a cursor depends on the nature of the application and the objective you want to accomplish.

The cursors that this module explains are session cursors. The following list describes the attributes of session cursors.

1. A cursor lives in session memory until the session ends, when it ceases to exist.
2. A session cursor that is constructed and managed by PL/SQL is an implicit cursor.
3. A session cursor that you construct and manage is an explicit cursor.

You can get information about any session cursor from its attributes (which you can reference in procedural statements, but not in SQL statements). To list the session cursors that each user session currently has opened and parsed, query the dynamic performance view V$OPEN_CURSOR.

The advantages of using cursors are as follows:

1. Cursor processing is server based and it utilizes the server memory. Therefore, processing time is shorter.
2. By using cursors, you can process records from different tables one at a time and have greater flexibility in manipulating the data.

Using too many cursors may consume a lot of resources, and thus slow down the server. Before executing a SQL statement, Oracle assigns an area of memory on the database server known as the context area for the statement. The context area contains information that includes the SQL statement and data that is returned or affected by the SQL. This information allows Oracle to complete processing the SQL statement. The context area is within Oracle's program global area (PGA). The PGA is a memory buffer that contains data, process information, session information, and cursors for a server process. 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.

Static SQL and Dynamic SQL are two ways of writing SQL statements in PL/SQL, a procedural language extension for SQL developed by Oracle Corporation. The principal difference between these two types of SQL lies in the way SQL statements are prepared and executed.

1. Static SQL: Static SQL is the SQL that is known at compile-time. It involves SQL statements written directly into the PL/SQL code and doesn't change at runtime. This implies that the full text of the SQL statement is known when the application is compiled. Because of this, the Oracle database can optimize the execution plan for these SQL statements when the PL/SQL program is compiled. The benefits of Static SQL include:
   
   • Efficiency: Since the database knows the SQL statement at compile time, it can work out the most efficient way to access the data requested by the SQL statement.
   • Security: Static SQL in PL/SQL supports declarative roles and privileges, providing granular control over who can access or modify data.
   • Compile-time syntax check: If there's any error in the SQL statement, it will be caught at compile time.
2. Dynamic SQL: In contrast, Dynamic SQL involves SQL statements that are not fully known until runtime. They are constructed dynamically as the program is running, usually in response to user input or changes in the database. This kind of SQL is used when the structure of a query or DML operation (such as INSERT, UPDATE, DELETE, or SELECT) is not known in advance or needs to be constructed on the fly. Dynamic SQL is constructed using string manipulation and then passed to the database for parsing and execution.
   Advantages: The benefits of Dynamic SQL include:
   
   • Flexibility: Dynamic SQL can handle almost any SQL task, even when the specifics of the task are not known until runtime.
   • Runtime construction: SQL queries can be constructed based on user input or the state of the database.
   
   Disadvantages: Dynamic SQL also comes with certain challenges:
   
   • Performance: The database might not optimize dynamic SQL as well as static SQL. Each unique SQL statement requires a new execution plan, which can be slower to create than reusing the plan for a static SQL statement.
   • Security: Dynamic SQL can be susceptible to SQL Injection attacks if not properly handled, as it involves constructing SQL statements using string manipulation techniques.

In summary, while static SQL is efficient and safe, it lacks the flexibility of dynamic SQL. Conversely, dynamic SQL offers great flexibility, but requires careful handling to maintain performance and security. The choice between static and dynamic SQL will depend on the specific needs of your PL/SQL program.
The SQL in a cursor can be static or dynamic. In a static cursor, the user defines the exact syntax of the SELECT statement, and execution of the statement does not change its syntax. On the other hand, you can build dynamic SQL with the DBMS_SQL package. In a dynamic SQL, the exact syntax of the SELECT statement is not defined at compilation time. It is generated when Oracle executes the PL/SQL block, however.

You can use native dynamic SQL to create general-purpose procedures and to execute DDL commands within PL/SQL. You can use dynamic SQL to execute SQL commands that are built at run time rather than at program-creation time within stored procedural objects (such as packages) and in anonymous PL/SQL blocks. For example, you can create procedures that create tables, that take table names as input parameters, or that take where clauses as input parameters.

The script in the following example creates a procedure called ANYSTRING. The ANYSTRING procedure has a single input variable: a SQL command. When you execute the ANYSTRING procedure, you pass it the SQL command to execute; the SQL command may be either a DML command (such as select or insert) or a DDL command (such as create table).

create or replace procedure ANYSTRING(String IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE (String);
END;
/

You can execute the ANYSTRING procedure via the execute command, as shown in the upcoming examples of DDL and DML commands.
Note that the strings are not terminated with semicolons. If you are going to run DDL commands through dynamic SQL, you must have been granted the necessary system privileges explicitly and not via roles. If you do not have the CREATE TABLE system privilege explicitly granted to you, the following command will fail with an

ORA-01031: insufficient privileges error:

execute ANYSTRING('create table CD (Artist VARCHAR2(25), Title VARCHAR2(25)) ');
execute ANYSTRING('insert into CD values(''Glenn Gould'', ''Goldberg Variations'') ');

The first command in the preceding listing executes the following SQL command:

create table CD
(Artist VARCHAR2(25),
Title VARCHAR2(25));

The second command in the listing inserts a record into the CD table:

insert into CD values ('Glenn Gould', 'Goldberg Variations');

If the SQL statements passed to ANYSTRING contain quotes, each quote should be replaced with a set of two quotes, as shown in the preceding execution of ANYSTRING for the insert command. After each command, you will receive the following response:
PL/SQL procedure successfully completed.

Static and dynamic SQL are each appropriate for different circumstances. You should consider the differences between the two when determining whether static SQL or dynamic SQL is best for your application.

The source form of a static SQL statement is embedded within an application program written in a host language like PL/I. The statement is prepared before the program is executed and the operational form of the statement persists beyond the execution of the program. Static SQL statements in a source program must be processed before the program is compiled. This processing can be accomplished through the precompiler or the coprocessor. The precompiler or the coprocessor checks the syntax of the SQL statements, turns them into host language comments, and generates host language statements to invoke . The preparation of an SQL application program includes precompilation, the preparation of its static SQL statements, and compilation of the modified source program
The following section discusses typical cursor applications.

Cursors can be used in many applications where data is processed sequentially; for example, migrating data from a legacy system to an Oracle database or loading data into a data warehouse. For instance, a company might need to build a data warehouse where it can process information about marketing, inventory, and customers to understand the market potential and improve its sales. To populate the data warehouse, the company would have to upload the information from all the different data sources into a temporary set of tables on a regular basis. The data within the temporary tables would have to be validated before being loaded into the data warehouse tables. This is an ideal application for the use of cursors. Once the data is loaded into temporary tables, a cursor can be used to select each record, validate it, and then insert it into the data warehouse table.

A CURSOR expression returns a nested cursor. This form of expression is equivalent to the PL/SQL REF CURSOR and can be passed as a REF CURSOR argument to a function. A nested cursor is implicitly opened when the cursor expression is evaluated. For example, if the cursor expression appears in a select list, a nested cursor will be opened for each row fetched by the query. The nested cursor is closed only when:

1. The nested cursor is explicitly closed by the user
2. The parent cursor is reexecuted
3. The parent cursor is closed
4. The parent cursor is cancelled
5. An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up)

The following restrictions apply to CURSOR expressions:

1. If the enclosing statement is not a SELECT statement, nested cursors can appear only as REF CURSOR arguments of a procedure.
2. If the enclosing statement is a SELECT statement, nested cursors can also appear inthe outermost select list of the query specification or in the outermost select list of another nested cursor.
3. Nested cursors cannot appear in views.
4. You cannot perform BIND and EXECUTE operations on nested cursors

The following example shows the use of a CURSOR expression in the select list of a query:

SELECT department_name, CURSOR(SELECT salary, commission_pct
FROM employees e
WHERE e.department_id = d.department_id)
FROM departments d;

The next example shows the use of a CURSOR expression as a function argument. The example begins by creating a function in the sample OE schema that can accept the REF CURSOR argument. In the next lesson, we will look at implicit cursors, one of the two types of cursors.