Learning Oracle PL/SQL

9.7. Still More PL/SQL Features

This is the part of the book where you get to see a wealth of PL/SQL capabilities in only a few pages. The examples here are not full-fledged tutorials; they are only meant to let you in on more of the features you may decide to use as you progress as a PL/SQL programmer.

These are the features I'll run through in this section:

• Object types

• Large objects

• XML

• The code profiler

• (Pipelined) table functions

• More built-in packages and types

9.7.1. Object Types

Earlier in this chapter, I showed how you can create your own user-defined types that will hold collections. Oracle Version 8.0 and later offer another way to add custom datatypes to your application. In PL/SQL, an object type is a database construct that lets you define a data structure and a set of related operations upon it. The object type can have multiple attributes, in the same way that a record can have multiple fields. Those "operations" are really just a name for procedures or functions you define in the object type; the procedures and functions are also known as methods.

An example of an object type designed to hold information about library patrons is:

CREATE TYPE lib_patron_t AS OBJECT ( id NUMBER, username VARCHAR2(60), encrypted_password RAW(16), account_creation_date DATE, email_address VARCHAR2(2000), STATIC FUNCTION make (username IN VARCHAR2, plaintext_password IN VARCHAR2, email_address IN VARCHAR2, requestor_id IN NUMBER) RETURN lib_patron_t, MEMBER PROCEDURE save, MEMBER PROCEDURE ...