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.

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.

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.

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.

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.

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

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

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.

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

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.

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

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.

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.

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