Plsql 简明教程

PL/SQL - Object Oriented

在本章中，我们将讨论面向对象的 PL/SQL。PL/SQL 允许定义对象类型，这有助于在 Oracle 中设计面向对象数据库。对象类型允许创建复合类型。使用对象允许您实现具有特定数据结构和对其操作方法的真实世界对象。对象具有属性和方法。属性是对象的性质，用于存储对象的状态；方法用于对行为建模。

In this chapter, we will discuss Object-Oriented PL/SQL. PL/SQL allows defining an object type, which helps in designing object-oriented database in Oracle. An object type allows you to create composite types. Using objects allow you to implement real world objects with specific structure of data and methods for operating it. Objects have attributes and methods. Attributes are properties of an object and are used for storing an object’s state; and methods are used for modeling its behavior.

使用 CREATE [OR REPLACE] TYPE 语句创建对象。以下是一个创建包含几个属性的简单 address 对象的示例：

Objects are created using the CREATE [OR REPLACE] TYPE statement. Following is an example to create a simple address object consisting of few attributes −

CREATE OR REPLACE TYPE address AS OBJECT
(house_no varchar2(10),
 street varchar2(30),
 city varchar2(20),
 state varchar2(10),
 pincode varchar2(10)
);
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type created.

让我们再创建一个对象 customer ，其中我们把 attributes 和 methods 结合在一起，以获得面向对象的体验：

Let’s create one more object customer where we will wrap attributes and methods together to have object-oriented feeling −

CREATE OR REPLACE TYPE customer AS OBJECT
(code number(5),
 name varchar2(30),
 contact_no varchar2(12),
 addr address,
 member procedure display
);
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type created.

Instantiating an Object

定义对象类型为对象提供蓝图。为了使用此对象，需要创建此对象的实例。您可以使用实例名称和 the access operator (.) 访问对象的属性和方法，如下所示：

Defining an object type provides a blueprint for the object. To use this object, you need to create instances of this object. You can access the attributes and methods of the object using the instance name and the access operator (.) as follows −

DECLARE
   residence address;
BEGIN
   residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');
   dbms_output.put_line('House No: '|| residence.house_no);
   dbms_output.put_line('Street: '|| residence.street);
   dbms_output.put_line('City: '|| residence.city);
   dbms_output.put_line('State: '|| residence.state);
   dbms_output.put_line('Pincode: '|| residence.pincode);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

House No: 103A
Street: M.G.Road
City: Jaipur
State: Rajasthan
Pincode: 201301

PL/SQL procedure successfully completed.

Member Methods

Member methods 用于操作对象的 attributes 。在声明对象类型时，您提供了成员方法的声明。对象主体定义了成员方法的代码。对象主体是使用 CREATE TYPE BODY 语句创建的。

Member methods are used for manipulating the attributes of the object. You provide the declaration of a member method while declaring the object type. The object body defines the code for the member methods. The object body is created using the CREATE TYPE BODY statement.

Constructors 是函数，它们返回一个新对象作为其值。每个对象都有一个系统定义的构造函数方法。构造函数的名称与对象类型相同。例如：

Constructors are functions that return a new object as its value. Every object has a system defined constructor method. The name of the constructor is same as the object type. For example −

residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');

comparison methods 用于比较对象。有两种方法可以比较对象：

The comparison methods are used for comparing objects. There are two ways to compare objects −

Map method

Map method 是一种通过这种方式实现的函数，其值取决于属性的值。例如，对于一个客户对象，如果两个客户的客户代码相同，那么这两个客户可能是一样的。因此，这两个对象之间的关系将取决于代码的值。

The Map method is a function implemented in such a way that its value depends upon the value of the attributes. For example, for a customer object, if the customer code is same for two customers, both customers could be the same. So the relationship between these two objects would depend upon the value of code.

Order method

Order method 实现了用于比较两个对象的一些内部逻辑。例如，对于一个矩形对象，如果它的两边都更大，那么一个矩形将大于另一个矩形。

The Order method implements some internal logic for comparing two objects. For example, for a rectangle object, a rectangle is bigger than another rectangle if both its sides are bigger.

Using Map method

