编写pl sql 数组类型时遇到的问题-优快云博客

本文探讨了PL/SQL中表与数组的不同之处，详细解释了两种类型的声明方式及其实现方法。通过实例对比了嵌套表类型与关联数组在初始化、使用等方面的差异，并总结了它们在数据类型支持、大小限制、数据库存储能力等方面的特点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE FUNCTION SPLIT1
(
P_LIST VARCHAR2,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL
IS
l_idx PLS_INTEGER;
l_list VARCHAR2(32767) := P_LIST;
v_table SPLIT_TBL [color=red]:= SPLIT_TBL()[/color]; [color=green]少了红色代码会报错ORA-06531[/color]
v_temp PLS_INTEGER := 1;
BEGIN
LOOP
l_idx := INSTR(l_list,p_del);
IF l_idx > 0 THEN
[color=red]v_table.extend;[/color]
v_table(v_temp) := SUBSTR(l_list,1,l_idx-1);
l_list := SUBSTR(l_list,l_idx+LENGTH(P_DEL));
v_temp := v_temp + 1;
ELSE
v_table.extend;
v_table(v_temp) := l_list;
EXIT;
END IF;
END LOOP;
RETURN v_table;
END;

--------------------------------------------------------------------------------------------------------------------------------------------
[size=x-large]问题[/size]：
[color=brown]difference between array and plsql table？？？[/color]

TYPE array_typ IS TABLE OF VARCHAR2 (4000);

TYPE pltab_typ IS TABLE OF VARCHAR2 (4000)
INDEX BY BINARY_INTEGER;

wat is the diff between the two types of declaration?
the web site says the first one is array and the second one is a plsql table and not an array...

[size=x-large]回答1[/size]：
[color=brown]The first one is a nested table type. It needs to be initialized explicitly.[/color]

SQL> declare
2 type t is table of number;
3 l t;
4 begin
5 l(1):=1;
6 end;
7 /
[color=blue]declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5[/color]

SQL> declare
2 type t is table of number;
3 l t [color=red]:= t()[/color];
4 begin
5 [color=red]l.extend;[/color]
6 l(1):=1;
7 end;
8 /
[color=blue]PL/SQL procedure successfully completed.[/color]

[color=brown]The other one is a associative array which need not be initialized.[/color]

SQL> declare
2 type t is table of number index by pls_integer;
3 l t;
4 begin
5 l(1):=1;
6 end;
7 /
[color=blue]PL/SQL procedure successfully completed.[/color]

[size=x-large]回答2[/size]：
there are lot of differences between pl/sql table and arrays.

1.array is set of values of same datatype.. where as tables can store values of diff datatypes.. also tables has no upper limit where as arrays has.

2. PL/SQL tables are temporary array like objects used in a PL/SQL Block. The size of pl/sql table is unconstrained. For varrays we need to specify upperbound.

3. Pl/sql tables can not be stored in database where as varrays can be stored in database

4. We can use negetive index for pl/sql tables. In varrays negetive index is not allowed

5. You can not perform DML operations on PL/SQL table . DML operations can be performed on Varrays.