Oracle PL/SQL Tutorial/Collections/Associative Arrays
Содержание
- 1 Assign value to PL/SQL table
- 2 Associative Arrays demo
- 3 Associative arrays (index-by tables)
- 4 Change PL/SQL table element by index
- 5 Clear the salaries table by assigning the empty version to it
- 6 FIRST and LAST Table Attributes
- 7 Index by string
- 8 Indexing Associative Arrays
- 9 Insert data in PL/SQL table to a real table
- 10 Nested Table with Table row elements inside
- 11 NEXT and PRIOR Table Attributes
- 12 Place some values into the salaries table
- 13 PL/SQL Table
- 14 PL/SQL table of cursor
- 15 Select data into PL/SQL table of cursor
- 16 TABLE.Exist
- 17 The COUNT Table Attribute
- 18 The DELETE Table Attribute
- 19 The EXISTS Table Attribute
- 20 Use for all loop to loop through the PL/SQL table
- 21 Use For loop to output data in a PL/SQL table of cursor
- 22 Uses the COUNT method to display the number of rows contained in an index-by table
- 23 Using INDEX BY BINARY_INTEGER
Assign value to PL/SQL table
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 --Define an index-by table type.
3 TYPE num_table IS TABLE OF NUMBER
4 INDEX BY BINARY_INTEGER;
5
6 nums num_table;
7 some_num NUMBER;
8 BEGIN
9 nums(10) := 11;
10
11 BEGIN
12 some_num := nums(11);
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 DBMS_OUTPUT.PUT_LINE("Element 11 does not exist.");
16 END;
17
18 END;
19 /
Element 11 does not exist.
PL/SQL procedure successfully completed.
Associative Arrays demo
The associative array behaves like an array (although it is called a TABLE or INDEX-BY TABLE).
The "associative" part of the object comes from the PL/SQL ability to use nonnumeric subscripts.
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE myProc0
2 AS
3 CURSOR ccur is SELECT first_name, city FROM employee;
4 TYPE nametab IS TABLE OF employee.first_name%type;
5 ch nametab;
6 i integer := 0;
7 imax integer;
8 BEGIN
9 FOR j IN ccur LOOP
10 i := i + 1;
11 ch(i) := j.first_name;
12 END LOOP;
13 imax := i;
14 i := 0;
15 dbms_output.put_line("number of values read: "||imax);
16 FOR k IN 1..imax LOOP
17 dbms_output.put_line("Name ... "||ch(k));
18 END LOOP;
19 END myProc0;
20 /
Procedure created.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Associative arrays (index-by tables)
An associative array is a collection of elements that use arbitrary numbers and strings for subscript values.
You cannot use an associative array in SQL.
declare
type AssocArray is table of ElementType index by binary_integer|pls_integer|VARCHAR2(size);
Change PL/SQL table element by index
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 TYPE emp_id IS TABLE OF employee.id%TYPE;
3 TYPE emp_name IS TABLE OF employee.first_name%TYPE;
4
5 emp_ids emp_id;
6 emp_names emp_name;
7 inx1 PLS_INTEGER;
8 BEGIN
9 emp_ids := emp_id();
10 emp_names := emp_name();
11
12 emp_ids.extend(10);
13 emp_names.extend(10);
14
15 FOR inx1 IN 1..10 LOOP
16 emp_ids(inx1) := inx1 + 1100;
17 emp_names(inx1) := "emp #" || TO_CHAR(inx1+1100);
18 END LOOP;
19
20 FORALL x IN emp_ids.first..emp_ids.last
21 INSERT INTO employee (id, first_name) VALUES (emp_ids(x), emp_names(x));
22 END;
23 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
1101 emp #1101
1102 emp #1102
1103 emp #1103
1104 emp #1104
1105 emp #1105
1106 emp #1106
1107 emp #1107
1108 emp #1108
1109 emp #1109
1110 emp #1110
18 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Clear the salaries table by assigning the empty version to it
SQL>
SQL> SET ECHO ON
SQL>
SQL> DECLARE
2 TYPE myTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3
4 salaries myTable;
5 salaries_empty myTable;
6 BEGIN
7
8 salaries(20) := 50550;
9 salaries(40) := 50550;
10 salaries(60) := 50550;
11
12 salaries := salaries_empty;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
FIRST and LAST Table Attributes
SQL> DECLARE
2 Type t_LastNameTable IS TABLE OF VARCHAR(20)
3 INDEX BY BINARY_INTEGER;
4 v_LastNames t_LastNameTable;
5 v_Index BINARY_INTEGER;
6 BEGIN
7 -- Insert rows in the table.
8 v_LastNames(43) := "A";
9 v_LastNames(50) := "B";
10 v_LastNames(47) := "C";
11
12 -- Assigns 43 to v_Index.
13 v_Index := v_LastNames.FIRST;
14
15 DBMS_OUTPUT.put_line(v_Index);
16
17 -- Assigns 50 to v_Index.
18 v_Index := v_LastNames.LAST;
19 DBMS_OUTPUT.put_line(v_Index);
20
21 END;
22 /
43
50
PL/SQL procedure successfully completed.
SQL>
SQL>
Index by string
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE myProc
2 AS
3 CURSOR ccur IS SELECT first_name, city FROM employee;
4 TYPE nametab IS TABLE OF employee.first_name%type
5 INDEX BY employee.city%type;
6 ch nametab;
7 i integer := 0;
8 imax integer;
9 BEGIN
10 FOR j IN ccur LOOP
11 /* i := i + 1; */
12 ch(j.first_name) := j.first_name;
13 END LOOP;
14 /* imax := i;
15 i := 0;
16 dbms_output.put_line("number of values read: "||imax); */
17 dbms_output.put_line("Name ... "||ch("Vancouver"));
18 END myProc;
19 /
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
SQL>
Indexing Associative Arrays
Generate a list of employees grouped by two parameters
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> declare
2 type list_aa is table of VARCHAR2(2000) index by VARCHAR2(256);
3 v_list_aa list_aa;
4
5 cursor c_dept is select id from employee;
6 cursor c_emp is select first_name, id,to_char(start_Date,"q") q_nr from employee;
7 v_subscript_tx VARCHAR2(256);
8 begin
9 for r_dept in c_dept loop
10 v_list_aa(r_dept.id||"|1"):="Q1 Dept#" ||r_dept.id||":";
11 v_list_aa(r_dept.id||"|2"):="Q2 Dept#" ||r_dept.id||":";
12 v_list_aa(r_dept.id||"|3"):="Q3 Dept#" ||r_dept.id||":";
13 v_list_aa(r_dept.id||"|4"):="Q4 Dept#" ||r_dept.id||":";
14 end loop;
15
16 for r_emp in c_emp loop
17 v_list_aa(r_emp.id||"|"||r_emp.q_nr):= v_list_aa(r_emp.id||"|"||r_emp.q_nr)|| " "||r_emp.first_Name;
18 end loop;
19
20 v_subscript_tx:=v_list_aa.first;
21 loop
22 DBMS_OUTPUT.put_line(v_list_aa(v_subscript_tx));
23 v_subscript_tx:=v_list_aa.next(v_subscript_tx);
24 exit when v_subscript_tx is null;
25 end loop;
26 end;
27 /
Q1 Dept#01:
Q2 Dept#01:
Q3 Dept#01: Jason
Q4 Dept#01:
Q1 Dept#02: Alison
Q2 Dept#02:
Q3 Dept#02:
Q4 Dept#02:
Q1 Dept#03:
Q2 Dept#03:
Q3 Dept#03:
Q4 Dept#03: James
Q1 Dept#04:
Q2 Dept#04:
Q3 Dept#04:
Q4 Dept#04: Celia
Q1 Dept#05: Robert
Q2 Dept#05:
Q3 Dept#05:
Q4 Dept#05:
Q1 Dept#06:
Q2 Dept#06:
Q3 Dept#06: Linda
Q4 Dept#06:
Q1 Dept#07:
Q2 Dept#07:
Q3 Dept#07:
Q4 Dept#07: David
Q1 Dept#08:
Q2 Dept#08:
Q3 Dept#08: James
Q4 Dept#08:
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Insert data in PL/SQL table to a real table
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 TYPE emp_id IS TABLE OF employee.id%TYPE;
3 TYPE emp_name IS TABLE OF employee.first_name%TYPE;
4
5 emp_ids emp_id;
6 emp_names emp_name;
7 inx1 PLS_INTEGER;
8 BEGIN
9 emp_ids := emp_id();
10 emp_names := emp_name();
11
12 emp_ids.extend(10);
13 emp_names.extend(10);
14
15 FOR inx1 IN 1..10 LOOP
16 emp_ids(inx1) := inx1 + 1100;
17 emp_names(inx1) := "emp #" || TO_CHAR(inx1+1100);
18 END LOOP;
19
20 FORALL x IN emp_ids.first..emp_ids.last
21 INSERT INTO employee (id, first_name) VALUES (emp_ids(x), emp_names(x));
22 END;
23 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
1101 emp #1101
1102 emp #1102
1103 emp #1103
1104 emp #1104
1105 emp #1105
1106 emp #1106
1107 emp #1107
1108 emp #1108
1109 emp #1109
1110 emp #1110
18 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Nested Table with Table row elements inside
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR all_emps IS
3 SELECT *
4 FROM employee
5 ORDER BY first_name;
6
7 --Define an index-by table type.
8 TYPE emp_table IS TABLE OF employee%ROWTYPE
9 INDEX BY BINARY_INTEGER;
10
11 --Declare an index-by table variable to
12 --hold the employee records that we read in.
13 emps emp_table;
14 emps_max BINARY_INTEGER;
15 BEGIN
16 emps_max := 0;
17
18 FOR emp IN all_emps LOOP
19 emps_max := emps_max + 1;
20
21 emps(emps_max).id := emp.id;
22 emps(emps_max).first_name := emp.first_name;
23 emps(emps_max).last_name := emp.last_name;
24 emps(emps_max).salary := emp.salary;
25 END LOOP;
26 END;
27 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
NEXT and PRIOR Table Attributes
SQL>
SQL> DECLARE
2 TYPE t_MajorTable IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
3 v_Majors t_MajorTable;
4 v_Index BINARY_INTEGER;
5 BEGIN
6 -- Insert values into the table.
7 v_Majors(-7) := "A";
8 v_Majors(4) := "B";
9 v_Majors(5) := "C";
10
11 -- Loop over all the rows in the table, and insert them into
12 -- temp_table.
13 v_Index := v_Majors.FIRST;
14 LOOP
15 DBMS_OUTPUT.put_line(v_Index||" " || v_Majors(v_Index));
16 EXIT WHEN v_Index = v_Majors.LAST;
17 v_Index := v_Majors.NEXT(v_Index);
18 END LOOP;
19 END;
20 /
-7 A
4 B
5 C
PL/SQL procedure successfully completed.
SQL>
Place some values into the salaries table
SQL>
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL>
SQL> DECLARE
2 TYPE myTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3
4 salaries myTable;
5 salaries_empty myTable;
6 BEGIN
7
8 salaries(20) := 50550;
9 salaries(40) := 50550;
10 salaries(60) := 50550;
11
12 salaries := salaries_empty;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL>
PL/SQL Table
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> declare
2 type dept_rty is record(id VARCHAR(30), extra_tx VARCHAR2(2000));
3 type dept_nt is table of dept_rty;
4 v_dept_nt dept_nt:=dept_nt();
5 cursor c_emp is select first_Name, id from employee;
6 begin
7 v_dept_nt.extend(3);
8 v_dept_nt(1).id :="10";
9 v_dept_nt(2).id :="20";
10 v_dept_nt(3).id :="30";
11
12 for r_emp in c_emp loop
13 for i in v_dept_nt.first..v_dept_nt.last loop
14 if v_dept_nt(i).id =r_emp.id then
15 v_dept_nt(i).extra_tx:= v_dept_nt(i).extra_tx||" "||r_emp.first_Name;
16 end if;
17 end loop;
18 end loop;
19 end;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
PL/SQL table of cursor
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR all_emps IS
3 SELECT *
4 FROM employee
5 ORDER BY first_name;
6
7 TYPE emp_table IS TABLE OF employee%ROWTYPE;
8
9 emps emp_table;
10 emps_max PLS_INTEGER;
11 inx1 PLS_INTEGER;
12 BEGIN
13 emps_max := 0;
14
15 emps := emp_table ();
16
17 FOR emp IN all_emps LOOP
18 emps_max := emps_max + 1;
19 emps.extend;
20 emps(emps_max).id := "99";
21 emps(emps_max).first_name := "new";
22 emps(emps_max).salary := 1111;
23 END LOOP;
24
25 emps.extend(5,1);
26
27 FOR inx1 IN 1..emps_max+5 LOOP
28 DBMS_OUTPUT.PUT_LINE (emps(inx1).id ||" " || emps(inx1).first_name);
29 END LOOP;
30 END;
31 /
99 new
99 new
99 new
99 new
99 new
99 new
99 new
99 new
99 new
99 new
99 new
99 new
99 new
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Select data into PL/SQL table of cursor
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR all_emps IS
3 SELECT *
4 FROM employee
5 ORDER BY first_name;
6
7 TYPE emp_table IS TABLE OF employee%ROWTYPE;
8
9 emps emp_table;
10 emps_max PLS_INTEGER;
11 inx1 PLS_INTEGER;
12 BEGIN
13 emps_max := 0;
14
15 emps := emp_table ();
16
17 FOR emp IN all_emps LOOP
18 emps_max := emps_max + 1;
19 emps.extend;
20 emps(emps_max).id := emp.id;
21 emps(emps_max).first_name := emp.first_name;
22 emps(emps_max).salary := emp.salary;
23 END LOOP;
24
25 emps.extend(5,1);
26
27 FOR inx1 IN 1..emps_max+5 LOOP
28 DBMS_OUTPUT.PUT_LINE(emps(inx1).id ||" " || emps(inx1).first_name);
29 END LOOP;
30 emps.trim(5);
31
32 emps.delete(1);
33
34 DBMS_OUTPUT.PUT_LINE(emps.count);
35
36 FOR inx1 IN 1..emps_max+5 LOOP
37 IF emps.exists(inx1) THEN
38 DBMS_OUTPUT.PUT_LINE (emps(inx1).id ||" " || emps(inx1).first_name);
39 END IF;
40 END LOOP;
41 END;
42 /
02 Alison
04 Celia
07 David
03 James
08 James
01 Jason
06 Linda
05 Robert
02 Alison
02 Alison
02 Alison
02 Alison
02 Alison
7
04 Celia
07 David
03 James
08 James
01 Jason
06 Linda
05 Robert
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
TABLE.Exist
SQL>
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 --Define an index-by table type.
3 TYPE num_table IS TABLE OF NUMBER
4 INDEX BY BINARY_INTEGER;
5
6 nums num_table;
7 some_num NUMBER;
8 BEGIN
9 nums(10) := 11;
10
11 IF nums.EXISTS(11) THEN
12 some_num := nums(11);
13 ELSE
14 DBMS_OUTPUT.PUT_LINE("Element 11 still does not exist.");
15 END IF;
16
17 END;
18 /
Element 11 still does not exist.
PL/SQL procedure successfully completed.
SQL>
The COUNT Table Attribute
SQL>
SQL> DECLARE
2 TYPE t_NumberTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 v_Numbers t_NumberTable;
4 v_Total NUMBER;
5 BEGIN
6 -- Insert 50 rows into the table.
7 FOR v_Counter IN 1..50 LOOP
8 v_Numbers(v_Counter) := v_Counter;
9 END LOOP;
10
11 v_Total := v_Numbers.COUNT;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL>
The DELETE Table Attribute
SQL> DECLARE
2 TYPE t_ValueTable IS TABLE OF VARCHAR2(10)
3 INDEX BY BINARY_INTEGER;
4 v_Values t_ValueTable;
5 BEGIN
6 -- Insert rows into the table.
7 v_Values(1) := "One";
8 v_Values(3) := "Three";
9 v_Values(-2) := "Minus Two";
10 v_Values(0) := "Zero";
11 v_Values(100) := "Hundred";
12
13 v_Values.DELETE(100); -- Removes "Hundred"
14 v_Values.DELETE(1,3); -- Removes "One" and "Three"
15 v_Values.DELETE; -- Removes all remaining values
16 END;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL>
The EXISTS Table Attribute
SQL>
SQL> DECLARE
2 Type t_FirstNameTable IS TABLE OF VARCHAR(20)
3 INDEX BY BINARY_INTEGER;
4 FirstNames t_FirstNameTable;
5 BEGIN
6 -- Insert rows into the table.
7 FirstNames(1) := "Scott";
8 FirstNames(3) := "Joanne";
9
10 -- Check to see if rows exist.
11 IF FirstNames.EXISTS(1) THEN
12 DBMS_OUTPUT.put_line("Row 1 exists!");
13 ELSE
14 DBMS_OUTPUT.put_line("Row 1 doesn""t exist!");
15 END IF;
16 IF FirstNames.EXISTS(2) THEN
17 DBMS_OUTPUT.put_line("Row 2 exists!");
18 ELSE
19 DBMS_OUTPUT.put_line("Row 2 doesn""t exist!");
20 END IF;
21 END;
22 /
Row 1 exists!
Row 2 doesn"t exist!
PL/SQL procedure successfully completed.
SQL>
SQL>
Use for all loop to loop through the PL/SQL table
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR all_emps IS
3 SELECT id, first_name
4 FROM employee
5 ORDER BY first_name;
6
7 TYPE emp_id IS TABLE OF employee.id%TYPE;
8 TYPE emp_name IS TABLE OF employee.first_name%TYPE;
9
10 emp_ids emp_id;
11 emp_names emp_name;
12 inx1 PLS_INTEGER;
13 BEGIN
14 OPEN all_emps;
15 FETCH all_emps BULK COLLECT INTO emp_ids, emp_names;
16 CLOSE all_emps;
17
18 FOR inx1 IN 1..emp_ids.count LOOP
19 emp_names(inx1) := UPPER(emp_names(inx1));
20 DBMS_OUTPUT.PUT_LINE (emp_ids(inx1) ||" " || emp_names(inx1));
21 END LOOP;
22
23 FORALL x IN emp_ids.first..emp_ids.last
24 UPDATE employee SET first_name = emp_names(x) WHERE id = emp_ids(x);
25 END;
26 /
02 ALISON
04 CELIA
07 DAVID
03 JAMES
08 JAMES
01 JASON
06 LINDA
05 ROBERT
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Use For loop to output data in a PL/SQL table of cursor
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR all_emps IS
3 SELECT *
4 FROM employee
5 ORDER BY first_name;
6
7 TYPE emp_table IS TABLE OF employee%ROWTYPE;
8
9 emps emp_table;
10 emps_max PLS_INTEGER;
11 inx1 PLS_INTEGER;
12 BEGIN
13 emps_max := 0;
14
15 emps := emp_table ();
16
17 FOR emp IN all_emps LOOP
18 emps_max := emps_max + 1;
19 emps.extend;
20 emps(emps_max).id := emp.id;
21 emps(emps_max).first_name := emp.first_name;
22 emps(emps_max).salary := emp.salary;
23 END LOOP;
24
25 emps.extend(5,1);
26
27 FOR inx1 IN 1..emps_max+5 LOOP
28 DBMS_OUTPUT.PUT_LINE(emps(inx1).id ||" " || emps(inx1).first_name);
29 END LOOP;
30 emps.trim(5);
31
32 emps.delete(1);
33
34 DBMS_OUTPUT.PUT_LINE(emps.count);
35
36 FOR inx1 IN 1..emps_max+5 LOOP
37 IF emps.exists(inx1) THEN
38 DBMS_OUTPUT.PUT_LINE (emps(inx1).id ||" " || emps(inx1).first_name);
39 END IF;
40 END LOOP;
41 END;
42 /
02 Alison
04 Celia
07 David
03 James
08 James
01 Jason
06 Linda
05 Robert
02 Alison
02 Alison
02 Alison
02 Alison
02 Alison
7
04 Celia
07 David
03 James
08 James
01 Jason
06 Linda
05 Robert
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Uses the COUNT method to display the number of rows contained in an index-by table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> -- Uses the COUNT method to display the number of rows contained in an index-by table:
SQL>
SQL> DECLARE
2 TYPE employee_table_struct IS TABLE OF employee.first_name%TYPE INDEX BY BINARY_INTEGER;
3
4 employee_table employee_table_struct;
5
6 CURSOR employee_cursor IS
7 SELECT first_name
8 FROM employee
9 ORDER BY id;
10
11 v_row NUMBER := 1;
12 BEGIN
13
14 OPEN employee_cursor;
15
16 LOOP
17 FETCH employee_cursor
18 INTO employee_table(v_row);
19
20 EXIT WHEN employee_cursor%NOTFOUND;
21
22 DBMS_OUTPUT.PUT_LINE(employee_table(v_row));
23
24 v_row := v_row + 1;
25 END LOOP;
26
27 CLOSE employee_cursor;
28
29 /* Use the table method COUNT to find out how many rows are in the index-by table employee_table */
30 DBMS_OUTPUT.PUT_LINE("Total rows: "||employee_table.COUNT);
31 END;
32 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Using INDEX BY BINARY_INTEGER
There is a way of using a FOR loop with the associative arrays INDEX BY BINARY_INTEGER
SQL>
SQL> declare
2 type dept_rty is record(id number, extra_tx VARCHAR2(2000));
3 type dept_aa is table of dept_rty index by binary_integer;
4 v_dept_aa dept_aa;
5 begin
6 v_dept_aa(10).id :=10;
7 v_dept_aa(20).id :=20;
8
9 for i in v_dept_aa.first..v_dept_aa.last loop
10 if v_dept_aa.exists(i) then
11 DBMS_OUTPUT.put_line(v_dept_aa(i).id);
12 end if;
13 end loop;
14 end;
15 /
10
20
PL/SQL procedure successfully completed.
SQL>