в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:28Z

1 версия

Новая страница

== Accessing VARRAY elements==



<source lang="sql">
SQL> declare
2 Type numberVarray is VARRAY(5)OF NUMBER;
3 v_numvarray numberVarray;
4 begin
5 v_numvarray :=numberVarray(10,20,30,40);
6
7
8 dbms_output.put_line(to_char(v_numvarray(1))||", "||to_char(v_numvarray(2))||", "||to_char(v_numvarray(3))||", "||to_char(v_numvarray(4)));
9
10 v_numvarray(4):=60;
11
12 dbms_output.put_line(to_char(v_numvarray(1))||", "||to_char(v_numvarray(2))||", "||to_char(v_numvarray(3))||", "||to_char(v_numvarray(4)));
13 end;
14 /
10, 20, 30, 40
10, 20, 30, 60
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Defining a VARRAY==



<source lang="sql">
SQL>
SQL> DECLARE
2
3 TYPE numberVarray IS VARRAY(5)OF NUMBER;
4
5 v_numvarray numberVarray;
6
7 BEGIN
8
9 /*Do some processing */
10
11 null;
12
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Initializing a VARRAY==



<source lang="sql">
SQL>
SQL> DECLARE
2
3 TYPE numberVarray IS VARRAY(5)OF NUMBER;
4
5 v_numvarray numberVarray :=numberVarray(10,20,30,40);
6
7 BEGIN
8
9 dbms_output.put_line("The first element of v_numvarray is "|| TO_CHAR(v_numvarray(1)));
10 END;
11 /
The first element of v_numvarray is 10
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Is a varray null value==



<source lang="sql">
SQL>
SQL> declare
2 Type numberVarray is VARRAY(5)OF NUMBER;
3
4 v_numvarray1 numberVarray;
5
6 begin
7
8 if v_numvarray1 IS NULL then
9
10 dbms_output.put_line("v_numvarray1 is null");
11
12 end if;
13
14 if v_numvarray1(1) IS NULL then
15
16 dbms_output.put_line("The first element of v_numvarray1 is null");
17
18 end if;
19
20 end;
21 /
v_numvarray1 is null
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 14</source>

== Multilevel Collections==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE TYPE varray_one IS VARRAY(10)OF NUMBER;
2 /
Type created.
SQL> CREATE OR REPLACE TYPE varray_one_nested IS VARRAY(10)OF varray_one;
2 /
Type created.
SQL> CREATE OR REPLACE TYPE varray_two_nested IS VARRAY(10)OF varray_one_nested;
2 /
Type created.
SQL>
SQL> DECLARE
2 v1 varray_one;
3 v2 varray_one_nested;
4 v3 varray_two_nested;
5 BEGIN
6 v1 :=varray_one(1,2,3);
7
8 v2 :=varray_one_nested(varray_one(11,12,13),varray_one(21,22,23));
9
10 v3 :=varray_two_nested(
11 varray_one_nested(varray_one(111,112,113),varray_one(121,122,123)),
12 varray_one_nested(varray_one(211,212,213),varray_one(221,222,223)));
13
14 for i in 1..v1.count loop
15
16 dbms_output.put_line(v1(i));
17
18 end loop;
19
20 for i in 1..v2.count loop
21
22 for j in 1..v2(i).count loop
23
24 dbms_output.put_line(v2(i)(j));
25
26 end loop;
27
28 end loop;
29
30 for i in 1..v3.count loop
31
32 for j in 1..v3(i).count loop
33
34 for k in 1..v3(i)(j).count loop
35
36 dbms_output.put_line(v3(i)(j)(k));
37
38 end loop;
39
40 end loop;
41
42 end loop;
43
44 END;
45 /
1
2
3
11
12
13
21
22
23
111
112
113
121
122
123
211
212
213
221
222
223
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop type varray_one force;
Type dropped.
SQL>
SQL> drop type varray_one_nested force;
Type dropped.
SQL>
SQL> drop type varray_two_nested force;
Type dropped.
SQL>
SQL></source>

== NULL VARRAYS and NULL Elements==



<source lang="sql">
SQL>
SQL> declare
2 Type numberVarray is VARRAY(5)OF NUMBER;
3 v_numvarray numberVarray;
4 begin
5
6 if v_numvarray IS NULL then
7 dbms_output.put_line("v_numvarray is null");
8 end if;
9
10 v_numvarray :=NULL;
11
12 if v_numvarray IS NULL then
13 dbms_output.put_line("v_numvarray is null after assignment");
14 end if;
15 end;
16 /
v_numvarray is null
v_numvarray is null after assignment
PL/SQL procedure successfully completed.
SQL></source>

== Pass NULL value to varray==



<source lang="sql">
SQL>
SQL> declare
2 Type numberVarray is VARRAY(5)OF NUMBER;
3 v_numvarray2 numberVarray;
4 begin
5 v_numvarray2 :=numberVarray(NULL);
6
7 if v_numvarray2 IS NULL then
8
9 dbms_output.put_line("v_numvarray2 is null");
10
11 else
12
13 dbms_output.put_line("v_numvarray2 is not null");
14
15 end if;
16
17 if v_numvarray2(1) IS NULL then
18
19 dbms_output.put_line("The first element of v_numvarray2 is null");
20
21 end if;
22 end;
23 /
v_numvarray2 is not null
The first element of v_numvarray2 is null
PL/SQL procedure successfully completed.
SQL>
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Collections/Varray of Number - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия