Mega Code Archive
An anonymous PLSQL procedure to demonstrate the elementary use of PLSQL tables
<![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 TYPE name_table IS TABLE OF employee%ROWTYPE
3 INDEX BY BINARY_INTEGER;
4
5 t_name name_table;
6
7 n_name binary_integer;
8
9 begin
10 t_name(1).name := 'AAA';
11 t_name(10).name := 'BBB';
12
13 dbms_output.put_line(t_name(1).name);
14 dbms_output.put_line(t_name(10).name);
15
16 dbms_output.put_line('There are '||t_name.count()||' elements.');
17 n_name := t_name.first();
18
19 dbms_output.put_line('The first element is '||n_name||'.');
20 n_name := t_name.next(n_name);
21
22 dbms_output.put_line('The next element is '||n_name||'.');
23 n_name := t_name.last();
24
25 dbms_output.put_line('The last element is '||n_name||'.');
26 n_name := t_name.prior(n_name);
27
28 dbms_output.put_line('The prior element is '||n_name||'.');
29 if t_name.exists(1) then
30 dbms_output.put_line('Element 1 exists.');
31 end if;
32
33 dbms_output.put_line('I''m deleting element 10');
34
35 t_name.delete(10);
36
37 dbms_output.put_line('There are '||t_name.count()||' elements.');
38
39 if not t_name.exists(10) then
40 dbms_output.put_line('Element 10 no longer exists.');
41 end if;
42
43 dbms_output.put_line('There are '||t_name.count()||' elements.');
44
45 dbms_output.put_line('I''m deleting all elements');
46
47 t_name.delete();
48
49 dbms_output.put_line('There are '||t_name.count()||' elements.');
50 end;
51 /
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>
]]>