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