Composite Data Types Conclusion
This module discussed composite datatypes used when programming PL/SQL.
In this module, you learned how to:
- Describe the structure of a PL/SQL table
- Define and populate PL/SQL record
- Use %Type and %RowType attributes to manipulate data
Running PL/SQL Blocks
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:
- You type DECLARE or BEGIN. After you enter PL/SQL mode in this way, type the remainder of your PL/SQL subprogram.
- 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:
x NUMBER := 100;
FOR i IN 1..10 LOOP
IF MOD (i, 2) = 0 THEN --i is even
INSERT INTO temp VALUES (i, x, 'i is even');
INSERT INTO temp VALUES (i, x, 'i is odd');
x := x + 100;
In this module you were introduced to the following glossary terms:
- 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.
- Composite:Composite types have internal components that can be manipulated individually, such as the elements of an array, record, or table.
- Cursor:A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor.
- 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).
- 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.
- 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.