PL/SQL的面向对象编程

CREATE OR REPLACE TYPE liao_opp_test AS OBJECT
(
-- Author : HAND
-- Created : 2013/1/5 16:44:20
-- Purpose : 

-- Attributes
 mail_host VARCHAR2(20),
 mail_port INTEGER,

-- Member functions and procedures
 MEMBER PROCEDURE sent_mail,
 MEMBER FUNCTION get_address(i NUMBER) RETURN VARCHAR2
)

CREATE OR REPLACE TYPE BODY liao_opp_test IS

 -- Member procedures and functions
 MEMBER PROCEDURE sent_mail IS
 BEGIN
 dbms_output.put_line('send email success to ' || self.mail_host || ':' ||
 self.mail_port);
 END sent_mail;

 MEMBER FUNCTION get_address(i NUMBER) RETURN VARCHAR2 IS
 BEGIN
 RETURN i || '. ' || self.mail_host || ':' || self.mail_port;
 END get_address;
END;

DECLARE
 --init Object’s Attributes
 t liao_opp_test := liao_opp_test('192.168.1.1', 8080);
BEGIN
 t.sent_mail;

END;

create table cux_liao_opp_test of liao_opp_test;
insert into cux_liao_opp_test(mail_host,mail_port) values('192.168.1.1',8081);
insert into cux_liao_opp_test(mail_host,mail_port) values('192.168.1.2',8082);
insert into cux_liao_opp_test(mail_host,mail_port) values('192.168.1.3',8083);
insert into cux_liao_opp_test(mail_host,mail_port) values('192.168.1.4',8084);
insert into cux_liao_opp_test(mail_host,mail_port) values('192.168.1.5',8085);
insert into cux_liao_opp_test(mail_host,mail_port) values('192.168.1.6',8086);

SELECT lot.mail_host, lot.mail_port, lot.get_address(1)
 FROM cux_liao_opp_test lot

--上面的是不能派生子类的，下面的是可以派生子类的
CREATE OR REPLACE TYPE cux_person AS OBJECT
(
 p_name VARCHAR2(50),
 p_sex VARCHAR2(2),
 p_age INT,

 MEMBER FUNCTION get_person RETURN VARCHAR2
)
NOT FINAL;

CREATE OR REPLACE TYPE BODY cux_person IS
 MEMBER FUNCTION get_person RETURN VARCHAR2 IS
 BEGIN
 RETURN self.p_name || ',' || self.p_sex || ',' || self.p_age;
 END get_person;
END;

CREATE OR REPLACE TYPE cux_student UNDER cux_person
(

 stuid NUMBER,

 MEMBER FUNCTION get_student RETURN VARCHAR2
)
;
CREATE OR REPLACE TYPE BODY cux_student IS
 -- Member procedures and functions
 MEMBER FUNCTION get_student RETURN VARCHAR2 IS
 BEGIN
 RETURN self.stuid || '.' || self.get_person;
 END get_student;

END;
drop TYPE cux_student;
drop TYPE cux_person;

drop table cux_stuInfo;
create table cux_stuInfo of cux_student;

insert into cux_stuInfo(p_Name,p_Sex,p_Age,Stuid) values('阿呆','B',18,1);
insert into cux_stuInfo(p_Name,p_Sex,p_Age,Stuid) values('阿傻','G',19,2);
insert into cux_stuInfo(p_Name,p_Sex,p_Age,Stuid) values('阿笨','G',12,3);

select cs.p_name,cs.p_sex,cs.p_age,cs.get_person(),cs.get_student() from cux_stuInfo cs;
--ref(表别名)函数用来返回对象的OID，也就是对象标识符，对象表也有rowid
select ref(cs) from cux_stuInfo cs;

--创建学生分数表，注意外键
drop table cux_stuScore;
create table cux_stuScore (
 stu ref cux_student, --stu这一列的值必须出现在stuInfo表中，
 --且stu这一列存的对象的OID而不是对象本身,对这个列的操作都是基于OID来的 
 score int --分数
 ); 
insert into cux_stuScore(Stu,Score) select ref(s),round(dbms_random.value(20,40)) + 70 from cux_stuInfo s;

select * from cux_stuScore;

--deref(列名)函数可以把OID还原为对象，主键列显示有问题
select deref(s.stu) from cux_stuscore s;