Oracle PLSQL Tables
- PLSQL tables are composite datatypes.
- These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend.
- They are also called index by table
- PLSQL Table contains two elements
(1) A primary key of BINARY_INTEGER datatypes that index the table, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.
we are able to index by a string value also
(2) A column of scalar or record datatype which stores the index by table elements
How to define and Declare Table type
To create PL/SQL tables, you take two steps. First, you define a TABLE type, then declare PL/SQL tables of that type. You can define TABLE types in the declarative part of any block, subprogram, or package using the syntax
Syntax
TYPE table_type_name IS TABLE OF datatype [NOT NULL] INDEX BY BINARY_INTEGER;
If the element type is a record type, every field in the record must have a scalar datatype such as CHAR, DATE, or NUMBER.
To specify the element type, you can use %TYPE to provide the data type of a variable or database column
DECLARE TYPE DEPTTabTyp IS TABLE OF dept.dept_name%TYPE INDEX BY BINARY_INTEGER;
You can add the NOT NULL constraint to a TABLE type definition and so prevent the storing of nulls in PL/SQL tables of that type:
DECLARE TYPE DEPTTabTyp IS TABLE OF dept.location%TYPE NOT NULL INDEX BY BINARY_INTEGER;
You can also use %ROWTYPE to specify the element type.
DECLARE TYPE deptTabTyp IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
you use a User define RECORD type to specify the element type:
DECLARE TYPE emp_phonetyp IS RECORD ( Stdcode PLS_INTEGER, phn_number PLS_INTEGER, extension PLS_INTEGER ); TYPE emp_contact IS TABLE OF emp_phonetyp INDEX BY BINARY_INTEGER;
After you define a TABLE type, you can declare PL/SQL tables of that type
DECLARE TYPE SalTabTyp IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER; TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; sal_tab SalTabTyp; -- declare PL/SQL table emp_tab EmpTabTyp; -- declare another PL/SQL table
PLSQL Table attributes or Method
A PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE.
They make PL/SQL tables easier to use and your applications easier to maintain.
| plsql_table_name.attribute_name |
The attributes exists, PRIOR, NEXT, and DELETE take parameters. Each parameter must be an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype.
DELETE acts like a procedure, which is called as a statement. However, the other PL/SQL table attributes act like a function, which is called as part of an expression.
| Exists(n) | Return true if nth element in the table exists |
| count | Return the number of elements in the table |
| First and last | FIRST and LAST return the first and last (smallest and largest) index numbers in a PL/SQL table |
| PRIOR(n) | returns the index number that precedes index n in a PL/SQL table |
| NEXT(n) | returns the index number that succeeds index n |
| Delete | DELETE removes all elements from a PL/SQL table. DELETE(n) removes the nth element. If n is null, DELETE(n) does nothing. DELETE(m, n) removes all elements in the range m .. n. If m is larger than n or if m or n is null, DELETE(m, n) does nothing |
PL/SQL tables to move bulk data into and out of database tables or between client-side applications and stored subprograms.
How to populate the data in PLSQL Table
Tables with simple datatypes can be populated as:
<variable>(<integer>) := <value>;
Tables with complex datatypes will need the columns populated individually as:
<variable>(<integer>).<column_name> := <value>;
Or from a cursor:
fetch <cursor_name> into <variable>(<integer>);
Type emptabletype is table of varchar2(10)
Index by binary_integer;
emp_table emptabletyp;
emp_table (1) := ‘good’;
emp_table (2) := ‘bad’’;
You can retrieve Oracle data into a PL/SQL table in other three ways also
a)SELECT INTO statement lets you select a single row of data
Using the SELECT INTO statement, you can select a column entry into a scalar element. Or, you can select an entire row into a record element. In the following example, you select a row from the database table dept into a record stored by the first element of the PL/SQL table dept_tab:
DECLARE
TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_tab DeptTabTyp;
BEGIN
/* Select entire row into record stored by first element. */
SELECT * INTO dept_tab(1) FROM dept WHERE deptno = 10;
IF dept_tab(1).dname = 'ACCOUNTING' THEN ...
...
END;
b) FETCH statement
With the FETCH statement, you can fetch an entire column of Oracle data into a PL/SQL table of scalars.
Or you can fetch an entire table of Oracle data into a PL/SQL table of records.
DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
i BINARY_INTEGER := 0;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
OPEN c1;
LOOP
i := i + 1;
/* Fetch entire row into record stored by ith element. */
FETCH c1 INTO emp_tab(i);
EXIT WHEN c1%NOTFOUND;
-- process data record
END LOOP;
CLOSE c1;
END;
c) cursor FOR loop lets you fetch multiple rows.
DECLARE
TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
ename_tab EnameTabTyp;
sal_tab SalTabTyp;
n BINARY_INTEGER := 0;
BEGIN
/* Fetch entire columns into PL/SQL tables. */
FOR emp_rec IN (SELECT ename, sal FROM emp) LOOP
n := n + 1;
ename_tab(n) := emp_rec.ename;
sal_tab(n) := emp_rec.sal;
END LOOP;
...
END;
You must read these other PLSQL posts
Oracle PLSQL Block Structure and Types
Oracle PLSQL records
Oracle plsql interview questions
Quick Oracle sql and plsql tutorials