什么是PL/SQL集合（关联阵列，嵌套表，Varrays），它们如何使用？

PL/SQL集合用于在单个变量中存储多个值，主要有三种类型：1. 关联数组（Index-By表）适用于PL/SQL块内的临时存储，如缓存错误消息或配置设置，无需初始化即可直接赋值且索引可从任意数字开始；2. 嵌套表支持存储在数据库表中并用于SQL语句，适合传递数据集或存储结构化列表，可扩展且支持DML操作；3. 可变数组（Varrays）用于有序、固定大小的列表，如星期或月份，需定义最大容量，存储时内联性能较好但无法删除单个元素。选择哪种类型取决于具体应用场景是否涉及存储、传递或临时使用数据。

PL/SQL collections are like containers for holding multiple values in a single variable. They’re super useful when you need to work with groups of related data, especially in PL/SQL blocks or stored procedures. There are three main types: associative arrays (index-by tables), nested tables, and varrays.

Each has its own strengths and use cases — let’s break them down one by one.

Associative Arrays (Index-By Tables)

These are the simplest type of collection in PL/SQL. Think of them as key-value pairs, where each value is associated with a unique index — usually an integer or string.

Use case:
They're great for temporary storage within a PL/SQL block. For example, caching a list of error messages or configuration settings that you want to access quickly by key.

How to declare and use:

DECLARE
  TYPE t_employee_names IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  employee_names t_employee_names;
BEGIN
  employee_names(1) := 'Alice';
  employee_names(2) := 'Bob';

  DBMS_OUTPUT.PUT_LINE('Employee 1: ' || employee_names(1));
END;

Some things to know:

• They can’t be stored in database tables.
• You don’t need to initialize them before using — just assign values directly.
• Indexes can start from any number and don’t have to be sequential.

Nested Tables

These are similar to associative arrays but more flexible. Unlike associative arrays, they can be stored in database tables and used in SQL statements.

Use case:
When you need to pass sets of data between PL/SQL and SQL, or store structured lists in a column of a table.

How to declare and use:

First create a type at the schema level:

CREATE OR REPLACE TYPE t_number_list AS TABLE OF NUMBER;

Then use it in a table or PL/SQL block:

DECLARE
  numbers t_number_list := t_number_list(1, 2, 3);
BEGIN
  numbers.EXTEND;
  numbers(4) := 4;

  FOR i IN 1..numbers.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(numbers(i));
  END LOOP;
END;

Important notes:

• Nested tables are unbounded — you can keep adding elements.
• When stored in a database table, they’re stored out-of-line (like a separate table).
• They support DML operations through the TABLE() function in SQL.

Varrays (Variable-size Arrays)

Varrays are like fixed-size arrays — you define a maximum size when you declare the type. They maintain order and can be accessed by index.

Use case:
When you need a limited set of ordered items, such as days of the week, months, or a fixed list of options.

How to declare and use:

CREATE OR REPLACE TYPE t_week_days AS VARRAY(7) OF VARCHAR2(10);

In a PL/SQL block:

DECLARE
  weekdays t_week_days := t_week_days('Mon', 'Tue', 'Wed', 'Thu', 'Fri');
BEGIN
  weekdays.EXTEND;
  weekdays(6) := 'Sat';
  weekdays(7) := 'Sun';

  FOR i IN 1..weekdays.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(weekdays(i));
  END LOOP;
END;

Key points:

• You must specify the max size upfront.
• Stored inline in tables — better performance if you have small, predictable datasets.
• Cannot delete individual elements; only truncate or extend.

So depending on whether you're working inside a PL/SQL block, storing data in a table, or dealing with fixed-length lists, you'll choose one of these collection types.

基本上就这些。

以上是什么是PL/SQL集合（关联阵列，嵌套表，Varrays），它们如何使用？的详细内容。更多信息请关注PHP中文网其他相关文章！