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]
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
Connect to the database by using PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Approve the entries.
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.
Open the cursor and loop through the records.
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.
Retrieve data from a nested table column and display it by using DBMS_OUTPUT.
End the first FOR loop, close the cursor, and end the PL/SQL block.
Compile the PL/SQL block.
SQL*Plus now displays the result of the compilation of your block. This is the end of the simulation. Click the Exit button.
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
Literals, characters, and reserved words
Data types available for PL/SQL
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.