Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database.
A D V E R T I S E M E N T
PL/SQL objects are named blocks of PL/SQL with declarative,
executable and exception handling sections. The PL/SQL objects are
stored in the data dictionary with a name and can be reused. PL/SQL
objects include Packages, Stored Procedures, Functions and triggers.
An object type is a user-defined composite datatype representing a data structure and functions and procedures to manipulate the data. With scalar datatypes, each variable holds a single value. With collections, all the elements have the same type. Only object types let you associate code with the data.
The variables within the data structure are called attributes. The functions and procedures of the object type are called methods.
We usually think of an object as having attributes and actions. For example, a baby has the attributes gender, age, and weight, and the actions eat, drink, and sleep. Object types let you represent such real-world behavior in an application.
When you define an object type using the CREATE TYPE statement, you create an abstract template for some real-world object. The template specifies the attributes and behaviors the object needs in the application environment.
Each Application Uses a Subset of Object Attributes
Although the attributes are public (visible to client programs), well-behaved programs manipulate the data only through methodsthat you provide, not by assigning or reading values directly. Because the methods can do extra checking, the data is kept in a proper state.
At run time, you create instances of an abstract type, real objects with filled-in attributes.
Object Type and Objects (Instances) of that Type
Object types let you break down a large system into logical entities. This lets you create software components that are modular, maintainable, and reusable across projects and teams.
By associating code with data, object types move maintenance code out of SQL scripts and PL/SQL blocks into methods. Instead of writing a long procedure that does different things based on some parameter value, you can define different object types and make each operate slightly differently. By declaring an object of the correct type, you ensure that it can only perform the operations for that type. Object types allow for realistic data modeling.
Like a package, an object type has a specification and a body. The specification (or spec for short) defines the programming interface; it declares a set of attributes along with the operations (methods) to manipulate the data. The body defines the code for the methods.
Object Type Structure
All the information a program needs to use the methods is in the spec. You can change the body without changing the spec, and without affecting client programs.
In an object type spec, all attributes must be declared before any methods. If an object type spec declares only attributes, the object type body is unnecessary. You cannot declare attributes in the body. All declarations in the object type spec are public (visible outside the object type).
Components of an Object Type
An object type encapsulates data and operations. You can declare attributes and methods in an object type spec, but not constants, exceptions, cursors, or types. You must declare at least one attribute (the maximum is 1000). Methods are optional.
Like a variable, an attribute is declared with a name and datatype. The name must be unique within the object type (but can be reused in other object types). The datatype can be any Oracle type except:
- LONG and LONG RAW
- ROWID and UROWID
- The PL/SQL-specific types BINARY_INTEGER (and its subtypes), BOOLEAN, PLS_INTEGER, RECORD, REF CURSOR, %TYPE, and %ROWTYPE
- Types defined inside a PL/SQL package
You cannot initialize an attribute in its declaration using the assignment operator or DEFAULT clause. Also, you cannot impose the NOT NULL constraint on an attribute. However, objects can be stored in database tables on which you can impose constraints.
The kind of data structure formed by a set of attributes depends on the real-world object being modeled. For example, to represent a rational number, which has a numerator and a denominator, you need only two INTEGER variables. On the other hand, to represent a college student, you need several VARCHAR2 variables to hold a name, address, phone number, status, and so on, plus a VARRAY variable to hold courses and grades.
The data structure can be very complex. For example, the datatype of an attribute can be another object type (called a nested object type). That lets you build a complex object type from simpler object types. Some object types such as queues, lists, and trees are dynamic, meaning that they can grow as they are used. Recursive object types, which contain direct or indirect references to themselves, allow for highly sophisticated data models.
In general, a method is a subprogram declared in an object type spec using the keyword MEMBER or STATIC. The method cannot have the same name as the object type or any of its attributes. MEMBER methods are invoked on instances, as in
However, STATIC methods are invoked on the object type, not its instances, as in
Like packaged subprograms, methods have two parts: a specification and a body. The specification (spec for short) consists of a method name, an optional parameter list, and, for functions, a return type. The body is the code that executes to perform a specific task.
For each method spec in an object type spec, there must either be a corresponding method body in the object type body, or the method must be declared NOT INSTANTIABLE to indicate that the body is only present in subtypes of this type. To match method specs and bodies, the PL/SQL compiler does a token-by-token comparison of their headers. The headers must match exactly.
Like an attribute, a formal parameter is declared with a name and datatype. However, the datatype of a parameter cannot be size-constrained. The datatype can be any Oracle type except those disallowed for attributes. The same restrictions apply to return types.
An object type can represent any real-world entity. For example, an object type can represent a student, bank account, computer screen, rational number, or data structure such as a queue, stack, or list. This section gives several complete examples, which teach you a lot about the design of object types and prepare you to start writing your own.
Currently, you cannot define object types in a PL/SQL block, subprogram, or package. You can define them interactively in SQL*Plus using the SQL statement CREATE TYPE.
PL/SQL supports a single-inheritance model. You can define subtypes of object types. These subtypes contain all the attributes and methods of the parent type (or supertype). The subtypes can also contain additional attributes and additional methods, and can override methods from the supertype.
You can define whether or not subtypes can be derived from a particular type. You can also define types and methods that cannot be instantiated directly, only by declaring subtypes that instantiate them.
Some of the type properties can be changed dynamically with the ALTER TYPE statement. When changes are made to the supertype, either through ALTER TYPE or by redefining the supertype, the subtypes automatically reflect those changes.
You can use the TREAT operator to return only those objects that are of a specified subtype.
The values from the REF and DEREF functions can represent either the declared type of the table or view, or one or more of its subtypes.