Oracle PL/SQL 集合

引言

PL/SQL 应用程序通常由 SQL 语句与过程逻辑混合组成，用于处理从数据库检索的数据。如果编译为存储过程，您的 PL/SQL 代码将驻留在服务器上，这是需要大量数据库交互的程序的理想位置。话虽如此，任何时候软件应用程序与数据库连接，都会付出性能代价。不仅如此，那些在代码和 SQL 之间不断切换的程序可能会变得相当复杂。PL/SQL 集合可以解决其中一些问题。

为什么要使用集合？

几乎所有现代编程语言都支持集合。集合可以宽泛地定义为一组有序的、类型相同的元素，允许通过索引以编程方式访问其元素。编程世界中常用的集合类型包括数组、映射和列表。

将元素存储在集合中可以带来许多好处。首先，集合可以帮助简化代码。如果您需要处理许多相似类型的项，将这些项存储在集合中将允许您轻松地循环遍历每个元素，并通过索引引用每个元素。此外，大多数语言定义的集合类型都包含用于操作集合的内置方法。可能集合提供的最大优势是提高应用程序性能。开发人员利用集合来“缓存”需要经常访问的static数据。这可以减少对数据库的调用。

Oracle 集合

Oracle 提供了三种 PL/SQL 集合类型：关联数组、嵌套表和 Varray。每种集合类型将在以下部分进行描述。

关联数组

关联数组是键值对的集合，其中每个键都是唯一的，并用于在数组中定位相应的键值。键可以是integer或string。

关联数组代表任意大小的数据集，可以在不知道其在数组中位置的情况下快速查找单个元素，而无需遍历所有数组元素。由于关联数组旨在临时存储数据而不是持久存储数据，因此不能与INSERT和SELECT INTO等 SQL 语句一起使用。可以通过在包中声明类型并在包体中赋值来使它们在数据库会话期间保持持久。

-- Declaration and initialization of Associative Array  
DECLARE
  TYPE book_title_tab IS TABLE OF book.title%TYPE INDEX BY BINARY_INTEGER;
  book_titles   book_title_tab;
BEGIN
  book_titles(1) := 'Computer fundamentals';
  book_titles(2) := 'Database programming';
  …
END;

PL/SQL 关联数组允许 .NET 代码将数组作为参数传递给 PL/SQL 代码（存储过程或匿名 PL/SQL 块）。

名为‘PERMISSIONS’的包中的数据库存储过程‘INSERT_PERMISSIONS’是从 ASP.NET 应用程序调用的。

-- Create table 
CREATE TABLE TABPERMISSIONS (ID NUMBER(8),NAME VARCHAR2(40));

-- Create package specification 
CREATE OR REPLACE PACKAGE SCOTT.PERMISSIONS
AS
   TYPE PER_TY
   IS
      TABLE OF TABPERMISSIONS.ID%TYPE
         INDEX BY BINARY_INTEGER;

   PROCEDURE INSERT_PERMISSIONS (permissions PER_TY);
END PERMISSIONS;
/

-- Create package body 
CREATE OR REPLACE PACKAGE BODY SCOTT.PERMISSIONS
AS
   PROCEDURE INSERT_PERMISSIONS (permissions PER_TY)
   AS
      P_ID   NUMBER (8) := 0;
   BEGIN
      FOR i IN PERMISSIONS.FIRST .. PERMISSIONS.LAST
      LOOP
         INSERT INTO TABPERMISSIONS (id)
           VALUES   (permissions (i));
      END LOOP;
   END INSERT_PERMISSIONS;
END permissions;
/

以下代码片段解释了如何将数组从 ASP.NET 应用程序传递到 Oracle 存储过程。

protected void btnUpdate_Click(object sender, EventArgs e)
    {
        string constr = "User Id=abc; Password=testpwd; 
	Data Source=ORCL; enlist=false; pooling=false";
        OracleConnection con = new OracleConnection(constr);
        con.Open();
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = con;
        cmd.CommandText = "PERMISSIONS.INSERT_PERMISSIONS";
        cmd.CommandType = CommandType.StoredProcedure;

        OracleParameter p_in_values = new OracleParameter();
        p_in_values.OracleDbType = OracleDbType.Int32;
        p_in_values.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        p_in_values.Value = new decimal[4] { 1, 2, 3, 4 };
        p_in_values.Size = 4;
        p_in_values.Direction = ParameterDirection.Input;

        cmd.Parameters.Add(p_in_values);

        try
        {
            int a = 0;
            a= cmd.ExecuteNonQuery();
        }
        catch (Exception EX)
        {
            Console.WriteLine(EX.Message);
        }
    }

摘要

1. 关联数组适用于相对较小的查找表，其中集合可以在每次调用过程或初始化包时在内存中构建。
2. 它们适用于收集事先未知数量的信息，因为它们的尺寸没有固定限制。
3. 它们的索引值更灵活，因为关联数组的下标可以是负数、非顺序的，并且可以在适当时使用string值而不是数字。

Varrays

Varray是 Variable Array 的缩写。Varray以添加的顺序存储相同类型的元素。Varray中的元素数量必须在声明时已知。换句话说，Varray具有固定的下界和上界，这使其与其他编程语言的集合类型最为相似。创建并填充后，可以通过数字索引访问每个元素。

以下语句声明，然后填充一个Varray，该Varray将包含 4 个与table Department 中的 column name 类型相同的元素。

-- Declaration and initialization of VARRAY.
DECLARE
    TYPE deptnames IS VARRAY(4) OF Department.name%TYPE;
    dept_names deptnames;
