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类
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;