Oracle PL/SQL - 如何创建一个简单的数组变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7012625/
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
Oracle PL/SQL - How to create a simple array variable?
提问by contactmatt
I'd like to create an in-memory array variable that can be used in my PL/SQL code. I can't find any collections in Oracle PL/SQL that uses pure memory, they all seem to be associated with tables. I'm looking to do something like this in my PL/SQL (C# syntax):
我想创建一个可在我的 PL/SQL 代码中使用的内存中数组变量。我在 Oracle PL/SQL 中找不到任何使用纯内存的集合，它们似乎都与表相关联。我希望在我的 PL/SQL（C# 语法）中做这样的事情：
string[] arrayvalues = new string[3] {"Matt", "Joanne", "Robert"};
Edit:Oracle: 9i
编辑：甲骨文：9i
回答by Tony Andrews
You can use VARRAY for a fixed-size array:
您可以将 VARRAY 用于固定大小的数组：
declare
type array_t is varray(3) of varchar2(10);
array array_t := array_t('Matt', 'Joanne', 'Robert');
begin
for i in 1..array.count loop
dbms_output.put_line(array(i));
end loop;
end;
Or TABLE for an unbounded array:
或 TABLE 对于无界数组：
...
type array_t is table of varchar2(10);
...
The word "table" here has nothing to do with database tables, confusingly. Both methods create in-memory arrays.
这里的“表”一词与数据库表无关，令人困惑。这两种方法都创建内存数组。
With either of these you need to both initialise and extend the collection before adding elements:
使用其中任何一个，您都需要在添加元素之前初始化和扩展集合：
declare
type array_t is varray(3) of varchar2(10);
array array_t := array_t(); -- Initialise it
begin
for i in 1..3 loop
array.extend(); -- Extend it
array(i) := 'x';
end loop;
end;
The first index is 1 not 0.
第一个索引是 1 而不是 0。
回答by Ollie
You could just declare a DBMS_SQL.VARCHAR2_TABLE to hold an in-memory variable length array indexed by a BINARY_INTEGER:
你可以声明一个 DBMS_SQL.VARCHAR2_TABLE 来保存一个由 BINARY_INTEGER 索引的内存可变长度数组：
DECLARE
name_array dbms_sql.varchar2_table;
BEGIN
name_array(1) := 'Tim';
name_array(2) := 'Daisy';
name_array(3) := 'Mike';
name_array(4) := 'Marsha';
--
FOR i IN name_array.FIRST .. name_array.LAST
LOOP
-- Do something
END LOOP;
END;
You could use an associative array (used to be called PL/SQL tables) as they are an in-memory array.
您可以使用关联数组（以前称为 PL/SQL 表），因为它们是内存中的数组。
DECLARE
TYPE employee_arraytype IS TABLE OF employee%ROWTYPE
INDEX BY PLS_INTEGER;
employee_array employee_arraytype;
BEGIN
SELECT *
BULK COLLECT INTO employee_array
FROM employee
WHERE department = 10;
--
FOR i IN employee_array.FIRST .. employee_array.LAST
LOOP
-- Do something
END LOOP;
END;
The associative array can hold any make up of record types.
关联数组可以保存任何组成的记录类型。
Hope it helps, Ollie.
希望它有帮助，奥利。
回答by Jika
You can also use an oracle defined collection
您还可以使用 oracle defined collection
DECLARE
arrayvalues sys.odcivarchar2list;
BEGIN
arrayvalues := sys.odcivarchar2list('Matt','Joanne','Robert');
FOR x IN ( SELECT m.column_value m_value
FROM table(arrayvalues) m )
LOOP
dbms_output.put_line (x.m_value||' is a good pal');
END LOOP;
END;
I would use in-memory array. But with the .COUNTimprovement suggested by uziberia:
我会使用内存数组。但随着.COUNTuziberia 提出的改进：
DECLARE
TYPE t_people IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
arrayvalues t_people;
BEGIN
SELECT *
BULK COLLECT INTO arrayvalues
FROM (select 'Matt' m_value from dual union all
select 'Joanne' from dual union all
select 'Robert' from dual
)
;
--
FOR i IN 1 .. arrayvalues.COUNT
LOOP
dbms_output.put_line(arrayvalues(i)||' is my friend');
END LOOP;
END;
Another solution would be to use a Hashmap like @Jchomel did here.
另一种解决方案是使用 Hashmap 就像 @Jchomel 在这里所做的那样。
NB:
注意：
With Oracle 12c you can even query arrays directly now!
回答by J. Chomel
Another solution is to use an Oracle Collection as a Hashmap:
另一种解决方案是使用 Oracle 集合作为 Hashmap：
declare
-- create a type for your "Array" - it can be of any kind, record might be useful
type hash_map is table of varchar2(1000) index by varchar2(30);
my_hmap hash_map ;
-- i will be your iterator: it must be of the index's type
i varchar2(30);
begin
my_hmap('a') := 'apple';
my_hmap('b') := 'box';
my_hmap('c') := 'crow';
-- then how you use it:
dbms_output.put_line (my_hmap('c')) ;
-- or to loop on every element - it's a "collection"
i := my_hmap.FIRST;
while (i is not null) loop
dbms_output.put_line(my_hmap(i));
i := my_hmap.NEXT(i);
end loop;
end;
回答by sudhirkondle
Sample programs as follows and provided on link also https://oracle-concepts-learning.blogspot.com/
示例程序如下并在链接上提供https://oracle-concepts-learning.blogspot.com/
plsql table or associated array.
plsql 表或关联数组。
DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish') := 62000; salary_list('Minakshi') := 75000;
salary_list('Martin') := 100000; salary_list('James') := 78000;
-- printing the table name := salary_list.FIRST; WHILE name IS NOT null
LOOP
dbms_output.put_line ('Salary of ' || name || ' is ' ||
TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;
/
