编写pl sql 数组类型时遇到的问题
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 := SPLIT_TBL(); 少了红色代码会报错ORA-06531
v_temp PLS_INTEGER := 1;
BEGIN
LOOP
l_idx := INSTR(l_list,p_del);
IF l_idx > 0 THEN
v_table.extend;
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;
--------------------------------------------------------------------
问题：
difference between array and plsql table？？？
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...
回答1：
The first one is a nested table type. It needs to be initialized explicitly.
SQL> declare
2 type t is table of number;
3 l t;
4 begin
5 l(1):=1;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5
SQL> declare
2 type t is table of number;
3 l t := t();
4 begin
5 l.extend;
6 l(1):=1;
7 end;
8 /
PL/SQL procedure successfully completed.
The other one is a associative array which need not be initialized.
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 /
PL/SQL procedure successfully completed.
回答2：
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.
