PL/SQL Tables
Features of PL/SQL tables are as follows –
1) It is a composite data type.
2) They are modeled as similar to database tables, but they are not permanent tables. So they can be created and manipulated only in a PL SQL block.
3) They can have only one column but any data type
4) It will have a primary key which is compulsory for the reference of values
5) There is no name to the column and primary key
6) The data type of the primary key is BINARY_INTEGER.
BINARY_INTEGER is a special data type which can be given only to the column of PL/SQL table for it’s indexing purpose to store and retrieve values.
Range of binary_integer is -2147483647 to 2147483647
7) Size is unconstrained (Table size grows as the rows are added to the table).
8) Can visualize a Pl/SQL table as a single dimensional vertical array, which can hold unlimited elements.
Suitable for storing and displaying the values of one column of a table given by a cursor.
Example of PL SQL Table –
Each name from the emp table is given to the vname plsql table by using cursor. Then those names from vname table are displayed .
Declare
Type nametable IS TABLE OF CHAR(10) INDEX BY BINARY_INTEGER;
/*Creating variable vname of nametable type.*/
vname nametable;
Cursor cf is select ename from emp;
i number;
/*i is for the loop and vrows is for displaying the total names from the vname table*/
Begin
Open cf;
i := 1;
Loop
Fetch cf into vname(i); /*Transferring each ename into vname table*/
Exit when cf%NotFound;
i := i 1;
End Loop;
Close cf;
/*Now retrieving the names from the vname plsql table using for loop.*/
For n in 1 .. vname.count
Loop
dbms_output.put_line('Name is '||vname(n));
End Loop;
End;
Properties of a PL SQL table ---
· Exists
· Count
· First
· Last
· Next
· Prior
· Delete
declare
Type discount is TABLE OF number INDEX By Binary_Integer;
d discount;
Begin
d(5) := 90;
d(2) := 50;
d(8) := 70;
d(11) := 67;
d(14) := 68;
d(1) := 1;
d(23) := 5;
d(23) := 51;
dbms_output.put_line('The value at 23 index number is ' || d(23));
dbms_output.put_line('The value at index number 6 is ' || d(6));
/* if d.EXISTS(6) Then
dbms_output.put_line(d(6));
else
dbms_output.put_line('There is no element in the sixth row');
end if;
dbms_output.put_line('The total number of elements in d are '|| d.count);
dbms_output.put_line('The first index number is ' || d.FIRST);
dbms_output.put_line('The last index number is ' || d.LAST);
dbms_output.put_line('The index number after 2 is ' || d.next(2));
dbms_output.put_line('The index number before 8 is ' || d.prior(8));
d.delete(5);
dbms_output.put_line('The total number of elements in d are '|| d.count);
d.delete(11,14);
dbms_output.put_line('The total number of elements in d are '|| d.count);
d.delete;
dbms_output.put_line('The total number of elements in d are '|| d.count); */
end;
/*Table made of record type */
declare
type r is record (a number, b number);
type t is table of r index by binary_integer;
v t;
v1 r;
begin
v1.a := 89;
v1.b := 9;
v(1) := v1;
dbms_output.put_line(v(1).a);
end;
or
declare
type r is record (a number, b number);
type t is table of r index by binary_integer;
v t;
--v1 r;
begin
-- v1.a := 89;
--v1.b := 9;
v(1).a := 8;
dbms_output.put_line(v(1).a);
end;
/
Discussion about this article
| Author | Body |
admin
8/24/2009 2:05 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|