编写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.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值