Wednesday, April 5, 2017

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;
/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect