Sunday, July 9, 2017
Wednesday, September 2, 2015
PL/SQL COLLECTION EXAMPLES.
The following examples are taken from the book named THE PL/SQL REFERENCE 11G. The examples fall in chapter 5 which cover collections and records.
Example 5-1 : Declaring and Using an Associative Array.
----------------------------------------------------------------------
declare
type population is table of number index by varchar2(64);
city_population population;
i varchar2(64);
begin
city_population('SallVille') := 2000;
city_population('MidLand') := 750000;
city_population('Megaloplis') := 1000000;
i := city_population.first;
while i is not null loop
dbms_output.put_line('Population of '|| i || ' is ' || to_char(city_population(i)));
i := city_population.next(i);
end loop;
end;
/
Example 5-2 : Declaring an Associative Array.
--------------------------------------------------------
declare
type emptabtype is table of employees%rowtype index by pls_integer;
emp_tab emptabtype;
begin
select * into emp_tab(100) from employees where employee_id = 100;
end;
/
Example 5-3 : Declaring Nested Tables, Varrays and Associative Arrays.
----------------------------------------------------------------------------------------
declare
type nested_type is table of varchar2(30);
type nested_type2 is table of number;
type varray_type is varray(5) of integer;
type assoc_array_num_type is table of number index by pls_integer;
type assoc_array_str_type is table of varchar2(32) index by pls_integer;
type assoc_array_str_type2 is table of varchar2(32) index by varchar2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
v6 nested_type2;
begin
v1 := nested_type('Shipping', 'Sales', 'Finance', 'Payroll');
v2 := varray_type(1, 2, 3, 4, 5);
v3(99) := 10;
v3(7) := 100;
v4(42) := 'Smith';
v4(54) := 'Johes';
v5('Canada') := 'North America';
v5('Greece') := 'Europe';
v6 := nested_type2(10, 20, 30, 40, 50);
dbms_output.put_line(v6(2));
end;
/
Example 5-4 : Declaring Collection with %Type;
------------------------------------------------------------
declare
type few_depts is varray(10) of varchar2(30);
type many_depts is varray(10) of varchar2(64);
some_depts few_depts;
local_depts some_depts%type;
globale_depts some_depts%type;
begin
local_depts := few_depts('ad', 'man', 'hr');
dbms_output.put_line(local_depts(1));
end;
/
Example 5-5 : Declaring a Procedure Parameter as a Nested Table.
---------------------------------------------------------------
Create or replace package personnel as
type staff_list is table of employees.employee_id%type;
procedure award_bonuses(empleos_buenos staff_list);
end personnel;
/
Create or replace package body personnel as
procedure award_bonuses(empleos_buenos staff_list)is
begin
for i in empleos_buenos.first..empleos_buenos.last
loop
update employees set salary = salary+ 100
where employees.employee_id = empleos_buenos(i);
end loop;
end award_bonuses;
end personnel ;
/
Create or replace package personnel as
type staff_list is table of number;
procedure award_bonuses(empleos_buenos staff_list);
end personnel;
/
Create or replace package body personnel as
procedure award_bonuses(empleos_buenos staff_list)is
begin
for i in empleos_buenos.first..empleos_buenos.last
loop
update employees set salary = salary+ 100
where employees.employee_id = empleos_buenos(i);
end loop;
end award_bonuses;
end personnel ;
/
>>>>>>>>>>>execute personnel.award_bonuses(personnel.staff_list());>>>>>>>>>>>
Example 5-6 : Invoking a Prcedure with a Nested Table Parameter.
----------------------------------------------------------------
Declare
good_employees personnel.staff_list;
begin
good_employees := personnel.staff_list(100, 103, 107);
personnel.award_bonuses(good_employees);
end;
/
<<<<<<<<<
select salary from employees
where employee_id in (100, 103, 107)
/
>>>>>>>>>>
Example 5-7 : Specifying Collection Element Types with %Type and %RowType.
--------------------------------------------------------------------------
declare
Type EmpList is table of employees.employee_id%type;
Type EmpList2 is table of employees%rowtype;
Cursor c1 is select employee_id from employees;
Type Senior_SalesPeople is varray(10) of employees%rowtype;
Cursor c2 is select first_name, last_name from employees;
Type NameList is varray(20) of c2%rowtype;
begin
null;
end;
/
Example 5-8 : Varray of Records.
-------------------------------
Declare
Type name_rec is record(first_name varchar2(20), last_name varchar2(25));
type names is varray(250) of name_rec;
begin
null;
end;
/
<<<<<<<<<<<
Declare
Type name_rec is record(first_name varchar2(20), last_name varchar2(25));
--type names is varray(250) of name_rec;
--a names;
b name_rec;
c name_rec;
begin
b.first_name := 'kamal';
b.last_name := 'jamal';
--a := names(b, c);
dbms_output.put_line(b.first_name);
end;
/
>>>>>>>>>>>>>
Example 5-9 : Not Null Constraint on Collection Elements.
--------------------------------------------------------
Declare
Type EmpList is table of employees.employee_id%type not null;
v_employees EmpList := EmpList (100, 150, 160,200);
begin
dbms_output.put_line(v_employees (3));
end;
/
Example 5-10 : Constructor for a Nested Table.
----------------------------------------------
declare
Type dnames_tab is table of varchar2(30);
dept_names dnames_tab;
begin
dept_names := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
end;
/
Example 5-11 : Constructor for a Varray.
----------------------------------------------
declare
Type dnames_tab is varray(10) of varchar2(30);
dept_names dnames_tab;
begin
dept_names := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
end;
/
Example 5-12 : Collection Constructor Including Null elements.
--------------------------------------------------------------
Declare
type dnames_tab is table of varchar2(30);
Type dnamesNoNulls_type is table of varchar2(3) not null;
dept_names dnames_tab;
begin
dept_names := dnames_tab('Shipping', null, 'Finance', Null);
end;
/
The Following is not allowed:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
type dnames_tab is table of varchar2(30);
Type dnamesNoNulls_type is table of varchar2(30) not null;
dept_names dnamesNoNulls_type;
begin
dept_names := dnamesNoNulls_type ('Shipping', null, 'Finance', null);
end;
/
Example : 5-13 : Combining Collection Declaration and Constructor.
------------------------------------------------------------------
declare
type dnames_tab is table of varchar2(30);
dept_names dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
begin
null;
end;
/
Example 5-14 : Empty Varray Constructor.
----------------------------------------
Declare
Type dnames_var is varray(20) of varchar2(30);
dept_names dnames_var;
begin
if dept_names is null then
dbms_output.put_line('Before Initialization the varray is null.');
--dbms_output.put_line('It has ' ||dept_names.count || ' elements');
else
dbms_output.put_line('Before Initialization the varray is not null.');
end if;
dept_names := dnames_var();
if dept_names is null then
dbms_output.put_line('After Initialization the varray is null.');
dbms_output.put_line('It has ' ||dept_names.count || ' elements');
else
dbms_output.put_line('After Initialization the varray is not null.');
dbms_output.put_line('It has ' ||dept_names.count || ' elements');
end if;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
Type dnames_var is table of varchar2(30);
dept_names dnames_var;
begin
if dept_names is null then
dbms_output.put_line('Before Initialization the varray is null.');
--dbms_output.put_line('It has ' ||dept_names.count || ' elements');
else
dbms_output.put_line('Before Initialization the varray is not null.');
end if;
dept_names := dnames_var();
if dept_names is null then
dbms_output.put_line('After Initialization the varray is null.');
dbms_output.put_line('It has ' ||dept_names.count || ' elements');
else
dbms_output.put_line('After Initialization the varray is not null.');
dbms_output.put_line('It has ' ||dept_names.count || ' elements');
end if;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Example 5-15 : Referencing a Neted Table Element.
-------------------------------------------------
declare
Type Roster is table of varchar2(15);
names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
procedure verify_names (the_name varchar2) is
begin
dbms_output.put_line(the_name);
end verify_names;
begin
for i in names.first..names.last loop
if names(i) = 'J Hamil' then
dbms_output.put_line(names(i));
end if;
end loop;
verify_names(names(3));
end;
/
Example 5-16 : Referencing an Element of an Associative Array.
-------------------------------------------------------------=
Declare
Type sum_multiples is table of pls_integer index by pls_integer;
n pls_integer := 2;
sn pls_integer := 20;
m pls_integer := 5;
function get_sum_multiples (multiple in pls_integer, num in pls_integer) return sum_multiples
is
s sum_multiples;
begin
for i in 1..num loop
s(i) := multiple*((i*(i+1))/2);
end loop;
return s;
end get_sum_multiples;
begin
dbms_output.put_line('Sum of the first ' || to_char(n) || ' multiples of '|| to_char(m) || ' is ' || to_char(get_sum_multiples (m, sn)(n)));
end;
/
Example 5-17 : data Type Compatibility for Collection Assignment.
-----------------------------------------------------------------
Declare
type last_name_type is varray (3) of varchar2(64);
Type surname_type is varray(30) of varchar2(42);
group1 last_name_type := last_name_type ('Jones', 'Wong', 'Marceau');
group2 last_name_type := last_name_type ('Klein', 'Patsos', 'Singh');
group3 surname_type := surname_type ('Trevisi', 'Macleod', 'Marquez');
begin
group1 := group2;
--Not allowed becaus they have different data types
-- group2 := group3;
end;
/
Example - 5-18: Assigning a Null value to a Nested Table.
---------------------------------------------------------
Declare
type dnames_tab is table of varchar2(30);
dept_names dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
empty_set dnames_tab;
begin
if
dept_names is not null then
dbms_output.put_line('OK, now dept_names is not null');
end if;
dept_names := empty_set;
if
dept_names is null then
dbms_output.put_line('OK, now dept_names has become null');
end if;
dept_names := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
end;
/
Example 5-19 : Assigning Nested Tables with Set Operators.
----------------------------------------------------------
Declare
Type nested_type is table of number;
nt1 nested_type := nested_type(1, 2, 3);
nt2 nested_type := nested_type(3, 2, 1);
nt3 nested_type := nested_type(2, 3, 1, 3);
nt4 nested_type := nested_type(1, 2, 4);
answer nested_type;
procedure print_nested_table(the_nt nested_type) is
output varchar2(128);
begin
if the_nt is null then
dbms_output.put_line('Results : Null');
return;
end if;
if the_nt.count = 0 then
dbms_output.put_line('Results : Empty Set');
return;
end if;
for i in the_nt.first..the_nt.last loop
output := output|| the_nt(i)|| ' ';
end loop;
dbms_output.put_line('Results : '||output);
end;
begin
answer := nt1 multiset union nt4;
print_nested_table(answer);
answer := nt1 multiset union distinct nt4;
print_nested_table(answer);
answer := nt1 multiset intersect nt4;
print_nested_table(answer);
answer := nt1 multiset intersect distinct nt4;
print_nested_table(answer);
answer := nt1 multiset except nt4;
print_nested_table(answer);
answer := nt1 multiset except distinct nt4;
print_nested_table(answer);
prln('-----------------------------');
answer := nt1 multiset intersect distinct nt4;
print_nested_table(answer);
answer := nt1 multiset union nt3;
print_nested_table(answer);
answer := nt1 multiset union distinct nt4;
print_nested_table(answer);
answer := nt1 multiset intersect nt4;
print_nested_table(answer);
answer := nt1 multiset intersect distinct nt4;
print_nested_table(answer);
answer := set(nt3);
print_nested_table(answer);
end;
/
Example 5-20 : Assigning Values to Varrays with Complex data Types.
------------------------------------------------------------------
Declare
Type emp_name_rec is record(
firstName employees.first_name%type,
lastName employees.last_name%type,
hireDate employees.hire_date%type );
Type EmpList_Arr is varray(10) of emp_name_rec;
SeniorSalespeople EmpList_arr;
Cursor c1 is select first_name, last_name, hire_date from employees;
Type NameSet is table of c1%rowtype;
SeniorTen NameSet;
EndCounter number := 10;
begin
seniorSalespeople := empList_arr();
select first_name, last_name, hire_date bulk collect into seniorTen from employees
where job_id = 'SA_REP'
order by hire_date;
if seniorTen.last > 0 then
if seniorTen.last < 10 then
endCounter := seniorTen.last;
end if;
for i in 1..endcounter loop
seniorSalespeople.extend(1);
seniorSalesPeople(i) := seniorTen(i);
dbms_output.put_line(SeniorSalesPeople(i).lastname||' ---------- ' || seniorSalesPeople(i).firstName||' -------------- '|| seniorsalesPeople(i).hiredate);
end loop;
end if;
end;
/
Example 5-21 : Assigningn Values to Tables with Complex Data Types.
-------------------------------------------------------------------
Declare
Type emp_name_rec is record ( firstName employees.first_name%type, lastName employees.last_name%type, hireDate employees.hire_date%type);
Type EmpList_tab is table of emp_name_rec;
SeniorSalesPeople EmpList_tab;
Cursor c1 is select first_name, last_name, hire_date from employees;
endcounter number := 10;
Type EmpCurType is ref Cursor;
emp_cv EmpCurType;
begin
open emp_cv for select first_name, last_name, hire_date
from employees
where job_id = 'SA_REP' order by hire_date;
fetch emp_cv bulk collect into SeniorSalesPeople;
close emp_cv;
if seniorsalespeople.last > 0 then
if seniorsalespeople.last > 10 then
endcounter := seniorsalespeople.last;
end if;
for i in 1..endcounter loop
prln(seniorsalespeople(i).lastname ||' ========'|| seniorsalespeople(i).firstname||'========'|| seniorsalespeople(i).hiredate);
end loop;
end if;
end;
/
Example 5-22 : Checking if a Collection is null.
------------------------------------------------
Declare
Type emp_name_rec is record(
firstName employees.first_name%type,
lastName employees.last_name%type,
hireDate employees.hire_date%type);
Type staff is table of emp_name_rec;
members staff;
begin
if members is null then
dbms_output.put_line('Null');
else
dbms_output.put_line('Not Null');
end if;
end;
/
Example 5-23 : Coparing Two Nested Tables.
------------------------------------------
Declare
Type dnames_tab is table of varchar2(30);
dept_names1 dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
dept_names2 dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
dept_names3 dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance');
begin
if dept_names1 = dept_names2 then
dbms_output.put_line('dept1 and dept2 have the same members');
else
dbms_output.put_line('dept1 and dept2 have different members');
end if;
if dept_names1 = dept_names3 then
dbms_output.put_line('dept1 and dept2 have the same members');
else
dbms_output.put_line('dept1 and dept3 have different members');
end if;
end;
/
Example 5-24 : Comparing Nested Tables with Set Operators.
----------------------------------------------------------
Declare
Type nested_type is table of number;
nt1 nested_type := nested_type(1, 2, 3);
nt2 nested_type := nested_type(3, 2, 1);
nt3 nested_type := nested_type(2, 3, 1, 3);
nt4 nested_type := nested_type(1, 2, 4);
answer boolean;
howmany number;
procedure testify(truth boolean default null, quantity number default null) is
begin
if
truth is not null then
dbms_output.put_line(Case truth when true then 'True' when false then 'False' end);
end if;
if quantity is not null then
dbms_output.put_line(quantity);
end if;
end;
begin
answer := nt1 in (nt2, nt3, nt4);
testify(truth => answer);
answer := nt1 submultiset of nt3;
testify(truth => answer);
answer := nt1 not submultiset of nt4;
testify(truth => answer);
howmany := cardinality(nt3);
testify (quantity => howmany);
howmany := cardinality(set(nt3));
testify (quantity => howmany);
answer := 4 member of nt1;
testify (truth => answer);
answer:= nt3 is a set;
testify (truth => answer);
answer:= nt3 is not a set;
testify (truth => answer);
answer:= nt1 is empty;
testify (truth => answer);
end;
/
>>>>>>>>>>>Use of Cardinality : Calculate the element of a nested table>>>>>>>>>>>>>>>>>
declare
type a is table of number;
howmany number;
b a := a(1, 2, 3, 4);
begin
howmany := cardinality(b);
dbms_output.put_line(howmany);
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Example 5-25 : Multilevel Array.
--------------------------------
Declare
Type t1 is varray(10) of number;
Type nt1 is varray(10) of t1;
va t1 := t1(1, 2, 3);
nva nt1 := nt1 (va, t1(55, 6, 73), t1(2, 4), va);
i integer;
va1 t1;
begin
i := nva (1)(3);
dbms_output.put_line('I = '|| i);
--dbms_output.put_line(nva (5)(1));
dbms_output.put_line('I = '|| i);
nva.extend;
dbms_output.put_line(nva (5)(1));
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
Type t1 is varray(10) of number;
Type nt1 is varray(10) of t1;
va t1 := t1(1, 2, 3);
va2 t1 := t1(4, 5, 6);
nva nt1 := nt1(t1(55, 6, 73), t1(2, 4), va2);
begin
null;
end;
/
Example : 5-26 : Multilevel Nested Table.
-----------------------------------------
DECLARE
TYPE tb1 IS TABLE OF VARCHAR2(20);
TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
TYPE Tv1 IS VARRAY(10) OF INTEGER;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
-- table of varray elements
BEGIN
vntb1.EXTEND;
vntb1(2) := vntb1(1);
dbms_output.put_line(vntb1(1)(1)||' '||vntb1(1)(2));
-- delete the first element in vntb1
vntb1.DELETE(1);
-- delete the first string
-- from the second table in the nested table
vntb1(2).DELETE(1);
END;
/
>>>>>>>>>my excercise>>>>>>>>>>>>>>>>>
DECLARE
TYPE tb1 IS TABLE OF VARCHAR2(20);
TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
TYPE Tv1 IS VARRAY(10) OF INTEGER;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
-- table of varray elements
BEGIN
vntb1.EXTEND;
vntb1(2) := vntb1(1);
dbms_output.put_line(vntb1(1)(1)||' '||vntb1(2)(2));
-- delete the first element in vntb1
vntb1.DELETE(1);
dbms_output.put_line(vntb1(2)(1)||' '||vntb1(2)(2));
-- delete the first string
-- from the second table in the nested table
vntb1(2).DELETE(1);
END;
/
>>>>>>>>>my excercise>>>>>>>>>>>>>>>>>
DECLARE
TYPE tb1 IS TABLE OF VARCHAR2(20);
TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
TYPE Tv1 IS VARRAY(10) OF INTEGER;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
-- table of varray elements
BEGIN
vntb1.EXTEND;
vntb1(2) := vntb1(1);
dbms_output.put_line(vntb1(1)(1)||' '||vntb1(2)(2));
-- delete the first element in vntb1
vntb1.DELETE(1);
dbms_output.put_line(vntb1(2)(1)||' '||vntb1(2)(2));
-- delete the first string
-- from the second table in the nested table
vntb1(2).DELETE(1);
dbms_output.put_line(vntb1(2)(2)||' '||vntb1(2)(2));
END;
/
>>>>>>>>>my excercise>>>>>>>>>>>>>>>>>
create procedure prln(obj varchar2) is
begin
dbms_output.put_line(obj);
end;
/
Example 5-27 : Multilevel Associative Array.
-------------------------------------------
Declare
Type tb1 is table of integer index by pls_integer;
Type ntb1 is table of tb1 index by pls_integer;
Type va1 is varray(10) of varchar2(20);
Type ntb2 is table of va1 index by pls_integer;
v1 va1 := va1('hello', 'world');
v2 ntb1;
v3 ntb2;
v4 tb1;
v5 tb1;
begin
v4(1) := 34;
v4(2) := 46456;
v4(456) := 343;
v2(23) := v4;
v3 (34) := va1(33, 456, 656, 343);
v5(1) := 10;
v5(2) := 20;
v2(35) := v5;
--v2(35)(2) := 78;
V2(35) := V4;
prln(v2(35)(2));
prln(v5.count);
end;
/
Example 5-28 : Checking Wheter a Collection Element Exists.
----------------------------------------------------------------------------
Declare
Type NumList is table of Integer;
n NumList := NumList(1, 3, 5, 7);
begin
--n.delete(2);
prln(n(1));
if n.exists(1) then
prln('OK, Element #1 exists! ');
else
prln('OK, Element #1 has been Deleted!');
end if;
if n.exists(2) = false then
prln('OK, Element #2 has been Deleted!');
else
prln(n(2));
end if;
if n.exists(99) = false then
prln('OK, Element #99 does not exists at all! ');
end if;
end;
/
Example 5-29 : Counting Collection Elements with Count.
-----------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList (2, 4, 6, 8);
Begin
dbms_output.put_line('There are '|| n.count || ' elements in N.');
n.extend(3); -- Add 3 new elements at the end.
dbms_output.put_line('There are '|| n.count || ' elements in N.');
n := NumList (86, 99, 15);
dbms_output.put_line('There are '|| n.count || ' elements in N.');
n.trim(2);
dbms_output.put_line('There are '|| n.count || ' elements in N.');
end;
/
Example 5-30 : Checking the Maximum Size of a Collection with LIMIT.
------------------------------------------------------------------------------------------
Declare
Type dnames_var is varray(7) of varchar2(30);
dept_names dnames_var := dnames_var('Shipping', 'Sales', 'Finance', 'Payroll');
Begin
prln('dept_names has '|| dept_names.count);
prln('dept_names''s type can hold a maximum of '|| dept_names.limit ||' elements');
prln('The maximum number you can use with '|| ' dept_names.extend() is '|| (dept_names.limit-dept_names.count));
prln(dept_names.first || ' '|| dept_names.last);
end;
/
Example 5-31: Using First and Last with a Collection.
------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList (1, 3, 5, 7);
counter integer;
Begin
prln('The first subscript of N is '||n.first);
prln('The last subscript of N is '||n.last);
for i in n.first..n.last loop
prln('Element #' || i || ' = ' || n(i));
end loop;
n.delete(2);
if n is not null then
counter := n.first;
while counter is not null loop
prln('Element # '|| counter || ' = '|| n(counter));
counter := n.next(counter);
end loop;
else
prln('N is null, nothing to do' );
end if;
end;
/
Example 5-32 : Using Prior and Next to Access Collection Elements.
-------------------------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList(1966, 1971, 1984, 1989, 1999);
Begin
prln('The element after #2 is #'||n.next(2));
prln('The element before #2 is #'||n.prior(2));
n.delete(3);
prln('The element after #2 is #'||n.next(2));
if n.prior(n.first) is null then
prln('Can''t get Prior of the first element or Next of the Last');
end if;
end;
/
Example 5-33 : Using Next to Access Elements of a Nested Table.
-----------------------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList
Example 5–34 Using EXTEND to Increase the Size of a Collection
----------------------------------------------------------------------------------
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(2,4,6,8);
x NumList := NumList(1,3);
PROCEDURE print_numlist(the_list NumList) IS
output VARCHAR2(128);
BEGIN
FOR i IN the_list.FIRST .. the_list.LAST
LOOP
output :=
output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';
END LOOP;
DBMS_OUTPUT.PUT_LINE(output);
END;
BEGIN
DBMS_OUTPUT.PUT_LINE
('At first, N has ' || n.COUNT || ' elements.');
n.EXTEND(5); -- Add 5 elements at the end.
DBMS_OUTPUT.PUT_LINE
('Now N has ' || n.COUNT || ' elements.');
-- Elements 5, 6, 7, 8, and 9 are all NULL.
print_numlist(n);
DBMS_OUTPUT.PUT_LINE
('At first, X has ' || x.COUNT || ' elements.');
x.EXTEND(4,2); -- Add 4 elements at the end.
DBMS_OUTPUT.PUT_LINE
('Now X has ' || x.COUNT || ' elements.');
-- Elements 3, 4, 5, and 6 are copies of element #2.
print_numlist(x);
END;
/
Example : 5-35 : Using Trim to Decrease the Size of a Collection.
--------------------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList (1, 2, 3, 5, 7, 11);
procedure print_numlist(the_list NumList) is
output varchar2(128);
begin
if
n.count = 0 then
prln('No elements in collection !');
else
for i in the_list.first..the_list.last
loop
output := output|| nvl(to_char(the_list(i)), 'NULL') || ' ' ;
end loop;
prln(output);
end if;
end;
Begin
print_numlist(n);
n.trim(2);
print_numlist(n);
n.trim(n.count);
print_numlist(n);
Begin
n := NumList (1, 2 , 3);
n.trim(100);
Exception
when subscript_beyond_count then
prln('There are not 100 elemnts to trim');
n := NumList(1, 2, 3, 4);
n.delete(3);
n.trim(2);
print_numList(n);
end;
end;
/
Example 5-36 : Using Trim on Deleted Elements.
-----------------------------------------------------------
Declare
Type CourseList is table of Varchar2(10);
Courses CourseList;
Begin
Courses := CourseList('Biol 4412', 'Psych 3112', 'Anth 3001');
prln( courses.last);
courses.delete(courses.last);
prln(courses.last);
courses.trim(courses.count);
prln(courses(courses.count));
prln(courses(1));
end;
/
Example 5-37 : Using the Delete Method on a Collection.
----------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList(10, 20, 30, 40, 50, 60, 70, 80, 90, 100);
Type NickList is table of Varchar2(64) index by varchar2(21);
nickNames NickList;
Begin
n.delete(2);
n.delete(3, 6); -- deletes elements 3 through 6
n.delete(7, 7);
n.delete(6, 3); -- does nothing since 6 > 3
n.delete; -- delete all elements.
nicknames('Bob') := 'Robert';
nicknames('Buffy') := 'Esmerelda';
nicknames('Chip') := 'Charles';
nicknames('Dan') := 'Daniel';
nicknames('Fluffy') := 'Ernestina';
nicknames('Rob') := 'Robert';
nicknames('Dan') := 'Daniel';
prln(nicknames.count);
nicknames.delete('Chip');
prln(nicknames.count);
nicknames.delete;
prln(nicknames.count);
end;
/
Example 5-38 : Collection Exception.
----------------------------------------------
Declare
Type WordList is table of varchar2(5);
Words WordList;
err_msg varchar2(100);
procedure display_error is
Begin
err_msg := substr(sqlerrm, 1, 100);
prln('Error Message = ' || err_msg);
End;
Begin
Begin
words(1) := 10;
Exception
When others then display_error;
end;
words := wordList('1st', '2nd', '3rd');
words(3) := words(1)||'+2';
Begin
words(3) := 'Longer than 5 characters';
exception
when others then display_error;
end;
Begin
words('B') := 'dunno';
exception
when others then display_error;
end;
Begin
words(0) := 'zero';
exception
when others then display_error;
end;
Begin
words(4) := 'maybe';
exception
when others then display_error;
end;
Begin
words.delete(1);
if words(1) = 'First' then NULL;
end if;
exception
when others then display_error;
End;
end;
/
Example 5-39 : How Invalid Subscript are Handled with Delete(n).
----------------------------------------------------------------------------------
Declare
Type NumList is table of number;
nums NumList := NumList(10, 20, 30);
Begin
nums.delete(-1);
nums.delete(3);
nums(3) := 30;
prln(nums.count);
End;
/
Example 5-40 : Incompatibility Between Package and Local Collection Types.
------------------------------------------------------------------------------------------------
Create package pkg as
Type NumList is table of Number;
procedure print_numlist(nums NumList);
end pkg;
/
Create or replace package body pkg as
procedure print_numlist(nums NumList) is
Begin
for i in nums.first..nums.last loop
prln(nums(i));
end loop;
end;
end pkg;
/
Declare
Type NumList is table of number;
n1 pkg.NumList := pkg.NumList(2, 4);
n2 NumList := NumList(6, 8);
Begin
pkg.print_numlist(n1);
--pkg.print_numlist(n2); -- not allowed
end;
/
Example 5-41 : Declaring and Initializing a Simple Record.
-----------------------------------------------------------------------
Declare
Type DeptRecTyp is record(
deptid number (4) not null := 99,
dname departments.department_NAME%type,
loc departments.location_id%type,
region regions%rowtype
);
Dept_Rec DeptRecTyp;
BEGIN
dept_rec.dname := 'PURCHASING';
END;
/
Example 5-42 : Declaring and Initializing RECORD Types.
------------------------------------------------------
DECLARE
Type rec1_t is record (
field1 varchar2(16),
field2 number,
field3 DATE);
Type rec2_t is record (
id integer not null := -1,
name varchar2(64) not null := '[anonymous]'
);
rec1 rec1_t;
rec2 rec2_t;
rec3 employees%rowtype;
Type rec4_t is record (first_name employees.first_name%type,
last_name employees.last_name%type,
rating number
);
rec4 rec4_t;
BEGIN
rec1.field1 := 'Yesterday';
rec1.field2 := 60;
rec1.field3 := trunc(sysdate-1);
prln(rec2.name);
END;
/
Example 5-43 : Using %ROWTYPE to Declare a Record.
-----------------------------------------------------------------------
Declare
Cursor c1 is
select department_id, department_name, location_id
from departments;
rec1 c1%rowtype;
Type DeptRec2 is Record (dept_id departments.department_id%type,
dept_name departments.department_name%type,
dept_loc departments.location_id%type);
rec2 DeptRec2;
Type DeptRec3 is record(
dept_id number,
dept_name varchar2(14),
dept_loc varchar2(13)
);
rec3 DeptRec3;
Begin
null;
end;
/
Example 5-44 : Returning a Record from a Function.
-----------------------------------------------------------------
Declare
Type EmpRecTyp is Record(
emp_id number(6),
salary number(8, 2)
);
Cursor desc_salary return EmpRecTyp is
select employee_id, salary
from employees
order by salary desc;
emp_rec EmpRecTyp;
Function nth_highest_salary(n integer) return EmpRecTyp is
Begin
open desc_salary;
for i in 1..n loop
fetch desc_salary into emp_rec;
end loop;
return emp_rec;
end nth_highest_salary;
Begin
null;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
a number;
b number;
function
mysum(x number, y number) return number is
begin
return x*y;
end mysum;
Begin
prln(mysum(2, 15));
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Example 5-45 : Using a Record as Parameter to a Procedure.
---------------------------------------------------------------------------
Declare
Type EmpRecTyp is Record( emp_id number (6), emp_sal number(8, 2));
Procedure raise_salary (emp_info EmpRecTyp) is
Begin
update employees set salary = salary+ salary* .10
where employee_id = emp_info.emp_id;
end raise_salary;
Begin
null;
end;
/
Example 5-46 : Declaring a Nested Record.
-----------------------------------------------------
Declare
Type TimeTyp is record(minutes smallint, hours smallint);
Type meetingTyp is record(day date, time_of Timetyp, dept departments%rowtype, place varchar2(20), purpose varchar2(50));
meeting meetingTyp;
seminar meetingTyp;
Begin
seminar.time_of := meeting.time_of;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
Type TimeTyp is record(minutes smallint, hours smallint);
Type meetingTyp is record(day date, time_of Timetyp, dept departments%rowtype, place varchar2(20), purpose varchar2(50));
meeting meetingTyp;
seminar meetingTyp;
Begin
meeting.day := '01-JUN-15';
seminar.time_of := meeting.time_of;
end;
/
Example 5-47 : Assigning Default values to a record.
----------------------------------------------------------------
declare
type recordType is record(field1 number, field2 varchar2(32) default 'something');
rec1 recordType;
rec2 recordType;
begin
rec1.field1 := 100;
rec1.field2 := 'something else';
rec2.field1 := 200;
rec1 := rec2;
dbms_output.put_line(rec1.field1 ||' ' || rec1.field2);
end;
/
Example 5-48 : Assigning all the fields of a record in one statement.
---------------------------------------------------------------------
declare
type DeptRec1 is record (dept_num number(2), dept_name varchar2(14));
type DeptRec2 is record (dept_num number(2), dept_name varchar2(14));
dept1_info deptRec1;
dept2_info deptRec2;
dept3_info deptRec2;
begin
--dept1_info := dept2_info;
dept3_info.dept_num := 10;
dept3_info.dept_name := 'Admin';
dept2_info := dept3_info;
dbms_output.put_line('Department Id is '|| dept2_info.dept_num);
end;
/
------------------------------------------------------------------------
declare
type recordtype is record (last employees.last_name%type, id employees.employee_id%type);
cursor c1 is select last_name, employee_id from employees;
rec1 recordType;
rec2 c1%rowtype;
begin
select last_name, employee_id into rec2 from employees
where rownum <2;
rec1 := rec2;
dbms_output.put_line(rec1.last);
end;
/
---------------------------------------------------------------------------
declare
cursor c1 is select last_name from employees;
begin
for item in c1 loop
dbms_output.put_line(item.last_name);
end loop;
end;
/
Example : 5-49 Using select itno to assign values in a Record.
----------------------------------------------------------------------------
declare
type recordType is record(last employees.last_name%type, id employees.employee_id%type);
rec1 recordType;
begin
select last_name, employee_id into rec1 from employees where rownum <2;
dbms_output.put_line('employee '|| rec1.id);
end;
/
declare
type recordType is record(last employees.last_name%type, id employees.employee_id%type);
rec1 recordType;
begin
select last_name, employee_id into rec1 from employees where rownum <2;
dbms_output.put_line('employee id : '|| rec1.id);
dbms_output.put_line('employee name : '|| rec1.last);
end;
/
--------------------------
declare
cursor c1 is select employee_id, last_name from employees order by employee_id;
v1 c1%rowtype;
begin
open c1;
fetch c1 into v1;
dbms_output.put_line(v1.employee_id ||' '||v1.last_name );
end;
/
Example 5-50 : Insertig a PL/SQL Record Using %Rowtype
---------------------------------------------------------------------------
declare
dept_info departments%rowtype;
begin
dept_info.department_id := 300;
dept_info.department_name := 'Personnel';
dept_info.location_id := 1700;
insert into departments values dept_info;
end;
/
Example 5-51 : Updating a Row Using a Record
--------------------------------------------------------------------------
declare
dept_info departments%rowtype;
begin
dept_info.department_id := 300;
dept_info.department_name := 'Personnel2';
dept_info.location_id := 1700;
update departments set row = dept_info
where department_id = 300;
end;
/
Example 5-52 : Using the Returning Into Clause with a Record.
---------------------------------------------------------------------------
Declare
type EmpRec is record (last_name employees.last_name%type, salary employees.salary%type, commission_pct employees.commission_pct%type);
emp_info EmpRec;
emp_id number := 100;
begin
update employees set salary = salary* nvl(commission_pct, 1)
where employee_id = emp_id
returning last_name, salary, commission_pct into emp_info;
dbms_output.put_line('Just gave a raise to '|| emp_info.last_name || ' who now makes '|| emp_info.salary);
rollback;
end;
/
---------------------------------------------------------------------------------
Declare
type EmpRec is record (last_name employees.last_name%type, salary employees.salary%type, commission_pct employees.commission_pct%type);
emp_info EmpRec;
emp_id number := 101;
begin
delete from emp
where employee_id = emp_id
returning last_name, salary, commission_pct into emp_info;
dbms_output.put_line('Just gave a raise to '|| emp_info.last_name || ' who now makes '|| emp_info.salary);
rollback;
end;
/
Example 5-53 : Using Bulk Collect with Selectr into statement.
-------------------------------------------------------------
Declare
Type employeeSet is table of employees%rowtype;
underPaid employeeSet;
Cursor c1 is select first_name, last_name from employees;
type NameSet is table of c1%rowtype;
some_names NameSet;
begin
select * bulk collect into underPaid from employees
where salary < 5000 order by salary desc;
dbms_output.put_line(underPaid.count|| ' people make less than 5000');
for i in underPaid.first..underPaid.last loop
dbms_output.put_line(underpaid(i).last_name || ' makes '|| underPaid(i).salary || ' '||underPaid(i).department_id);
end loop;
select first_name, last_name bulk collect into some_names from employees where rownum < 11;
for i in some_names.first..some_names.last loop
dbms_output.put_line(some_names(i).first_name ||' ' || some_names(i).last_name);
end loop;
end;
/
>>>>>>>>>>>>excercise>>>>>>>>>>
declare
a departments%rowtype;
begin
a.department_id := 100;
dbms_output.put_line(a.department_id);
end;
/
Creating Object
--------------------
CREATE OR REPLACE TYPE CUSTOMER_TYP_DEMO AS OBJECT (
CUSTOMER_ID NUMBER(6),
CUST_FIRST_NAME VARCHAR2(2),
CUST_LAST_NAME VARCHAR2(20),
CUST_ADDRESS CUST_ADDRESS_TYP,
PHONE_NUMBERS PHONE_LIST_TYP,
NLS_LANGUAGE VARCHAR2(30),
CREDIT_LIMIT NUMBER(30),
CUST_EMAIL VARCHAR2(30),
CUST_ORDERS ORDER_LIST_TYP
);
/
CREATE OR REPLACE TYPE ASS AS TABLE OF CUSTOMER_TYP_DEMO;
/
CREATE TYPE DATA_TYP1 AS OBJECT (YEAR NUMBER, MEMBER FUNCTION PROD (INVENT NUMBER) RETURN NUMBER);
/
CREATE TYPE BODY DATA_TYP1 IS
MEMBER FUNCTION PROD(INVENT NUMBER) RETURN NUMBER IS
BEGIN
RETURN (YEAR + INVENT);
END;
END;
/
CREATE TYPE CORPORATE_CUSTOMER_TYP_DEMO UNDER CUSTOMER_TYP (ACCOUNT_MGR_ID NUMBER(6));
/
CREATE or replace TYPE address_t AS OBJECT
EXTERNAL NAME 'Examples.Address' LANGUAGE JAVA
USING SQLData(
STREET_ATTR VARCHAR (250) EXTERNAL NAME 'STREET',
CITTY_ATTR VARCHAR(250) EXTERNAL NAME 'CITY',
STATE VARCHAR(50) EXTERNAL NAME 'STATE',
ZIP_CODE_ATTR NUMBER EXTERNAL NAME 'ZIPCODE',
STATIC FUNCTION recom_width RETURN NUMBER
EXTERNAL VARIABLE NAME 'recommendedWidth',
STATIC FUNCTION create_address RETURN address_t
EXTERNAL NAME 'create() return Examples.Address',
STATIC FUNCTION construct RETURN address_t
EXTERNAL NAME 'create() return Examples.Address',
STATIC FUNCTION create_address (street VARCHAR, city VARCHAR, state VARCHAR, zip NUMBER) RETURN address_t
EXTERNAL NAME 'create (java.lang.String, java.lang.String, java.lang.String, int ) return Examples.Address',
STATIC FUNCTION construct (street VARCHAR, city VARCHAR, state VARCHAR, zip NUMBER) RETURN address_t
EXTERNAL NAME 'create (java.lang.String, java.lang.String, java.lang.String, int ) return Examples.Address',
MEMBER FUNCTION to_string RETURN VARCHAR
EXTERNAL NAME 'tojava.lang.String() return java.lang.String',
MEMBER FUNCTION strip RETURN SELF AS RESULT
EXTERNAL NAME 'removeLeadingBlanks() return Examples.Address'
) NOT FINAL;
/
Create type my_type as object
external name 'Examples.Address' language java
using SQLData (myName varchar(25) external name 'myName') not final;
/
Create type my_type2 as object
external name 'Examples' language java
using SQLData (myName varchar(25) external name 'myName') not final;
/
Create type my_type3 as object
external name 'EX' language java
using SQLData (myName varchar(25) external name 'myName') not final;
/
Create or replace Type long_address_t
under address_t
external Name 'Examples.LongAddress' Language Java
Using SQLData(
street2_attr varchar(250) external name 'street2',
country_attr varchar(250) external name 'country',
address_code_attr varchar(50) external name 'addrcode',
static function create_address return long_address_t
external name 'create () return Examples.LongAddress',
static function construct (street varchar, city varchar, state varchar, country varchar, addrs_cd varchar) return long_address_t
external name 'create (java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return Examples.LongAddress',
static function construct return long_address_t
external name 'Examples.LongAddress() return Examples.LongAddress',
Static function create_longaddress(street varchar, city varchar, state varchar, country varchar, addrs_cd varchar) return long_address_t
external name 'Examples.LongAddress(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String ) return Examples.LongAddress',
member function get_country return varchar
external name 'country_with_code () return java.lang.String'
);
/
Create type person_t as object (name varchar2(100), ssn number) not final;
/
Create type employee_t under person_t (department_id number, salary number) not final;
/
Create type part_time_emp_t under employee_t (num_hrs number);
/
Create type textdoc_typ as object (document_typ varchar2(32), formatted_doc blob);
/
Create type phone_list_typ_demo as varray(5) of varchar2(25);
/
Create type textdoc_tab as table of textdoc_typ;
/
create type cust_address_typ2 as object (
street_address varchar2(40),
postal_code varchar2(10),
city varchar2(30),
state_province varchar2(10),
country_id char(2),
phone phone_list_typ
);
/
create type cust_nt_address_typ as table of cust_address_typ2;
/
CREATE TYPE DEMO_TYP1 AS OBJECT (X NUMBER, Y NUMBER);
/
CREATE TABLE DEMO_TAB1 (B1 NUMBER, B2 DEMO_TYP1);
/
INSERT INTO DEMO_TAB1 VALUES (1, DEMO_TYP1(2, 3));
/
CREATE OR REPLACE TYPE DEMO_TYP2 AS OBJECT (A1 NUMBER, MEMBER FUNCTION GET_SQUARE RETURN NUMBER);
/
CREATE TABLE DEMO_TAB2 (COL DEMO_TYP2);
/
CREATE OR REPLACE TYPE BODY DEMO_TYP2 IS
MEMBER FUNCTION GET_SQUARE
RETURN NUMBER
IS X NUMBER;
BEGIN
SELECT C.COL.A1 * C.COL.A1 INTO X FROM DEMO_TAB2 C;
RETURN (X);
END;
END;
/
Example 5-1 : Declaring and Using an Associative Array.
----------------------------------------------------------------------
declare
type population is table of number index by varchar2(64);
city_population population;
i varchar2(64);
begin
city_population('SallVille') := 2000;
city_population('MidLand') := 750000;
city_population('Megaloplis') := 1000000;
i := city_population.first;
while i is not null loop
dbms_output.put_line('Population of '|| i || ' is ' || to_char(city_population(i)));
i := city_population.next(i);
end loop;
end;
/
Example 5-2 : Declaring an Associative Array.
--------------------------------------------------------
declare
type emptabtype is table of employees%rowtype index by pls_integer;
emp_tab emptabtype;
begin
select * into emp_tab(100) from employees where employee_id = 100;
end;
/
Example 5-3 : Declaring Nested Tables, Varrays and Associative Arrays.
----------------------------------------------------------------------------------------
declare
type nested_type is table of varchar2(30);
type nested_type2 is table of number;
type varray_type is varray(5) of integer;
type assoc_array_num_type is table of number index by pls_integer;
type assoc_array_str_type is table of varchar2(32) index by pls_integer;
type assoc_array_str_type2 is table of varchar2(32) index by varchar2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
v6 nested_type2;
begin
v1 := nested_type('Shipping', 'Sales', 'Finance', 'Payroll');
v2 := varray_type(1, 2, 3, 4, 5);
v3(99) := 10;
v3(7) := 100;
v4(42) := 'Smith';
v4(54) := 'Johes';
v5('Canada') := 'North America';
v5('Greece') := 'Europe';
v6 := nested_type2(10, 20, 30, 40, 50);
dbms_output.put_line(v6(2));
end;
/
Example 5-4 : Declaring Collection with %Type;
------------------------------------------------------------
declare
type few_depts is varray(10) of varchar2(30);
type many_depts is varray(10) of varchar2(64);
some_depts few_depts;
local_depts some_depts%type;
globale_depts some_depts%type;
begin
local_depts := few_depts('ad', 'man', 'hr');
dbms_output.put_line(local_depts(1));
end;
/
Example 5-5 : Declaring a Procedure Parameter as a Nested Table.
---------------------------------------------------------------
Create or replace package personnel as
type staff_list is table of employees.employee_id%type;
procedure award_bonuses(empleos_buenos staff_list);
end personnel;
/
Create or replace package body personnel as
procedure award_bonuses(empleos_buenos staff_list)is
begin
for i in empleos_buenos.first..empleos_buenos.last
loop
update employees set salary = salary+ 100
where employees.employee_id = empleos_buenos(i);
end loop;
end award_bonuses;
end personnel ;
/
Create or replace package personnel as
type staff_list is table of number;
procedure award_bonuses(empleos_buenos staff_list);
end personnel;
/
Create or replace package body personnel as
procedure award_bonuses(empleos_buenos staff_list)is
begin
for i in empleos_buenos.first..empleos_buenos.last
loop
update employees set salary = salary+ 100
where employees.employee_id = empleos_buenos(i);
end loop;
end award_bonuses;
end personnel ;
/
>>>>>>>>>>>execute personnel.award_bonuses(personnel.staff_list());>>>>>>>>>>>
Example 5-6 : Invoking a Prcedure with a Nested Table Parameter.
----------------------------------------------------------------
Declare
good_employees personnel.staff_list;
begin
good_employees := personnel.staff_list(100, 103, 107);
personnel.award_bonuses(good_employees);
end;
/
<<<<<<<<<
select salary from employees
where employee_id in (100, 103, 107)
/
>>>>>>>>>>
Example 5-7 : Specifying Collection Element Types with %Type and %RowType.
--------------------------------------------------------------------------
declare
Type EmpList is table of employees.employee_id%type;
Type EmpList2 is table of employees%rowtype;
Cursor c1 is select employee_id from employees;
Type Senior_SalesPeople is varray(10) of employees%rowtype;
Cursor c2 is select first_name, last_name from employees;
Type NameList is varray(20) of c2%rowtype;
begin
null;
end;
/
Example 5-8 : Varray of Records.
-------------------------------
Declare
Type name_rec is record(first_name varchar2(20), last_name varchar2(25));
type names is varray(250) of name_rec;
begin
null;
end;
/
<<<<<<<<<<<
Declare
Type name_rec is record(first_name varchar2(20), last_name varchar2(25));
--type names is varray(250) of name_rec;
--a names;
b name_rec;
c name_rec;
begin
b.first_name := 'kamal';
b.last_name := 'jamal';
--a := names(b, c);
dbms_output.put_line(b.first_name);
end;
/
>>>>>>>>>>>>>
Example 5-9 : Not Null Constraint on Collection Elements.
--------------------------------------------------------
Declare
Type EmpList is table of employees.employee_id%type not null;
v_employees EmpList := EmpList (100, 150, 160,200);
begin
dbms_output.put_line(v_employees (3));
end;
/
Example 5-10 : Constructor for a Nested Table.
----------------------------------------------
declare
Type dnames_tab is table of varchar2(30);
dept_names dnames_tab;
begin
dept_names := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
end;
/
Example 5-11 : Constructor for a Varray.
----------------------------------------------
declare
Type dnames_tab is varray(10) of varchar2(30);
dept_names dnames_tab;
begin
dept_names := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
end;
/
Example 5-12 : Collection Constructor Including Null elements.
--------------------------------------------------------------
Declare
type dnames_tab is table of varchar2(30);
Type dnamesNoNulls_type is table of varchar2(3) not null;
dept_names dnames_tab;
begin
dept_names := dnames_tab('Shipping', null, 'Finance', Null);
end;
/
The Following is not allowed:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
type dnames_tab is table of varchar2(30);
Type dnamesNoNulls_type is table of varchar2(30) not null;
dept_names dnamesNoNulls_type;
begin
dept_names := dnamesNoNulls_type ('Shipping', null, 'Finance', null);
end;
/
Example : 5-13 : Combining Collection Declaration and Constructor.
------------------------------------------------------------------
declare
type dnames_tab is table of varchar2(30);
dept_names dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
begin
null;
end;
/
Example 5-14 : Empty Varray Constructor.
----------------------------------------
Declare
Type dnames_var is varray(20) of varchar2(30);
dept_names dnames_var;
begin
if dept_names is null then
dbms_output.put_line('Before Initialization the varray is null.');
--dbms_output.put_line('It has ' ||dept_names.count || ' elements');
else
dbms_output.put_line('Before Initialization the varray is not null.');
end if;
dept_names := dnames_var();
if dept_names is null then
dbms_output.put_line('After Initialization the varray is null.');
dbms_output.put_line('It has ' ||dept_names.count || ' elements');
else
dbms_output.put_line('After Initialization the varray is not null.');
dbms_output.put_line('It has ' ||dept_names.count || ' elements');
end if;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
Type dnames_var is table of varchar2(30);
dept_names dnames_var;
begin
if dept_names is null then
dbms_output.put_line('Before Initialization the varray is null.');
--dbms_output.put_line('It has ' ||dept_names.count || ' elements');
else
dbms_output.put_line('Before Initialization the varray is not null.');
end if;
dept_names := dnames_var();
if dept_names is null then
dbms_output.put_line('After Initialization the varray is null.');
dbms_output.put_line('It has ' ||dept_names.count || ' elements');
else
dbms_output.put_line('After Initialization the varray is not null.');
dbms_output.put_line('It has ' ||dept_names.count || ' elements');
end if;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Example 5-15 : Referencing a Neted Table Element.
-------------------------------------------------
declare
Type Roster is table of varchar2(15);
names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
procedure verify_names (the_name varchar2) is
begin
dbms_output.put_line(the_name);
end verify_names;
begin
for i in names.first..names.last loop
if names(i) = 'J Hamil' then
dbms_output.put_line(names(i));
end if;
end loop;
verify_names(names(3));
end;
/
Example 5-16 : Referencing an Element of an Associative Array.
-------------------------------------------------------------=
Declare
Type sum_multiples is table of pls_integer index by pls_integer;
n pls_integer := 2;
sn pls_integer := 20;
m pls_integer := 5;
function get_sum_multiples (multiple in pls_integer, num in pls_integer) return sum_multiples
is
s sum_multiples;
begin
for i in 1..num loop
s(i) := multiple*((i*(i+1))/2);
end loop;
return s;
end get_sum_multiples;
begin
dbms_output.put_line('Sum of the first ' || to_char(n) || ' multiples of '|| to_char(m) || ' is ' || to_char(get_sum_multiples (m, sn)(n)));
end;
/
Example 5-17 : data Type Compatibility for Collection Assignment.
-----------------------------------------------------------------
Declare
type last_name_type is varray (3) of varchar2(64);
Type surname_type is varray(30) of varchar2(42);
group1 last_name_type := last_name_type ('Jones', 'Wong', 'Marceau');
group2 last_name_type := last_name_type ('Klein', 'Patsos', 'Singh');
group3 surname_type := surname_type ('Trevisi', 'Macleod', 'Marquez');
begin
group1 := group2;
--Not allowed becaus they have different data types
-- group2 := group3;
end;
/
Example - 5-18: Assigning a Null value to a Nested Table.
---------------------------------------------------------
Declare
type dnames_tab is table of varchar2(30);
dept_names dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
empty_set dnames_tab;
begin
if
dept_names is not null then
dbms_output.put_line('OK, now dept_names is not null');
end if;
dept_names := empty_set;
if
dept_names is null then
dbms_output.put_line('OK, now dept_names has become null');
end if;
dept_names := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
end;
/
Example 5-19 : Assigning Nested Tables with Set Operators.
----------------------------------------------------------
Declare
Type nested_type is table of number;
nt1 nested_type := nested_type(1, 2, 3);
nt2 nested_type := nested_type(3, 2, 1);
nt3 nested_type := nested_type(2, 3, 1, 3);
nt4 nested_type := nested_type(1, 2, 4);
answer nested_type;
procedure print_nested_table(the_nt nested_type) is
output varchar2(128);
begin
if the_nt is null then
dbms_output.put_line('Results : Null');
return;
end if;
if the_nt.count = 0 then
dbms_output.put_line('Results : Empty Set');
return;
end if;
for i in the_nt.first..the_nt.last loop
output := output|| the_nt(i)|| ' ';
end loop;
dbms_output.put_line('Results : '||output);
end;
begin
answer := nt1 multiset union nt4;
print_nested_table(answer);
answer := nt1 multiset union distinct nt4;
print_nested_table(answer);
answer := nt1 multiset intersect nt4;
print_nested_table(answer);
answer := nt1 multiset intersect distinct nt4;
print_nested_table(answer);
answer := nt1 multiset except nt4;
print_nested_table(answer);
answer := nt1 multiset except distinct nt4;
print_nested_table(answer);
prln('-----------------------------');
answer := nt1 multiset intersect distinct nt4;
print_nested_table(answer);
answer := nt1 multiset union nt3;
print_nested_table(answer);
answer := nt1 multiset union distinct nt4;
print_nested_table(answer);
answer := nt1 multiset intersect nt4;
print_nested_table(answer);
answer := nt1 multiset intersect distinct nt4;
print_nested_table(answer);
answer := set(nt3);
print_nested_table(answer);
end;
/
Example 5-20 : Assigning Values to Varrays with Complex data Types.
------------------------------------------------------------------
Declare
Type emp_name_rec is record(
firstName employees.first_name%type,
lastName employees.last_name%type,
hireDate employees.hire_date%type );
Type EmpList_Arr is varray(10) of emp_name_rec;
SeniorSalespeople EmpList_arr;
Cursor c1 is select first_name, last_name, hire_date from employees;
Type NameSet is table of c1%rowtype;
SeniorTen NameSet;
EndCounter number := 10;
begin
seniorSalespeople := empList_arr();
select first_name, last_name, hire_date bulk collect into seniorTen from employees
where job_id = 'SA_REP'
order by hire_date;
if seniorTen.last > 0 then
if seniorTen.last < 10 then
endCounter := seniorTen.last;
end if;
for i in 1..endcounter loop
seniorSalespeople.extend(1);
seniorSalesPeople(i) := seniorTen(i);
dbms_output.put_line(SeniorSalesPeople(i).lastname||' ---------- ' || seniorSalesPeople(i).firstName||' -------------- '|| seniorsalesPeople(i).hiredate);
end loop;
end if;
end;
/
Example 5-21 : Assigningn Values to Tables with Complex Data Types.
-------------------------------------------------------------------
Declare
Type emp_name_rec is record ( firstName employees.first_name%type, lastName employees.last_name%type, hireDate employees.hire_date%type);
Type EmpList_tab is table of emp_name_rec;
SeniorSalesPeople EmpList_tab;
Cursor c1 is select first_name, last_name, hire_date from employees;
endcounter number := 10;
Type EmpCurType is ref Cursor;
emp_cv EmpCurType;
begin
open emp_cv for select first_name, last_name, hire_date
from employees
where job_id = 'SA_REP' order by hire_date;
fetch emp_cv bulk collect into SeniorSalesPeople;
close emp_cv;
if seniorsalespeople.last > 0 then
if seniorsalespeople.last > 10 then
endcounter := seniorsalespeople.last;
end if;
for i in 1..endcounter loop
prln(seniorsalespeople(i).lastname ||' ========'|| seniorsalespeople(i).firstname||'========'|| seniorsalespeople(i).hiredate);
end loop;
end if;
end;
/
Example 5-22 : Checking if a Collection is null.
------------------------------------------------
Declare
Type emp_name_rec is record(
firstName employees.first_name%type,
lastName employees.last_name%type,
hireDate employees.hire_date%type);
Type staff is table of emp_name_rec;
members staff;
begin
if members is null then
dbms_output.put_line('Null');
else
dbms_output.put_line('Not Null');
end if;
end;
/
Example 5-23 : Coparing Two Nested Tables.
------------------------------------------
Declare
Type dnames_tab is table of varchar2(30);
dept_names1 dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
dept_names2 dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance', 'Payroll');
dept_names3 dnames_tab := dnames_tab('Shipping', 'Sales', 'Finance');
begin
if dept_names1 = dept_names2 then
dbms_output.put_line('dept1 and dept2 have the same members');
else
dbms_output.put_line('dept1 and dept2 have different members');
end if;
if dept_names1 = dept_names3 then
dbms_output.put_line('dept1 and dept2 have the same members');
else
dbms_output.put_line('dept1 and dept3 have different members');
end if;
end;
/
Example 5-24 : Comparing Nested Tables with Set Operators.
----------------------------------------------------------
Declare
Type nested_type is table of number;
nt1 nested_type := nested_type(1, 2, 3);
nt2 nested_type := nested_type(3, 2, 1);
nt3 nested_type := nested_type(2, 3, 1, 3);
nt4 nested_type := nested_type(1, 2, 4);
answer boolean;
howmany number;
procedure testify(truth boolean default null, quantity number default null) is
begin
if
truth is not null then
dbms_output.put_line(Case truth when true then 'True' when false then 'False' end);
end if;
if quantity is not null then
dbms_output.put_line(quantity);
end if;
end;
begin
answer := nt1 in (nt2, nt3, nt4);
testify(truth => answer);
answer := nt1 submultiset of nt3;
testify(truth => answer);
answer := nt1 not submultiset of nt4;
testify(truth => answer);
howmany := cardinality(nt3);
testify (quantity => howmany);
howmany := cardinality(set(nt3));
testify (quantity => howmany);
answer := 4 member of nt1;
testify (truth => answer);
answer:= nt3 is a set;
testify (truth => answer);
answer:= nt3 is not a set;
testify (truth => answer);
answer:= nt1 is empty;
testify (truth => answer);
end;
/
>>>>>>>>>>>Use of Cardinality : Calculate the element of a nested table>>>>>>>>>>>>>>>>>
declare
type a is table of number;
howmany number;
b a := a(1, 2, 3, 4);
begin
howmany := cardinality(b);
dbms_output.put_line(howmany);
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Example 5-25 : Multilevel Array.
--------------------------------
Declare
Type t1 is varray(10) of number;
Type nt1 is varray(10) of t1;
va t1 := t1(1, 2, 3);
nva nt1 := nt1 (va, t1(55, 6, 73), t1(2, 4), va);
i integer;
va1 t1;
begin
i := nva (1)(3);
dbms_output.put_line('I = '|| i);
--dbms_output.put_line(nva (5)(1));
dbms_output.put_line('I = '|| i);
nva.extend;
dbms_output.put_line(nva (5)(1));
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
Type t1 is varray(10) of number;
Type nt1 is varray(10) of t1;
va t1 := t1(1, 2, 3);
va2 t1 := t1(4, 5, 6);
nva nt1 := nt1(t1(55, 6, 73), t1(2, 4), va2);
begin
null;
end;
/
Example : 5-26 : Multilevel Nested Table.
-----------------------------------------
DECLARE
TYPE tb1 IS TABLE OF VARCHAR2(20);
TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
TYPE Tv1 IS VARRAY(10) OF INTEGER;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
-- table of varray elements
BEGIN
vntb1.EXTEND;
vntb1(2) := vntb1(1);
dbms_output.put_line(vntb1(1)(1)||' '||vntb1(1)(2));
-- delete the first element in vntb1
vntb1.DELETE(1);
-- delete the first string
-- from the second table in the nested table
vntb1(2).DELETE(1);
END;
/
>>>>>>>>>my excercise>>>>>>>>>>>>>>>>>
DECLARE
TYPE tb1 IS TABLE OF VARCHAR2(20);
TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
TYPE Tv1 IS VARRAY(10) OF INTEGER;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
-- table of varray elements
BEGIN
vntb1.EXTEND;
vntb1(2) := vntb1(1);
dbms_output.put_line(vntb1(1)(1)||' '||vntb1(2)(2));
-- delete the first element in vntb1
vntb1.DELETE(1);
dbms_output.put_line(vntb1(2)(1)||' '||vntb1(2)(2));
-- delete the first string
-- from the second table in the nested table
vntb1(2).DELETE(1);
END;
/
>>>>>>>>>my excercise>>>>>>>>>>>>>>>>>
DECLARE
TYPE tb1 IS TABLE OF VARCHAR2(20);
TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
TYPE Tv1 IS VARRAY(10) OF INTEGER;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
-- table of varray elements
BEGIN
vntb1.EXTEND;
vntb1(2) := vntb1(1);
dbms_output.put_line(vntb1(1)(1)||' '||vntb1(2)(2));
-- delete the first element in vntb1
vntb1.DELETE(1);
dbms_output.put_line(vntb1(2)(1)||' '||vntb1(2)(2));
-- delete the first string
-- from the second table in the nested table
vntb1(2).DELETE(1);
dbms_output.put_line(vntb1(2)(2)||' '||vntb1(2)(2));
END;
/
>>>>>>>>>my excercise>>>>>>>>>>>>>>>>>
create procedure prln(obj varchar2) is
begin
dbms_output.put_line(obj);
end;
/
Example 5-27 : Multilevel Associative Array.
-------------------------------------------
Declare
Type tb1 is table of integer index by pls_integer;
Type ntb1 is table of tb1 index by pls_integer;
Type va1 is varray(10) of varchar2(20);
Type ntb2 is table of va1 index by pls_integer;
v1 va1 := va1('hello', 'world');
v2 ntb1;
v3 ntb2;
v4 tb1;
v5 tb1;
begin
v4(1) := 34;
v4(2) := 46456;
v4(456) := 343;
v2(23) := v4;
v3 (34) := va1(33, 456, 656, 343);
v5(1) := 10;
v5(2) := 20;
v2(35) := v5;
--v2(35)(2) := 78;
V2(35) := V4;
prln(v2(35)(2));
prln(v5.count);
end;
/
Example 5-28 : Checking Wheter a Collection Element Exists.
----------------------------------------------------------------------------
Declare
Type NumList is table of Integer;
n NumList := NumList(1, 3, 5, 7);
begin
--n.delete(2);
prln(n(1));
if n.exists(1) then
prln('OK, Element #1 exists! ');
else
prln('OK, Element #1 has been Deleted!');
end if;
if n.exists(2) = false then
prln('OK, Element #2 has been Deleted!');
else
prln(n(2));
end if;
if n.exists(99) = false then
prln('OK, Element #99 does not exists at all! ');
end if;
end;
/
Example 5-29 : Counting Collection Elements with Count.
-----------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList (2, 4, 6, 8);
Begin
dbms_output.put_line('There are '|| n.count || ' elements in N.');
n.extend(3); -- Add 3 new elements at the end.
dbms_output.put_line('There are '|| n.count || ' elements in N.');
n := NumList (86, 99, 15);
dbms_output.put_line('There are '|| n.count || ' elements in N.');
n.trim(2);
dbms_output.put_line('There are '|| n.count || ' elements in N.');
end;
/
Example 5-30 : Checking the Maximum Size of a Collection with LIMIT.
------------------------------------------------------------------------------------------
Declare
Type dnames_var is varray(7) of varchar2(30);
dept_names dnames_var := dnames_var('Shipping', 'Sales', 'Finance', 'Payroll');
Begin
prln('dept_names has '|| dept_names.count);
prln('dept_names''s type can hold a maximum of '|| dept_names.limit ||' elements');
prln('The maximum number you can use with '|| ' dept_names.extend() is '|| (dept_names.limit-dept_names.count));
prln(dept_names.first || ' '|| dept_names.last);
end;
/
Example 5-31: Using First and Last with a Collection.
------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList (1, 3, 5, 7);
counter integer;
Begin
prln('The first subscript of N is '||n.first);
prln('The last subscript of N is '||n.last);
for i in n.first..n.last loop
prln('Element #' || i || ' = ' || n(i));
end loop;
n.delete(2);
if n is not null then
counter := n.first;
while counter is not null loop
prln('Element # '|| counter || ' = '|| n(counter));
counter := n.next(counter);
end loop;
else
prln('N is null, nothing to do' );
end if;
end;
/
Example 5-32 : Using Prior and Next to Access Collection Elements.
-------------------------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList(1966, 1971, 1984, 1989, 1999);
Begin
prln('The element after #2 is #'||n.next(2));
prln('The element before #2 is #'||n.prior(2));
n.delete(3);
prln('The element after #2 is #'||n.next(2));
if n.prior(n.first) is null then
prln('Can''t get Prior of the first element or Next of the Last');
end if;
end;
/
Example 5-33 : Using Next to Access Elements of a Nested Table.
-----------------------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList
Example 5–34 Using EXTEND to Increase the Size of a Collection
----------------------------------------------------------------------------------
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(2,4,6,8);
x NumList := NumList(1,3);
PROCEDURE print_numlist(the_list NumList) IS
output VARCHAR2(128);
BEGIN
FOR i IN the_list.FIRST .. the_list.LAST
LOOP
output :=
output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';
END LOOP;
DBMS_OUTPUT.PUT_LINE(output);
END;
BEGIN
DBMS_OUTPUT.PUT_LINE
('At first, N has ' || n.COUNT || ' elements.');
n.EXTEND(5); -- Add 5 elements at the end.
DBMS_OUTPUT.PUT_LINE
('Now N has ' || n.COUNT || ' elements.');
-- Elements 5, 6, 7, 8, and 9 are all NULL.
print_numlist(n);
DBMS_OUTPUT.PUT_LINE
('At first, X has ' || x.COUNT || ' elements.');
x.EXTEND(4,2); -- Add 4 elements at the end.
DBMS_OUTPUT.PUT_LINE
('Now X has ' || x.COUNT || ' elements.');
-- Elements 3, 4, 5, and 6 are copies of element #2.
print_numlist(x);
END;
/
Example : 5-35 : Using Trim to Decrease the Size of a Collection.
--------------------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList (1, 2, 3, 5, 7, 11);
procedure print_numlist(the_list NumList) is
output varchar2(128);
begin
if
n.count = 0 then
prln('No elements in collection !');
else
for i in the_list.first..the_list.last
loop
output := output|| nvl(to_char(the_list(i)), 'NULL') || ' ' ;
end loop;
prln(output);
end if;
end;
Begin
print_numlist(n);
n.trim(2);
print_numlist(n);
n.trim(n.count);
print_numlist(n);
Begin
n := NumList (1, 2 , 3);
n.trim(100);
Exception
when subscript_beyond_count then
prln('There are not 100 elemnts to trim');
n := NumList(1, 2, 3, 4);
n.delete(3);
n.trim(2);
print_numList(n);
end;
end;
/
Example 5-36 : Using Trim on Deleted Elements.
-----------------------------------------------------------
Declare
Type CourseList is table of Varchar2(10);
Courses CourseList;
Begin
Courses := CourseList('Biol 4412', 'Psych 3112', 'Anth 3001');
prln( courses.last);
courses.delete(courses.last);
prln(courses.last);
courses.trim(courses.count);
prln(courses(courses.count));
prln(courses(1));
end;
/
Example 5-37 : Using the Delete Method on a Collection.
----------------------------------------------------------------------
Declare
Type NumList is table of number;
n NumList := NumList(10, 20, 30, 40, 50, 60, 70, 80, 90, 100);
Type NickList is table of Varchar2(64) index by varchar2(21);
nickNames NickList;
Begin
n.delete(2);
n.delete(3, 6); -- deletes elements 3 through 6
n.delete(7, 7);
n.delete(6, 3); -- does nothing since 6 > 3
n.delete; -- delete all elements.
nicknames('Bob') := 'Robert';
nicknames('Buffy') := 'Esmerelda';
nicknames('Chip') := 'Charles';
nicknames('Dan') := 'Daniel';
nicknames('Fluffy') := 'Ernestina';
nicknames('Rob') := 'Robert';
nicknames('Dan') := 'Daniel';
prln(nicknames.count);
nicknames.delete('Chip');
prln(nicknames.count);
nicknames.delete;
prln(nicknames.count);
end;
/
Example 5-38 : Collection Exception.
----------------------------------------------
Declare
Type WordList is table of varchar2(5);
Words WordList;
err_msg varchar2(100);
procedure display_error is
Begin
err_msg := substr(sqlerrm, 1, 100);
prln('Error Message = ' || err_msg);
End;
Begin
Begin
words(1) := 10;
Exception
When others then display_error;
end;
words := wordList('1st', '2nd', '3rd');
words(3) := words(1)||'+2';
Begin
words(3) := 'Longer than 5 characters';
exception
when others then display_error;
end;
Begin
words('B') := 'dunno';
exception
when others then display_error;
end;
Begin
words(0) := 'zero';
exception
when others then display_error;
end;
Begin
words(4) := 'maybe';
exception
when others then display_error;
end;
Begin
words.delete(1);
if words(1) = 'First' then NULL;
end if;
exception
when others then display_error;
End;
end;
/
Example 5-39 : How Invalid Subscript are Handled with Delete(n).
----------------------------------------------------------------------------------
Declare
Type NumList is table of number;
nums NumList := NumList(10, 20, 30);
Begin
nums.delete(-1);
nums.delete(3);
nums(3) := 30;
prln(nums.count);
End;
/
Example 5-40 : Incompatibility Between Package and Local Collection Types.
------------------------------------------------------------------------------------------------
Create package pkg as
Type NumList is table of Number;
procedure print_numlist(nums NumList);
end pkg;
/
Create or replace package body pkg as
procedure print_numlist(nums NumList) is
Begin
for i in nums.first..nums.last loop
prln(nums(i));
end loop;
end;
end pkg;
/
Declare
Type NumList is table of number;
n1 pkg.NumList := pkg.NumList(2, 4);
n2 NumList := NumList(6, 8);
Begin
pkg.print_numlist(n1);
--pkg.print_numlist(n2); -- not allowed
end;
/
Example 5-41 : Declaring and Initializing a Simple Record.
-----------------------------------------------------------------------
Declare
Type DeptRecTyp is record(
deptid number (4) not null := 99,
dname departments.department_NAME%type,
loc departments.location_id%type,
region regions%rowtype
);
Dept_Rec DeptRecTyp;
BEGIN
dept_rec.dname := 'PURCHASING';
END;
/
Example 5-42 : Declaring and Initializing RECORD Types.
------------------------------------------------------
DECLARE
Type rec1_t is record (
field1 varchar2(16),
field2 number,
field3 DATE);
Type rec2_t is record (
id integer not null := -1,
name varchar2(64) not null := '[anonymous]'
);
rec1 rec1_t;
rec2 rec2_t;
rec3 employees%rowtype;
Type rec4_t is record (first_name employees.first_name%type,
last_name employees.last_name%type,
rating number
);
rec4 rec4_t;
BEGIN
rec1.field1 := 'Yesterday';
rec1.field2 := 60;
rec1.field3 := trunc(sysdate-1);
prln(rec2.name);
END;
/
Example 5-43 : Using %ROWTYPE to Declare a Record.
-----------------------------------------------------------------------
Declare
Cursor c1 is
select department_id, department_name, location_id
from departments;
rec1 c1%rowtype;
Type DeptRec2 is Record (dept_id departments.department_id%type,
dept_name departments.department_name%type,
dept_loc departments.location_id%type);
rec2 DeptRec2;
Type DeptRec3 is record(
dept_id number,
dept_name varchar2(14),
dept_loc varchar2(13)
);
rec3 DeptRec3;
Begin
null;
end;
/
Example 5-44 : Returning a Record from a Function.
-----------------------------------------------------------------
Declare
Type EmpRecTyp is Record(
emp_id number(6),
salary number(8, 2)
);
Cursor desc_salary return EmpRecTyp is
select employee_id, salary
from employees
order by salary desc;
emp_rec EmpRecTyp;
Function nth_highest_salary(n integer) return EmpRecTyp is
Begin
open desc_salary;
for i in 1..n loop
fetch desc_salary into emp_rec;
end loop;
return emp_rec;
end nth_highest_salary;
Begin
null;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
a number;
b number;
function
mysum(x number, y number) return number is
begin
return x*y;
end mysum;
Begin
prln(mysum(2, 15));
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Example 5-45 : Using a Record as Parameter to a Procedure.
---------------------------------------------------------------------------
Declare
Type EmpRecTyp is Record( emp_id number (6), emp_sal number(8, 2));
Procedure raise_salary (emp_info EmpRecTyp) is
Begin
update employees set salary = salary+ salary* .10
where employee_id = emp_info.emp_id;
end raise_salary;
Begin
null;
end;
/
Example 5-46 : Declaring a Nested Record.
-----------------------------------------------------
Declare
Type TimeTyp is record(minutes smallint, hours smallint);
Type meetingTyp is record(day date, time_of Timetyp, dept departments%rowtype, place varchar2(20), purpose varchar2(50));
meeting meetingTyp;
seminar meetingTyp;
Begin
seminar.time_of := meeting.time_of;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare
Type TimeTyp is record(minutes smallint, hours smallint);
Type meetingTyp is record(day date, time_of Timetyp, dept departments%rowtype, place varchar2(20), purpose varchar2(50));
meeting meetingTyp;
seminar meetingTyp;
Begin
meeting.day := '01-JUN-15';
seminar.time_of := meeting.time_of;
end;
/
Example 5-47 : Assigning Default values to a record.
----------------------------------------------------------------
declare
type recordType is record(field1 number, field2 varchar2(32) default 'something');
rec1 recordType;
rec2 recordType;
begin
rec1.field1 := 100;
rec1.field2 := 'something else';
rec2.field1 := 200;
rec1 := rec2;
dbms_output.put_line(rec1.field1 ||' ' || rec1.field2);
end;
/
Example 5-48 : Assigning all the fields of a record in one statement.
---------------------------------------------------------------------
declare
type DeptRec1 is record (dept_num number(2), dept_name varchar2(14));
type DeptRec2 is record (dept_num number(2), dept_name varchar2(14));
dept1_info deptRec1;
dept2_info deptRec2;
dept3_info deptRec2;
begin
--dept1_info := dept2_info;
dept3_info.dept_num := 10;
dept3_info.dept_name := 'Admin';
dept2_info := dept3_info;
dbms_output.put_line('Department Id is '|| dept2_info.dept_num);
end;
/
------------------------------------------------------------------------
declare
type recordtype is record (last employees.last_name%type, id employees.employee_id%type);
cursor c1 is select last_name, employee_id from employees;
rec1 recordType;
rec2 c1%rowtype;
begin
select last_name, employee_id into rec2 from employees
where rownum <2;
rec1 := rec2;
dbms_output.put_line(rec1.last);
end;
/
---------------------------------------------------------------------------
declare
cursor c1 is select last_name from employees;
begin
for item in c1 loop
dbms_output.put_line(item.last_name);
end loop;
end;
/
Example : 5-49 Using select itno to assign values in a Record.
----------------------------------------------------------------------------
declare
type recordType is record(last employees.last_name%type, id employees.employee_id%type);
rec1 recordType;
begin
select last_name, employee_id into rec1 from employees where rownum <2;
dbms_output.put_line('employee '|| rec1.id);
end;
/
declare
type recordType is record(last employees.last_name%type, id employees.employee_id%type);
rec1 recordType;
begin
select last_name, employee_id into rec1 from employees where rownum <2;
dbms_output.put_line('employee id : '|| rec1.id);
dbms_output.put_line('employee name : '|| rec1.last);
end;
/
--------------------------
declare
cursor c1 is select employee_id, last_name from employees order by employee_id;
v1 c1%rowtype;
begin
open c1;
fetch c1 into v1;
dbms_output.put_line(v1.employee_id ||' '||v1.last_name );
end;
/
Example 5-50 : Insertig a PL/SQL Record Using %Rowtype
---------------------------------------------------------------------------
declare
dept_info departments%rowtype;
begin
dept_info.department_id := 300;
dept_info.department_name := 'Personnel';
dept_info.location_id := 1700;
insert into departments values dept_info;
end;
/
Example 5-51 : Updating a Row Using a Record
--------------------------------------------------------------------------
declare
dept_info departments%rowtype;
begin
dept_info.department_id := 300;
dept_info.department_name := 'Personnel2';
dept_info.location_id := 1700;
update departments set row = dept_info
where department_id = 300;
end;
/
Example 5-52 : Using the Returning Into Clause with a Record.
---------------------------------------------------------------------------
Declare
type EmpRec is record (last_name employees.last_name%type, salary employees.salary%type, commission_pct employees.commission_pct%type);
emp_info EmpRec;
emp_id number := 100;
begin
update employees set salary = salary* nvl(commission_pct, 1)
where employee_id = emp_id
returning last_name, salary, commission_pct into emp_info;
dbms_output.put_line('Just gave a raise to '|| emp_info.last_name || ' who now makes '|| emp_info.salary);
rollback;
end;
/
---------------------------------------------------------------------------------
Declare
type EmpRec is record (last_name employees.last_name%type, salary employees.salary%type, commission_pct employees.commission_pct%type);
emp_info EmpRec;
emp_id number := 101;
begin
delete from emp
where employee_id = emp_id
returning last_name, salary, commission_pct into emp_info;
dbms_output.put_line('Just gave a raise to '|| emp_info.last_name || ' who now makes '|| emp_info.salary);
rollback;
end;
/
Example 5-53 : Using Bulk Collect with Selectr into statement.
-------------------------------------------------------------
Declare
Type employeeSet is table of employees%rowtype;
underPaid employeeSet;
Cursor c1 is select first_name, last_name from employees;
type NameSet is table of c1%rowtype;
some_names NameSet;
begin
select * bulk collect into underPaid from employees
where salary < 5000 order by salary desc;
dbms_output.put_line(underPaid.count|| ' people make less than 5000');
for i in underPaid.first..underPaid.last loop
dbms_output.put_line(underpaid(i).last_name || ' makes '|| underPaid(i).salary || ' '||underPaid(i).department_id);
end loop;
select first_name, last_name bulk collect into some_names from employees where rownum < 11;
for i in some_names.first..some_names.last loop
dbms_output.put_line(some_names(i).first_name ||' ' || some_names(i).last_name);
end loop;
end;
/
>>>>>>>>>>>>excercise>>>>>>>>>>
declare
a departments%rowtype;
begin
a.department_id := 100;
dbms_output.put_line(a.department_id);
end;
/
Creating Object
--------------------
CREATE OR REPLACE TYPE CUSTOMER_TYP_DEMO AS OBJECT (
CUSTOMER_ID NUMBER(6),
CUST_FIRST_NAME VARCHAR2(2),
CUST_LAST_NAME VARCHAR2(20),
CUST_ADDRESS CUST_ADDRESS_TYP,
PHONE_NUMBERS PHONE_LIST_TYP,
NLS_LANGUAGE VARCHAR2(30),
CREDIT_LIMIT NUMBER(30),
CUST_EMAIL VARCHAR2(30),
CUST_ORDERS ORDER_LIST_TYP
);
/
CREATE OR REPLACE TYPE ASS AS TABLE OF CUSTOMER_TYP_DEMO;
/
CREATE TYPE DATA_TYP1 AS OBJECT (YEAR NUMBER, MEMBER FUNCTION PROD (INVENT NUMBER) RETURN NUMBER);
/
CREATE TYPE BODY DATA_TYP1 IS
MEMBER FUNCTION PROD(INVENT NUMBER) RETURN NUMBER IS
BEGIN
RETURN (YEAR + INVENT);
END;
END;
/
CREATE TYPE CORPORATE_CUSTOMER_TYP_DEMO UNDER CUSTOMER_TYP (ACCOUNT_MGR_ID NUMBER(6));
/
CREATE or replace TYPE address_t AS OBJECT
EXTERNAL NAME 'Examples.Address' LANGUAGE JAVA
USING SQLData(
STREET_ATTR VARCHAR (250) EXTERNAL NAME 'STREET',
CITTY_ATTR VARCHAR(250) EXTERNAL NAME 'CITY',
STATE VARCHAR(50) EXTERNAL NAME 'STATE',
ZIP_CODE_ATTR NUMBER EXTERNAL NAME 'ZIPCODE',
STATIC FUNCTION recom_width RETURN NUMBER
EXTERNAL VARIABLE NAME 'recommendedWidth',
STATIC FUNCTION create_address RETURN address_t
EXTERNAL NAME 'create() return Examples.Address',
STATIC FUNCTION construct RETURN address_t
EXTERNAL NAME 'create() return Examples.Address',
STATIC FUNCTION create_address (street VARCHAR, city VARCHAR, state VARCHAR, zip NUMBER) RETURN address_t
EXTERNAL NAME 'create (java.lang.String, java.lang.String, java.lang.String, int ) return Examples.Address',
STATIC FUNCTION construct (street VARCHAR, city VARCHAR, state VARCHAR, zip NUMBER) RETURN address_t
EXTERNAL NAME 'create (java.lang.String, java.lang.String, java.lang.String, int ) return Examples.Address',
MEMBER FUNCTION to_string RETURN VARCHAR
EXTERNAL NAME 'tojava.lang.String() return java.lang.String',
MEMBER FUNCTION strip RETURN SELF AS RESULT
EXTERNAL NAME 'removeLeadingBlanks() return Examples.Address'
) NOT FINAL;
/
Create type my_type as object
external name 'Examples.Address' language java
using SQLData (myName varchar(25) external name 'myName') not final;
/
Create type my_type2 as object
external name 'Examples' language java
using SQLData (myName varchar(25) external name 'myName') not final;
/
Create type my_type3 as object
external name 'EX' language java
using SQLData (myName varchar(25) external name 'myName') not final;
/
Create or replace Type long_address_t
under address_t
external Name 'Examples.LongAddress' Language Java
Using SQLData(
street2_attr varchar(250) external name 'street2',
country_attr varchar(250) external name 'country',
address_code_attr varchar(50) external name 'addrcode',
static function create_address return long_address_t
external name 'create () return Examples.LongAddress',
static function construct (street varchar, city varchar, state varchar, country varchar, addrs_cd varchar) return long_address_t
external name 'create (java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return Examples.LongAddress',
static function construct return long_address_t
external name 'Examples.LongAddress() return Examples.LongAddress',
Static function create_longaddress(street varchar, city varchar, state varchar, country varchar, addrs_cd varchar) return long_address_t
external name 'Examples.LongAddress(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String ) return Examples.LongAddress',
member function get_country return varchar
external name 'country_with_code () return java.lang.String'
);
/
Create type person_t as object (name varchar2(100), ssn number) not final;
/
Create type employee_t under person_t (department_id number, salary number) not final;
/
Create type part_time_emp_t under employee_t (num_hrs number);
/
Create type textdoc_typ as object (document_typ varchar2(32), formatted_doc blob);
/
Create type phone_list_typ_demo as varray(5) of varchar2(25);
/
Create type textdoc_tab as table of textdoc_typ;
/
create type cust_address_typ2 as object (
street_address varchar2(40),
postal_code varchar2(10),
city varchar2(30),
state_province varchar2(10),
country_id char(2),
phone phone_list_typ
);
/
create type cust_nt_address_typ as table of cust_address_typ2;
/
CREATE TYPE DEMO_TYP1 AS OBJECT (X NUMBER, Y NUMBER);
/
CREATE TABLE DEMO_TAB1 (B1 NUMBER, B2 DEMO_TYP1);
/
INSERT INTO DEMO_TAB1 VALUES (1, DEMO_TYP1(2, 3));
/
CREATE OR REPLACE TYPE DEMO_TYP2 AS OBJECT (A1 NUMBER, MEMBER FUNCTION GET_SQUARE RETURN NUMBER);
/
CREATE TABLE DEMO_TAB2 (COL DEMO_TYP2);
/
CREATE OR REPLACE TYPE BODY DEMO_TYP2 IS
MEMBER FUNCTION GET_SQUARE
RETURN NUMBER
IS X NUMBER;
BEGIN
SELECT C.COL.A1 * C.COL.A1 INTO X FROM DEMO_TAB2 C;
RETURN (X);
END;
END;
/
ANALYTIC FUNCTION : 1. LAG FUNCTION
LAG FUNCTION
------------------------
Syntax: LAG (VALUE EXPRESSION[, OFFSET, DEFAULT[) [RESPECT/IGNORE NULLS] OVER ([QUERY_PARTITION_CLAUSE] ORDER_BY_CLAUSE).................
⦁ Single Row function একটা মাত্র row কে process করে এবং একটি মাত্র value রিটার্ন করে।
⦁ Multiple Row Function একাধিক row কে process করে এবং একটি মাত্র value রিটার্ন করে।
⦁ Analytic Function একাধিক row কে process করে এবং প্রত্যেক row এর বিপরীতে একটি value রিটার্ন করে।
Lag একটি analytic function. এর দ্বারা self join ছাড়া একই সময়ে একাধিক row কে access করা যায়। এই function টি একাধিক row কে process করে এবং প্রত্যেক row এর against এ value রিটার্ন করে।
OFFSET clause টি optional. এই clause টি ব্যবহার না করলে Default 1 নিয়ে নিবে। offset টি শুন্যের উপরে যে কোন integer ব্যবহার করা যাবে। offset দ্বারা প্রদত্ত row থেকে retrieved row এর দূরত্ব বুঝায়।
যেমন- আমরা employees table এর employee_id, last_name এবং salary দেখব। পাশাপাশি প্রত্যেক employee এর salary এর পাশে প্রত্যেক্ অন্য কোন employee এর salary দেখব যে প্রথম employee এর পূর্বে বা পরে join করেছে। অথবা বর্ণানুক্রিমকভাবে সাজালে যার নাম প্রথম ব্যক্তির আগে বা পরে আসে। যে column এর উপর ভিত্তি করে আগে বা পরে নির্ধারণ হবে তা order_by_clause এ ব্যবহৃত হবে।
DEFAUL clause টি দ্বারা বুঝায় যদি কোন value পাওয়া না যায় তাহলে Default Value প্রদর্শন করবে।
RESPECT/IGNORE NULLS clause টিও optional.
নিচের code টি job_id ‘PU_CLERK’ এর অধীনে থাকা employee এর join date, name এবং salary দেখাবে । পাশাপাশি অন্য column আকারে প্রত্যেক employee এর পাশে অন্য একজন employee এর salary প্রদর্শন করবে যে প্রদত্ত employee এর ১ ডেট পূর্বে যোগদান করেছে।
-----------------------------------------------------------------------------------------------------------------------------------------
SELECT HIRE_DATE, LAST_NAME, SALARY, LAG(SALARY, 1, 0) OVER (ORDER BY HIRE_DATE ) AS PREV_SAL FROM EMPLOYEES
WHERE JOB_ID = 'PU_CLERK'
ORDER BY HIRE_DATE;
এই রেজাল্টে আমরা দেখতে পাচ্ছি,
PU_CLERK এর অধীনে প্রথম জয়ন করা employee এর hire_date 18-MAY-95. তার পূর্বে আর কোন employee জয়ন করেনি। তাই prev_sal তে ০ দেখাচ্ছে । কারন আমরা default clause 0 দিয়েছি। এই job_id এর অধীনে দ্বিতীয় ডেট এ জয়ন করা employee টবিয়াস। তার সেলারি ২৮০০ টাকা। তার পাশের কলামে দেখাচ্ছে ৩১০০ টাকা যা হল এক ডেট আগে জয়ন করা employee খূ এর সেলারি।
-----------------------------------------------------------------------------------------------------------------------------------------
নিচের code টি job_id ‘PU_CLERK’ এর অধীনে থাকা employee এর join date, name এবং salary দেখাবে । পাশাপাশি অন্য column আকারে প্রত্যেক employee এর পাশে অন্য একজন employee এর salary প্রদর্শন করবে যে প্রদত্ত employee এর ১ ডেট পর যোগদান করেছে।
SELECT HIRE_DATE, LAST_NAME, SALARY, LAG(SALARY, 1, 0) OVER (ORDER BY HIRE_DATE DESC) AS PREV_SAL FROM EMPLOYEES
WHERE JOB_ID = 'PU_CLERK'
ORDER BY HIRE_DATE;
------------------------------------------------------------------------------------------------------------------------------------------
নিচর code টি দ্বারা আমরা একটি নির্দিষ্ট job_id (PU_CLERK) এর against এ hire_date, last_name, salary দেখব, পাশাপাশি পাশের কলামে অন্য একজন employee এর last_name দেখব যে এক ডেট আগে জয়ন করেছে এবং আরেকটি কলামে অন্য একজন employee এর salary দেখব যে দুই ডেট আগে জয়ন করেছে।
SELECT HIRE_DATE, LAST_NAME, SALARY,
LAG(LAST_NAME, 1, 0) OVER (ORDER BY HIRE_DATE ) AS PREV_NAME,
LAG(SALARY, 2, 0) OVER (ORDER BY HIRE_DATE ) AS PREV_SAL ,
LAG(DEPARTMENT_ID, 1, 0) OVER (ORDER BY DEPARTMENT_ID, 1, 0) AS PREV_DEPT
FROM EMPLOYEES
WHERE JOB_ID = 'PU_CLERK'
ORDER BY HIRE_DATE;
MORE EXERCISES
1. ORDER BY LAST_NAME
SELECT HIRE_DATE, LAST_NAME, SALARY, LAG(SALARY, 4, 0) OVER (ORDER BY LAST_NAME ) AS PREV_SAL FROM EMPLOYEES
--WHERE JOB_ID = 'PU_CLERK'
ORDER BY LAST_NAME;
2. ORDER BY LAST_NAME desc
SELECT HIRE_DATE, LAST_NAME, SALARY, LAG(SALARY,4, 0) OVER (ORDER BY LAST_NAME DESC ) AS PREV_SAL FROM EMPLOYEES
--WHERE JOB_ID = 'PU_CLERK'
ORDER BY LAST_NAME;
3. ORDER BY DEPARTMENT_ID
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, LAG(SALARY, 2, 0) OVER (ORDER BY department_id) OTHER_SALARY FROM EMPLOYEES
WHERE JOB_ID = 'PU_CLERK';
4. ORDER BY JOB_ID
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, LAG(SALARY, 2, 0) OVER (ORDER BY job_id) OTHER_SALARY FROM EMPLOYEES;
5. PARTITION BY DEPARTMENT_ID ORDER BY JOB_ID
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, LAG(SALARY, 2, 0) OVER (partition by department_id ORDER BY job_id) OTHER_SALARY FROM EMPLOYEES
ORDER BY DEPARTMENT_ID;
SUGGESTIONS ARE WELCOME
PREPARED BY MASUMKHAN/ROUND21
Subscribe to:
Posts (Atom)