oracle PL/SQL 中的类映射对象类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/996386/
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
Map-like object type in PL/SQL?
提问by mamboking
I want to write a map-like object type in PL/SQL. What I mean is a key-value-pair list, where a value can be another key-value-pair list. Simple, or so I thought. Here are the two simplified
我想在 PL/SQL 中编写一个类似于地图的对象类型。我的意思是一个键值对列表，其中一个值可以是另一个键值对列表。很简单，或者我是这么想的。这是两个简化的
CREATE OR REPLACE TYPE TKey AS OBJECT
(
name varchar2(240),
value_text varchar2(2000),
value_map TMap
)
CREATE OR REPLACE TYPE TMap AS TABLE OF TKey
Maybe not so simple, because now I have a "Chicken or the egg"-problem. If I put TKey in first, he will complain that TMap is not defined. If I put in TMap first, he will complain that TKey is not defined. If I put TKey in and leave out the value_map line, then add the TMap type, then try to replace the TKey type, he will not allow it.
也许不是那么简单，因为现在我有一个“先有鸡还是先有蛋”的问题。如果我先把 TKey 放进去，他会抱怨 TMap 没有定义。如果我先放入 TMap，他会抱怨 TKey 没有定义。如果我把TKey放进去，把value_map这行去掉，然后加上TMap类型，然后尝试替换TKey类型，他不会允许的。
Am I out of luck? Is such a construct not possible in PL/SQL?
我倒霉了吗？这样的构造在 PL/SQL 中是不可能的吗？
Thanks in advance
提前致谢
Clarification: What I want is something, that can give me this: A map containing the keys a, b, and c. The value of a is the varchar "hello", the value of b is the varchar "world", and the value of c is another map, with the keys x and y, and the value of x being "what's" and the value of y being "up".
澄清：我想要的是一些东西，它可以给我：一个包含键 a、b 和 c 的映射。a 的值是 varchar“hello”，b 的值是 varchar“world”，c 的值是另一个映射，键为 x 和 y，x 的值是“what's”和值y 处于“向上”状态。
This is what it would look like in Java:
这就是它在 Java 中的样子：
Map<String, Object> map = new HashMap<String, Object>();
map.set("a", "Hello");
map.set("b", "World");
Map<String, Object> child = new HashMap<String, Object>();
child.set("x", "What's");
child.set("y", "up");
map.set("c", child);
Now I know that something like "object" as being able to store anything is not possible. What I need is an object that can store a list of objects being of the same type as this object. So basically, a tree, yeah.
现在我知道像“对象”这样能够存储任何东西是不可能的。我需要的是一个对象，它可以存储与该对象具有相同类型的对象列表。所以基本上，一棵树，是的。
采纳答案by Jeffrey Kemp
Perhaps you need to think more relationally :)
也许你需要更相关地思考:)
Instead of storing a TMap (value_map) inside the TKey type, just store value_map_name, which can then be used to lookup another entry in the table.
而不是value_map在 TKey 类型中存储 TMap ( ) ，只需 store value_map_name，然后可以使用它来查找表中的另一个条目。
CREATE OR REPLACE TYPE TKey AS OBJECT(
name varchar2(240),
value_text varchar2(2000),
value_map_name varchar2(240));
Then you can use associative arrays (as per mamboking's answer) to store them.
然后您可以使用关联数组（根据 mamboking 的回答）来存储它们。
回答by mamboking
You can use associative arrays. From the PL/SQL User guide:
您可以使用关联数组。从 PL/SQL 用户指南：
Understanding Associative Arrays (Index-By Tables)
了解关联数组（Index-By Tables）
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
关联数组是一组键值对，其中每个键都是唯一的，用于在数组中定位相应的值。键可以是整数或字符串。
Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique. For example, key values might come from the primary key of a database table, from a numeric hash function, or from concatenating strings to form a unique string value.
第一次使用键分配值会将该键添加到关联数组。使用相同键的后续分配更新相同的条目。选择唯一的密钥很重要。例如，键值可能来自数据库表的主键、数字散列函数或连接字符串以形成唯一的字符串值。
For example, here is the declaration of an associative array type, and two arrays of that type, using keys that are strings:
例如，这里是一个关联数组类型和该类型的两个数组的声明，使用字符串作为键：
Example 5-1 Declaring Collection Types
示例 5-1 声明集合类型
DECLARE TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;
which VARCHAR2(64);
BEGIN
country_population('Greenland') := 100000; -- Creates new entry
country_population('Iceland') := 750000; -- Creates new entry
-- Looks up value associated with a string
howmany := country_population('Greenland');
continent_population('Australia') := 30000000;
continent_population('Antarctica') := 1000; -- Creates new entry
continent_population('Antarctica') := 1001; -- Replaces previous value
-- Returns 'Antarctica' as that comes first alphabetically.
which := continent_population.FIRST;
-- Returns 'Australia' as that comes last alphabetically. which := continent_population.LAST;
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
howmany := continent_population(continent_population.LAST);
END;
/
回答by Andrew not the Saint
You're trying to squeeze a key-value paradigm into a relational DBMS which doesn't make logical sense. It'd be much easier to go pure relational:
您试图将键值范式压缩到没有逻辑意义的关系 DBMS 中。纯粹的关系会容易得多：
CREATE TABLE key_value AS
(
key varchar2(240), -- PRIMARY KEY
value_text varchar2(2000)
);
CREATE TABLE key_hierarchy AS
(
child_key varchar2(240), -- PRIMARY KEY, FOREIGN KEY to key_value.key
parent_key varchar2(240) -- FOREIGN KEY to key_value.key
);
And that's it! If you want to change later on that a child can have many parents, just change the PK constraint (beauty of relational DBMSs)
就是这样！如果您想稍后更改一个孩子可以有多个父母，只需更改 PK 约束（关系 DBMS 的美）
