Oracle中对象操作示例

select * from v$version;
/*
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production    
PL/SQL Release 11.2.0.1.0 - Production                                          
CORE 11.2.0.1.0 Production                                                        
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                         
NLSRTL Version 11.2.0.1.0 - Production  
*/

--创建对象
create or replace type employee_type as object
(
eno number(6),
name varchar2(10),
salary number(6,2),
job varchar2(10),
dno number(2),
member PROCEDURE change_job(new_job VARCHAR2),
member PROCEDURE change_salary(new_sal number),
member PROCEDURE change_dept(new_dno NUMBER),
member function get_sal return number,
constructor function employee_type(eno number,name VARCHAR2) return self as RESULT,
map MEMBER function sal_sort return number,
static function get_time return varchar2
);

--创建对对象体
create or replace type body employee_type as
  member PROCEDURE change_job(new_job VARCHAR2) is
  begin
    job:=new_job;
  end;
  member PROCEDURE change_salary(new_sal number) is
  begin
    salary:=new_sal;
  end;
  member PROCEDURE change_dept(new_dno NUMBER) is
  begin
    dno:=new_dno;
  end;
  member function get_sal return number is
  begin
    return salary;
  end;
  constructor function employee_type(eno number,name VARCHAR2) return self as RESULT is
  begin
    self.eno :=eno;
    self.name:=name;
    return ;
  end;
  map MEMBER function sal_sort return number is
  begin
    return salary;
  end;
  static function get_time return varchar2 is
  begin
    return to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
  end;
end;

--查看类型对象
desc employee_type

--使用静态对象示例
select employee_type.get_time() from dual;

--使用对象建表，对象作为行
create table t_employee_object of employee_type;

desc t_employee_object

insert into t_employee_object (eno,name) values (1111,'scott');
insert into t_employee_object values(employee_type(2222,'hr'));
commit;

select value(a) from t_employee_object a

--列对象，对象作为表的一个字段
create table t_employee_ex(
basic_info employee_type,
sex varchar2(6)
);

insert into t_employee_ex values(employee_type(3333,'wallimn'),'Male');

insert into t_employee_ex values(employee_type(1112,'smith',1000,'clerk',10),'Female');

update t_employee_ex a set a.basic_info.salary=2000 where a.basic_info.eno=3333;
commit;
select a.basic_info.eno eno, a.basic_info.name name,a.basic_info.salary salary from t_employee_ex a;

--此时这种查询就不支持了。
select value(a.basic_info),sex from t_employee_ex a;

--这个支持
select a.basic_info,sex from t_employee_ex a

--对象参照类型
create or replace type hm_type as object(
province varchar2(20),city varchar2(10),
street varchar2(20),doorplate varchar2(20),
name varchar2(10),
member function info return varchar2
);

create or replace type body hm_type as
member function info return varchar2
is
begin
  return name||':'||province||','||city||','||street||','||doorplate;
end;
end;

create table t_housemaster of hm_type;

insert into t_housemaster values('北京','北京','长安街','20号','张朋');
insert into t_housemaster values('上海','上海','外滩','20号','王胜');
commit;

select * from t_housemaster;

create table t_housemaster_ex(
id number(8) primary key,
name varchar2(10),
master ref hm_type);

insert into t_housemaster_ex select 1,'张朋',ref(a) from t_housemaster a where a.name='张朋';

select a.id,a.name,rawtohex(a.master) ,deref(a.master).info() mi from t_housemaster_ex a;
--master字段中仅存的是raw格式的地址。指向实际存储的内容所在的位置。

--更对参照类型所指的对象，再查查看。
update t_housemaster set street='23号' where name='张朋';
select a.id,a.name,deref(a.master).info() mi from t_housemaster_ex a;

--增加对象属性
alter type employee_type ADD ATTRIBUTE
remark varchar2(50) cascade;

--使用对象创建的表结构自动发生了变化
desc t_employee_object

--使用该对象定义的字段，也自动增加上了该属性。值为NULL
select a.basic_info.remark from t_employee_ex a;

select dump(a.basic_info) from t_employee_ex a;

select a.basic_info.eno ,a.basic_info.name from t_employee_ex a where a.basic_info.eno=3333;