Oracle中对象操作示例

曾经终败给现在 2022-04-14 04:29 95阅读 0赞

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;