Mega Code Archive
Use of PLSQL tables of types
<![CDATA[
SQL>
SQL> create table employee (
2 id number not null,
3 employee_type_id number not null,
4 external_id varchar2(30) not null,
5 first_name varchar2(30) not null,
6 middle_name varchar2(30),
7 last_name varchar2(30) not null,
8 name varchar2(100) not null,
9 birth_date date not null,
10 gender_id number not null );
Table created.
SQL>
SQL> declare
2
3
4 TYPE name_table IS TABLE OF employee.name%TYPE INDEX BY BINARY_INTEGER;
5
6 t_name name_table;
7
8 n_name binary_integer;
9
10 begin
11 t_name(1) := 'AAA';
12 t_name(10) := 'BBB';
13
14 DBMS_OUTPUT.PUT_LINE(t_name(1));
15 DBMS_OUTPUT.PUT_LINE(t_name(10));
16
17 DBMS_OUTPUT.PUT_LINE('There are '||t_name.count()||' elements.');
18 n_name := t_name.first();
19
20 DBMS_OUTPUT.PUT_LINE('The first element is '||n_name||'.');
21 n_name := t_name.next(n_name);
22
23 DBMS_OUTPUT.PUT_LINE('The next element is '||n_name||'.');
24 n_name := t_name.last();
25
26 DBMS_OUTPUT.PUT_LINE('The last element is '||n_name||'.');
27 n_name := t_name.prior(n_name);
28
29 DBMS_OUTPUT.PUT_LINE('The prior element is '||n_name||'.');
30
31 if t_name.exists(1) then
32 DBMS_OUTPUT.PUT_LINE('Element 1 exists.');
33 end if;
34
35 DBMS_OUTPUT.PUT_LINE('I''m deleting element 10');
36
37 t_name.delete(10);
38
39 DBMS_OUTPUT.PUT_LINE('There are '||t_name.count()||' elements.');
40
41 if not t_name.exists(10) then
42 DBMS_OUTPUT.PUT_LINE('Element 10 no longer exists.');
43 end if;
44
45 DBMS_OUTPUT.PUT_LINE('There are '||t_name.count()||' elements.');
46
47 DBMS_OUTPUT.PUT_LINE('I''m deleting all elements');
48
49 t_name.delete();
50
51 DBMS_OUTPUT.PUT_LINE('There are '||t_name.count()||' elements.');
52
53 end;
54 /
AAA
BBB
There are 2 elements.
The first element is 1.
The next element is 10.
The last element is 10.
The prior element is 1.
Element 1 exists.
I'm deleting element 10
There are 1 elements.
Element 10 no longer exists.
There are 1 elements.
I'm deleting all elements
There are 0 elements.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
]]>