RelationalDBDesign RelationalDBDesign



Composite Datatypes   «Prev  Next»
Lesson 5

Composite Data Types Conclusion

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

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:
  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;
.
/

Glossary

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
  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.