BEGIN
    dept_names := deptnames ('COMPUTER','MECHANICAL', 'ELCTRICAL');
END;

-- Create Varray
CREATE TYPE depts IS VARRAY (4) OF VARCHAR2(50);
/
--Create table with varray column.
CREATE TABLE Department (
    DEPTNO     NUMBER,
    SECTION    VARCHAR2(30),
    Dept_names   depts);
/
--Insert a new collection into the column on our book_library table.
INSERT INTO Department (DEPTNO, SECTION, Dept_names)
  VALUES (DEPTNO_SEQ.NEXTVAL,'Engineering', 
          depts ('Electronics','Computer', 'Instrumentation', 'Information technology'));
/

-- Retrieve varray from the database.
SELECT section, Dept_names from Department; 
NAME                  DEPT_NAMES
--------------------  ---------------------------------------------
Engineering      DEPTS('Electronics','Computer','Instrumentation',  
                       'Information technology')                                     
-- Note: Insertion order of elements of varray retained after retrieval.

摘要

1. Varray可以添加到数据库表中。
2. Varray非常适合存储需要集体处理的固定值。
3. 无法对Varray中的单个元素执行insert、update和delete操作。
4. 当表包含Varray类型时，其数据将与表的其余数据内联包含。
5. 当元素数量已知且通常按顺序访问所有元素时，Varray是一个不错的选择。
6. 存储在数据库中时，varray会保留其顺序和下标。

嵌套表

嵌套表与Varray一样，可以存储在关系表中，也可以作为 PL/SQL 程序变量。与Varray不同，嵌套表不需要指定大小。换句话说，它们是无界的。

CREATE TYPE dept_tab IS TABLE OF VARCHAR2(50);
/
CREATE TABLE Department (
    DEPTNO     NUMBER,
    SECTION    VARCHAR2(30),
    dept_name_tab dept_tab)
    NESTED TABLE dept_name_tab STORE AS names_table;
/
--Insert a record into Department, with a Nested Table of dept names.
INSERT INTO Department (DEPTNO, SECTION, dept_name_tab)
  VALUES (DEPTNO_SEQ.NEXTVAL,'Engineering', 
          dept_tab('Electronics','Computer', _
		'Instrumentation', 'Information technology'));
/
--Declare a nested table type
DECLARE 
  DeptName_tab dept_tab;
BEGIN
  DeptName_tab := 
    dept_tab ('Mechanical',''Electronics', 'Computer', 'Instrumentation',
               ''Information technology','Production',’ELECTRICAL’);
 
--Update the existing record with a new dept names Nested Table.
  UPDATE Department 
    SET dept_name_tab = DeptName_tab;
END;
/

嵌套表在数据库中如何存储？

对于嵌套表，将有一个单独的数据库表来存储数据。此表在‘STORE AS’子句之后指定。如果数据库表有多个嵌套表类型，同一个存储表将为该父表的所有嵌套表存储数据。这些存储表包含一个名为NESTED_TABLE_ID的列，该列允许父表引用行的嵌套表数据。

如何操作集合的单个元素？

要操作集合元素，请使用TABLE命令。TABLE命令运算符告知 Oracle 您希望您的操作直接针对集合，而不是其父表。

--1.Select all depts from section 'Engineering' that are like %ELCTRO%'.
SELECT column_value FROM TABLE(SELECT dept_name_tab
FROM Department WHERE SECTION = ''Engineering')
   WHERE column_value LIKE '%ELECTRO%';
-- Output of the above query
COLUMN_VALUE
------------------------------
ELECTRONICS
ELCTRICAL 
--2.Update DPET NAME ‘Production’ to a new value ‘Production sandwitch’.  
--This is possible Only with a nested table, Not Possible in Varray!!
UPDATE TABLE(SELECT dept_name_tab 
             FROM Department WHERE SECTION = 'ENGINEERING')
  SET column_value   = 'Production'
  WHERE column_value = 'Production Sandwitch';
 
--3.Select all department entries for ENGINEERING SECTION.
SELECT column_value FROM TABLE(SELECT dept_name_tab
                               FROM Department WHERE SECTION = 'Engineering');
-- Output of the above query
COLUMN_VALUE
------------------------------
MECHANICAL
ELECTRONICS
COMPUTER
PRODUCTION SANDWITCH

摘要

1. 嵌套表适用于必须持久存储的重要数据关系。
2. 嵌套表可以是稀疏的：您可以删除任意元素，而不仅仅是从末尾移除一项。
3. 嵌套表数据存储在外部的存储表中，这是一个系统生成的数据库表，与嵌套表相关联。
4. 存储在数据库中时，嵌套表的元素顺序和下标不会保留。
5. 嵌套表比varray具有优势，因为它们允许对单个元素进行insert、update和delete操作。

何时使用什么？

Varray

1. 用于保留有序列表
2. 用于处理固定集合，具有已知数量的条目
3. 用于需要在数据库中存储并整体操作集合

嵌套表

1. 用于处理需要动态增加的无界列表
2. 用于需要在数据库中存储并单独操作元素

关联数组

1. 当不需要将集合存储在数据库中时。它的速度和索引灵活性使其成为内部应用程序的理想选择。

结论

Oracle PL/SQL 并不是一门难以学习的编程语言。然而，与所有优秀的编程语言一样，有很多方法可以最大化效率和最小化复杂性。考虑到 PL/SQL 与数据库交互的强大功能，很容易养成过度调用数据库来完成工作的习惯。集合可以帮助您构建更简单、更快的 Oracle 数据库应用程序，这是每个优秀 PL/SQL 开发人员的目标。