Index by tables are
user defined data types which is used to store multiple data items. Basically
these tables are unconstrained tables. Index by table having two parts, these
are value field and key field. In value field, oracle server stores actual
data, where as in key field oracle server stores indexes, that’s why index by
table having ‘key value’ pairs and also indexes are by default integers, and
these indexes are in between negative to positive numbers. This index field
behaves like a primary key, it doesn’t accept duplicate values.
Generally index by tables are used to improve
the performance or application, because these tables are stored in memory area,
that’s why these tables are also called a memory tables.
Generally to improve
the performance of the application, these table indexes are using
‘binary-integer’ data type, so we are creating in two step process. First we
are creating type, then only we are creating a variable of that type.
Syntax:-
Step1:- Type typename
is table of data type (size) index by binary_integer;
Step2:- Variablename
typename;
Index by tables, nested
tables, varrays also called as collections. If we want to integrate these
collection methods. These are exists first, last, count, prior, next, delete, delete
(index number, indexnumber). These collection methods are operating on indexes.
Example:
DECLARE
TYPE t1 IS TABLE OF NUMBER (10)
INDEX BY BINARY_INTEGER;
v_t t1;
BEGIN
v_t (1) := 10;
v_t (2) := 20;
v_t (3) := 30;
v_t (4) := 40;
v_t (5) := 50;
DBMS_OUTPUT.put_line
(v_t (4));
DBMS_OUTPUT.put_line
(v_t.FIRST);
DBMS_OUTPUT.put_line
(v_t.LAST);
DBMS_OUTPUT.put_line
(v_t.PRIOR (3));
DBMS_OUTPUT.put_line
(v_t.COUNT);
DBMS_OUTPUT.put_line
(v_t.NEXT (4));
v_t.delete (1, 3);
DBMS_OUTPUT.put_line
(v_t.COUNT);
DBMS_OUTPUT.put_line
(v_t.FIRST);
DBMS_OUTPUT.put_line
(v_t.LAST);
v_t.delete;
DBMS_OUTPUT.put_line
(v_t.COUNT);
END;
*write a pl/sql program
to transfer all employee names form emp table and store it into index by table,
and also display the content form index by table.
DECLARE
TYPE t1 IS TABLE OF VARCHAR2 (10)
INDEX BY BINARY_INTEGER;
V_t t1;
CURSOR c1
IS
SELECT ename FROM emp;
N NUMBER (10) := 1;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_t (n);
EXIT WHEN c1%NOTFOUND;
n := n + 1;
END LOOP;
FOR i IN v_t.FIRST .. v_t.LAST
LOOP
DBMS_OUTPUT.put_line
(v_t (i));
END LOOP;
END;
By Using Bulk Collect
to improve performance:
DECLARE
TYPE t1 IS TABLE OF VARCHAR2 (10)
INDEX BY BINARY_INTEGER;
v_t t1;
BEGIN
SELECT ename
BULK COLLECT INTO v_t
FROM emp;
FOR I IN v_t.FIRST .. v_t.LAST
LOOP
DBMS_OUTPUT.put_line
(v_t (i));
END LOOP;
END;
Example:-
DECLARE
TYPE t1 IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
V_t t1;
BEGIN
FOR I IN 1 .. 10
LOOP
v_t (i) := SYSDATE + i;
END LOOP;
FOR I IN v_t.FIRST .. v_t.LAST
LOOP
DBMS_OUTPUT.put_line
(v_t (i));
END LOOP;
END;
Example:-
DECLARE
TYPE t1 IS TABLE OF VARCHAR2 (10)
INDEX BY VARCHAR2 (10);
V_t t1;
x VARCHAR2 (10);
BEGIN
v_t ('A') := 'murali';
v_t ('B') := 'XYZ';
v_t ('C') := 'ABC';
v_t ('D') := 'ZZZ';
X := 'A';
LOOP
DBMS_OUTPUT.put_line
(v_t (x));
x := v_t.NEXT (x);
EXIT WHEN x IS NULL;
END LOOP;
END;
Example:-
DECLARE
TYPE t1 IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
V_t t1;
X NUMBER (10);
BEGIN
SELECT *
BULK COLLECT INTO v_t
FROM emp;
X := 1;
LOOP
DBMS_OUTPUT.
put_line (
v_t (x).ename
|| ' ' || v_t (x).sal || ' ' || v_t (x).hiredate);
X := v_t.NEXT (x);
EXIT WHEN x IS NULL;
END LOOP;
END;
Example:-
DECLARE
TYPE t1 IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
v_t t1;
BEGIN
SELECT *
BULK COLLECT INTO v_t
FROM emp;
FOR I IN v_t.FIRST .. v_t.LAST
LOOP
DBMS_OUTPUT.
put_line (
v_t (i).ename
|| ' ' || v_t (i).sal || ' ' || v_t (i).deptno);
END LOOP;
END;
Package
specification:-
CREATE OR REPLACE PACKAGE ph1
IS
TYPE t1 IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
FUNCTION f1
RETURN t1;
END;
/
Package
body:-
CREATE OR REPLACE PACKAGE BODY ph1
IS
FUNCTION f1
RETURN t1
IS
V_t t1;
BEGIN
SELECT *
BULK COLLECT INTO v_t
FROM emp;
RETURN v_t;
END f1;
END;
DECLARE
X ph1.t1;
BEGIN
X := ph1.f1;
FOR I IN x.FIRST .. x.LAST
LOOP
DBMS_OUTPUT.
put_line (x (i).ename
|| ' ' || x (i).sal || ' ' || x (i).hiredate);
END LOOP;
Selamat Petang,
ReplyDeleteThe challenge however, is we don’t yet know how it will be used making to hard to assess their value proposition and consequently value.
how to apply refcursor in package with multiple insted of multple cursors in oracle?
Give one example.
Thank you very much and will look for more postings from you.
Regards,
This is an awesome post. Really very informative and creative contents. This concept is a good way to enhance the knowledge. I like it and help me to development very well. Thank you for this brief explanation and very nice information. Well, got a good knowledge.
ReplyDeletePhp course in chennai
Ohayo,
ReplyDeleteBrilliant article, glad I slogged through the PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays it seems that a whole lot of the details really come back to from my past project.
Please, can you advise me on this query if it is correct? I have been asked to produce a report of vacation by staff for the year 2017 and group by line manager. The question is looking at this query, I have ordered by line manager which I think is showing staff under each manager but I am not sure If I need to break on the line manager and how to accomplish this. Please note that I inherited these table definitions.
By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
Many Thanks,
Radhey
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDeleteThis is the first & best article to make me satisfied by presenting good content. I feel so happy and delighted. Thank you so much for this article. tahnks u so much.
ReplyDeleteAi & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
An awesome blog for the freshers. Thanks for posting this information.
ReplyDeletepega robotic process automation training
pega robotic process automation course
learn pega rpa
pega rpa course
pega rpa training
pega rpa
The website is looking bit flashy and it catches the visitors eyes. Design is pretty simple and a good user friendly interface. https://www.vanityliving.com/collections/stools-seating-dubai-uae
ReplyDelete