Composite Datatypes   «Prev  Next»

Composite Types Used In PL/SQL

Using composite datatypes within PL/SQL

The correct composite datatypes for the scenarios are:
  1. Need to store 10 product names: Use PL/SQL table
  2. Need to store name , sale_price , current_inventory_count for a single product: Use PL/SQL record
  3. Need to store product_name , but the size of the column in the database is unknown: Use %TYPE
  4. Need to store the details of all the columns within the PRODUCT table: Use %ROWTYPE

PL/SQL composite data types

Composite types have internal components that can be manipulated individually, such as the elements of an array, record, or table. Oracle TimesTen In-Memory Database supports the following composite data types:
  1. Associative array (index-by table)
  2. Nested table
  3. Varray
  4. Record
Associative arrays, nested tables, and varrays are also referred to as collections. The following sections discuss the use of composite data types:
  1. Using collections in PL/SQL
  2. Using records in PL/SQL
  3. Using associative arrays from applications

Calling PL/SQL from Other Languages

Oracle gives you the ability to call PL/SQL from other languages such as C, Java, Perl, PHP, or any number of other places. This seems like a reasonable request, but if you have ever worked with cross-language environments, you may be aware of some of the intricacies involved with connecting different systems with language-specific datatypes, specifically composite datatypes like
  1. arrays,
  2. records, and
  3. objects and,
  4. differing parameter semantics or vendor extensions
to standard application programming interfaces (APIs) like Microsoft's ODBC.
I will show a simple example of calling PL/SQL from the outside world. Let us say that I have written a PL/SQL function that accepts an ISBN expressed as a string and returns the corresponding book title:

/* File on web: booktitle.fun */
FUNCTION booktitle (isbn_in IN VARCHAR2)
RETURN VARCHAR2
IS
l_title books.title%TYPE;
CURSOR icur IS SELECT title FROM books WHERE isbn = isbn_in;
BEGIN
OPEN icur;
FETCH icur INTO l_title;
CLOSE icur;
RETURN l_title;
END;
In SQL*Plus, I could call this in several different ways. The shortest way would be as follows:
SQL> EXEC DBMS_OUTPUT.PUT_LINE(booktitle('0-596-00180-0'))
Learning Oracle PL/SQL
PL/SQL procedure successfully completed.

Composite Variables

A composite variable has internal components, which you can access individually. You can pass entire composite variables to subprograms as parameters. PL/SQL has two kinds of composite variables, collections and records