当前位置:数据库 > Oracle >>

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, 
易做图 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),易做图 from t_employee_ex a; 
 
--这个支持 
select a.basic_info,易做图 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; 
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,