oracle “按表索引”和“按记录表索引”有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6004910/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the difference between "index by table" and "index by table of records"?
提问by Marty
I've come across both terms, but they sound synonymous. Is there a distinction between the two?
我遇到过这两个术语，但它们听起来是同义词。两者之间有区别吗？
回答by eaolson
An "index-by table" is Oracle's term for "associative array". These are arrays that contain elements that you can address (or index by) either an integer or string. They're probably called that because of the use of the INDEX BY keywords when defining the array.
“索引表”是 Oracle 对“关联数组”的术语。这些数组包含可以寻址（或索引）整数或字符串的元素。之所以这么称呼它们，可能是因为在定义数组时使用了 INDEX BY 关键字。
An abbreviation of the example given in the Oracle documentation:
Oracle 文档中给出的示例的缩写：
DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
howmany NUMBER;
BEGIN
country_population('Greenland') := 100000; -- Creates new entry
howmany := country_population('Greenland');
...
You can create index-by tables containing records, where records are essentially a structure containing multiple types. A record, for example, often contains the same types as a row in a table.
您可以创建包含记录的索引表，其中记录本质上是一个包含多种类型的结构。例如，记录通常包含与表中的行相同的类型。
Again, from the Oracle documentation:
同样，来自Oracle 文档：
DECLARE
TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
emp_tab EmpTabTyp;
BEGIN
/* Retrieve employee record. */
SELECT * INTO emp_tab(100) FROM employees
WHERE employee_id = 100;
END;
Here, emp_tab is an index-by table, indexed by integers, containing records of employees%ROWTYPE.
这里，emp_tab 是一个索引表，以整数索引，包含雇员%ROWTYPE 的记录。
回答by Datajam
I'm not sure where you got the phrases from, but TABLE OFand INDEX BYare separate parts of a collection type declaration. TABLE OFdefines the type of a collection's field(s), which can be a datatype (i.e. TABLE OF NUMBER) or a record type (i.e. TABLE OF MY_TABLE%TYPE).
我不知道你在哪里得到了来自短语，但TABLE OF并INDEX BY是一个集合类型声明的不同部分。TABLE OF定义集合字段的类型，它可以是数据类型（即TABLE OF NUMBER）或记录类型（即TABLE OF MY_TABLE%TYPE）。
INDEX BYrefers to the method of looking up this collection, almost like a key-value pair. For example, I might use INDEX BY VARCHAR2(10)so that I can use a textual key to retrieve a value from the collection type.
INDEX BY指的是查找这个集合的方法，跟key-value对差不多。例如，我可能会使用INDEX BY VARCHAR2(10)以便我可以使用文本键从集合类型中检索值。
Here's an illustration:
这是一个插图：
DECLARE
TYPE my_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
n_my_value NUMBER;
BEGIN
my_type ('the key') := 99;
n_my_value := my_type ('the key');
END;
/
回答by Dayakark
Index by tables are user defined data types which is used to store multiple data items. Basically these tables are unconstrained tables. Index by table having two parts, these are value field and key field. In value field, oracle server stores actual data, where as in key field oracle server stores indexes, that's why index by table having ‘key value' pairs and also indexes are by default integers, and these indexes are in between negative to positive numbers. This index field behaves like a primary key, it doesn't accept duplicate values. Generally index by tables are used to improve the performance or application, because these tables are stored in memory area, that's why these tables are also called a memory tables. Generally to improve the performance of the application, these table indexes are using ‘binary-integer' data type, so we are creating in two step process. First we are creating type, then only we are creating a variable of that type.
按表索引是用户定义的数据类型，用于存储多个数据项。基本上这些表是不受约束的表。按表索引有两部分，它们是值字段和键字段。在 value 字段中，oracle server 存储实际数据，而在 key 字段中 oracle server 存储索引，这就是为什么按表索引具有“键值”对并且索引默认为整数，并且这些索引在负数到正数之间。此索引字段的行为类似于主键，它不接受重复值。通常使用表索引来提高性能或应用程序，因为这些表存储在内存区域中，这就是为什么这些表也被称为内存表。通常为了提高应用程序的性能，这些表索引都使用“二进制整数” 数据类型，所以我们分两步创建。首先我们创建类型，然后才创建该类型的变量。
Syntax:-
句法：-
Step1:- Type typename is table of data type (size) index by binary_integer; Step2:- Variablename typename;
Step1:- 类型 typename 是由 binary_integer 索引的数据类型（大小）表；Step2:- 变量名类型名；
For More Information >>>>http://www.oraappdata.com/2017/02/plsql-index-by-tables-or-plsql-tables-or-associative-arrays.html
更多信息>>>> http://www.oraappdata.com/2017/02/plsql-index-by-tables-or-plsql-tables-or-associative-arrays.html