让我们尝试使用以下矩形对象来理解上述概念－

Let us try to understand the above concepts using the following rectangle object −

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 member procedure display,
 map member function measure return number
);
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type created.

创建类型主体－

Creating the type body −

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
   MAP MEMBER FUNCTION measure return number IS
   BEGIN
      return (sqrt(length*length + width*width));
   END measure;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type body created.

现在使用矩形对象及其成员函数－

Now using the rectangle object and its member functions −

DECLARE
   r1 rectangle;
   r2 rectangle;
   r3 rectangle;
   inc_factor number := 5;
BEGIN
   r1 := rectangle(3, 4);
   r2 := rectangle(5, 7);
   r3 := r1.enlarge(inc_factor);
   r3.display;
   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Length: 8
Width: 9
Length: 5
Width: 7

PL/SQL procedure successfully completed.

Using Order method

现在， same effect could be achieved using an order method 。让我们使用一个 order 方法来重新创建矩形对象－

Now, the same effect could be achieved using an order method. Let us recreate the rectangle object using an order method −

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member procedure display,
 order member function measure(r rectangle) return number
);
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type created.

创建类型主体－

Creating the type body −

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
   ORDER MEMBER FUNCTION measure(r rectangle) return number IS
   BEGIN
      IF(sqrt(self.length*self.length + self.width*self.width)>
         sqrt(r.length*r.length + r.width*r.width)) then
         return(1);
      ELSE
         return(-1);
      END IF;
   END measure;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type body created.

使用矩形对象及其成员函数－

Using the rectangle object and its member functions −

DECLARE
   r1 rectangle;
   r2 rectangle;
BEGIN
   r1 := rectangle(23, 44);
   r2 := rectangle(15, 17);
   r1.display;
   r2.display;
   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Length: 23
Width: 44
Length: 15
Width: 17
Length: 23
Width: 44

PL/SQL procedure successfully completed.

Inheritance for PL/SQL Objects

PL/SQL允许从现有的基础对象创建对象。要实施继承，基础对象应被声明为 NOT FINAL 。默认值为 FINAL 。

PL/SQL allows creating object from the existing base objects. To implement inheritance, the base objects should be declared as NOT FINAL. The default is FINAL.

以下程序展示了PL/SQL对象中的继承。让我们创建另一个名为 TableTop 的对象，它从Rectangle对象中继承而来。为此，我们需要创建基础矩形对象－

The following programs illustrate the inheritance in PL/SQL Objects. Let us create another object named TableTop, this is inherited from the Rectangle object. For this, we need to create the base rectangle object −

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 NOT FINAL member procedure display) NOT FINAL
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type created.

创建基础类型主体－

Creating the base type body −

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type body created.

创建子对象桌台－

Creating the child object tabletop −

CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
   material varchar2(20),
   OVERRIDING member procedure display
)
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type created.

创建子对象桌台的类型主体－

Creating the type body for the child object tabletop

CREATE OR REPLACE TYPE BODY tabletop AS
OVERRIDING MEMBER PROCEDURE display IS
BEGIN
   dbms_output.put_line('Length: '|| length);
   dbms_output.put_line('Width: '|| width);
   dbms_output.put_line('Material: '|| material);
END display;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type body created.

使用桌台对象及其成员函数－

Using the tabletop object and its member functions −

DECLARE
   t1 tabletop;
   t2 tabletop;
BEGIN
   t1:= tabletop(20, 10, 'Wood');
   t2 := tabletop(50, 30, 'Steel');
   t1.display;
   t2.display;
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Length: 20
Width: 10
Material: Wood
Length: 50
Width: 30
Material: Steel

PL/SQL procedure successfully completed.

Abstract Objects in PL/SQL

NOT INSTANTIABLE 子句允许您声明一个抽象对象。您不能按原样使用抽象对象；您将需要创建此类对象的子类型或子类型来使用它的功能。

The NOT INSTANTIABLE clause allows you to declare an abstract object. You cannot use an abstract object as it is; you will have to create a subtype or child type of such objects to use its functionalities.

例如，

For example,

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
 NOT INSTANTIABLE NOT FINAL
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Type created.