Composite Types Used In PL/SQL
Using composite datatypes within PL/SQL
The correct composite datatypes for the scenarios are:
- Need to store 10 product names: Use PL/SQL table
- Need to store name , sale_price , current_inventory_count for a single product: Use PL/SQL record
- Need to store product_name , but the size of the column in the database is unknown: Use %TYPE
- 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:
- Associative array (index-by table)
- Nested table
Associative arrays, nested tables, and varrays are also referred to as collections.
The following sections discuss the use of composite data types:
- Using collections in PL/SQL
- Using records in PL/SQL
- 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
- records, and
- objects and,
- differing parameter semantics or vendor extensions
to standard application programming interfaces (APIs) like Microsoft's
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)
CURSOR icur IS SELECT title FROM books WHERE isbn = isbn_in;
FETCH icur INTO l_title;
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.
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