Oracle8i Java Stored Procedures Developer's Guide Release 2 (8.1.6) Part Number A81358-01 |
Library | Service | Contents | Index

## Writing Object Type Call Specs

In SQL, object-oriented programming is based on object types, which are user-defined composite datatypes that encapsulate a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are known as attributes. The functions and procedures that characterize the behavior of the object type are known as methods, which can be written in Java.

As with a package, an object type has two parts: a specification (spec) and a body. The spec is the interface to your applications; it declares a data structure (set of attributes) along with the operations (methods) needed to manipulate the data. The body implements the spec by defining PL/SQL subprogram bodies and/or call specs. (For details, see the PL/SQL User's Guide and Reference.)

If an object type spec declares only attributes and/or call specs, then the object type body is unnecessary. (You cannot declare attributes in the body.) So, if you implement all your methods in Java, you can place their call specs in the object type spec and omit the body.

In SQL*Plus, you can define SQL object types interactively using this syntax:

    CREATE [OR REPLACE] TYPE type_name
      [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} OBJECT (
      attribute_name datatype[, attribute_name datatype]...
      [{MAP | ORDER} MEMBER {function_spec | call_spec},]
      [{MEMBER | STATIC} {subprogram_spec | call_spec}
      [, {MEMBER | STATIC} {subprogram_spec | call_spec}]...]
    );

    [CREATE [OR REPLACE] TYPE BODY type_name {IS | AS}
      { {MAP | ORDER} MEMBER function_body;
       | {MEMBER | STATIC} {subprogram_body | call_spec};}
      [{MEMBER | STATIC} {subprogram_body | call_spec};]...
    END;]

The `AUTHID` clause determines whether all member methods execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker.

### Declaring Attributes

In an object type spec, all attributes must be declared before any methods. At least one attribute is required (the maximum is 1000). Methods are optional.

As with a Java variable, you declare an attribute 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 SQL type except `LONG`, `LONG` `RAW`, `NCHAR`, `NVARCHAR2`, `NCLOB`, `ROWID`, or `UROWID`.

You cannot initialize an attribute in its declaration using the assignment operator or `DEFAULT` clause. Furthermore, you cannot impose the `NOT` `NULL` constraint on an attribute. However, objects can be stored in database tables on which you can impose constraints.

### Declaring Methods

`MEMBER` methods accept a built-in parameter known as `SELF`, which is an instance of the object type. Whether declared implicitly or explicitly, it is always the first parameter passed to a `MEMBER` method. In the method body, `SELF` denotes the object whose method was invoked. `MEMBER` methods are invoked on instances, as follows:

    instance_expression.method()

However, `STATIC` methods, which cannot accept or reference `SELF`, are invoked on the object type, not its instances, as follows:

    object_type_name.method()

If you want to call a non-`static` Java method, you specify the keyword `MEMBER` in its call spec. Likewise, if you want to call a `static` Java method, you specify the keyword `STATIC` in its call spec.

#### Map and Order Methods

The values of a SQL scalar datatype such as `CHAR` have a predefined order, which allows them to be compared. However, instances of an object type have no predefined order. To put them in order, SQL calls a user-defined map method.

SQL uses the ordering to evaluate Boolean expressions such as `x > y` and to make comparisons implied by the `DISTINCT`, `GROUP` `BY`, and `ORDER` `BY` clauses. A map method returns the relative position of an object in the ordering of all such objects. An object type can contain only one map method, which must be a parameterless function with one of the following return types: `DATE`, `NUMBER`, or `VARCHAR2`.

Alternatively, you can supply SQL with an order method, which compares two objects. Every order method takes just two parameters: the built-in parameter `SELF` and another object of the same type. If `o1` and `o2` are objects, a comparison such as `o1 > o2` calls the order method automatically. The method returns a negative number, zero, or a positive number signifying that `SELF` is respectively less than, equal to, or greater than the other parameter. An object type can contain only one order method, which must be a function that returns a numeric result.

You can declare a map method or an order method but not both. If you declare either method, you can compare objects in SQL and PL/SQL. However, if you declare neither method, you can compare objects only in SQL and solely for equality or inequality. (Two objects of the same type are equal if the values of their corresponding attributes are equal.)

#### Constructor Methods

Every object type has a constructor method (constructor for short), which is a system-defined function with the same name as the object type. The constructor initializes and returns an instance of that object type.

Oracle generates a default constructor for every object type. The formal parameters of the constructor match the attributes of the object type. That is, the parameters and attributes are declared in the same order and have the same names and datatypes. SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed.

Note: To invoke a Java constructor from SQL, you must wrap calls to it in a `static` method and declare the corresponding call spec as a `STATIC` member of the object type.

### Examples

In this section, each example builds on the previous one. To begin, you create two SQL object types to represent departments and employees. First, you write the spec for object type `Department` as shown below. The body is unnecessary because the spec declares only attributes.

    CREATE TYPE Department AS OBJECT (
      deptno NUMBER(2),
      dname  VARCHAR2(14),
      loc    VARCHAR2(13)
    );

Then, you create object type `Employee` as shown below. Its last attribute, `deptno`, stores a handle, called a ref, to objects of type `Department`. A ref indicates the location of an object in an object table, which is a database table that stores instances of an object type. The ref does not point to a specific instance copy in memory. To declare a ref, you specify the datatype `REF` and the object type that the ref targets.

    CREATE TYPE Employee AS OBJECT (
      empno    NUMBER(4),
      ename    VARCHAR2(10),
      job      VARCHAR2(9),
      mgr      NUMBER(4),
      hiredate DATE,
      sal      NUMBER(7,2),
      comm     NUMBER(7,2),
      deptno   REF Department
    );

Next, as shown below, you create SQL object tables to hold objects of type `Department` and `Employee`. First, you create object table `depts`, which will hold objects of type `Department`. You populate the object table by selecting data from the relational table `dept` and passing it to a constructor, which is a system-defined function with the same name as the object type. You use the constructor to initialize and return an instance of that object type.

    CREATE TABLE depts OF Department AS
      SELECT Department(deptno, dname, loc) FROM dept;

Finally, as shown below, you create the object table `emps`, which will hold objects of type `Employee`. The last column in object table `emps`, which corresponds to the last attribute of object type `Employee`, holds references to objects of type `Department`. To fetch the references into that column, you use the operator REF, which takes as its argument a table alias associated with a row in an object table.

    CREATE TABLE emps OF Employee AS
      SELECT Employee(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal,
        e.comm, (SELECT REF(d) FROM depts d WHERE d.deptno = e.deptno))
        FROM emp e;

Selecting a ref returns a handle to an object; it does not materialize the object itself. To do that, you can use methods in class `oracle.sql.REF`, which supports Oracle object references. This class, which is a subclass of `oracle.sql.Datum`, extends the standard JDBC interface `oracle.jdbc2.Ref`. For more information, see the Oracle8i JDBC Developer's Guide and Reference.

#### Using Class oracle.sql.STRUCT

To continue, you write a Java stored procedure, as shown below. The class `Paymaster` has one method, which computes an employee's wages. The method `getAttributes()` defined in class `oracle.sql.STRUCT` uses the default JDBC mappings for the attribute types. So, for example, `NUMBER` maps to `BigDecimal`.

    import java.sql.*;
    import java.io.*;
    import oracle.sql.*;
    import oracle.jdbc.driver.*;
    import oracle.oracore.*;
    import oracle.jdbc2.*;
    import java.math.*;

    public class Paymaster {
      public static BigDecimal wages(STRUCT e)
        throws java.sql.SQLException {
        // Get the attributes of the Employee object.
        Object[] attribs = e.getAttributes();
        // Must use numeric indexes into the array of attributes.
        BigDecimal sal = (BigDecimal)(attribs[5]);  // [5] = sal
        BigDecimal comm = (BigDecimal)(attribs[6]); // [6] = comm
        BigDecimal pay = sal;
        if (comm != null) pay = pay.add(comm);
        return pay;
      }
    }

Because the method `wages` returns a value, you write a function call spec for it, as follows:

    CREATE OR REPLACE FUNCTION wages (e Employee) RETURN NUMBER AS
      LANGUAGE JAVA
      NAME 'Paymaster.wages(oracle.sql.STRUCT) return BigDecimal';

This is a top-level call spec because it is not defined inside a package or object type.

#### Implementing the SQLData Interface

To make access to object attributes more natural, you can create a Java class that implements the `SQLData` interface. To do so, you must provide the methods `readSQL()` and `writeSQL()` as defined by the `SQLData` interface. The JDBC driver calls method `readSQL()` to read a stream of database values and populate an instance of your Java class. (For details, see the Oracle8i JDBC Developer's Guide and Reference.) In the following example, you revise class `Paymaster`, adding a second method named `raiseSal()`:

    import java.sql.*;
    import java.io.*;
    import oracle.sql.*;
    import oracle.jdbc.driver.*;
    import oracle.oracore.*;
    import oracle.jdbc2.*;
    import java.math.*;

    public class Paymaster implements SQLData {
      // Implement the attributes and operations for this type.
      private BigDecimal empno;
      private String ename;
      private String job;
      private BigDecimal mgr;
      private Date hiredate;
      private BigDecimal sal;
      private BigDecimal comm;
      private Ref dept;

      public static BigDecimal wages(Paymaster e) {
        BigDecimal pay = e.sal;
        if (e.comm != null) pay = pay.add(e.comm);
        return pay;
      }

      public static void raiseSal(Paymaster[] e, BigDecimal amount) {
        e[0].sal =               // IN OUT passes [0]
          e[0].sal.add(amount);  // increase salary by given amount
      }

      // Implement SQLData interface.

      private String sql_type;

      public String getSQLTypeName() throws SQLException {
        return sql_type;
      }

      public void readSQL(SQLInput stream, String typeName)
        throws SQLException {
        sql_type = typeName;
        empno = stream.readBigDecimal();
        ename = stream.readString();
        job = stream.readString();
        mgr = stream.readBigDecimal();
        hiredate = stream.readDate();
        sal = stream.readBigDecimal();
        comm = stream.readBigDecimal();
        dept = stream.readRef();
      }

      public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeBigDecimal(empno);
        stream.writeString(ename);
        stream.writeString(job);
        stream.writeBigDecimal(mgr);
        stream.writeDate(hiredate);
        stream.writeBigDecimal(sal);
        stream.writeBigDecimal(comm);
        stream.writeRef(dept);
      }
    }

You must revise the call spec for method `wages`, as follows, because its parameter has changed from `oralce.sql.STRUCT` to `Paymaster`:

    CREATE OR REPLACE FUNCTION wages (e Employee) RETURN NUMBER AS
      LANGUAGE JAVA
      NAME 'Paymaster.wages(Paymaster) return BigDecimal';

Because the new method `raiseSal` is void, you write a procedure call spec for it, as follows:

    CREATE OR REPLACE PROCEDURE raise_sal (e IN OUT Employee, r NUMBER)
      AS LANGUAGE JAVA
      NAME 'Paymaster.raiseSal(Paymaster[], java.math.BigDecimal)';

Again, this is a top-level call spec.

#### Implementing Object Type Methods

Later, you decide to drop the top-level call specs `wages` and `raise_sal` and redeclare them as methods of object type `Employee`, as shown below. In an object type spec, all methods must be declared after the attributes. The object type body is unnecessary because the spec declares only attributes and call specs.

    CREATE TYPE Employee AS OBJECT (
      empno    NUMBER(4),
      ename    VARCHAR2(10),
      job      VARCHAR2(9),
      mgr      NUMBER(4),
      hiredate DATE,
      sal      NUMBER(7,2),
      comm     NUMBER(7,2),
      deptno   REF Department
      MEMBER FUNCTION wages RETURN NUMBER
        AS LANGUAGE JAVA
        NAME 'Paymaster.wages() return java.math.BigDecimal',
      MEMBER PROCEDURE raise_sal (r NUMBER)
        AS LANGUAGE JAVA
        NAME 'Paymaster.raiseSal(java.math.BigDecimal)'
    );

Then, you revise class `Paymaster` accordingly, as shown below. You need not pass an array to method `raiseSal` because the SQL parameter `SELF` corresponds directly to the Java parameter `this`--even when `SELF` is declared as `IN` `OUT` (the default for procedures).

    import java.sql.*;
    import java.io.*;
    import oracle.sql.*;
    import oracle.jdbc.driver.*;
    import oracle.oracore.*;
    import oracle.jdbc2.*;
    import java.math.*;

    public class Paymaster implements SQLData {
      // Implement the attributes and operations for this type.
      private BigDecimal empno;
      private String ename;
      private String job;
      private BigDecimal mgr;
      private Date hiredate;
      private BigDecimal sal;
      private BigDecimal comm;
      private Ref dept;

      public BigDecimal wages() {
        BigDecimal pay = sal;
        if (comm != null) pay = pay.add(comm);
        return pay;
      }

      public void raiseSal(BigDecimal amount) {
        // For SELF/this, even when IN OUT, no array is needed.
        sal = sal.add(amount);
      }

      // Implement SQLData interface.

      String sql_type;

      public String getSQLTypeName() throws SQLException {
        return sql_type;
      }

      public void readSQL(SQLInput stream, String typeName)
        throws SQLException {
        sql_type = typeName;
        empno = stream.readBigDecimal();
        ename = stream.readString();
        job = stream.readString();
        mgr = stream.readBigDecimal();
        hiredate = stream.readDate();
        sal = stream.readBigDecimal();
        comm = stream.readBigDecimal();
        dept = stream.readRef();
      }

      public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeBigDecimal(empno);
        stream.writeString(ename);
        stream.writeString(job);
        stream.writeBigDecimal(mgr);
        stream.writeDate(hiredate);
        stream.writeBigDecimal(sal);
        stream.writeBigDecimal(comm);
        stream.writeRef(dept);
      }
    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index