PL/SQL with Object Types
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.
Get PL SQL Certification in Just 20 Hours
Declaring and Initializing Objects in PL/SQL
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.
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, 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
CREATE TYPE address_typ AS OBJECT ( street VARCHAR2(30), city VARCHAR2(20), state CHAR(2), postal_code VARCHAR2(6) ); END; /
Declaring Objects in a PL/SQL Block
You can use object types wherever built-in types such as CHAR or NUMBER can be used.
Example: Declaring Object Types in a PL/SQL Block
DECLARE emp employee_typ; -- emp is atomically null BEGIN -- call the constructor for employee_typ emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN', '555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110, address_typ('376 Mission', 'San Francisco', 'CA', '94222')); DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name); -- display details emp.display_address(); -- call object method to display details END; /
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 ...
Free Ebook: Step by Step Guide to Master PL SQL
Manipulating Objects in PL/SQL
It 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
DECLARE emp employee_typ; BEGIN emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN', '555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110, address_typ('376 Mission', 'San Francisco', 'CA', '94222')); DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name); DBMS_OUTPUT.PUT_LINE(emp.address.street); DBMS_OUTPUT.PUT_LINE(emp.address.city || ', ' ||emp. address.state || ' ' || emp.address.postal_code); END; /
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
DECLARE emp employee_typ; BEGIN INSERT INTO employee_tab VALUES (employee_typ(310, 'Evers', 'Boston', 'EBOSTON', '555.111.2222', '01-AUG-04', 'SA_REP', 9000, .15, 101, 110, address_typ('123 Main', 'San Francisco', 'CA', '94111')) ); INSERT INTO employee_tab VALUES (employee_typ(320, 'Martha', 'Dunn', 'MDUNN', '555.111.3333', '30-SEP-04', 'AC_MGR', 12500, 0, 101, 110, address_typ('123 Broadway', 'Redwood City', 'CA', '94065')) ); END; /
3. Updating and Deleting Objects
From inside a PL/SQL block you can modify and delete rows in an object table.
Example: Updating and Deleting Rows in an Object Table
DECLARE emp employee_typ; BEGIN INSERT INTO employee_tab VALUES (employee_typ(370, 'Robert', 'Myers', 'RMYERS', '555.111.2277', '07-NOV-04', 'SA_REP', 8800, .12, 101, 110, address_typ('540 Fillmore', 'San Francisco', 'CA', '94011')) ); UPDATE employee_tab e SET e.address.street = '1040 California' WHERE e.employee_id = 370; DELETE FROM employee_tab e WHERE e.employee_id = 310; END; /
Defining SQL Types Equivalent to PL/SQL Collection Types
To store nested tables and varrays 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
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type / CREATE TYPE student AS OBJECT ( -- create object id_num INTEGER(4), name VARCHAR2(25), address VARCHAR2(35), status CHAR(2), courses CourseList); -- declare nested table as attribute / CREATE TABLE sophomores of student NESTED TABLE courses STORE AS courses_nt;
The identifier courses represents an entire nested table. Each element of courses stores the name of a college course such as ‘Math 1020’.
PL/SQL Collections with SQL Object Types
Collections 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. Afterwards, 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.