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;