PL/SQL with Object Types

In this part of the PL/SQL tutorial you will get to know PL/SQL with object types, how to declare objects in a PL/SQL block, manipulating objects in a PL/SQL block, calling object constructors and methods, updating and deleting objects, PL/SQL object collections with SQL object types and more.

OPPs concepts in PL/SQL

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.

Declaring and Initializing Objects in PL/SQLAn 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. 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. After an object type is defined and installed in the schema, you can use it to declare objects in any PL/SQL block, a subprogram, or package. For example, you can use the object type to specify the datatype of an attribute, column, variable, bind variable, record field, table element, formal parameter, or function result. At run time, instances of the object type are created; that is, objects of that type are instantiated. Each object can hold different values.Example: Working With Object Types

You can declare objects as the formal parameters of functions and procedures. That way, you can pass objects to stored subprograms and from one subprogram to another. In the next example, you use object type employee_typ to specify the datatype of a formal parameter:

PROCEDURE open_acct (new_acct IN OUT employee_typ) IS ...

In the following example, you use object type employee_typ to specify the return type of a function:

FUNCTION get_acct (acct_id IN NUMBER) RETURN employee_typ IS ...

Manipulating Objects in PL/SQLIt describes how to manipulate object attributes and methods in PL/SQL.1. Accessing Object Attributes With Dot Notation To access or change the value of an attribute, you use dot notation. Attribute names can be chained, which lets you access the attributes of a nested object type. For example:Example: Accessing Object Attributes

2. Calling Object Constructors and Methods Calls to a constructor are allowed wherever function calls are allowed. Like all functions, a constructor is called as part of an expression.Example: Inserting Rows in an Object Table

Defining SQL Types Equivalent to PL/SQL Collection Types To store nested tables and arrays inside database tables, you must also declare SQL types using the CREATE TYPE statement. The SQL types can be used as columns or as attributes of SQL object types. You can declare equivalent types within PL/SQL, or use the SQL type name in a PL/SQL variable declaration.Example: Declaring a Nested Table in SQL

PL/SQL Collections with SQL Object TypesCollections let you manipulate complex data types within PL/SQL. Your program can compute subscripts to process specific elements in memory, and use SQL to store the results in database tables. The NESTED TABLE clause is required whenever a database table has a nested table column. The clause identifies the nested table and names a system-generated store table, in which Oracle stores the nested table data. Within PL/SQL, you can manipulate the nested table by looping through its elements, using methods such as TRIM or EXTEND, and updating some or all of the elements. Afterward, you can store the updated table in the database again. You can insert table rows containing nested tables, update rows to replace its nested table, and select nested tables into PL/SQL variables. You cannot update or delete individual nested table elements directly with SQL; you have to select the nested table from the table, change it in PL/SQL, then update the table to include the new nested table.