PLSQL Collection index by varchar [message #644547] |
Mon, 09 November 2015 10:02 |
|
wantsunny007
Messages: 10 Registered: November 2015
|
Junior Member |
|
|
Hi everyone,
i am trying to use a plsql collection with varchar2 as index , can anyone let me know how to print all the values including the repeating values for the same index
example:
list_tab('sunny') :='salary_1';
list_tab('sunny') :='salary_2';
list_tab('test') :='salary_1';
Thank you in advance, hoping for your replies .
[Updated on: Mon, 09 November 2015 10:04] Report message to a moderator
|
|
|
|
Re: PLSQL Collection index by varchar [message #644551 is a reply to message #644547] |
Mon, 09 November 2015 10:42 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Hi,
There is nothing like "repeating values" for one index, it is the same situation as one variable may hold only one value - it is overwritten by the latest assignment.
According the work with the associative array, consider consulting PL/SQL Language Reference, which is available with other Oracle documentation books e.g. online on http://docs.oracle.com/en/database/database.html
It contains the chapter about collections. Here is a small code snippet anyway.
declare
type lt_type is table of varchar2(30) index by varchar2(30);
list_tab lt_type;
i varchar2(30);
begin
list_tab('sunny') :='salary_1';
list_tab('sunny') :='salary_2';
list_tab('test') :='salary_1';
i := list_tab.first;
while i is not null loop
dbms_output.put_line( i||': '||list_tab(i) );
i := list_tab.next(i);
end loop;
end;
/
sunny: salary_2
test: salary_1
|
|
|
|
|
|
|
|
Re: PLSQL Collection index by varchar [message #644560 is a reply to message #644559] |
Mon, 09 November 2015 12:09 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You cannot with PL/SQL types, you have to use SQL types for this:
SQL> create or replace type list_tab1 IS object (name varchar2(20),address varchar2(20));
2 /
Type created.
SQL> create or replace type lt_type is table of list_tab1;
2 /
Type created.
SQL> declare
2 list_tab lt_type;
3 begin
4 list_tab := lt_type(list_tab1('sunny','test_1'),
5 list_tab1('sunny','test_2'),
6 list_tab1('sunny','test_3'));
7 FOR i IN list_tab.FIRST .. list_tab.LAST LOOP
8 dbms_output.put_line(list_tab(i).name||' '||list_tab(i).address);
9 end loop;
10 end;
11 /
sunny test_1
sunny test_2
sunny test_3
PL/SQL procedure successfully completed.
|
|
|
Re: PLSQL Collection index by varchar [message #644561 is a reply to message #644560] |
Mon, 09 November 2015 12:14 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Also, use the way I showed in my first post instead of "FOR i IN list_tab.FIRST .. list_tab.LAST LOOP", it is safer:
SQL> declare
2 list_tab lt_type;
3 begin
4 list_tab := lt_type(list_tab1('sunny','test_1'),
5 list_tab1('sunny','test_2'),
6 list_tab1('sunny','test_3'));
7 list_tab.delete(2);
8 FOR i IN list_tab.FIRST .. list_tab.LAST LOOP
9 dbms_output.put_line(list_tab(i).name||' '||list_tab(i).address);
10 end loop;
11 end;
12 /
sunny test_1
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9
SQL> declare
2 list_tab lt_type;
3 i pls_integer;
4 begin
5 list_tab := lt_type(list_tab1('sunny','test_1'),
6 list_tab1('sunny','test_2'),
7 list_tab1('sunny','test_3'));
8 list_tab.delete(2);
9 i := list_tab.first;
10 loop
11 exit when i is null;
12 dbms_output.put_line(list_tab(i).name||' '||list_tab(i).address);
13 i := list_tab.next(i);
14 end loop;
15 end;
16 /
sunny test_1
sunny test_3
PL/SQL procedure successfully completed.
|
|
|
|
|
|
|
Re: PLSQL Collection index by varchar [message #644566 is a reply to message #644565] |
Mon, 09 November 2015 13:17 |
|
wantsunny007
Messages: 10 Registered: November 2015
|
Junior Member |
|
|
Can you help me to write this code in two procedures i.e collection in a procedure and rest in another procedure, so that collection values are passed to another procedure Confused
DECLARE
type t_features
IS
TABLE OF VARCHAR(100) INDEX BY pls_integer;
type t_car_rec
IS
record
(
make VARCHAR2(50),
model VARCHAR2(50),
features t_features );
type t_car_tab
IS
TABLE OF t_car_rec INDEX BY pls_integer;
car_tab t_car_tab;
PROCEDURE show_detail
IS
car_idx pls_integer;
features_idx pls_integer;
BEGIN
car_idx := car_tab.first;
LOOP
EXIT
WHEN car_idx IS NULL;
dbms_output.put_line('Details for ' || car_tab(car_idx).make || ' ' || car_tab(car_idx).model);
features_idx := car_tab(car_idx).features.first;
LOOP
EXIT
WHEN features_idx IS NULL;
dbms_output.put_line(' =>' || car_tab(car_idx).features(features_idx));
features_idx := car_tab(car_idx).features.next(features_idx);
END LOOP;
car_idx := car_tab.next(car_idx);
END LOOP;
END;
BEGIN
-- using sequential index values
car_tab(1).make := 'Ferrari';
car_tab(1).model := 'Testarossa';
car_tab(1).features(1) := 'Fast';
car_tab(1).features(2) := 'Looks cool';
car_tab(1).features(3) := 'Expensive';
-- using random index values (sparse)
car_tab(2).make := 'Acura';
car_tab(2).model := 'TSX';
car_tab(2).features(14) := 'Small';
car_tab(2).features(200) := 'Good MPG';
car_tab(2).features(36) := 'Inexpensive';
show_detail;
END;
output :
output:
Details for Ferrari Testarossa
=>Fast
=>Looks cool
=>Expensive
Details for Acura TSX
=>Small
=>Inexpensive
=>Good MPG
[Updated on: Mon, 09 November 2015 13:19] Report message to a moderator
|
|
|
|
|
|
|
|
|
|