Plsql 简明教程

PL/SQL - Arrays

在本章中，我们将讨论 PL/SQL 中的数组。PL/SQL 编程语言提供了一个称为 VARRAY 的数据结构，它可以存储相同类型元素的固定大小顺序集合。varray 用于存储有序数据集合，但是往往最好将数组视为相同类型变量的集合。

In this chapter, we will discuss arrays in PL/SQL. The PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. A varray is used to store an ordered collection of data, however it is often better to think of an array as a collection of variables of the same type.

所有 varray 均由连续的内存位置组成。最低地址对应于第一个元素，最高地址对应于最后一个元素。

All varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

varrays

数组是集合类型数据的一部分，它代表可变大小的数组。我们将在后面一章中学习其他集合类型 'PL/SQL Collections' 。

An array is a part of collection type data and it stands for variable-size arrays. We will study other collection types in a later chapter 'PL/SQL Collections'.

varray 中的每个元素都有一个与之关联的索引。它还具有可以动态更改的最大大小。

Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically.

Creating a Varray Type

Varray 类型由 CREATE TYPE 语句创建。你必须指定 varray 中存储的最大大小和元素类型。

A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray.

在模式级别创建 VARRAY 类型的基本句法是 −

The basic syntax for creating a VARRAY type at the schema level is −

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

其中，

Where,

  1. varray_type_name is a valid attribute name,

  2. n is the number of elements (maximum) in the varray,

  3. element_type is the data type of the elements of the array.

可以使用 ALTER TYPE 语句更改 varray 的最大大小。

Maximum size of a varray can be changed using the ALTER TYPE statement.

例如，

For example,

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);
/

Type created.

在 PL/SQL 块中创建 VARRAY 类型的基本语法是 −

The basic syntax for creating a VARRAY type within a PL/SQL block is −

TYPE varray_type_name IS VARRAY(n) of <element_type>

例如 -

For example −

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;

现在让我们通过几个示例来理解这个概念 -

Let us now work out on a few examples to understand the concept −

Example 1

以下程序说明了 varray 的使用 −

The following program illustrates the use of varrays −

DECLARE
   type namesarray IS VARRAY(5) OF VARCHAR2(10);
   type grades IS VARRAY(5) OF INTEGER;
   names namesarray;
   marks grades;
   total integer;
BEGIN
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i in 1 .. total LOOP
      dbms_output.put_line('Student: ' || names(i) || '
      Marks: ' || marks(i));
   END LOOP;
END;
/

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

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

Total 5 Students
Student: Kavita  Marks: 98
Student: Pritam  Marks: 97
Student: Ayan  Marks: 78
Student: Rishav  Marks: 87
Student: Aziz  Marks: 92

PL/SQL procedure successfully completed.

Please note −

Please note −

  1. In Oracle environment, the starting index for varrays is always 1.

  2. You can initialize the varray elements using the constructor method of the varray type, which has the same name as the varray.

  3. Varrays are one-dimensional arrays.

  4. A varray is automatically NULL when it is declared and must be initialized before its elements can be referenced.

Example 2

varray 的元素也可以是数据库表的任何 %ROWTYPE 或数据库表字段的任何 %TYPE。以下示例对该概念进行了说明。

Elements of a varray could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept.

我们将使用存储在数据库中的 CUSTOMERS 表，如下所示 −

We will use the CUSTOMERS table stored in our database as −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

下面的示例使用 cursor ，你将在另一章中详细学习它。

Following example makes the use of cursor, which you will study in detail in a separate chapter.

DECLARE
   CURSOR c_customers is
   SELECT  name FROM customers;
   type c_list is varray (6) of customers.name%type;
   name_list c_list := c_list();
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter + 1;
      name_list.extend;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter));
   END LOOP;
END;
/

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

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

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed.