Oracle中对象操作示例
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;
