Lesson 5

This module discussed composite datatypes used when programming PL/SQL.
In this module, you learned how to:

1. Describe the structure of a PL/SQL table
2. Define and populate PL/SQL record
3. Use %Type and %RowType attributes to manipulate data

You can also use PL/SQL subprograms (called blocks) to manipulate data in the database. SQL*Plus treats PL/SQL subprograms in the same manner as SQL commands, except that a semicolon (;) or a blank line does not terminate and execute a block.
A programmer terminates PL/SQL subprograms by entering a period (.) by itself on a new line. You can also terminate and execute a PL/SQL subprogram by entering a slash (/) by itself on a new line. You enter the mode for entering PL/SQL statements when:

1. You type DECLARE or BEGIN. After you enter PL/SQL mode in this way, type the remainder of your PL/SQL subprogram.
2. You type a SQL command (such as CREATE PROCEDURE) that creates a stored procedure. After you enter PL/SQL mode in this way, type the stored procedure you want to create.

SQL*Plus stores the subprograms you enter in the SQL buffer. Execute the current subprogram with a RUN or slash (/) command. A semicolon (;) is treated as part of the PL/SQL subprogram and will not execute the command. SQL*Plus sends the complete PL/SQL subprogram to Oracle Database for processing (as it does SQL commands). You might enter and execute a PL/SQL subprogram as follows:

DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD (i, 2) = 0 THEN --i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
END;
.
/

In this module you were introduced to the following glossary terms:

1. Block: The basic unit of a PL/SQL source program is the block, or anonymous block, which groups related declarations and statements. TimesTen supports PL/SQL blocks.
2. Composite:Composite types have internal components that can be manipulated individually, such as the elements of an array, record, or table.
3. Cursor:A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor.
4. Field: The smallest structure in a table; contains data describing the subject of a table.
5. Package:
6. Record: The record data structure provides a high-level way of addressing and manipulating data defined inside PL/SQL programs (as opposed to stored in database tables).
7. Subprogram: A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly. If the subprogram has parameters, their values can differ for each invocation.
8. View: A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

In the next module, you will learn to work with PL/SQL explicit cursors.