PLSQL-Code-Samples/9. Varrays (Code Samples).html at main · sumaiya1996/PLSQL-Code-Samples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
<pre class="prettyprint linenums">--------------------------------------------------------------------------------------------------------------------
---------------------------------------------------VARRAYS----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------

---------------A simple working example
Declare
  type e_list is varray(5) of varchar2(50);
  employees e_list;
begin
  employees := e_list('Alex','Bruce','John','Bob','Richard');
  for i in 1..5 loop
    dbms_output.put_line(employees(i));
  end loop;
end;
---------------limit exceeding error example
declare
  type e_list is varray(4) of varchar2(50);
  employees e_list;
begin
  employees := e_list('Alex','Bruce','John','Bob','Richard');
  for i in 1..5 loop
    dbms_output.put_line(employees(i));
  end loop;
end;
---------------Subscript beyond cound error example
declare
  type e_list is varray(5) of varchar2(50);
  employees e_list;
begin
  employees := e_list('Alex','Bruce','John','Bob');
  for i in 1..5 loop
    dbms_output.put_line(employees(i));
  end loop;
end;
---------------A working count() example
declare
  type e_list is varray(5) of varchar2(50);
  employees e_list;
begin
  employees := e_list('Alex','Bruce','John','Bob');
  for i in 1..employees.count() loop
    dbms_output.put_line(employees(i));
  end loop;
end;
---------------A working first() last() functions example
declare
  type e_list is varray(5) of varchar2(50);
  employees e_list;
begin
  employees := e_list('Alex','Bruce','John','Bob');
  for i in employees.first()..employees.last() loop
    dbms_output.put_line(employees(i));
  end loop;
end;
--------------- A working exists() function example
declare
  type e_list is varray(5) of varchar2(50);
  employees e_list;
begin
  employees := e_list('Alex','Bruce','John','Bob');
  for i in 1..5 loop
    if employees.exists(i) then
      dbms_output.put_line(employees(i));
    end if;
  end loop;
end;
---------------A working limit() function example
declare
  type e_list is varray(5) of varchar2(50);
  employees e_list;
begin
  employees := e_list('Alex','Bruce','John','Bob');
      dbms_output.put_line(employees.limit());
end;
--------------- A create-declare at the same time error example
declare
  type e_list is varray(5) of varchar2(50);
  employees e_list('Alex','Bruce','John','Bob');
begin
 -- employees := e_list('Alex','Bruce','John','Bob');
  for i in 1..5 loop
    if employees.exists(i) then
      dbms_output.put_line(employees(i));
    end if;
  end loop;
end;
--------------- A post insert varray example
declare
  type e_list is varray(15) of varchar2(50);
  employees e_list := e_list();
  idx number := 1;
begin
  for i in 100..110 loop
    employees.extend;
    select first_name into employees(idx) from employees where employee_id = i;
    idx := idx + 1;
  end loop;
  for x in 1..employees.count() loop
    dbms_output.put_line(employees(x));
  end loop;
end;
--------------- An example for the schema level varray types
create type e_list is varray(15) of varchar2(50);
/
create or replace type e_list as varray(20) of varchar2(100);
/
declare
  employees e_list := e_list();
  idx number := 1;
begin
  for i in 100..110 loop
    employees.extend;
    select first_name into employees(idx) from employees where employee_id = i;
    idx := idx + 1;
  end loop;
  for x in 1..employees.count() loop
    dbms_output.put_line(employees(x));
  end loop;
end;
/
DROP TYPE E_LIST;</pre>