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 varchar