Oracle PL/SQL Collection Metarial

àTable Based records

declare

employee_rec emp%rowtype;

begin

select * into employee_rec from emp where empno=&a;

dbms_output.put_line('Employee ID     :     '||employee_rec.empno);

dbms_output.put_line('Employee Name   :     '||employee_rec.ename);

dbms_output.put_line('Employee Job    :     '||employee_rec.job);

dbms_output.put_line('Employee Salary :     '||employee_rec.sal);

end;

/

Enter value for eno: 7902

old   4: select * into employee_rec from emp where empno=&eno;

new   4: select * into employee_rec from emp where empno=7902;

Employee ID     :7902

Employee Name   :FORD

Employee Job    :ANALYST

Employee Salary :4600

àCursor based records

declare

cursor employee_cur is select empno,ename,job,sal from emp

where empno=&eno;

employee_rec employee_cur%rowtype;

begin

open employee_cur;

loop

fetch employee_cur into employee_rec;

exit when employee_cur%notfound;

dbms_output.put_line('Employee ID     :     '||employee_rec.empno);

dbms_output.put_line('Employee Name   :     '||employee_rec.ename);

dbms_output.put_line('Employee Job    :     '||employee_rec.job);

dbms_output.put_line('Employee Salary :     '||employee_rec.sal);

end loop;

end;

/

Enter value for eno: 7902

old   2: cursor employee_cur is select empno,ename,job,sal from emp where empno=&eno;

new   2: cursor employee_cur is select empno,ename,job,sal from emp where empno=7902;

Employee ID     :7902

Employee Name   :FORD

Employee Job    :ANALYST

Employee Salary :4600

àUser defined records

type type_name is record (field_name1 datatype(size),.....);

record_variable type_name;

declare

type emp_rec is record(empno emp.empno%type,

                       ename emp.ename%type,

                       job emp.job%type,

                       sal emp.sal%type);

emp_var emp_rec;

begin

select empno,ename,job,sal into emp_var from emp where empno=&eno;

dbms_output.put_line('Employee ID     :     '||emp_var.empno);

dbms_output.put_line('Employee Name   :     '||emp_var.ename);

dbms_output.put_line('Employee Job    :     '||emp_var.job);

dbms_output.put_line('Employee Salary :     '||emp_var.sal);

end;

/ 

Enter value for eno: 7902

old   8: select empno,ename,job,sal into emp_var from emp where empno=&eno;

new   8: select empno,ename,job,sal into emp_var from emp where empno=7902;

Employee ID     :7902

Employee Name   :FORD

Employee Job    :ANALYST

Employee Salary :4600

àIndex by table

declare

type salary_ty is table of number index by varchar2(20);

salary_list salary_ty;            

name varchar2(20);

begin

--adding elements to the table

salary_list('Mahesh'):=5000;

salary_list('Ganesh'):=4000;

salary_list('Suresh'):=3000;

salary_list('Dinesh'):=2000;

salary_list('Yogesh'):=1000;

--printing the table

name:=salary_list.first;

while name is not null loop

dbms_output.put_line('Salary of :'||name||' is '||to_char(salary_list(name)));

name:=salary_list.next(name);

end loop;

end;

/

Output:-

Salary of :Dinesh is 2000

Salary of :Ganesh is 4000

Salary of :Mahesh is 5000

Salary of :Suresh is 3000

Salary of :Yogesh is 1000

PL/SQL Collection :-A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.

PL/SQL collection types:

1) Index-by tables or Associative array

2) Nested table

3) Variable-size array or Varray

Index-By Table:-An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.

Syntax:-

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

table_name type_name;

Example:

DECLARE

   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);

   salary_list salary;

   name   VARCHAR2(20);

BEGIN

   -- adding elements to the table

   salary_list('Rajnish')  := 62000;

   salary_list('Minakshi')  := 75000;

   salary_list('Martin') := 100000;

   salary_list('James') := 78000;

   -- printing the table

   name := salary_list.FIRST;

   WHILE name IS NOT null LOOP

      dbms_output.put_line

      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));

      name := salary_list.NEXT(name);

   END LOOP;

