PL/SQL   «Prev  Next»

Lesson 6 Nested table queries with PL/SQL
Objective Write a PL/SQL procedure that retrieves nested table data.

PL/SQL Procedure that retrieves a nested table using a PL/SQL Construct

In this lesson, we will write a PL/SQL block to query an object table that has a nested table.
Let us look at the SALE_HEADER table closely. This table includes two basic parts, the SALE_HEADER object type and the SALE_DETAIL object type. The SALE_DETAIL object type is a nested table, which is referred as a column within the SALE_HEADER object table. A parent-child (one-to-many) relationship exists between these two object types.Querying an object table with a nested table within it, the SALE_HEADER table, for instance, requires a simple cursor and a FOR LOOP structure. However, to retrieve the data from the nested table, we must define another FOR LOOP because the nested table has more than one record for every instance of the object table.
The following series of images explains the results of applying this query to the SALE_HEADER table:

Querying Oracle Nested Table[Process Description]

1) Three variable for the three columns within the SALE_HEADER table and define a cursor that selects these three columsn from the SALE_HEADER table
1) 
DECLARE t_sale_id sale_header.sale_id%type;
Three variables for the three columns within the SALE_HEADER table and define a cursor that selects these three columns from the SALE_HEADER table.

2) Within the procedural section, open the cursor and loop through all the records.
2) 
BEGIN
 OPEN obj_table;
  LOOP
Within the procedural section, open the cursor and loop through all the records.

3) Next, fetch the data into the variables defined within the declaration section and display the data pertaining to the attributes of the object table by using DBMS _OUTPUT.
3) 
FETCH obj_table into t_sale_id,
 t_sale_total, t_detail_nest;
Next, fetch the data into the variables defined within the declaration section and display the data pertaining to the attributes of the object table by using DBMS _OUTPUT.

4) To display the data of the nested table, initiate another FOR loop to go through each record of the nested table.
4) 
FOR nested_table in 1..t_detail_nest.count
 LOOP
To display the data of the nested table, initiate another FOR loop to go through each record of the nested table, which is ascertained by using a count of the variable that contains the value of the nested table, here it is t_detail_nest.

The following section discusses how to build a PL/SQL block that queries a nested table. You will query the SALE_HEADER table and its attributes, including the nested table within it, and display the data by using DBMS_OUTPUT.

Oracle PL-SQL Block

  1. Connect to the database by using PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Approve the entries.
  2. Declare variables for all the columns within the SALE_HEADER table. Name each variable as the column name prefixed with t_. Next, enter the query for a cursor that selects all the columns from SALE_HEADER, restricted to records only where sale_id is 36.
  3. Open the cursor and loop through the records.
  4. Fetch the records into the variables and display the data for the object table attributes by using DBMS_OUTPUT. The display should be in two lines, where the first line is the header containing the columns displayed and the second line is the data.
  5. Retrieve data from a nested table column and display it by using DBMS_OUTPUT.
  6. End the first FOR loop, close the cursor, and end the PL/SQL block.
  7. Compile the PL/SQL block.
  8. SQL*Plus now displays the result of the compilation of your block. This is the end of the simulation. Click the Exit button.

PL/SQL Basics

A PL/SQL block is a complete section of PL/SQL code. A PL/SQL program is made up of one or more blocks that logically divide the work. Blocks can even be nested within other blocks. The following description contains a full discussion on block structure.

PL/SQL Block Syntax Rules

Syntax and rules govern what you can and cannot do in PL/SQL. While the following syntax and programming standards alone do not make a program good, failure to understand the rules of the language can certainly make a program bad. In this module, we discuss the basic principles of the language, including PL/SQL block structure
  1. Variable declarations
  2. Literals, characters, and reserved words
  3. Data types available for PL/SQL
  4. Wrapper utility to hide code

The basic program unit in PL/SQL is called a block. Blocks contain sets of instructions for Oracle to execute, display information to the screen, write to files, call other programs, manipulate data, and more. All PL/SQL programs are made of at least one block. Methods of implementation range from programs that are executed one time only and are not stored anywhere, to blocks that are stored in the database for later use. Blocks support all DML statements, and using Native Dynamic SQL (NDS) or the built-in DBMS_SQL, they can run DDL statements.

The next lesson will wrap up this module.