Rumah >pangkalan data >tutorial mysql >PL/SQL的面向对象编程
一切都是对象,世间万事万物都是对象,我们可以利用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;