Oracle与Java之对象表的操作

1. 创建department对象类型
create type department as object
(
dept_id number(4),
dept_name varchar2(30)
);

2. 创建employee对象类型
create type employee as object
(
emp_id number(6),
firstname varchar2(20),
job varchar2(10),
mgr number(6),
hiredate date,
sal number(8,2),
comm number(8,2),
dept 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)'
);

3. 创建两个对象表
create table depts of department as
select department(department_id,department_name) from departments;

create table emps of employee as
select employee(e.employee_id, e.first_name, e.job_id, e.manager_id,
                e.hire_date, e.salary, e.commission_pct,
                ref(d))
--(select ref(d) from depts d where d.dept_id = e.department_id))
from employees e, depts d
where e.department_id = d.dept_id;

4. 实现java类
import java.math.*; import java.sql.*; public class Paymaster implements SQLData { private BigDecimal emp_id; private String firstname; private String job_id; 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(sal.multiply(comm)); return pay; } public void raiseSal(BigDecimal amount) { sal = sal.add(amount); } String sql_type; public String getSQLTypeName() throws SQLException { return sql_type; } public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; this.emp_id = stream.readBigDecimal(); this.firstname = stream.readString(); this.job_id = stream.readString(); this.mgr = stream.readBigDecimal(); this.hiredate = stream.readDate(); this.sal = stream.readBigDecimal(); this.comm = stream.readBigDecimal(); this.dept = stream.readRef(); } public void writeSQL(SQLOutput stream) throws SQLException { stream.writeBigDecimal(this.emp_id); stream.writeString(this.firstname); stream.writeString(this.job_id); stream.writeBigDecimal(this.mgr); stream.writeDate(this.hiredate); stream.writeBigDecimal(this.sal); stream.writeBigDecimal(this.comm); stream.writeRef(this.dept); } }

5. 测试
select e.firstname,e.sal,value(e).wages(),e.dept.dept_name from emps e
where e.comm is not null;
-- 要调用employee的成员方法，要使用value函数将实例转换为employee，再调用wages()方法。
-- 要调用reference的dept中的成员属性，必须要别名来引用。

declare
   pe employee;
begin
   select value(e) into pe from emps e
   where e.emp_id = 100;
   pe.raise_sal(3333);
   dbms_output.put_line(pe.sal);
   update emps e set e = pe
   where e.emp_id = pe.emp_id;
end;