Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Oracle SQL *Plus PL/SQL Cursors

Using PL/SQL Cursors

This article describes using Implicit and Explicit cursors in SQL *Plus. In addition, views, column formatting, and ROWTYPE are also covered. The tables used in the article originated from the text Guide to Oracle9i, Morrison & Morrison by Thomson Course Technology. The SQL files to create the tables and populate the data are available through www.course.com.

Creating a PL/SQL View and Retrieval of Table Information

To get started, use the Describe statements to review the columns and datatypes for the tables to use in the SELECT statement to generate the data for the cursor(s). Examining the ID fields, the similar names across the tables make it easy to determine the keys to use for the Joins in the SELECT statement: S_ID for Student and Enrollment, C_SEC_ID for Enrollment and Course_Section and Course_no for Course_Section and Course.

Table Schema using DESC

Create the SELECT statement with the joined columns and view the data. Notice that the data wraps and is difficult to read. A column format command is needed for a clearer view of the data displayed.

SELECT statement

A column statement that formats the concatenated StudentName column to 25 characters (the FORMAT A25 portion of the statement) and displays the data in a readable format. The data can now be reviewed to determine if these are the columns that we would like to display in a View or if additional columns are needed.

SELECT statement Formatted Output

To Create the view, start out with a Create or Replace statement and use the query with the multiple joins to get the columns to use in a Cursor. If the view has no errors, a simple statement of "View Created" will appear.

Create View

PL/SQL Implicit Cursor

Using an anonymous code block, set the serveroutput environment variable on and declare a variable to hold the data. Rather than declare a variable for each column using varchar2, number or %TYPE, this code uses the %ROWTYPE datatype. The %ROWTYPE can be used on either Tables or Views. The columns in the select statement must match the columns of the row or an error will be generated. This code uses the view studentClasses%ROWTYPE to hold the information.

Note that only RowNum 1 is being inserted into the curr_student variable. An error will be generated for an implicit cursor if more than 1 row is returned by the SELECT statement into the variable. This example demonstrates a successful execution of the code block with a single row returned.

Implicit Cursor

Error generated when SELECT statement columns do not match the columns of the Table\View%ROWTYPE.

ROWTYPE Error

Implicit cursor error generated when more than 1 row returned by the SELECT statement.

Cursor Error

Explicit PL/SQL Cursor

To process multiple rows returned by a SELECT statement, declare a Cursor, open it and loop through the records. The following code demonstrates the declaration of a cursor using a SELECT statement and a variable to capture the row data based on the cursor%ROWTYPE called curr_student_row. The cursor is then Opened, a Loop is started, each record is Fetched into the cursor until there are no more records (%NOTFOUND). The PL SQL code outputs the information to the SQL *Plus screen. Finally, the cursor is Closed.

Explicit Cursor

When the code is executed the records are processed as expected.

Cursor Output

An alternate way to process multiple rows of data into an Explicit Cursor is a For loop. The cursor and variable are again declared. A For loop that retrieves the data into the variable from the cursor. The data is again output to the user in the SQL *Plus screen.

FOR loop with Cursor

The data output appears to be the same as the data from the previous Explicit cursor example.

FOR Loop Cursor Output

Declaring and Using a PL/SQL Cursor Type

The last example of using a cursor requires creating a Type (of ref cursor) and a stored procedure that will return the ref cursor type that will be created. Using a Create or Replace Package Type statement, the type is declared and created. The stored procedure returns the cursor_ref type using the OUT keyword. The cursor type takes the select statement based on the view studentClasses.

Oracle Cursor Type

In an anonymous code block, the cursor_ref type is declared as one of the variable which will be used to output the cursor data. The other variables are based on the view studentClasses datatypes using the %TYPE keyword. The code calls the stored procedure, initiates the Loop by Fetching the returned data from the cursor_ref type variable (v_dataSet) into the other declared variables. For each record found, an output line will be printed in SQL *Plus. Finally, the cursor is closed.

Oracle SQL *Plus Cursor Code

The code is executed and the data is output in the SQL *Plus window.

Cursor Output Oracle