欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

PL/SQL的面向对象编程

程序员文章站 2024-01-11 15:41:10
...

一切都是对象,世间万事万物都是对象,我们可以利用PL/SQL来实现面向对象的功能,让程序能够具有更好的性能和可读性 无 CREATE OR REPLACE TYPE liao_opp_test AS OBJECT(-- Author : HAND-- Created : 2013/1/5 16:44:20-- Purpose : -- Attributes mail_ho

一切都是对象,世间万事万物都是对象,我们可以利用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;