Example:
1- Define Record type
2- Define PLSQL table type
3- Define the PLSQL table.
1- TYPE Seasolo_ERP_Call_Rec_Type IS RECORD(
PART_NO VARCHAR2(50),
C_M_FLAG VARCHAR2(50),
QUANTITY VARCHAR2(50),
REQ_DOCK_DATE VARCHAR2(50),
LINE_KEY VARCHAR2(50),
SEA_PART_NO seaeng_ccitemnumber.ccitemnumber%TYPE,
SEA_PART_DESC VARCHAR2(240),
SEA_MOD_NO seaeng_ccfamilymodelinfo.stmodelnumber%TYPE,
CUST_PART_NO VARCHAR2(30),
LIST_PRICE VARCHAR2(50),
SPA_NO VARCHAR2(50),
SPA_ID VARCHAR2(50),
WARR_PRICE VARCHAR2(50),
WARR_MONTH VARCHAR2(50),
TOTAL_WARR_MONTH VARCHAR2(50),
CPT_NAME VARCHAR2(6),
BILLING_FLAG VARCHAR2(50),
PRICING_RULE VARCHAR2(50),
DESCRIPTION VARCHAR2(50),
MIN_ORDER_QTY VARCHAR2(50),
MIN_ORDER_QTY_MULTI VARCHAR2(50),
ERROR_CODE VARCHAR2(50),
ERROR_MSG VARCHAR2(1000),
FREIGHT_TERM VARCHAR2(50),
FREIGHT_QUAL VARCHAR2(50),
REGION seaoe_eta_zones.zone_description%TYPE);
2- TYPE Chg_Req_Tab_Type IS TABLE OF Seasolo_ERP_Call_Rec_Type
INDEX BY PLS_INTEGER;
3- l_tab Chg_Req_Tab_Type;
Manipulation à
l_tab.delete;
l_ctr := 1;
For cur1 in cur_abc
loop
if l_ctr = 1 then
l_tab(1).c1 := cur1.c1;
l_tab(1).c2 := cur1.c2;
l_ctr := l_ctr + 1;
else
l_tab(l_tab.last + 1).c1 := cur1.c1;
l_tab(l_tab.last + 1).c2 := cur1.c2;
end if;
end loop;
for indx in 1..l_tab.count
loop
dbms_output.put_line( 'avg sal = ', l_tab(indx).c1 + 20);
end loop;
Also bulk collect to a PLSQL table...
SELECT * BULK COLLECT
INTO l_chg_req_tab
FROM seacmgt_change_request_log
WHERE erp_interfaced_flag = 'N'
AND error_code IS NULL
AND error_message IS NULL
AND return_status IS NULL
AND operation_code IN ('ADD_ORDER', 'ADD_LINE')
AND active_flag = 'Y'
AND cart_key IN
( SELECT cart_key
FROM seacmgt_change_request_log
WHERE erp_interfaced_flag = 'N'
AND error_code IS NULL
AND error_message IS NULL
AND return_status IS NULL
AND operation_code = 'ADD_ORDER'
AND active_flag = 'Y')
ORDER BY change_request_key ASC;
Showing posts with label Advance PLSQL. Show all posts
Showing posts with label Advance PLSQL. Show all posts
Feb 2, 2010
Collection Methods
Collection Methods
PL/SQL collections contain a number of built-in methods that prove useful when working with them. Here is some of them.
COUNT:
This method returns the number of elements in the collection.
Example:
declare
Type book is VARRAY(2) OF VARCHAR2(60);
book_tab book;
begin
book_tab:=book('The Angel','Hope on your way');
dbms_output.put_line('Number of elements in the Varray is 'book_tab.count());
end;
Output:
Number of elements in the Varray is 2
EXISTS:
Ø Returns Boolean true if element at specified index exists
Ø Returns Boolean false if element at specified index does not exists
Example:
DECLARE
TYPE numlist IS TABLE OF INTEGER;
n numlist := numlist (1, 3, 5, 7);
BEGIN
IF n.EXISTS (1)
THEN
DBMS_OUTPUT.put_line ('First element exists ');
ELSE
DBMS_OUTPUT.put_line ('First element Not exists');
END IF;
IF n.EXISTS (5)
THEN
DBMS_OUTPUT.put_line ('Fifth element exists ');
ELSE
DBMS_OUTPUT.put_line ('Fifth element Not exists');
END IF;
END;
Output:
First element exists
Fifth element Not exists
EXTEND :
Increases size of Collection by 1 or number specified
Note: Cannot use with Associative Array.
Ø EXTEND appends one null element to a collection.
Ø EXTEND(n) appends n null elements to a collection.
Ø EXTEND(n,i) appends n copies of the ith element to a collection.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
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 ('Now there are ' n.COUNT ' elements in N.');
n.EXTEND;
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');
END;
Output:
There are 4 elements in N.
Now there are 7 elements in N.
Now there are 8 elements in N.
FIRST and LAST:
Ø FIRST-Navigates to the first element in the Collection.
Ø LAST -Navigates to last element in the Collection.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' n.FIRST);
DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' n.LAST);
END;
Output:
N's first subscript is 1.
N's last subscript is 4.
PRIOR and NEXT:
Ø PRIOR -Navigates to the previous element.
Ø NEXT -Navigates to the next element.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
DBMS_OUTPUT.PUT_LINE('The element after #2 is #' n.NEXT(2));
DBMS_OUTPUT.PUT_LINE('The element before #2 is #' n.PRIOR(2));
END;
Output:
The element after #2 is 3
The element before #2 is 1
TRIM:
Removes the last element, or the last n elements if a number is specified.
Ø TRIM removes one element from the end of a collection.
Ø TRIM(n) removes n elements from the end of a collection.
Note: Cannot use with Associative Array.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,2,3,5,7,11);
BEGIN
n.TRIM(2); -- Remove last 2 elements.
DBMS_OUTPUT.PUT_LINE('There are ' n.COUNT ' elements in N.');
n.TRIM; -- Remove last element.
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
END;
Output:
There are 4 elements in N.
There are 3 elements in N.
DELETE:
Removes all elements of a Collection, or the nth element, if a parameter is specified.
Ø DELETE removes all elements from a collection.
Ø DELETE(n) removes the nth element from an associative array or nested table.
Ø DELETE(n) does nothing,if n is null,
Ø DELETE(m,n) removes all elements in the range m..n from an associative array or nested table.
Ø DELETE(m,n) does nothing,if m is larger than n or if m or n is null,
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
BEGIN
n.DELETE(2); -- deletes element 2
n.DELETE(3,6); -- deletes elements 3 through 6
n.DELETE(7,7); -- deletes element 7
n.DELETE(6,3); -- does nothing since 6 > 3
n.DELETE; -- deletes all elements
END;
PL/SQL collections contain a number of built-in methods that prove useful when working with them. Here is some of them.
COUNT:
This method returns the number of elements in the collection.
Example:
declare
Type book is VARRAY(2) OF VARCHAR2(60);
book_tab book;
begin
book_tab:=book('The Angel','Hope on your way');
dbms_output.put_line('Number of elements in the Varray is 'book_tab.count());
end;
Output:
Number of elements in the Varray is 2
EXISTS:
Ø Returns Boolean true if element at specified index exists
Ø Returns Boolean false if element at specified index does not exists
Example:
DECLARE
TYPE numlist IS TABLE OF INTEGER;
n numlist := numlist (1, 3, 5, 7);
BEGIN
IF n.EXISTS (1)
THEN
DBMS_OUTPUT.put_line ('First element exists ');
ELSE
DBMS_OUTPUT.put_line ('First element Not exists');
END IF;
IF n.EXISTS (5)
THEN
DBMS_OUTPUT.put_line ('Fifth element exists ');
ELSE
DBMS_OUTPUT.put_line ('Fifth element Not exists');
END IF;
END;
Output:
First element exists
Fifth element Not exists
EXTEND :
Increases size of Collection by 1 or number specified
Note: Cannot use with Associative Array.
Ø EXTEND appends one null element to a collection.
Ø EXTEND(n) appends n null elements to a collection.
Ø EXTEND(n,i) appends n copies of the ith element to a collection.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
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 ('Now there are ' n.COUNT ' elements in N.');
n.EXTEND;
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');
END;
Output:
There are 4 elements in N.
Now there are 7 elements in N.
Now there are 8 elements in N.
FIRST and LAST:
Ø FIRST-Navigates to the first element in the Collection.
Ø LAST -Navigates to last element in the Collection.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' n.FIRST);
DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' n.LAST);
END;
Output:
N's first subscript is 1.
N's last subscript is 4.
PRIOR and NEXT:
Ø PRIOR -Navigates to the previous element.
Ø NEXT -Navigates to the next element.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
DBMS_OUTPUT.PUT_LINE('The element after #2 is #' n.NEXT(2));
DBMS_OUTPUT.PUT_LINE('The element before #2 is #' n.PRIOR(2));
END;
Output:
The element after #2 is 3
The element before #2 is 1
TRIM:
Removes the last element, or the last n elements if a number is specified.
Ø TRIM removes one element from the end of a collection.
Ø TRIM(n) removes n elements from the end of a collection.
Note: Cannot use with Associative Array.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,2,3,5,7,11);
BEGIN
n.TRIM(2); -- Remove last 2 elements.
DBMS_OUTPUT.PUT_LINE('There are ' n.COUNT ' elements in N.');
n.TRIM; -- Remove last element.
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
END;
Output:
There are 4 elements in N.
There are 3 elements in N.
DELETE:
Removes all elements of a Collection, or the nth element, if a parameter is specified.
Ø DELETE removes all elements from a collection.
Ø DELETE(n) removes the nth element from an associative array or nested table.
Ø DELETE(n) does nothing,if n is null,
Ø DELETE(m,n) removes all elements in the range m..n from an associative array or nested table.
Ø DELETE(m,n) does nothing,if m is larger than n or if m or n is null,
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
BEGIN
n.DELETE(2); -- deletes element 2
n.DELETE(3,6); -- deletes elements 3 through 6
n.DELETE(7,7); -- deletes element 7
n.DELETE(6,3); -- does nothing since 6 > 3
n.DELETE; -- deletes all elements
END;
Nested Table
What is the Nested Table
Ans : A nested table is collection of rows, represented as a column within the table. A nested table is collection of rows, represented as a column within the table. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table.
CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE;
Size specification is required in NESTED table as varrays ?
Ans : NO
What is the TABLE command in NESTED table collection ?
Ans : To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.
Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.
Examples:
v Select the collection elements using table command as follows
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC2 Sozhinganallur Chennai
v Update one element in a collection
UPDATE TABLE(SELECT place FROM employee WHERE name ='Neal') SET
column_value = 'CDC5' WHERE column_value = 'CDC2';
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai
v Delete one element in a collection
DELETE from TABLE(SELECT place FROM employee WHERE name ='Neal')
WHERE column_value = 'CDC5'
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ Sozhinganallur Chennai
v Insert one element in a collection
INSERT INTO TABLE(SELECT place FROM employee WHERE name ='Neal') VALUES ('CDC5')
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai
Que: What is the Associative Array
Ans: The “Associative Arrays” are also known as “Index-By” tables in PL/SQL. An “Associative Array” can hold a huge amount of information in several memory locations, identified by some “index.” This “index” could simply be an integer or string, or something else.
In general, an “Associative Array” stores pairs of data (either sequentially or non- sequentially). Each pair of data would generally contain a “key” and a “value.” If stored sequentially, the “key” would be nothing but a consecutive number holding the “value.” While storing information into an “Associative Array,” the user needs to specify both “key” and “value.”
The main drawback to the Associative Array type is that, like the PL/SQL Table type before it, you are not able to store them in the database.
Syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY key_type [BINARY_INTEGER PLS_INTEGER VARCHAR2(size_limit)];
Þ type_name is a type specifier used later to declare collections
Þ element_type is any PL/SQL datatype
Þ key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG.
Þ
Please give us some example ,how to use the associative array
Ans:
v Declare and populate a associative array using the key_type BINARY_INTEGER
DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (1) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(1) is ' pno (1));
pno (100) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(100) is ' pno (100));
END;
Output:
phone_no(1) is 022 667 943 2666
phone_no(100) is 022 667 943 2666
As you can see from the above example the elements (cells) of the array are created automatically by Oracle.
v Declare and populate a associative array using the key_type VARCHAR2
DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (‘office’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(office) is ' pno (‘office’));
pno (‘home’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(home) is ' pno (‘office’));
END;
Output:
phone_no(office) is 022 667 943 2666
phone_no(home) is 022 667 943 2666
v Select records from table and store it in the associative array as follows:
The employee table contains the following data:
1. Select * from employee
ENO NAME PLACE
------------- ------------- -------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
2. Inserting the above records in another table using associative array as follows
Create table employ(eno number, name varchar2(60),place varchar2(60));
DECLARE CURSOR all_emps IS SELECT * FROM employee ORDER BY name; TYPE emp_table IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER; emps emp_table; emps_max BINARY_INTEGER;BEGIN emps_max := 0;
FOR emp IN all_emps LOOP emps_max := emps_max + 1; emps(emps_max).eno := emp. eno; emps(emps_max).name := emp. name; emps(emps_max).place := emp. place;
Insert into employ values (emps(emps_max).eno, emps(emps_max).name, emps(emps_max).place);Dbms_output.put_line(‘ENO ENAME PLACE ‘);
Dbms_output.put_line(‘--------------------------------------------------------- ‘);
Dbms_output.put_line(emps(emps_max).eno ’ ‘emps(emps_max).name ’ ‘ emps(emps_max).place);
END LOOP;
END;
Output:
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
Select * from employ
This Query returns the following
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
Thus the records in employee table is inserted into the employ table.
Ans : A nested table is collection of rows, represented as a column within the table. A nested table is collection of rows, represented as a column within the table. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table.
CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE;
Size specification is required in NESTED table as varrays ?
Ans : NO
What is the TABLE command in NESTED table collection ?
Ans : To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.
Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.
Examples:
v Select the collection elements using table command as follows
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC2 Sozhinganallur Chennai
v Update one element in a collection
UPDATE TABLE(SELECT place FROM employee WHERE name ='Neal') SET
column_value = 'CDC5' WHERE column_value = 'CDC2';
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai
v Delete one element in a collection
DELETE from TABLE(SELECT place FROM employee WHERE name ='Neal')
WHERE column_value = 'CDC5'
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ Sozhinganallur Chennai
v Insert one element in a collection
INSERT INTO TABLE(SELECT place FROM employee WHERE name ='Neal') VALUES ('CDC5')
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai
Que: What is the Associative Array
Ans: The “Associative Arrays” are also known as “Index-By” tables in PL/SQL. An “Associative Array” can hold a huge amount of information in several memory locations, identified by some “index.” This “index” could simply be an integer or string, or something else.
In general, an “Associative Array” stores pairs of data (either sequentially or non- sequentially). Each pair of data would generally contain a “key” and a “value.” If stored sequentially, the “key” would be nothing but a consecutive number holding the “value.” While storing information into an “Associative Array,” the user needs to specify both “key” and “value.”
The main drawback to the Associative Array type is that, like the PL/SQL Table type before it, you are not able to store them in the database.
Syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY key_type [BINARY_INTEGER PLS_INTEGER VARCHAR2(size_limit)];
Þ type_name is a type specifier used later to declare collections
Þ element_type is any PL/SQL datatype
Þ key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG.
Þ
Please give us some example ,how to use the associative array
Ans:
v Declare and populate a associative array using the key_type BINARY_INTEGER
DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (1) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(1) is ' pno (1));
pno (100) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(100) is ' pno (100));
END;
Output:
phone_no(1) is 022 667 943 2666
phone_no(100) is 022 667 943 2666
As you can see from the above example the elements (cells) of the array are created automatically by Oracle.
v Declare and populate a associative array using the key_type VARCHAR2
DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (‘office’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(office) is ' pno (‘office’));
pno (‘home’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(home) is ' pno (‘office’));
END;
Output:
phone_no(office) is 022 667 943 2666
phone_no(home) is 022 667 943 2666
v Select records from table and store it in the associative array as follows:
The employee table contains the following data:
1. Select * from employee
ENO NAME PLACE
------------- ------------- -------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
2. Inserting the above records in another table using associative array as follows
Create table employ(eno number, name varchar2(60),place varchar2(60));
DECLARE CURSOR all_emps IS SELECT * FROM employee ORDER BY name; TYPE emp_table IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER; emps emp_table; emps_max BINARY_INTEGER;BEGIN emps_max := 0;
FOR emp IN all_emps LOOP emps_max := emps_max + 1; emps(emps_max).eno := emp. eno; emps(emps_max).name := emp. name; emps(emps_max).place := emp. place;
Insert into employ values (emps(emps_max).eno, emps(emps_max).name, emps(emps_max).place);Dbms_output.put_line(‘ENO ENAME PLACE ‘);
Dbms_output.put_line(‘--------------------------------------------------------- ‘);
Dbms_output.put_line(emps(emps_max).eno ’ ‘emps(emps_max).name ’ ‘ emps(emps_max).place);
END LOOP;
END;
Output:
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
Select * from employ
This Query returns the following
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
Thus the records in employee table is inserted into the employ table.
Varrays
Que: What is the varrays ?
Ans: A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index. We mentioned that a Varray differs from Nested Tables and Associative Arrays in that you must supply a size during its declaration.
Example:
The following statements declare, and then populate, a Varray that will contain 4 elements of the same type as the column genre_name in table book_genre:
DECLARE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE; Fiction_genres genres;BEGIN fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR');END;
Syntax :
TYPE type_name IS {VARRAY VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
Þ type_name is a type specifier used later to declare collections.
Þ size_limit is the number of elements in the array.
Þ element_type is any PL/SQL datatype .
Que: Could we modified the declared size of varrays ?
Ans: This example usese the EXTENDS method to demonstrate that it is possible to modify a Varray's size programmatically.--Add a new genre.IF adding_new_genre THEN --Is this genre id already in the collection? IF NOT fiction_genres.EXISTS(v_genre_id) THEN --**Add** another element to the varray. fiction_genres.EXTENDS(1); fiction_genres(v_genre_id) := v_genre; END IF; --Display the total # of elements. DBMS_OUTPUT.PUT_LINE('Total # of entries in fiction_genres is : 'fiction_genres.COUNT(); END IF;......--Remove all entries.IF deleting_all_genres THEN Fiction_genres.DELETE();END IF;
Que:Advantages of varrays
Ans:
Varrays (and Nested Tables) have over Associative Arrays is their ability to be added to the database. For example, you could add the genres type, a Varray, to a DML statement on the library table.
When a table contains a Varray type, its data is included in-line, with the rest of the table's data.
Example: --Insert a new collection into the column on our book_library table.INSERT INTO book_library (library_id, name, book_genres) VALUES (book_library_seq.NEXTVAL,'Brand New Library', Genres('FICTION','NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'));
The query SELECT name, book_genres from book_library returns us:NAME BOOK_GENRES-------------------- ---------------------------------------------Brand New Library GENRES('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE')
Que: Disdvantages of varrays?
Ans:
If you require your collection to be stored in the database but would like the flexibility to manipulate elements individually, Nested Tables are a better solution.
It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray. The Varray type does not because Varray data is stored as one single, delimited piece of data within the database.
Example: We couldn’t update to “FICTION” because this is the individual elements in this column.NAME BOOK_GENRES-------------------- ---------------------------------------------Brand New Library GENRES('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE')
Note : Below are the example which show that UPDATE/DELETE/INSET is possible in NESTED table on individual ELEEMENTS.
UPDATE TABLE(SELECT place FROM employee WHERE name ='Ravi') SET
column_value = 'CDC5' WHERE column_value = 'CDC2';
DELETE from TABLE(SELECT place FROM employee WHERE name ='Ravi')
WHERE column_value = 'CDC5'
Some more details on Vaarays :
The following statements declare, populate and then update a Varray that will contain 3 elements of the same type
v Create a Varray as follows
CREATE TABLE employee
(ENO NUMBER,NAME VARCHAR2(60), PLACE VARCHAR2 (60));
CREATE TYPE details IS VARRAY (3) OF employee.place%type(60);
(or)
CREATE TYPE details IS VARRAY (3) OF VARCHAR2(60);
CREATE TABLE employee
(ENO NUMBER,NAME VARCHAR2(60), PLACE details );
v Insert records into varray as follows
INSERT INTO employee VALUES(11004797,'SabariRaj',
details('CDC3','Guindy','Chennai'));
Select * from employee.
The above query returns:
ENO NAME PLACE
------------- ------------- --------------------------
11004797 Sabari Raj details('CDC3','Guindy','Chennai').
v Update the records in varray as follows
update employee set place= details('CDC1', 'Guindy','Chennai') where eno=11004797;
Select * from employee.
The above query returns:
ENO NAME PLACE
------------- ------------- --------------------------
11004797 Sabari Raj details('CDC1','Guindy','Chennai').
v It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray.
Collections
Que: Why collections are required in pl/sql programming ?
Ans: PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.
Que: What is the collection?
Ans: A collection can be defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index.
Advantages:
· Collections can help to simplify code
· A collection can provide is improved application performance.
We can utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database.
Que: What are the types of PLSQL collections
Ans: Oracle provides three types of PL/SQL collections:
· Nested tables
· Varrays
· Associative arrays
Que: Tell us about the history on PLSQL collections?
Ans:
· With the release of Oracle 7, Oracle introduced the PL/SQL Table. By using PL/SQL Tables, it was possible to create a collection of items, all of the same type, indexed by an integer.
TYPE book_title_tab IS TABLE OF book.title%TYPE
INDEX BY BINARY_INTEGER;
· The only way to access the elements of a PL/SQL Table was through its numeric index.
· In version 8, Oracle introduced two collection types, Nested Tables and Varrays. At this time, the PL/SQL Table was renamed to 'index-by table'. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table of old did.
Ans: PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.
Que: What is the collection?
Ans: A collection can be defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index.
Advantages:
· Collections can help to simplify code
· A collection can provide is improved application performance.
We can utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database.
Que: What are the types of PLSQL collections
Ans: Oracle provides three types of PL/SQL collections:
· Nested tables
· Varrays
· Associative arrays
Que: Tell us about the history on PLSQL collections?
Ans:
· With the release of Oracle 7, Oracle introduced the PL/SQL Table. By using PL/SQL Tables, it was possible to create a collection of items, all of the same type, indexed by an integer.
TYPE book_title_tab IS TABLE OF book.title%TYPE
INDEX BY BINARY_INTEGER;
· The only way to access the elements of a PL/SQL Table was through its numeric index.
· In version 8, Oracle introduced two collection types, Nested Tables and Varrays. At this time, the PL/SQL Table was renamed to 'index-by table'. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table of old did.
Subscribe to:
Comments (Atom)
OraApps Search
Custom Search