END;

/

output :-

Salary of James is 78000

Salary of Martin is 100000

Salary of Minakshi is 75000

Salary of Rajnish is 62000

Nested Tables:-A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects:

1) An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

2) An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

3) A nested table is created using the following syntax:

Syntax :-

TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;

This declaration is similar to declaration of an index-by table, but there is no INDEX BY clause.

A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

Example:

DECLARE

   TYPE names_table IS TABLE OF VARCHAR2(10);

   TYPE grades IS TABLE OF INTEGER;

   names names_table;

   marks grades;

   total integer;

BEGIN

   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');

   marks:= grades(98, 97, 78, 87, 92);

   total := names.count;

   dbms_output.put_line('Total '|| total || ' Students');

   FOR i IN 1 .. total LOOP

      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));

   end loop;

END;

/

Output:-

Total 5 Students

Student:Kavita, Marks:98

Student:Pritam, Marks:97

Student:Ayan, Marks:78

Student:Rishav, Marks:87

Student:Aziz, Marks:92

Collection Methods:-PL/SQL provides the built-in collection methods that make collections easier to use.

1) EXISTS(n) :-Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.

2) COUNT     :-Returns the number of elements that a collection currently contains.

3) LIMIT     :-Checks the Maximum Size of a Collection.

4) FIRST     :-Returns the first (smallest) index numbers in a  collection that uses integer subscripts.

5) LAST      :-Returns the last (largest) index numbers in a collection that uses integer subscripts.

6) PRIOR(n)  :-Returns the index number that precedes index n in a collection.

7) NEXT(n)   :-Returns the index number that succeeds index n.

8) EXTEND    :-Appends one null element to a collection.

9) EXTEND(n) :-Appends n null elements to a collection.

10)EXTEND(n,i):-Appends n copies of the ith element to a collection.

11)TRIM      :-Removes one element from the end of a collection.

12)TRIM(n)   :-Removes n elements from the end of a collection.

13)DELETE    :-Removes all elements from a collection, setting COUNT to 0.

14)DELETE(n) :-Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.

15)DELETE(m,n):-Removes all elements in the range m..n from an associativearray or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Collection Exceptions:-

1) COLLECTION_IS_NULL :-You try to operate on an atomically null collection.

2) NO_DATA_FOUND         :-A subscript designates an element that was deleted, or a nonexistent element of an associative array.

3) SUBSCRIPT_BEYOND_COUNT :-A subscript exceeds the number of elements in a collection.

4) SUBSCRIPT_OUTSIDE_LIMIT:-A subscript is outside the allowed range.

5) VALUE_ERROR        :-A subscript is null or not convertible to the key type.

This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

declare

   type tbl_ename is table of varchar2(50);

   enames tbl_ename;

   i number;

   tot_emp number;

begin

   enames:=tbl_ename('Ramesh','Suresh','Mahesh','Ganesh','Dinesh','Rajesh');

   tot_emp:=enames.count;

   i:=enames.first;

  

   while i<=enames.last loop

      dbms_output.put_line('Employee in '||i||' position in '||enames(i));

      i:=enames.next(i);

   end loop;

end;

/

declare

   type tbl_ename is table of varchar2(50);

   enames tbl_ename;

   i number;

   tot_emp number;

begin

   enames:=tbl_ename('Ramesh','Suresh','Mahesh','Ganesh','Dinesh','Rajesh');

   tot_emp:=enames.count;

   dbms_output.put_line('Total Employee Count is :-'||tot_emp );

   i:=enames.first;

  

   while i<=enames.last loop

      dbms_output.put_line('Employee in '||i||' position in '||enames(i));

      i:=enames.next(i);

   end loop;

   if enames.exists(9) then

      dbms_output.put_line('Employee in 9 position exists');

   else

      dbms_output.put_line('Employee in 9 position does not exists');

   end if;

end;

/