Scribd
Upload
15 views

PLSQL Collections

Uploaded by

ianzeha21
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
15 views

PLSQL Collections

Uploaded by

ianzeha21
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 8

PLSQL COLLECTIONS :- used to deal with multiple rows...

====================

--INDEX BY TABLE / ASSOCIATIVE ARRAY / PLSQL TABLE (UNBOUNDED)

--NESTED TABLE (UNBOUNDED)

--VARRAY / VARIABLE SIZE ARRAY (BOUNDED) range-set a limit e.g:- 1 to 5

COLLECTION METHODS :-
================

-COUNT

-FIRST

-LAST

-NEXT

-PRIOR

-DELETE

-EXISTS

INDEX BY TABLE / ASSOCIATIVE ARRAY / PLSQL TABLE (UNBOUNDED) :-


================================================================
DECLARE

TYPE greens IS TABLE OF NUMBER INDEX BY binary_integer;


i greens;

BEGIN

i(1) := 10;
i(2) := 20;
i(3) := 30;
i(4) := 40;
i(5) := 50;

FOR x IN 1..5 LOOP


DBMS_OUTPUT.PUT_LINE(i(x));
END LOOP;

END;
/

DECLARE

TYPE greens IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;


i greens;

BEGIN

i(1) := 'Steven';
i(2) := 'Neena';
i(3) := 'Lex';
i(4) := 'Alex';
i(5) := 'David';

FOR x IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(i(x));
END LOOP;

END;
/

DECLARE

TYPE greens IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;


i greens;

BEGIN

i(1) := 'Steven';
i(2) := 'Neena';
i(3) := 'Lex';
i(4) := 'David';
i(5) := 'Alex';

IF i.EXISTS(2) = TRUE THEN


i.DELETE(2);
END IF;

DBMS_OUTPUT.PUT_LINE(i.COUNT);
DBMS_OUTPUT.PUT_LINE(i.FIRST);
DBMS_OUTPUT.PUT_LINE(i.LAST);
DBMS_OUTPUT.PUT_LINE(i.NEXT(1));
DBMS_OUTPUT.PUT_LINE(i.PRIOR(4));

END;
/

DECLARE

TYPE greens IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;


i greens;
BEGIN

i(21) := 'Steven';
i(32) := 'Neena';
i(30) := 'Lex';
i(4) := 'Alex';
i(15) := 'David';

DBMS_OUTPUT.PUT_LINE(i.COUNT);

DBMS_OUTPUT.PUT_LINE(i.FIRST);

DBMS_OUTPUT.PUT_LINE(i.LAST);

DBMS_OUTPUT.PUT_LINE(i.NEXT(30));

DBMS_OUTPUT.PUT_LINE(i.PRIOR(15));

END;
/

6)DECLARE

TYPE greens IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;


i greens;

BEGIN

i(1) := 'Steven';
i(2) := 'Neena';
i(3) := 'Lex';
i(4) := 'Alex';
i(5) := 'David';

DBMS_OUTPUT.PUT_LINE(i(1));
DBMS_OUTPUT.PUT_LINE(i(2));
DBMS_OUTPUT.PUT_LINE(i(3));
DBMS_OUTPUT.PUT_LINE(i(4));
DBMS_OUTPUT.PUT_LINE(i(5));

END;
/

7)DECLARE

TYPE greens IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;


i greens;

BEGIN

i(1) := 'Steven';
i(2) := 'Neena';
i(3) := 'Lex';
i(4) := 'David';
i(5) := 'Alex';
FOR x IN 1..i.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(i(X));
END LOOP;

END;
/

8)DECLARE

TYPE greens IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;


i greens;

BEGIN

i(1) := 'Steven';
i(2) := 'Neena';
i(3) := 'Lex';
i(4) := 'David';
i(5) := 'Alex';

FOR x IN i.FIRST..i.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(i(X));
END LOOP;

END;
/

9)DECLARE

TYPE greens IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;


i greens;

BEGIN

i(1) := 'Steven';
i(2) := 'Neena';
i(3) := 'Lex';
i(4) := 'David';
i(5) := 'Alex';
i(6) := 'Kumar';

FOR x IN i.FIRST..i.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(i(X));
END LOOP;

END;
/
NESTED TABLE (UNBOUNDED):- 3 differences between index by table and nested
table.....
=====================
1)DECLARE

TYPE greens IS TABLE OF NUMBER ;


i GREENS;

BEGIN

i:= greens(10,20,30,40,50);
dbms_output.put_line(i.count);

FOR x IN 1..5 LOOP


DBMS_OUTPUT.PUT_LINE(i(x));
END LOOP;

END;
/

CREATE TYPE greenss IS TABLE OF VARCHAR2(20); database directly


/

DROP TYPE greenss;

VARRAY :-
==============

VARIABLE SIZE ARRAY (BOUNDED)

METHODS :-
===========

-LIMIT

-TRIM

-EXTEND

1)DECLARE

TYPE greens IS VARRAY(5) OF VARCHAR2(20);


i greens;

BEGIN

i := greens('Steven','Neena','Lex','X','Y');

FOR x IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(i(x));
END LOOP;

END;
/

===========

2)DECLARE

TYPE greens IS VARRAY(5) OF VARCHAR2(20);


i greens;

BEGIN

i := greens('Steven','Neena','Lex','X','Y');

DBMS_OUTPUT.PUT_LINE(i.COUNT);

END;
/

4)DECLARE

TYPE greens IS VARRAY(50) OF VARCHAR2(20);


i greens;

BEGIN

i := greens('Steven','Neena','Lex','X','Y','Z','Hema');

FOR x IN i.FIRST..i.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(i(x));
END LOOP;

END;
/

====

5)DECLARE

TYPE greens IS VARRAY(5) OF VARCHAR2(20);


i greens;

BEGIN

i := greens('Steven','Neena','Lex','X','Y');

FOR x IN 1..5 LOOP


DBMS_OUTPUT.PUT_LINE(i(x));
END LOOP;
END;
/

6)DECLARE

TYPE greens IS VARRAY(50) OF VARCHAR2(20);


i greens;

BEGIN

i := greens('Steven','Neena','Lex','X','Y');

FOR x IN i.FIRST..i.COUNT LOOP


DBMS_OUTPUT.PUT_LINE(i(x));
END LOOP;

END;
/

7)DECLARE
TYPE greens IS VARRAY(50) OF VARCHAR2(20);
i greens;

BEGIN
i := greens('Steven','Neena','Lex','X','Y');

DBMS_OUTPUT.PUT_LINE(i.limit);

i.TRIM(2);

DBMS_OUTPUT.PUT_LINE(i.COUNT);

i.EXTEND();

DBMS_OUTPUT.PUT_LINE(i.COUNT);

i.EXTEND(20);

DBMS_OUTPUT.PUT_LINE(i.COUNT);
END;
/

8)DECLARE

TYPE greens IS VARRAY(50) OF VARCHAR2(20);


i greens;

BEGIN
i := greens('Steven','Neena','Lex','X','Y','Z','Hema');

DBMS_OUTPUT.PUT_LINE(i.COUNT);
i.limit;
DBMS_OUTPUT.PUT_LINE(i.COUNT);
i.TRIM(2);
DBMS_OUTPUT.PUT_LINE(i.COUNT);
i.EXTEND;
DBMS_OUTPUT.PUT_LINE(i.COUNT);
i.EXTEND(20);
DBMS_OUTPUT.PUT_LINE(i.COUNT);

END;
/

============================

%type---1 column- 1 variable

%rowtype--* -1 variable

record--multiple columns--1 variable

collections--datatype---multiple rows---1 var

You might also like